[20121231]给sql打补丁.txt-CSDN博客

[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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-751900/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-751900/