ORACLE中sql_profile的使用详细说明

为了说明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