原始SQL如下：
alter system flush buffer_cache;
alter system flush shared_pool;
set autot trace
--alter session set events '10053 trace name context forever, level 65535';
select * from hx_zs.zs_zh_pzsyqk a
where kpr_dm = '25001070088'
and exists
(select 1 from hx_zs.zs_jks b where a.dzsphm=b.dzsphm
and b.sjxhrq between to_date('20130101','yyyymmdd') and to_date('20130131','yyyymmdd'));
--alter session set events '10053 trace name context off';
set autotrace off
no rows selected
Elapsed: 00:00:00.39
Execution Plan
----------------------------------------------------------
Plan hash value: 3157425661
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1441 | 287K| 663 (1)| 00:00:08 |
| 1 | NESTED LOOPS SEMI | | 1441 | 287K| 663 (1)| 00:00:08 |
| 2 | TABLE ACCESS BY INDEX ROWID| ZS_ZH_PZSYQK | 6944 | 1254K| 662 (1)| 00:00:08 |
|* 3 |
INDEX RANGE SCAN | I_ZS_ZH_PZSYQK_KPR_DM | 6944 | | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_ZS_JKS_DZSPHM_SJXHRQ | 299 | 5681 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("KPR_DM"='25001070088')
4 - access("A"."DZSPHM"="B"."DZSPHM" AND "B"."SJXHRQ">=TO_DATE(' 2013-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "B"."SJXHRQ"<=TO_DATE(' 2013-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
656 recursive calls
0 db block gets
19445 consistent gets
4909 physical reads
0 redo size
2568 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
227 sorts (memory)
0 sorts (disk)
0 rows processed
该查询分析如下：
主查询按KPR_DM检索数据，将检索6000多条符合条件的记录，但根据子查询中SJXHRQ字段过滤记录，对于特定的月份，可能检索多条，但也可能无记录。
因此该查询将造成典型的多列值问题，通过执行计划，可以发现即使无记录检索出来，数据库也将读取大量的IO，造成性能瓶颈。
对于子查询来说，符合KPR_DM的DZSPHM在特定的SJXHRQ具有数据分布严重不均匀的情况，但是由于查询优化器无法提前预知ZS_JKS的DZSPHM，因此如果不进行动态采样，查询优化器对于记录的估算绑定变量的估算方法：为row_nums*5%*sjxhrq.sel。这点通过操作4的ROW即能得出。
最后根据表连接的CARD计算公式，将得出与实际返回记录数相距甚远的结果。
在生产系统上，通过10046 TRAC
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 1 43.33 80.11 0 14265364 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 43.33 80.11 0 14265364 0 0
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 NESTED LOOPS SEMI (cr=14265364 pr=0 pw=0 time=80110196 us cost=117458 size=11405028 card=77061)
77061 77061 77061 PARTITION RANGE ITERATOR PARTITION: 33 37 (cr=66593 pr=0 pw=0 time=746234 us cost=1817 size=9324381 card=77061)
77061 77061 77061 TABLE ACCESS BY LOCAL INDEX ROWID ZS_ZH_PZSYQK PARTITION: 33 37 (cr=66593 pr=0 pw=0 time=699738 us cost=1817 size=9324381 card=77061)
78474 78474 78474 INDEX RANGE SCAN I_ZS_ZH_PZSYQK_2 PARTITION: 33 37 (cr=324 pr=0 pw=0 time=82659 us cost=14 size=0 card=7165)(object id 619515)
0 0 0 PARTITION RANGE ITERATOR PARTITION: 33 37 (cr=14198771 pr=0 pw=0 time=79283269 us cost=2 size=16107876 card=596588)
0 0 0 PARTITION HASH ALL PARTITION: 1 19 (cr=14198771 pr=0 pw=0 time=78978450 us cost=2 size=16107876 card=596588)
0 0 0 TABLE ACCESS BY LOCAL INDEX ROWID ZS_JKS PARTITION: 200 294 (cr=14198771 pr=0 pw=0 time=73343888 us cost=2 size=16107876 card=596588)
0 0 0 INDEX RANGE SCAN I_ZS_JKS_DZSPHM_SJXHRQ PARTITION: 200 294 (cr=14198771 pr=0 pw=0 time=51256732 us cost=1 size=0 card=1)(object id 656760)
行源操作中，操作4 CR=324 TIME=82659,返回到表中检索数据则CR为66593，TIME=746234，即主查询在索引中检索记录最后再到表中筛选记录的IO成本是巨大的，差不多为200倍，操作的8-5中，无论是CR还是TIME相差都不太大。然而最后再根据子查询筛检记录将过滤掉所有的记录。
根据以上结论，理想中的执行计划应为首先扫描主查询索引，与子查询的索引做NL，最后根据得出的记录再到主查询的表中返回。
要得出以上执行计划，必须改写SQL，并为主查询表添加索引：
create index hx_zs.i_zs_zh_pzsyqk_kpr_dzsphm on hx_zs.zs_zh_pzsyqk(kpr_dm,dzsphm) nologging parallel (degree 96) tablespace idx_hx_zs;
alter index hx_zs.i_zs_zh_pzsyqk_kpr_dzsphm noparallel;
alter system flush buffer_cache;
alter system flush shared_pool;
set autot trace
with aa as (select /*+materialize1*/a.rowid rown from hx_zs.zs_zh_pzsyqk a,hx_zs.zs_jks b
where kpr_dm = '25001070088'
and a.dzsphm=b.dzsphm
and b.sjxhrq between to_date('20130301','yyyymmdd') and to_date('20130331','yyyymmdd'))
select /*+DYNAMIC_SAMPLING1(AA 1)*/c.* from hx_zs.zs_zh_pzsyqk c where rowid in (select rown from aa);
set autotrace off
no rows selected
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 152690271
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 197 | 10 (10)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 197 | 10 (10)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 1441 | 17292 | 8 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 76373 | | |
| 4 | NESTED LOOPS | | 1441 | 76373 | 8 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_ZS_ZH_PZSYQK_KPR_DZSPHM | 6944 | 230K| 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_ZS_JKS_DZSPHM_SJXHRQ | 1 | 19 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY USER ROWID| ZS_ZH_PZSYQK | 1 | 185 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("KPR_DM"='25001070088')
6 - access("A"."DZSPHM"="B"."DZSPHM" AND "B"."SJXHRQ">=TO_DATE(' 2013-03-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "B"."SJXHRQ"<=TO_DATE(' 2013-03-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
748 recursive calls
18 db block gets
6421 consistent gets
55 physical reads
0 redo size
2568 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
256 sorts (memory)
0 sorts (disk)
0 rows processed
通过改写SQL，可以发现无论是IO还是时间，已经大大缩减。
当然对于此例，无论是通过/*+dynamic_sampling*/还是对JKS做扩展列的直方图均无法解决查询性能问题。
扩展列直方图如下：
select dbms_stats.create_extended_stats(USER,'ZS_JKS','(DZSPHM,SJXHRQ)') from dual;
exec dbms_stats.gather_table_stats(USER,'ZS_JKS',method_opt=>'for columns (DZSPHM,SJXHRQ)' size auto');
无法解决性能问题原因还是在于子查询无法提前预知DZSPHM。因此只能按照5%来计算。
当然对于此例一个可能的解决方案则为将ZS_JKS按照SJXHRQ做RANG分区，相应的索引做本地分区索引，可能得出比较理想的执行计划。由于无法在生产系统做分区表，因此未做该测试。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23371754/viewspace-758151/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23371754/viewspace-758151/
扫一扫
267
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
