使用多年Oracle的总结.doc

汇总：set autotrace onset autotrace traceonly explainset timing on 或通过SQL*PLUS trace，然后查看user_dump_dest下的跟踪文件，使用tkprof工具格式化后阅览。alter session set events 10046 trace name context forever,level 12;alter session set events 10046 trace name context off;SELECT p.spid,s.username FROM v$session s,v$process p WHERE s.audsid=USERENV(sessionid) AND s.paddr = p.addr; 使用方法示例：DBserver% sqlplus perf/perfSQL*Plus: Release .0 - Production on Mon Oct 17 14:32:29 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release .0 - ProductionSQL set timing onSQL set autotrace onSQL select count(*) from perf_sdcch_nn where start_time = (select max(start_time) from perf_sdcch_nn);COUNT(*)638Elapsed: 00:00:00.80Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)1 0 SORT (AGGREGATE)2 1 INDEX (RANGE SCAN) OF IDX02_PERF_SDCCH_NN (NON-UNIQUE)(Cost=2 Card=1495 Bytes=11960) 3 2 SORT (AGGREGATE)4 3 INDEX (FULL SCAN (MIN/MAX)) OF IDX02_PERF_SDCCH_NN(NON-UNIQUE) (Cost=1 Card=3852090 BytesStatistics0 recursive calls0 db block gets15 consistent gets0 physical reads0 redo size492 bytes sent via SQL*Net to client656 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL从上面的示例我们可以看到，该SQL语句执行花了0.8秒，select语句和inline view，都使用了名为IDX02_PERF_SDCCH_NN的索引物理读为0，redo log size为0，没有生成REDO日志。 一、ORACLE的启动和关闭1、在单机环境下要想启动或关闭ORACLE系统必须首先切换到ORACLE用户，如下su - oracle a、启动ORACLE系统oraclesvrmgrlSVRMGRconnect internalSVRMGRstartupSVRMGRquit b、关闭ORACLE系统oraclesvrmgrlSVRMGRconnect internalSVRMGRshutdownSVRMGRquit 启动oracle9i数据库命令：$ sqlplus /nolog SQL*Plus: Release .0 - Production on Fri Oct 31 13:53:53 2003 Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved. SQL connect / as sysdba? Connected to an idle instance.SQL