我对一个处理程序进行性能分析,使用SQL*TRACE 和 Tkprof 跟踪了这个程序的执行过程,
在生成的文件中截取了如下的片段:(还有类似的几个SQL,表现差不多)
********************************************************************************select * from base_table1
where crdtype = 0 and crdsvrsvd = 'TCADF' and
( crdsldate like '%23' or crdsldate like '%00' or
crdsldate like '%00' or crdsldate like '%00' )
and crdsldate <> '20120123'
and (crdstate = 0 or crdstate = 10)
union all
select * from base_table2
where crdtype = 0 and crdsvrsvd = 'TCADF' and
( crdsldate like '%23' or crdsldate like '%00' or
crdsldate like '%00' or crdsldate like '%00' )
and crdsldate <> '20120123'
and (crdstate = 0 or crdstate = 10)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 26022 12.32 369.68 79390 162211 0 52041
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26024 12.32 369.70 79390 162211 0 52041Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 Rows Row Source Operation
------- ---------------------------------------------------
52041 UNION-ALL
2 PARTITION HASH ALL PARTITION: 1 4
2 TABLE ACCESS BY LOCAL INDEX ROWID BASE_TABLE1 PARTITION: 1 4
46 INDEX RANGE SCAN TCARD_ACTCRDTYPE_SLPRSVD PARTITION: 1 4 (object id 458716)
52039 PARTITION HASH ALL PARTITION: 1 4
52039 TABLE ACCESS BY LOCAL INDEX ROWID BASE_TABLE2 PARTITION: 1 4
2209311 INDEX RANGE SCAN TCARD_SLPCRDTYPE_SLPRSVD PARTITION: 1 4 (object id 458721)********************************************************************************我感觉如上结果中的各字段值太大了……,是不是有问题啊,请高手指点一下!另外,base_table1和base_table2是两张结构相同的表,我在crdtype和crdsvrsvd字段建立了复合索引。
在生成的文件中截取了如下的片段:(还有类似的几个SQL,表现差不多)
********************************************************************************select * from base_table1
where crdtype = 0 and crdsvrsvd = 'TCADF' and
( crdsldate like '%23' or crdsldate like '%00' or
crdsldate like '%00' or crdsldate like '%00' )
and crdsldate <> '20120123'
and (crdstate = 0 or crdstate = 10)
union all
select * from base_table2
where crdtype = 0 and crdsvrsvd = 'TCADF' and
( crdsldate like '%23' or crdsldate like '%00' or
crdsldate like '%00' or crdsldate like '%00' )
and crdsldate <> '20120123'
and (crdstate = 0 or crdstate = 10)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 26022 12.32 369.68 79390 162211 0 52041
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26024 12.32 369.70 79390 162211 0 52041Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 Rows Row Source Operation
------- ---------------------------------------------------
52041 UNION-ALL
2 PARTITION HASH ALL PARTITION: 1 4
2 TABLE ACCESS BY LOCAL INDEX ROWID BASE_TABLE1 PARTITION: 1 4
46 INDEX RANGE SCAN TCARD_ACTCRDTYPE_SLPRSVD PARTITION: 1 4 (object id 458716)
52039 PARTITION HASH ALL PARTITION: 1 4
52039 TABLE ACCESS BY LOCAL INDEX ROWID BASE_TABLE2 PARTITION: 1 4
2209311 INDEX RANGE SCAN TCARD_SLPCRDTYPE_SLPRSVD PARTITION: 1 4 (object id 458721)********************************************************************************我感觉如上结果中的各字段值太大了……,是不是有问题啊,请高手指点一下!另外,base_table1和base_table2是两张结构相同的表,我在crdtype和crdsvrsvd字段建立了复合索引。
crdtype和crdsvrsvd字段 筛选的强度谁更大一些 就是各自筛选出来的结果总数 占总行数的比重谁大