中秋节一早起来,就接到电话,用户反馈系统有点慢哟,登陆系统,
发现一个sql语句的执行计划有点怪异,多了一个全表扫描,而且该表记录上百万,增加全表扫描,肯定慢
语句为:
SELECT A.RECIPE_ID
FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1)) B
WHERE A.RECIPE_ID = B.COLUMN_VALUE
OR A.PARENTID = B.COLUMN_VALUE;
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 142K(100)| |
| 1 | CONCATENATION | | | | | |
|* 2 | HASH JOIN | | 698M| 27G| 140K (7)| 00:12:27 |
| 3 | COLLECTION ITERATOR PICKLER FETCH | STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | CIS_OPD_CLINIC | 8547K| 326M| 132K (2)| 00:11:45 |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 408 | 17136 | 2470 (1)| 00:00:14 |
| 7 | COLLECTION ITERATOR PICKLER FETCH| STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_OUTPATIENT_CLINIC_ORDER2 | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | CIS_OPD_CLINIC | 1 | 40 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
试作修改该语句,加一个hint
SELECT /*+ rule */ A.RECIPE_ID
FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1)) B
WHERE A.RECIPE_ID = B.COLUMN_VALUE
OR A.PARENTID = B.COLUMN_VALUE;
重新执行,发现非常快
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2660 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 31393 | 1900K| 2660 (1)| 00:00:15 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP OR | | | | | |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 7 | INDEX RANGE SCAN | PK_OUTPATIENT_CLINIC_ORDER2 | | | 1 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 9 | INDEX RANGE SCAN | CIS_OPD_CLINIC_IX_PARENTID1 | | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | CIS_OPD_CLINIC | 4 | 240 | 2660 (1)| 00:00:15 |
-------------------------------------------------------------------------------------------------------------------
实际结果,应该是很少行,怎么会把全表进行扫描呢?
检查该表的统计信息,发现该表的num_rows为null,看来统计信息没有收集呀
重新收集统计信息
execute sys.dbms_stats.gather_table_stats(ownname=>'xxx',tabname=>'CIS_OPD_CLINIC',cascade=>true,estimate_percent=>100);
再去执行该语句,与优化器修改为rule一样,执行正常.
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2660 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 31393 | 1900K| 2660 (1)| 00:00:15 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP OR | | | | | |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 7 | INDEX RANGE SCAN | PK_OUTPATIENT_CLINIC_ORDER2 | | | 1 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 9 | INDEX RANGE SCAN | CIS_OPD_CLINIC_IX_PARENTID1 | | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | CIS_OPD_CLINIC | 4 | 240 | 2660 (1)| 00:00:15 |
-------------------------------------------------------------------------------------------------------------------
但前端反应现在系统依旧慢,什么情况呢,检查系统中该语句的实际执行情况:
1.找到sga中实际执行的sql语:
SQL> SELECT sql_id, child_number , sql_text
FROM v$sql
WHERE upper(sql_text) Like '%SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 )) B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
cn2c9mf5f9xda 0 SELECT sql_id, child_number , sql_text FROM v$sql WHERE upper(sql_text) Like '%
07zdmp13w1zw6 0 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6 1 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6 2 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6 3 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
gxa9q5swsq2cr 0 select sql_id, address, hash_value, executions, loads, parse_calls, invalidat
3akum7jd5b8j5 0 SELECT sql_id, child_number , sql_text FROM v$sql WHERE upper(sql_text) Like
1uwmkpzw17qb6 0 explain plan set statement_id = 'SYS' for SELECT A.RECIPE_ID FROM CIS_OPD_CLINI
2na7kxsh0msa6 0 select sql_id, address, hash_value, executions, loads, parse_calls, invalidation
9 rows selected
这里我们看到,sql_id=07zdmp13w1zw6
2.检查该语句的执行计划
select * from table(dbms_xplan.display_cursor('07zdmp13w1zw6'));
SQL_ID 07zdmp13w1zw6, child number 0
-------------------------------------
SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 ))
B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE
Plan hash value: 2166250467
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 142K(100)| |
| 1 | CONCATENATION | | | | | |
|* 2 | HASH JOIN | | 698M| 27G| 140K (7)| 00:12:27 |
| 3 | COLLECTION ITERATOR PICKLER FETCH | STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | CIS_OPD_CLINIC | 8547K| 326M| 132K (2)| 00:11:45 |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 408 | 17136 | 2470 (1)| 00:00:14 |
| 7 | COLLECTION ITERATOR PICKLER FETCH| STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_OUTPATIENT_CLINIC_ORDER2 | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | CIS_OPD_CLINIC | 1 | 40 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."PARENTID"=VALUE(KOKBF$))
8 - access("A"."RECIPE_ID"=VALUE(KOKBF$))
9 - filter(LNNVL("A"."PARENTID"=VALUE(KOKBF$)))
Note
-----
- dynamic sampling used for this statement (level=2)
看来系统中,该语句仍旧使用以前的执行计划,新的修改,对该语句没有起作用,如果让该语句使用新的执行执行计划,其实就是让以前的sql语句执行计划失效.
办法应该有很多,比如修改表结构,执行授权语句,清除共享池,但这些动作都比较大,能否只让该sql语句失效,上网上搜索,发现11g 的DBMS_SHARED_POOL包新增了PURGE功能，
可以完美的解决这个问题。
过程PURGE的第一个参数为V$SQLAREA中用逗号分隔的ADDRESS列和HASH_VALUE列的值，第二个参数’c’表示PURGE的对象是CURSOR，具体可以查看该过程的定义信息。
简单的语法为：
exec dbms_shared_pool.purge('ADDRESS,HASH_VALUE', 'C');
2.获取sql语句 addess, hash value
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where upper(sql_text) like '%SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 )) B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
cn2c9mf5f9xda 00000007C249CD38 2330260906 6 1 3 0
07zdmp13w1zw6 00000007CF3F9B68 1203830662 24 135 16 31
gxa9q5swsq2cr 00000007C95E2680 965413271 1 1 1 0
1uwmkpzw17qb6 00000007CDDFE060 4162050406 1 1 1 1
2na7kxsh0msa6 00000007CF243B00 537518406 10 1 3 0
3.清除该游标
exec dbms_shared_pool.purge('00000007CF3F9B68,1203830662', 'c');
4.再次去提取该sql的执行计划，找不到该语句了。再等一下，又可以看到了该语句了，而且执行计划已经变化。
SQL_ID 07zdmp13w1zw6, child number 0
-------------------------------------
SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 ))
B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE
Plan hash value: 1700308364
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2660 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 31393 | 1900K| 2660 (1)| 00:00:15 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2LIST | 8168 | 16336 | 17 (0)| 00:00:01 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP OR | | | | | |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 7 | INDEX RANGE SCAN | PK_OUTPATIENT_CLINIC_ORDER2 | | | 1 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 9 | INDEX RANGE SCAN | CIS_OPD_CLINIC_IX_PARENTID1 | | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | CIS_OPD_CLINIC | 4 | 240 | 2660 (1)| 00:00:15 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."RECIPE_ID"=VALUE(KOKBF$))
9 - access("A"."PARENTID"=VALUE(KOKBF$))
|
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2016-9-15 11:26:47
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对