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