使用sql profile固定执行计划实验
10g之前有outlines,10g之后sql profile作为新特性之一出现。
如果针对非绑定变量的sql,outlines则力不从心。

下面是实验过程

  
  

   
   
--1.准备阶段
   
   
sql>select*fromv$version;
   
   

   
   
BANNER
   
   
----------------------------------------------------------------
   
   
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Prod
   
   
PL/sqlRelease10.2.0.1.0-Production
   
   
CORE10.2.0.1.0Production
   
   
TNSfor32-bitWindows:Version10.2.0.1.0-Production
   
   
NLSRTLVersion10.2.0.1.0-Production
   
   

   
   
sql>createtabletest_raugherasfromdba_objects;
   
   

   
   
表已创建。
   
   

   
   
sql>indexind_objectidontest_raugher(object_id);
   
   

   
   
索引已创建。
   
   

   
   
sql>selectobject_idfromtest_raugherwhererownum<2;
   
   

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

   
   
sql>execdbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true);
   
   

   
   
PL/sql过程已成功完成。
   
   

   
   
--原sql执行计划
   
   
sql>setautottraceexplain
   
   
sql>whereobject_id=20;
   
   

   
   
执行计划
   
   
----------------------------------------------------------
   
   
Planhashvalue:800879874
   
   

   
   
--------------------------------------------------------------------------------------------
   
   
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
   
   
--------------------------------------------------------------------------------------------
   
   
|0|SELECTSTATEMENT||1|95|2(0)|00:00:01|
   
   
|1|TABLEACCESSBYINDEXROWID|TEST_RAUGHER|1|95|2(0)|00:00:01|
   
   
|*2|INDEXRANGESCAN|IND_OBJECTID|1||1(0)|00:00:01|
   
   
--------------------------------------------------------------------------------------------
   
   

   
   
PredicateInformation(identifiedbyoperationid):
   
   
---------------------------------------------------
   
   

   
   
2-access("OBJECT_ID"=20)
   
   

   
   
sql>
   
   
--新sql执行计划
   
   
sql>select/*+full(test_raugher)*/*whereobject_id=20;
   
   

   
   
执行计划
   
   
----------------------------------------------------------
   
   
Planhashvalue:3725671026
   
   

   
   
----------------------------------------------------------------------------------
   
   
|Id|Operation|Time|
   
   
----------------------------------------------------------------------------------
   
   
|0|SELECTSTATEMENT||1|95|166(2)|00:00:02|
   
   
|*1|FULL|TEST_RAUGHER|1|95|166(2)|00:00:02|
   
   
----------------------------------------------------------------------------------
   
   

   
   
PredicateInformation(identified---------------------------------------------------
   
   

   
   
1-filter("OBJECT_ID"=20)
   
   

   
   
--2.获取新sql的sql_id
   
   
sql>colsql_idfora20
   
   
sql>colsql_textfora100
   
   
sql>selectsql_id,sql_textfromv$sqlwheresql_textlike'%full(test_raugher)%';
   
   

   
   
sql_IDsql_TEXT
   
   
------------------------------------------------------------------------------------------------------------------------
   
   
5nkhk378705z3like'%full(test_raugher)%'
   
   
g23hbdmcsdahcwhereobject_id=20
   
   
dqp79vx5pmw0kEXPLAINPLANSETSTATEMENT_ID='PLUS4294967295'FORfromtest_raug
   
   
herwhereobject_id=20
   
   

   
   
--3.获取新sql的outline
   
   
sql>setpagesize1000
   
   
sql>fromtable(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline'));
   
   

   
   
PLAN_TABLE_OUTPUT
   
   
-----------------------------------------------------------------------------------------------
   
   
-----------------------------------------------------------------------------------------------
   
   
sql_IDg23hbdmcsdahc,childnumber0
   
   
-------------------------------------
   
   
whereobject_id=20
   
   

   
   
Planhashvalue:3725671026
   
   

   
   
----------------------------------------------------------------------------------
   
   
|Id|Operation|----------------------------------------------------------------------------------
   
   
|0|SELECTSTATEMENT||||166(100)||
   
   
|*1|FULL|TEST_RAUGHER|1|95|166(2)|00:00:02|
   
   
----------------------------------------------------------------------------------
   
   

   
   
OutlineData
   
   
-------------
   
   

   
   
/*+
   
   
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
   
   
*/
   
   

   
   
PredicateInformation(identified---------------------------------------------------
   
   

   
   
1-filter("OBJECT_ID"=20)
   
   

   
   

   
   
已选择31行。
   
   

   
   
--4.创建sqlprofile(sqlPROFILE_001)
   
   
sql>declare
   
   
2v_hintssys.sqlprof_attr;
   
   
3begin
   
   
4v_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');
   
   
12dbms_sqltune.import_sql_profile(
   
   
13'select*fromtest_raugherwhereobject_id=20',
   
   
14v_hints,'sqlPROFILE_001',
   
   
15force_match=>true,replace=>false);
   
   
16end;
   
   
17/
   
   

   
   
PL/sql过程已成功完成。
   
   

   
   
--5.查看是否使用sqlprofile
   
   
sql>setautottraceexplain
   
   
sql>---------------------------------------------------
   
   

   
   
1-filter("OBJECT_ID"=20)
   
   

   
   
Note
   
   
-----
   
   
-sqlprofile"sqlPROFILE_001"usedforthisstatement
   
   

   
   
sql>whereobject_id=200;
   
   

   
   
执行计划
   
   
---------------------------------------------------
   
   

   
   
1-filter("OBJECT_ID"=200)
   
   

   
   
Note
   
   
forthisstatement
   
   

  
  

创建sql profile

   
   
DBMS_sqlTUNE.IMPORT_sql_PROFILE(
   
   
sql_text=>'FULLQUERYTEXT',
   
   
profile=>sqlprof_attr('HINTSPECIFICATIONWITHFULLOBJECTALIASES'),
   
   
name=>'PROFILENAME',
   
   
force_match=>TRUE/FALSE,
   
   
FALSE);
  
  

sql_text用于指定sql的全文本，可查询V$sqlAREA.sql_FULLTEXT或DBA_HIST_sqlTEXT.sql_TEXT获得。 

删除sql proflie

   
   
BEGIN
   
   
DBMS_sqlTUNE.DROP_sql_PROFILE(name=>'PROFILENAME');
   
   
END;
   
   
/ 

  
  

sql profile相关视图

SELECTname,created,category,sql_Textfromdba_sql_profilesORDERBYcreatedDESC;
   
   

   
   
SELECTsql_attr.attr_valoutline_hints
   
   
FROMdba_sql_profilessql_profiles,sys.sqlPROF$ATTRsql_attr
   
   
WHEREsql_profiles.signature=sql_attr.signature
   
   
ANDsql_profiles.name='sqlPROFILE_001'
   
   
BYsql_attr.attr#ASC;