Oracle数据库优化的方案和实践

核心提示： 索引所包含的字段不超过4个，检查SQL语句是否使用了索引，Oracle数据库优化的方案和实践(6)，我们使用execute plan来看，获得explain的方法，我们通过SQL*PLUS工具，使用如下命令进行查看：set autotrace onset autotrace traceon

索引所包含的字段不超过4个。

检查SQL语句是否使用了索引，我们使用execute plan来看，获得explain的方法，我们通过SQL*PLUS工具，使用如下命令进行查看：

set autotrace on
set autotrace traceonly explain
set 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/perf
SQL*Plus: Release 9.2.0.6.0 - Production on Mon Oct 17 14:32:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from perf_sdcch_nn where start_time = (select max(start_time) from perf_sdcch_nn);
COUNT(*)
----------
638
Elapsed: 00:00:00.80
Execution Plan
----------------------------------------------------------
0 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 Bytes=30816720)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
492 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>