本实验室为了说明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