使用sql profile固定执行计划实验
10g之前有outlines,10g之后sql profile作为新特性之一出现。
如果针对非绑定变量的sql,outlines则力不从心。
下面是实验过程
--1.准备阶段
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test_raugher as select * from dba_objects;

表已创建。

SQL> create index ind_objectid on test_raugher(object_id);

索引已创建。

SQL> select object_id from test_raugher where rownum<2;

OBJECT_ID
----------
20

SQL> exec dbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true);

PL/SQL 过程已成功完成。

--原sql执行计划
SQL> set autot trace explain
SQL> select * from test_raugher where object_id=20;

执行计划
----------------------------------------------------------
Plan hash value: 800879874

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_RAUGHER | 1 | 95 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=20)

SQL>
--新sql执行计划
SQL> select /*+ full(test_raugher) */ * from test_raugher where object_id=20;

执行计划
----------------------------------------------------------
Plan hash value: 3725671026

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 166 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=20)

--2.获取新sql的sql_id
SQL> col sql_id for a20
SQL> col sql_text for a100
SQL> select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%';

SQL_ID SQL_TEXT
-------------------- ----------------------------------------------------------------------------------------------------
5nkhk378705z3 select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%'
g23hbdmcsdahc select /*+ full(test_raugher) */ * from test_raugher where object_id=20
dqp79vx5pmw0k EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(test_raugher) */ * from test_raug
her where object_id=20

--3.获取新sql的outline
SQL> set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
SQL_ID g23hbdmcsdahc, child number 0
-------------------------------------
select /*+ full(test_raugher) */ * from test_raugher where object_id=20

Plan hash value: 3725671026

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 166 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
----------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=20)

已选择31行。

--4.创建sql profile(SQLPROFILE_001)
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr(
5 'BEGIN_OUTLINE_DATA',
6 'IGNORE_OPTIM_EMBEDDED_HINTS',
7 'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
8 'ALL_ROWS',
9 'OUTLINE_LEAF(@"SEL$1")',
10 'FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")',
11 'END_OUTLINE_DATA');
12 dbms_sqltune.import_sql_profile(
13 'select * from test_raugher where object_id=20',
14 v_hints,'SQLPROFILE_001',
15 force_match=>true,replace=>false);
16 end;
17 /

PL/SQL 过程已成功完成。

--5.查看是否使用sql profile
SQL> set autot trace explain
SQL> select * from test_raugher where object_id=20;

执行计划
----------------------------------------------------------
Plan hash value: 3725671026

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 166 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=20)

Note
-----
- SQL profile "SQLPROFILE_001" used for this statement

SQL> select * from test_raugher where object_id=200;

执行计划
----------------------------------------------------------
Plan hash value: 3725671026

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 166 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=200)

Note
-----
- SQL profile "SQLPROFILE_001" used for this statement


创建sql profile
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => 'FULL QUERY TEXT',
profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'),
name => 'PROFILE NAME',
force_match => TRUE/FALSE,
replace=> TRUE/FALSE);
sql_text用于指定sql的全文本，可查询V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT获得。
删除sql proflie
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME');
END;
/
sql profile相关视图
SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC;

SELECT sql_attr.attr_val outline_hints
FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr
WHERE sql_profiles.signature = sql_attr.signature
AND sql_profiles.name = 'SQLPROFILE_001'

ORDER BY sql_attr.attr# ASC;