1、生成TOP SQL的执行计划
2、生成 TOP SQL的10046 TRACE文件
3、生成 10046 TRACE的tkprof 解析文件
4、统计信息是否过时，统计信息准确度
4.1 定位性瓶颈
4.1.1 查看统计信息是否过时
4.1.2 对比tkprof 解析文件中的执行计划与AWR生成的执行计划步骤是一致，各步骤中的ROWS是一致，如果步骤不一致
或ROWS相差很大证明统计信息不准确；对比执行计划中的TOP COST （全表扫描）与10046 TRACE中的等待事件是
否对应
4.2 优化
4.2.1 对执行计划与10046中ROWS差距很大的表收集统计信息
4.3 重新执行SQL生产新的执行计划和10046 TRACE，并对新的执行计划和10046 TRACE执行4.1.2的操作
5、优化访问路径（主要是优化全表访问）
5.1 定位性能瓶颈
5.1.1 解析最新执行计划，找到 TOP COST 的全表扫描，并对比执行计划中的TOP COST （全表扫描）与10046 TRACE中的
等待事件是否对应
5.1.2 对比 set autotrace trace 统计的IO信息与tkprof 解析文件中的IO信息是否一致或接近，并对比逻辑IO与物理IO
是否接近，如果逻辑IO与物理IO相差太远，证明SQL还有很大的优化空间
5.2 优化
5.2.1 查看全表扫描的表段大小
5.2.2 统计全表扫描表的ROWS
5.2.3 查看tkprof 解析文件中执行计划的ROWS，如果ROWS很少，却对一张大表进行了全表扫描就需要看是否可以使用索引
优化全表扫描
5.2.4 查看WHERE 子句中过滤的字段的选择性，如果选择性很高就适合创建索引
5.2.5 创建索引并收集统计信息
5.2.6 如果执行计划不稳定可以使用执行计划管理技术固定执行计划
5.3 重新执行SQL生产新的执行计划和10046 TRACE，并对新的执行计划和10046 TRACE执行5.1.1和5.1.2的操作
6. 优化表连接方式
6.1 性能瓶颈定位
6.1.1 查看执行计划，优化TOP COST 表连接
6.1.2 查看 TOP COST 表连相关表和索引段大小，表的ROWS及返回的ROWS（执行计划和10046 TRACE中的都要看）
6.1.3 通过返回的 ROWS、访问路径和IO次数对比，判断表连接方式是否合适。比如嵌套循环连接，返回
100条记录，驱动表过滤后有1000条记录，被驱动表有一千万条记录，假设对被驱动表进行全表扫描
，这个时候使用嵌套循环就不合适了，应该使用HASH连接。返回的ROWS很少，比如只有10条记录，就
适合使用嵌套循环连接。
7、优化表连接顺序（小结果集驱动，尽量减少参与表连接的结果集的数据量）
7.1 定位性能瓶颈
7.1.1 查看执行计划和tkprof 解析文件中执行计划的ROWS，确保每次表连接都是小结果集驱动。
7.2 优化
7.2.1 如果出现大结果集驱动的情况，使用提示固定表连接的顺序使用小结果集驱动
1、生成TOP SQL的执行计划
SQL> set autotrace trace;
SQL> set timing on;
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
2998 rows selected.
Elapsed: 00:00:00.54
Execution Plan
----------------------------------------------------------
Plan hash value: 98820498
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 38961 | 694 (1)| 00:00:09 |
|* 1 | HASH JOIN | | 999 | 38961 | 694 (1)| 00:00:09 |
|* 2 | HASH JOIN | | 1000 | 9000 | 350 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T2 | 1000 | 4000 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T3 | 2941 | 14705 | 344 (1)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | T1 | 2943 | 88290 | 344 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
4 - filter("T3"."OBJECT_ID"<3001)
5 - filter("T1"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
2722 consistent gets
2502 physical reads
0 redo size
72724 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
2998 rows processed
SQL> set autotrace off;
执行计划中最后的ROWS是999，统计信息中 rows processed 是2998，说明统计信息不准。
在有数据缓存的情况下再执行SQL
2998 rows selected.
Elapsed: 00:00:00.45
Execution Plan
----------------------------------------------------------
Plan hash value: 98820498
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 38961 | 694 (1)| 00:00:09 |
|* 1 | HASH JOIN | | 999 | 38961 | 694 (1)| 00:00:09 |
|* 2 | HASH JOIN | | 1000 | 9000 | 350 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T2 | 1000 | 4000 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T3 | 2941 | 14705 | 344 (1)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | T1 | 2943 | 88290 | 344 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
4 - filter("T3"."OBJECT_ID"<3001)
5 - filter("T1"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2635 consistent gets --逻辑读和物理读与10046追踪到的逻辑读和物理读次数是一致的，说明 set autorace 中的统计信息是正确的。
2485 physical reads
0 redo size
72724 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
2、生成 TOP SQL的10046 TRACE文件
alter session set tracefile_identifier='10046_0619_1';
alter session set events '10046 trace name context forever, level 12';
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
alter session set events '10046 trace name context off';
3、生成 10046 TRACE的tkprof 解析文件
tkprof orcl_ora_5968_10046_0619_1.trc 10046_0619_1.txt
4、统计信息是否过时，统计信息准确度
4.1 定位性瓶颈
4.1.1 查看统计信息是否过时，经检查统计信息没过时。
SELECT owner,
table_name,
num_rows,
sample_size,
STALE_STATS,
trunc(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner='LIXIA' AND table_name in('T3','T1','T2');
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE STA ESTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- --- ----------------
LIXIA T1 86318 86318 NO 100
LIXIA T2 1000 1000 NO 100
LIXIA T3 86327 86327 NO 100
4.1.2 对比tkprof 解析文件中的执行计划与AWR生成的执行计划步骤是一致，各步骤中的ROWS是一致，如果步骤不一致
或ROWS相差很大证明统计信息不步骤；对比执行计划中的TOP COST （全表扫描）与10046 TRACE中的等待事件是
否对应。经检查统计信息不准确。
tkprof 解析文件10046_0619_1.txt 追踪到的实际ROWS，与执行计划中的统计信息估算的ROWS相差太远，说明统计信息不准。
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.06 0.44 2485 2635 0 2998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.06 0.50 2485 2635 0 2998
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2998 HASH JOIN (cr=2635 pr=2485 pw=0 time=242416 us cost=694 size=38961 card=999)
2998 HASH JOIN (cr=1269 pr=1252 pw=0 time=36547 us cost=350 size=9000 card=1000)
1000 TABLE ACCESS FULL T2 (cr=15 pr=0 pw=0 time=567 us cost=6 size=4000 card=1000)
4998 TABLE ACCESS FULL T3 (cr=1254 pr=1252 pw=0 time=32534 us cost=344 size=14705 card=2941)
2998 TABLE ACCESS FULL T1 (cr=1366 pr=1233 pw=0 time=25298 us cost=344 size=88290 card=2943)
4.2 优化
4.2.1 对执行计划与10046中ROWS差距很大的表收集统计信息
exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
4.3 重新执行SQL生产新的执行计划和10046 TRACE，并对新的执行计划和10046 TRACE执行4.1.2的操作
收集统计信息后SQL执行计划表连接的顺序发生了改变
SQL> set autotrace trace;
SQL> set timing on;
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1184213596
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 699 (1)| 00:00:09 |
|* 1 | HASH JOIN | | 1000 | 39000 | 699 (1)| 00:00:09 |
|* 2 | HASH JOIN | | 1000 | 34000 | 350 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T2 | 1000 | 4000 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 2943 | 88290 | 344 (1)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | T3 | 4324 | 21620 | 349 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
4 - filter("T1"."OBJECT_ID"<3001)
5 - filter("T3"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2704 consistent gets
1252 physical reads
0 redo size
91045 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
按100%的采样比例收集统计信息，执行计划中T3和T1表的ROWS接近实际ROWS，但ID0和ID1的ROWS为1000,与实际不符（统计信息是准确的，
估计是CBO估算算法的问题）。
exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent => 100);
exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent => 100);
exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent => 100);
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 1184213596
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 699 (1)| 00:00:09 |
|* 1 | HASH JOIN | | 1000 | 39000 | 699 (1)| 00:00:09 |
|* 2 | HASH JOIN | | 1000 | 34000 | 350 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T2 | 1000 | 4000 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 2943 | 88290 | 344 (1)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | T3 | 4995 | 24975 | 349 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
4 - filter("T1"."OBJECT_ID"<3001)
5 - filter("T3"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2704 consistent gets
1252 physical reads
0 redo size
91045 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
5、优化访问路径（主要是优化全表访问）
5.1 定位性能瓶颈
5.1.1 解析最新执行计划，找到 TOP COST 的全表扫描，并对比执行计划中的TOP COST （全表扫描）与10046 TRACE中的
等待事件是否对应
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
2998 rows selected.
Elapsed: 00:00:00.49
Execution Plan
----------------------------------------------------------
Plan hash value: 1184213596
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 699 (1)| 00:00:09 |
|* 1 | HASH JOIN | | 1000 | 39000 | 699 (1)| 00:00:09 |
|* 2 | HASH JOIN | | 1000 | 34000 | 350 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T2 | 1000 | 4000 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 2943 | 88290 | 344 (1)| 00:00:05 |
|* 5 | TABLE ACCESS FULL | T3 | 4995 | 24975 | 349 (1)| 00:00:05 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
4 - filter("T1"."OBJECT_ID"<3001)
5 - filter("T3"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2702 consistent gets
2485 physical reads
0 redo size
91045 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
TOP CONST 是在T1和T3表的全表扫描
5.1.2 对比 set autotrace trace 统计的IO信息与tkprof 解析文件中的IO信息是否一致或接近，并对比逻辑IO与物理IO
是否接近，如果逻辑IO与物理IO相差太远，证明SQL还有很大的优化空间
SQL> set autotrace off;
SQL> alter session set tracefile_identifier='10046_621_2';
Session altered.
Elapsed: 00:00:00.04
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:00.10
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
alter session set events '10046 trace name context off';
tkprof orcl_ora_5624_10046_621_2.trc 10046_621_2.txt
********************************************************************************
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.09 0.48 2485 2702 0 2998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.10 0.54 2485 2702 0 2998
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2998 HASH JOIN (cr=2702 pr=2485 pw=0 time=313515 us cost=699 size=39000 card=1000)
1000 HASH JOIN (cr=1250 pr=1233 pw=0 time=35488 us cost=350 size=34000 card=1000)
1000 TABLE ACCESS FULL T2 (cr=15 pr=0 pw=0 time=815 us cost=6 size=4000 card=1000)
2998 TABLE ACCESS FULL T1 (cr=1235 pr=1233 pw=0 time=32558 us cost=344 size=88290 card=2943)
4998 TABLE ACCESS FULL T3 (cr=1452 pr=1252 pw=0 time=21496 us cost=349 size=24975 card=4995)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 201 0.00 0.00
direct path read 299 0.02 0.33
SQL*Net message from client 201 17.28 26.72
********************************************************************************
统计信息估算的card（rows）与10046追踪到的实际ROWS很接近，证明统计信息是正确的。
5.2 优化
5.2.1 查看全表扫描的表段大小
SQL> select bytes/1024/1024 m from dba_segments where segment_name='T1';
M
----------
10
SQL> select bytes/1024/1024 m from dba_segments where segment_name='T2' and owner='LIXIA';
M
----------
.125
SQL> select bytes/1024/1024 m from dba_segments where segment_name='T3' and owner='LIXIA';
M
----------
10
5.2.2 统计全表扫描表的ROWS
SQL> select NUM_ROWS,TABLE_NAME from dba_tables where table_name in ('T1','T2','T3') and OWNER='LIXIA';
NUM_ROWS TABLE_NAME
---------- ------------------------------
88327 T3
1000 T2
86318 T1
SELECT owner,
table_name,
num_rows,
sample_size,
STALE_STATS,
trunc(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner='LIXIA' AND table_name=upper('T3');
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE STA ESTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- --- ----------------
LIXIA T3 88327 88327 NO 100
SELECT owner,
table_name,
num_rows,
sample_size,
STALE_STATS,
trunc(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner='LIXIA' AND table_name in('T3','T1','T2');
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE STA ESTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- --- ----------------
LIXIA T1 86318 86318 NO 100
LIXIA T2 1000 1000 NO 100
LIXIA T3 88327 88327 NO 100
5.2.3 查看tkprof 解析文件中执行计划的ROWS(谓词选择性)，如果ROWS很少，却对一张大表进行了全表扫描就需要看是否可以使用索引
优化全表扫描
Rows Row Source Operation
------- ---------------------------------------------------
2998 HASH JOIN (cr=2702 pr=2485 pw=0 time=313515 us cost=699 size=39000 card=1000)
1000 HASH JOIN (cr=1250 pr=1233 pw=0 time=35488 us cost=350 size=34000 card=1000)
1000 TABLE ACCESS FULL T2 (cr=15 pr=0 pw=0 time=815 us cost=6 size=4000 card=1000)
2998 TABLE ACCESS FULL T1 (cr=1235 pr=1233 pw=0 time=32558 us cost=344 size=88290 card=2943)
4998 TABLE ACCESS FULL T3 (cr=1452 pr=1252 pw=0 time=21496 us cost=349 size=24975 card=4995)
T3表总记录数有88327条，返回的记录数是 4998，返回记录数为全表记录数的5.659%（4998/88327*100= 5.659 ）。
T1表总记录数有86318条，返回的记录数是 2998，返回记录数为全表记录数的3.473%（2998/86318*100= 3.473 ）。
T3和T1表的谓词选择性很好适合创建索引。
5.2.4 查看WHERE 子句中过滤的字段的选择性，如果选择性很高就适合创建索引
--查看字段的平均选择性
SQL> select 1/count( distinct object_id)*100 from lixia.t3;
1/COUNT(DISTINCTOBJECT_ID)*100
------------------------------
.001158373
SQL> select 1/count( distinct object_id)*100 from lixia.t1;
1/COUNT(DISTINCTOBJECT_ID)*100
------------------------------
.00115852
SQL> select 1/count( distinct object_id)*100 from lixia.t2;
1/COUNT(DISTINCTOBJECT_ID)*100
------------------------------
.1
T1/T2/T3表的OBJECT_ID选择性都是非常好的。
这里平均选择性非常好就不需要查看每个唯一值的选择性了，如果平均选择性不好比如5%或20%，就要看选择性不好
排在前十的唯一值的选择性。
select * from (
select num,count(1) v_rows,num/(select count(1) from lixia.t3)*100 xzx
from (
select count(1) num from lixia.t3 group by object_id
) group by num
) where rownum<11;
NUM V_ROWS XZX
---------- ---------- ----------
1 85331 .001132157
2 997 .002264313
1002 1 1.13442096
NUM是唯一值的记录数，V_ROWS是唯一值记录数出现的次数（比如第一行NUM为1，V_ROWS为85331表示有85331个唯一值的记录数为一），
有一个唯一值有1002条记录数，该唯一值的选择性为1.134%。T3表的OBJECT_ID字段是很适合创建索引的。
select * from (
select num,count(1) v_rows,num/(select count(1) from lixia.t2)*100 xzx
from (
select count(1) num from lixia.t2 group by object_id
) group by num
) where rownum<11;
NUM V_ROWS XZX
---------- ---------- ----------
1 1000 .1
select * from (
select num,count(1) v_rows,num/(select count(1) from lixia.t1)*100 xzx
from (
select count(1) num from lixia.t1 group by object_id
) group by num
) where rownum<11;
NUM V_ROWS XZX
---------- ---------- ----------
1 86318 .001158507
5.2.5 创建索引并收集统计信息
create index lixia.idx_t1_object_id on lixia.t1(object_id);
create index lixia.idx_t2_object_id on lixia.t2(object_id);
create index lixia.idx_t3_object_id on lixia.t3(object_id);
exec dbms_stats.gather_table_stats('LIXIA','T3',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
5.2.6 如果执行计划不稳定可以使用执行计划管理技术固定执行计划
5.3 重新执行SQL生产新的执行计划和10046 TRACE，并对新的执行计划和10046 TRACE执行5.1.1和5.1.2的操作
SQL> set autotrace trace;
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
----------------------------------------------------------
Plan hash value: 804385229
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 70 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 39000 | 70 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 1000 | 34000 | 57 (0)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 1000 | 4000 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2943 | 88290 | 54 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T1_OBJECT_ID | 2943 | | 8 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_T3_OBJECT_ID | 5098 | 25490 | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
5 - access("T1"."OBJECT_ID"<3001)
6 - access("T3"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
269 consistent gets
0 physical reads
0 redo size
72726 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
逻辑读269，物理读为零，逻辑读不到优化优化前的十分之一。
清空缓冲池再执行SQL
alter system flush buffer_cache;
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
Execution Plan
----------------------------------------------------------
Plan hash value: 804385229
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 70 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 39000 | 70 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 1000 | 34000 | 57 (0)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 1000 | 4000 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2943 | 88290 | 54 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T1_OBJECT_ID | 2943 | | 8 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_T3_OBJECT_ID | 5098 | 25490 | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
5 - access("T1"."OBJECT_ID"<3001)
6 - access("T3"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
269 consistent gets
64 physical reads
0 redo size
72725 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
逻辑读269，物理读64
清空缓冲池再执行SQL，并启用10046事件
SQL> set autotrace off;
alter system flush buffer_cache;
alter session set tracefile_identifier='10046_622_1';
alter session set events '10046 trace name context forever, level 12';
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
alter session set events '10046 trace name context off';
tkprof orcl_ora_568_10046_622_1.trc 10046_622_1.txt
********************************************************************************
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.01 0.15 64 269 0 2998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.01 0.21 64 269 0 2998
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2998 HASH JOIN (cr=269 pr=64 pw=0 time=205437 us cost=70 size=39000 card=1000)
1000 HASH JOIN (cr=57 pr=52 pw=0 time=76444 us cost=57 size=34000 card=1000)
1000 INDEX FAST FULL SCAN IDX_T2_OBJECT_ID (cr=6 pr=5 pw=0 time=35764 us cost=3 size=4000 card=1000)(object id 88067)
2998 TABLE ACCESS BY INDEX ROWID T1 (cr=51 pr=47 pw=0 time=39932 us cost=54 size=88290 card=2943)
2998 INDEX RANGE SCAN IDX_T1_OBJECT_ID (cr=8 pr=8 pw=0 time=21449 us cost=8 size=0 card=2943)(object id 88066)
4998 INDEX RANGE SCAN IDX_T3_OBJECT_ID (cr=212 pr=12 pw=0 time=19739 us cost=13 size=25490 card=5098)(object id 88068)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 201 0.00 0.00
db file sequential read 60 0.01 0.08
db file scattered read 1 0.00 0.00
SQL*Net message from client 201 1.44 5.97
********************************************************************************
从tkprof解析过的文件中我们看到时间返回的ROWS与CBO估计返回的ROWS是很接近的。实际执行计划于set autotrace 中看到的执行计划是一致的。
INDEX FAST FULL SCAN IDX_T2_OBJECT_ID引发了db file scattered read。
6. 优化表连接方式
6.1 性能瓶颈定位
6.1.1 查看执行计划，优化TOP COST 表连接
alter system flush buffer_cache;
select /*+ use_nl(t1,t2,t3)*/ t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
Execution Plan
----------------------------------------------------------
Plan hash value: 1009382155
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 3004 (1)| 00:00:37 |
| 1 | NESTED LOOPS | | 1000 | 39000 | 3004 (1)| 00:00:37 |
| 2 | NESTED LOOPS | | 1000 | 39000 | 3004 (1)| 00:00:37 |
| 3 | NESTED LOOPS | | 1000 | 9000 | 1003 (0)| 00:00:13 |
|* 4 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 1000 | 4000 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T3_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_T1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T2"."OBJECT_ID"<3001)
5 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
filter("T3"."OBJECT_ID"<3001)
6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1158 consistent gets
30 physical reads
0 redo size
73024 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
1158次逻辑读，30次物理读。 COST 为3004。COST远大于HASH 表连接。
在这个执行计划中，访问路径都是进行的索引扫描成本很大，成本是在进行嵌套循环连接中变的很大。
********************************************************************************
select /*+ use_nl(t1,t2,t3)*/ t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.11 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.14 0.28 30 1160 0 2998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.15 0.39 30 1160 0 2998
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2998 NESTED LOOPS (cr=1160 pr=30 pw=0 time=2411231 us cost=3004 size=39000 card=1000)
2998 NESTED LOOPS (cr=947 pr=17 pw=0 time=2287591 us cost=3004 size=39000 card=1000)
2998 NESTED LOOPS (cr=522 pr=13 pw=0 time=2250210 us cost=1003 size=9000 card=1000)
1000 INDEX FAST FULL SCAN IDX_T2_OBJECT_ID (cr=140 pr=5 pw=0 time=54356 us cost=3 size=4000 card=1000)(object id 88067)
2998 INDEX RANGE SCAN IDX_T3_OBJECT_ID (cr=382 pr=8 pw=0 time=61004 us cost=1 size=5 card=1)(object id 88068)
2998 INDEX RANGE SCAN IDX_T1_OBJECT_ID (cr=425 pr=4 pw=0 time=61453 us cost=1 size=0 card=1)(object id 88066)
2998 TABLE ACCESS BY INDEX ROWID T1 (cr=213 pr=13 pw=0 time=96282 us cost=2 size=30 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 201 0.00 0.00
Disk file operations I/O 1 0.01 0.01
db file sequential read 26 0.04 0.19
db file scattered read 1 0.00 0.00
SQL*Net message from client 201 4.35 14.59
********************************************************************************
tkprof解析的10046 事件中T3和T1ROWS为2998，CBO估算只有一条数据返回，两者相差太远。
查看了统计信息收集的采样比例为100%，而且统计信息也没过时，ROWS不一致估计是CBO算法
的问题。
SELECT owner,
table_name,
num_rows,
sample_size,
STALE_STATS,
trunc(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner='LIXIA' AND table_name in ('T1','T2','T3');
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE STA ESTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- --- ----------------
LIXIA T1 86318 86318 NO 100
LIXIA T2 1000 1000 NO 100
LIXIA T3 88327 88327 NO 100
6.1.2 查看 TOP COST 表连相关表和索引段大小表的ROWS
6.1.3 通过返回的 ROWS、访问路径和IO次数对比，判断表连接方式是否合适。比如嵌套循环连接，返回
100条记录，驱动表过滤后有1000条记录，被驱动表有一千万条记录，假设对被驱动表进行全表扫描
，这个时候使用嵌套循环就不合适了，应该使用HASH连接。返回的ROWS很少，比如只有10条记录，就
适合使用嵌套循环连接。
通过 tkprof的解析文件我们可以看到SQL返回的数据有2998条，使用嵌套循环连接是不合适的。而且在执行计划中
索引扫描和回表的成本都很低，表连接的成本很高（TOP COST）。
优化：去除提示让CBO选择HASH 连接。
7、优化表连接顺序（小结果集驱动，尽量减少参与表连接的结果集的数据量）
7.1 定位性能瓶颈
7.1.1 查看执行计划和tkprof 解析文件中执行计划的ROWS，确保每次表连接都是小结果集驱动。
alter system flush buffer_cache;
select /*+ leading(t3,t1,t2)*/ t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
Execution Plan
----------------------------------------------------------
Plan hash value: 1137224572
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 270K (1)| 00:54:06 |
|* 1 | HASH JOIN | | 1000 | 39000 | 270K (1)| 00:54:06 |
|* 2 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 1000 | 4000 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 15M| 500M| 270K (1)| 00:54:06 |
|* 4 | INDEX RANGE SCAN | IDX_T3_OBJECT_ID | 5098 | 25490 | 13 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 2943 | 88290 | 270K (1)| 00:54:06 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 2943 | 88290 | 53 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_T1_OBJECT_ID | 2943 | | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID" AND "T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - filter("T2"."OBJECT_ID"<3001)
4 - access("T3"."OBJECT_ID"<3001)
7 - access("T1"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
269 consistent gets
64 physical reads
0 redo size
72725 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2998 rows processed
使用leading提示后，连接方式和连接顺序都是错误的，出现了迪卡儿乘机连接，连接顺序如下:
1）第一步T3连接（迪卡儿乘积连接）T1
2）第一步的结果集连接T2表
alter system flush buffer_cache;
alter session set tracefile_identifier='10046_622_5';
alter session set events '10046 trace name context forever, level 12';
select /*+ leading(t3,t1,t2)*/ t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
alter session set events '10046 trace name context off';
select /*+ leading(t3,t1,t2)*/ t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 5.27 5.44 64 269 0 2998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 5.27 5.44 64 269 0 2998
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2998 HASH JOIN (cr=269 pr=64 pw=0 time=3321663 us cost=270485 size=39000 card=1000)
1000 INDEX FAST FULL SCAN IDX_T2_OBJECT_ID (cr=6 pr=5 pw=0 time=34602 us cost=3 size=4000 card=1000)(object id 88067)
14984004 MERGE JOIN CARTESIAN (cr=263 pr=59 pw=0 time=6804213 us cost=270439 size=525150115 card=15004289)
4998 INDEX RANGE SCAN IDX_T3_OBJECT_ID (cr=212 pr=12 pw=0 time=16938 us cost=13 size=25490 card=5098)(object id 88068)
14984004 BUFFER SORT (cr=51 pr=47 pw=0 time=3307091 us cost=270426 size=88290 card=2943)
2998 TABLE ACCESS BY INDEX ROWID T1 (cr=51 pr=47 pw=0 time=24662 us cost=53 size=88290 card=2943)
2998 INDEX RANGE SCAN IDX_T1_OBJECT_ID (cr=8 pr=8 pw=0 time=7519 us cost=7 size=0 card=2943)(object id 88066)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 201 0.00 0.00
db file sequential read 60 0.03 0.09
db file scattered read 1 0.00 0.00
SQL*Net message from client 201 0.01 1.19
7.2 优化
7.2.1 如果出现大结果集驱动的情况，使用提示固定表连接的顺序使用小结果集驱动
优化这个测试SQL的办法就是取消leading，让ORACLE 选择正确的表连接方式和正确的连接顺序
注意：错误使用提示指定表连接的顺序，生成的执行计划的连接方式和连接顺序都是错误的。
alter system flush buffer_cache;
alter session set tracefile_identifier='10046_622_4';
alter session set events '10046 trace name context forever, level 12';
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001;
alter session set events '10046 trace name context off';
select t1.object_id,t1.object_name
from lixia.t1 ,lixia.t2 ,lixia.t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t1.object_id <3001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 201 0.07 0.15 64 269 0 2998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 203 0.07 0.15 64 269 0 2998
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2998 HASH JOIN (cr=269 pr=64 pw=0 time=147255 us cost=70 size=39000 card=1000)
1000 HASH JOIN (cr=57 pr=52 pw=0 time=47223 us cost=57 size=34000 card=1000)
1000 INDEX FAST FULL SCAN IDX_T2_OBJECT_ID (cr=6 pr=5 pw=0 time=16604 us cost=3 size=4000 card=1000)(object id 88067)
2998 TABLE ACCESS BY INDEX ROWID T1 (cr=51 pr=47 pw=0 time=21771 us cost=54 size=88290 card=2943)
2998 INDEX RANGE SCAN IDX_T1_OBJECT_ID (cr=8 pr=8 pw=0 time=4735 us cost=8 size=0 card=2943)(object id 88066)
4998 INDEX RANGE SCAN IDX_T3_OBJECT_ID (cr=212 pr=12 pw=0 time=23644 us cost=13 size=25490 card=5098)(object id 88068)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 201 0.00 0.00
db file sequential read 60 0.01 0.06
db file scattered read 1 0.00 0.00
SQL*Net message from client 201 1.25 5.58
alter system flush buffer_cache;
set autotrace trace;
Execution Plan
----------------------------------------------------------
Plan hash value: 804385229
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 39000 | 70 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 39000 | 70 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 1000 | 34000 | 57 (0)| 00:00:01 |
|* 3 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 1000 | 4000 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2943 | 88290 | 54 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T1_OBJECT_ID | 2943 | | 8 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_T3_OBJECT_ID | 5098 | 25490 | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T2"."OBJECT_ID"<3001)
5 - access("T1"."OBJECT_ID"<3001)
6 - access("T3"."OBJECT_ID"<3001)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
269 consistent gets
64 physical reads
0 redo size
72725 bytes sent via SQL*Net to client
2665 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
SQL优化思路
-
lixiaxin200319
2015-06-23 10:28:17 -
Oracle
- 原创