[20190221]sql patch 問題.txt

[20190221]sql patch 問題.txt

--//連結 http://www.itpub.net/thread-2108398-1-1.html ,遇到的問題,重複測試看看.

1.環境:

SCOTT@book> @ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t01 as select * from dba_objects;

Table created.

SCOTT@book> create index ind_t01_object_id on t01(object_id);

Index created.

--//注:我的測試不能分析,分析執行計劃會選擇索引.

BEGIN

  SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(

    sql_text  => 'select * from t01 where object_id between 10000 and 30000',

    hint_text => 'INDEX(T01,IND_T01_OBJECT_ID)',

    name      => 't01_sql_patch');

END;

/

select * from t01 where object_id between 10000 and 30000;

--//可以發現依舊選擇全表掃描.

BEGIN

    DBMS_SQLDIAG.drop_sql_patch(name =>'t01_sql_patch');

END;

/

--//這樣不行.

select /*+ index(t01) */ * from t01 where object_id between 10000 and 30000;

SCOTT@book> @ dpc '' outline

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  8x42dpxgg1fgp, child number 0

-------------------------------------

select /*+ index(t01) */ * from t01 where object_id between 10000 and

30000

Plan hash value: 1880243119

--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |        |       |   463 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T01               |  26993 |  5456K|   463   (1)| 00:00:06 |

|*  2 |   INDEX RANGE SCAN          | IND_T01_OBJECT_ID |  26993 |       |    62   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 / T01@SEL$1

   2 - SEL$1 / T01@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      DB_VERSION('11.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=30000)

--//注意提示是INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID")).

--//改寫如下,以sys使用者執行.

BEGIN

  SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(

    sql_text  => 'select * from t01 where object_id between 10000 and 30000',

    hint_text => 'INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID"))',

    name      => 't01_sql_patch');

END;

/

select * from t01 where object_id between 10000 and 30000;

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  043grz1fjh9s7, child number 0

-------------------------------------

select * from t01 where object_id between 10000 and 30000

Plan hash value: 1880243119

--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |        |       |   463 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T01               |  26993 |  5456K|   463   (1)| 00:00:06 |

|*  2 |   INDEX RANGE SCAN          | IND_T01_OBJECT_ID |  26993 |       |    62   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 / T01@SEL$1

   2 - SEL$1 / T01@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=30000)

-----

   - dynamic sampling used for this statement (level=2)

   - SQL patch "t01_sql_patch" used for this statement

   - 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

33 rows selected.

--//OK,現在可以使用索引了.也就是sql patch不能使用平時的提示.

--//我個人認為最好使用sql profile，或者sql planbase...sql patch一般使用比較特殊的提示比如bind_ware,result_cache等等。