[20121231]給sql打補丁.txt

[20121231]給sql打補丁.txt

11G有一個快速的方法改寫sql執行計劃，加入hint來改變和穩定執行計劃。在連結
http://space.itpub.net/267265/viewspace-721817
我提到11G ACS的問題，可以透過加入提示/*+ BIND_AWARE */ 來解決：

拿這個例子來說明：

1.建立測試環境:

SQL> select * from v$version where rownum<=1;

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<=9999;

SQL> insert into t1 select rownum+9999,100 ,'test' NAME FROM DUAL CONNECT BY LEVEL<=9999;

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;