[20121231]给sql打补丁.txt
11G有一个快速的方法改写sql执行计划，加入hint来改变和稳定执行计划。在链接
http://space.itpub.net/267265/viewspace-721817
我提到11G ACS的问题，可以通过加入提示/*+ BIND_AWARE */ 来解决：
拿这个例子来说明：
1.建立测试环境:
--可以发现ID2建立的直方图是FREQUENCY直方图。
--可以看出ID2分布不均匀，如果查询id2=100,最好的执行计划是全表扫描。
--如果id2=100依旧使用索引扫描，效率肯定不好，11G的ACS就是用来解决这个问题，但是如果id2=:a (a=100)的执行次数很少，
--执行计划一直会使用索引，无法达到预期的效果，这样通过sql profile，SPM等可以加入提示来提高直接计划，我这里使用
--给sql打补丁的方式来解决这个问题.
exec sys.dbms_sqldiag_internal.i_create_patch (sql_text => 'Select * from t1 where id2= :a', hint_text => 'BIND_AWARE', name => 'patch_01yvuvyfm4fhb');
--注意:我修改select的第一个字母大写。必须以sys用户执行。
--回到原来的回话:
--即使你sql语句全部换成大写，该补丁依旧有效。但是如果写成这样（就是有comment），该补丁就不行了。
SelecT /*+ aaaa */ * from t1 where id2= :a;
11G有一个快速的方法改写sql执行计划，加入hint来改变和稳定执行计划。在链接
http://space.itpub.net/267265/viewspace-721817
我提到11G ACS的问题，可以通过加入提示/*+ BIND_AWARE */ 来解决：
拿这个例子来说明：
1.建立测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> CREATE TABLE t1 AS
SELECT ROWNUM id1,FLOOR(SQRT(ROWNUM)) id2 ,'test' NAME FROM DUAL CONNECT BY LEVEL
SQL> insert into t1 select rownum+9999,100 ,'test' NAME FROM DUAL CONNECT BY LEVEL
SQL> create index i_t1_id2 on t1(id2);
Index created.
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => user
,TabName => 'T1'
,Estimate_Percent => NULL
,Method_Opt => 'FOR COLUMNS ID2 SIZE 254 for all columns size 1'
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/
SQL> column low_value format a10
SQL> column high_value format a10
SQL> column data_type format a10
SQL> select column_name,data_type,num_distinct,low_value,high_value,num_buckets,histogram from dba_tab_cols where wner=user and table_name='T1';
COLUMN_NAME DATA_TYPE NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ------------ ---------- ---------- ----------- ---------------
ID1 NUMBER 19998 C102 C3026463 1 NONE
ID2 NUMBER 100 C102 C202 100 FREQUENCY
NAME CHAR 1 74657374 74657374 1 NONE
SQL>
--可以发现ID2建立的直方图是FREQUENCY直方图。
SQL> column ENDPOINT_ACTUAL_VALUE format a10
SQL> column COLUMN_NAME format a10
SQL> column owner format a10
SQL> column table_name format a10
SQL> select * from dba_tab_histograms where wner=user and table_name='T1' and column_name='ID2';
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SCOTT T1 ID2 3 1
SCOTT T1 ID2 8 2
SCOTT T1 ID2 15 3
SCOTT T1 ID2 24 4
SCOTT T1 ID2 35 5
SCOTT T1 ID2 48 6
SCOTT T1 ID2 63 7
SCOTT T1 ID2 80 8
SCOTT T1 ID2 99 9
SCOTT T1 ID2 120 10
....
SCOTT T1 ID2 7920 88
SCOTT T1 ID2 8099 89
SCOTT T1 ID2 8280 90
SCOTT T1 ID2 8463 91
SCOTT T1 ID2 8648 92
SCOTT T1 ID2 8835 93
SCOTT T1 ID2 9024 94
SCOTT T1 ID2 9215 95
SCOTT T1 ID2 9408 96
SCOTT T1 ID2 9603 97
SCOTT T1 ID2 9800 98
SCOTT T1 ID2 9999 99
SCOTT T1 ID2 19998 100
100 rows selected.
--可以看出ID2分布不均匀，如果查询id2=100,最好的执行计划是全表扫描。
variable a number;
exec :a := 42;
select * from t1 where id2= :a;
SQL> @dpc '' ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 01yvuvyfm4fhb, child number 0
-------------------------------------
select * from t1 where id2= :a
Plan hash value: 3984699272
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 85 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T1_ID2 | 85 | 1 (0)|
----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID2"=:A)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
--如果id2=100依旧使用索引扫描，效率肯定不好，11G的ACS就是用来解决这个问题，但是如果id2=:a (a=100)的执行次数很少，
--执行计划一直会使用索引，无法达到预期的效果，这样通过sql profile，SPM等可以加入提示来提高直接计划，我这里使用
--给sql打补丁的方式来解决这个问题.
exec sys.dbms_sqldiag_internal.i_create_patch (sql_text => 'Select * from t1 where id2= :a', hint_text => 'BIND_AWARE', name => 'patch_01yvuvyfm4fhb');
--注意:我修改select的第一个字母大写。必须以sys用户执行。
--回到原来的回话:
SQL> exec :a := 42;
PL/SQL procedure successfully completed.
SQL> select * from t1 where id2= :a;
SQL> @dpc ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 01yvuvyfm4fhb, child number 0
-------------------------------------
select * from t1 where id2= :a
Plan hash value: 3984699272
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 85 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T1_ID2 | 85 | 1 (0)|
----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID2"=:A)
Note
-----
- SQL patch "patch_01yvuvyfm4fhb" used for this statement
SQL> exec :a := 100;
PL/SQL procedure successfully completed.
SQL> @dpc ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID 01yvuvyfm4fhb, child number 1
-------------------------------------
select * from t1 where id2= :a
Plan hash value: 3617692013
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 17 (100)|
|* 1 | TABLE ACCESS FULL| T1 | 9999 | 17 (6)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID2"=:A)
Note
-----
- SQL patch "patch_01yvuvyfm4fhb" used for this statement
--即使你sql语句全部换成大写，该补丁依旧有效。但是如果写成这样（就是有comment），该补丁就不行了。
SelecT /*+ aaaa */ * from t1 where id2= :a;
