学无止境
分类： Oracle
2013-12-04 14:52:36
创建一张表，其中status字段数据存在倾斜，该字段上存在索引
SQL> create index idx_tab3_status on tab3(status);
SQL> select status,count(*) from tab3 group by status;
STATUS COUNT(*)
--------------- ----------
20 608
10 143328
对表进行分析
exec dbms_stats.gather_table_stats(ownname => 'ldy',tabname => 'tab3',cascade => true,method_opt => 'for columns size auto status');
采用常量值进行查询，可以看到当status='20'时，走了索引，当status='10'时，走全表扫描。对于SQL存在倾斜的条件字段，如果需要从不同的取值中得到收益（执行计划不同），最好不使用绑定变量，而采用常量带入。绑定变量是优化的方法，而不是优化的目的。
SQL> select * from tab3 where status='20';
608 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1608671757
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 608 | 32832 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB3 | 608 | 32832 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TAB3_STATUS | 608 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='20')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
178 consistent gets
3 physical reads
0 redo size
19209 bytes sent via SQL*Net to client
932 bytes received via SQL*Net from client
42 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
608 rows processed
SQL> select * from tab3 where status='10';
143328 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2826512543
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110K| 5816K| 125 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB3 | 110K| 5816K| 125 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='10')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
10199 consistent gets
5 physical reads
0 redo size
5163384 bytes sent via SQL*Net to client
105597 bytes received via SQL*Net from client
9557 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
143328 rows processed
使用绑定变量，首次执行先绑定变量a='10'，再次执行绑定变量a='20'，对比执行计划
var a varchar2(20);
exec :a:='10';
select * from tab3 where status=:a;
exec :a:='20';
select * from tab3 where status=:a;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 4j5q5hwnszu05, child number 0
-------------------------------------
select * from tab3 where status=:a
Plan hash value: 2826512543
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 125 (100)| |
|* 1 | TABLE ACCESS FULL| TAB3 | 110K| 5816K| 125 (3)| 00:00:02 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB3@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB3"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
1 - :A (VARCHAR2(30), CSID=871): '10' <-- 窥视的变量值
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
1 - "TAB3"."ID"[NUMBER,22], "TAB3"."NAME"[VARCHAR2,60],
"STATUS"[VARCHAR2,15]
Note
-----
- dynamic sampling used for this statement
将绑定变量变为'20',peeked binds的值并没有变化
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID 4j5q5hwnszu05, child number 0
-------------------------------------
select * from tab3 where status=:a
Plan hash value: 2826512543
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 125 (100)| |
|* 1 | TABLE ACCESS FULL| TAB3 | 110K| 5816K| 125 (3)| 00:00:02 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB3@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB3"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
1 - :A (VARCHAR2(30), CSID=871): '10' <-- 仍然是第一次窥视的变量值
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=:A)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
1 - "TAB3"."ID"[NUMBER,22], "TAB3"."NAME"[VARCHAR2,60],
"STATUS"[VARCHAR2,15]
Note
-----
- dynamic sampling used for this statement
查看目前共享池里游标的执行计划，发现为走了全表扫描
SQL> select * from table(dbms_xplan.display_cursor('4j5q5hwnszu05'));
SQL_ID 4j5q5hwnszu05, child number 0
-------------------------------------
select * from tab3 where status=:a
Plan hash value: 2826512543
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 125 (100)| |
|* 1 | TABLE ACCESS FULL| TAB3 | 110K| 5816K| 125 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=:A)
Note
-----
- dynamic sampling used for this statement
这之后，带入变量为'20',无论执行多少次，执行计划都没有变化，数据库只在硬解析时，窥视了变量值，并生成执行计划，之后的软解析不再窥视（该问题发生在11g之前，11g有了ACS的新特性，每次绑定变量都会被窥视）
如果想要重新生成执行计划，通常想到的是刷新共享池，但是在繁忙系统，并行共享池较大的数据库中，风险较大。
可以采取一些操作重新使得这一条SQL语句硬解析。
1.采用表授权的方式
revoke select on ldy.tab1 from system;
grant select on ldy.tab1 to system;
2.将统计信息导出再导入
exec dbms_stats.create_stat_table(ownname => 'ldy',stattab => 'ldy_tab_stat');
exec dbms_stats.export_table_stats(ownname =>'LDY' ,tabname => 'TAB3',stattab => 'ldy_tab_stat');
exec dbms_stats.import_table_stats(ownname => 'LDY',tabname =>'TAB3' ,stattab => 'ldy_tab_stat');
3.在10.2.0.4及以上版本，可以通过dbms_shared_pool来清除指定的游标
alter session set events '5614566 trace name context forever';
select hash_value, address, sql_id
from v$sql
where sql_id = '4j5q5hwnszu05';
HASH_VALUE ADDRESS SQL_ID
---------- ---------------- -------------
697296901 00000000711487C0 4j5q5hwnszu05
exec dbms_shared_pool.purge ('00000000711487C0,697296901','C');
