|
2013-02-05 16:19:36
标签：
oracle sql profile
版权声明：原创作品，如需转载，请与作者联系。否则将追究法律责任。
使用sql profile固定执行计划实验
10g之前有outlines,10g之后sql profile作为新特性之一出现。
如果针对非绑定变量的sql,outlines则力不从心。
下面是实验过程
- 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>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>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)
- 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
- 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行。
- 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过程已成功完成。
- SQL>setautottraceexplain
- SQL>---------------------------------------------------
- 1-filter("OBJECT_ID"=20)
- Note
- -SQLprofile"SQLPROFILE_001"usedforthisstatement
- SQL>whereobject_id=200;
- 执行计划
- 1-filter("OBJECT_ID"=200)
- Note
- thisstatement
创建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;
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
