SQL Profile 實驗03

實驗說明：手動固定執行計劃
實驗描述：讓走索引的執行計劃，透過手動固定Profile走全表掃描
##刪除原來的執行計劃
exec dbms_sqltune.drop_sql_profile('coe_0gmwrzua0usax_463314188');
##正常執行計劃，走索引T3_INDEX
SQL> select count(*) from t3 where object_id<=5000;

Execution Plan
----------------------------------------------------------
Plan hash value: 129501593

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| T3_INDEX |  2245 | 29185 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<=5000)

Note
-----
   - dynamic sampling used for this statement (level=2)
##增加hint，強制走全表掃描

SQL> select /*+full(t3)*/ count(*) from t3 where object_id<=5000;

Execution Plan
----------------------------------------------------------
Plan hash value: 463314188

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   277   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |  2245 | 29185 |   277   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<=5000)

Note
-----
   - dynamic sampling used for this statement (level=2)
##檢視SQL_ID等資訊
col sql_id for a20;
col sql_text for a100;
set linesize 200;
set pagesize 20000;
conn /as sysdba;

SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%count(*)%t3%where%';

SQL_ID               PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ----------------------------------------------------------------------------------------------------                                    <=5000

0gmwrzua0usax              129501593 select count(*) from t3 where object_id<=5000
fdv618tykuv26              463314188 select /*+full(t3)*/ count(*) from t3 where object_id<=500

##手動收集B語句（走hint的SQL）outline資訊
col PLAN_TABLE_OUTPUT for a100;
SQL> select * from table(dbms_xplan.display_cursor('fdv618tykuv26',null,'outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  fdv618tykuv26, child number 0
-------------------------------------
select /*+full(t3)*/ count(*) from t3 where object_id<=5000

Plan hash value: 463314188

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   277 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |  2245 | 29185 |   277   (1)| 00:00:04 |
---------------------------------------------------------------------------

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")
      FULL(@"SEL$1" "T3"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<=5000)

Note
-----
   - dynamic sampling used for this statement (level=2)
37 rows selected.

##建立Profile並匯入
declare
     h sys.sqlprof_attr;
     sql_txt clob;
     begin
    h := SYS.SQLPROF_ATTR(
    q'[BEGIN_OUTLINE_DATA]',
    q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
    q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',        ##此處需要注意單引號，可以透過q'[]'或者兩個單引號轉義''11.2.0.4''（此處為四個單引號）
    q'[DB_VERSION('11.2.0.4')]',                                    ##此處需要注意單引號，可以透過q'[]'或者兩個單引號轉義''11.2.0.4''（此處為四個單引號）
    q'[ALL_ROWS]',
    q'[OUTLINE_LEAF(@"SEL$1")]',
    q'[FULL(@"SEL$1" "T3"@"SEL$1")]',
    q'[END_OUTLINE_DATA]');
     select sql_fulltext into sql_txt from v$sql where sql_id='0gmwrzua0usax';
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
      sql_text => sql_txt,
      profile => h,
      name => 'SQLPROF_Manual',
      description => 'Manual SQLProfile',
            category    => 'DEFAULT',
      validate    => TRUE,
      replace => TRUE,
      force_match => TRUE);
     end;
 /

##重新檢視執行計劃
SQL> select count(*) from t3 where object_id<=5000;

Execution Plan
----------------------------------------------------------
Plan hash value: 463314188

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   277   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |  3606 | 18030 |   277   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"<=5000)

Note
-----
   - SQL profile "SQLPROF_Manual" used for this statement


##附錄資訊
##重新收集統計資訊
SQL> exec dbms_stats.gather_table_stats('scott','T3',cascade=>true);

##dbms_sqltune.accept_sql_profile 中force_match的作用 ，force_match的預設值為false，表示只有在sql文字完全一致的情況下才會應用sql_profile，這種情況下只要目標sql的sql文字發生一點改動，原來的profile將失去作用，即變數值更改影響SQL執行計劃  。force_match的改為true，就相當於目標sql的where條件中的具體的輸入值用繫結變數替換了，即變數值更改不影響SQL執行計劃 。
##oracle本身預設的是單引號，但是在大家寫儲存過程或者寫SQL語句時，有時候需要拼SQL或者是SQL的值裡需要傳入含單引號的值，此時就需要使用兩個單引號"''"來進行轉義，其實oracle本身提供了這種轉換預設單引號為其他標識的方法那就是——"q"  語法為q [Oracle's quote operator]


declare
     h sys.sqlprof_attr;
     sql_txt clob;
     begin
    h := SYS.SQLPROF_ATTR(
    '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")',
    'FULL(@"SEL$1" "T3"@"SEL$1")',
    'END_OUTLINE_DATA');
     select sql_fulltext into sql_txt from v$sql where sql_id='0gmwrzua0usax';
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
      sql_text => sql_txt,
      profile => h,
      name => 'SQLPROF_Manual',
      description => 'Manual SQLProfile',
            category    => 'DEFAULT',
      validate    => TRUE,
      replace => TRUE,
      force_match => TRUE);
     end;
 /