【转载】sql_profile的使用

原文地址：http://www.xifenfei.com/3044.html

本实验室为了说明sql profile的使用方法,不去研讨sql的执行效率.通过sql profile的方法使得一条本该使用index的sql该走全表扫描.
创建模拟表

SQL> select * from v$version;

 

BANNER

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

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

SQL> create table t_xifenfei as select * from dba_objects;

 

表已创建。

 

SQL> create index i_xifenfei on t_xifenfei(object_id);

 

索引已创建。

 

SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);

 

PL/SQL 过程已成功完成。

默认使用INDEX

SQL> SET AUTOT TRACE EXP

SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;

 

执行计划

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

Plan hash value: 1926396081

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT            |            |     1 |    30 |     2   (0)|00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)|00:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)|00:00:01 |

 

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

 

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=100)

使用hint实现全表扫描

SQL> SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=10

0;

 

执行计划

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

Plan hash value: 548923532

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT  |            |     1 |    30 |   300   (1)| 00:00:04 |

 

|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |

 

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

 

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=100)

查找hint对应sql的sql_id

SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%';

 

SQL_ID

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

SQL_TEXT

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

 

0bbt69m5yhf3p

SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100

 

68r1cnxmn8fjk

SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'

 

 

ddmhrzhatfdyh

EXPLAIN PLAN SET STATEMENT_ID='PLUS570193' FOR SELECT /*+ FULL(T_XIFENFEI)*/OBJE

 

CT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100

 

SQL_ID

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

SQL_TEXT

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

 

 

bybs0sds8yu9c

SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'

获得对应Outline

SQL> SET PAGESIZE 10000

SQL> select * from table(dbms_xplan.display_cursor('0bbt69m5yhf3p',null,'outline'));

 

PLAN_TABLE_OUTPUT

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

 

SQL_ID  0bbt69m5yhf3p, child number 0

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

SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100

 

Plan hash value: 548923532

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT  |            |       |       |   300 (100)|          |

 

|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |

 

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

 

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      DB_VERSION('11.2.0.3')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=100)

 

 

已选择33行。

创建sql profile

declare

 v_hints sys.sqlprof_attr;

 begin

 v_hints:=sys.sqlprof_attr(

      'BEGIN_OUTLINE_DATA',

      'IGNORE_OPTIM_EMBEDDED_HINTS',

      'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',

      'DB_VERSION(''11.2.0.3'')',

      'ALL_ROWS',

      'OUTLINE_LEAF(@"SEL$1")',

      'FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")',   --这个是由于hint产生,其实我们需要的就是这个

      'END_OUTLINE_DATA');

dbms_sqltune.import_sql_profile(

'SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100',

v_hints,'SQLPROFILE_XIFENFEI',                 --sql profile 名称

force_match=>true,replace=>true);

end;

/

验证sql profile

SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;

 

执行计划

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

Plan hash value: 548923532

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT  |            |     1 |    30 |   300   (1)| 00:00:04 |

 

|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |

 

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

 

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=100)

 

Note

-----

   - SQL profile "SQLPROFILE_XIFENFEI" used for this statement