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

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

转载于:http://blog.itpub.net/27067062/viewspace-2075677/