oracle 如何稳定执行计划

2.5.1automaticsqlprofile调整执行计划

适合sql无法改写或验证改写是否成功的情况

验证：

Oracel账号SYS

Conn/assysdba;

1>createtablet1(nnumber);

2>declare

begin

foriin1..10000

loop

insertintot1values(i);

commit;

endloop;

end;

/

3>selectcount(*)fromt1;

4>createindexidx_t1ont1(n);

5>execdbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',method_opt=>'forallcolumnssize1',cascade=>true);

6>select/*+no_index(t1idx_t1)*/*fromt1wheren=1;

7>select*fromtable(dbms_xplan.display_cursor(null,null,'advanced'));

8>declare

my_task_namevarchar2(30);

my_sqltextclob;

begin

my_sqltext:='select/*+no_index(t1idx_t1)*/*fromt1wheren=1';

my_task_name:=dbms_sqltune.create_tuning_task(

sql_text=>my_sqltext,

user_name=>'SYS',

scope=>'COMPREHENSIVE',

time_limit=>60,

task_name=>'my_sql_tuning_task_2',

description=>'Tasktoruneaqueryontablet1');

end;

/

9>begin

dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2');

end;

/

10>

SQL>setlong9000

SQL>setlongchunksize1000

SQL>setlinesize800

SQL>selectdbms_sqltune.report_tuning_task('my_sql_tuning_task_2')fromdual;

GENERALINFORMATIONSECTION

-------------------------------------------------------------------------------

TuningTaskName:my_sql_tuning_task_2

TuningTaskOwner:SYS

WorkloadType:SingleSQLStatement

ExecutionCount:2

CurrentExecution:EXEC_277

ExecutionType:TUNESQL

Scope:COMPREHENSIVE

TimeLimit(seconds):60

CompletionStatus:COMPLETED

Startedat:04/17/201612:09:37

Completedat:04/17/201612:09:37

-------------------------------------------------------------------------------

SchemaName:SYS

SQLID:4bh6sn1zvpgq7

SQLText:select/*+no_index(t1idx_t1)*/*fromt1wheren=1

-------------------------------------------------------------------------------

FINDINGSSECTION(1finding)

-------------------------------------------------------------------------------

1-SQLProfileFinding(seeexplainplanssectionbelow)

--------------------------------------------------------

为此语句找到了性能更好的执行计划。

Recommendation(estimatedbenefit:95%)

---------------------------------------

-考虑接受推荐的SQL概要文件。

executedbms_sqltune.accept_sql_profile(task_name=>

'my_sql_tuning_task_2',task_owner=>'SYS',replace=>TRUE);

Validationresults

------------------

已对SQLprofile进行测试,方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,

则另一计划可能只执行了一部分。

OriginalPlanWithSQLProfile%Improved

---------------------------------------

CompletionStatus:COMPLETECOMPLETE

ElapsedTime(us):5976888.6%

CPUTime(us):00

UserI/OTime(us):00

BufferGets:20195%

PhysicalReadRequests:00

PhysicalWriteRequests:00

PhysicalReadBytes:00

PhysicalWriteBytes:00

RowsProcessed:11

Fetches:11

Executions:11

Notes

-----

1.originalplan已首先执行以预热缓冲区高速缓存。

2.originalplan的统计信息是后面的9执行的平均值。

3.SQLprofileplan已首先执行以预热缓冲区高速缓存。

4.theSQLprofileplan的统计信息是后面的9执行的平均值。

-------------------------------------------------------------------------------

EXPLAINPLANSSECTION

-------------------------------------------------------------------------------

1-OriginalWithAdjustedCost

------------------------------

Planhashvalue:3617692013

--------------------------------------------------------------------------

|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|

--------------------------------------------------------------------------

|0|SELECTSTATEMENT||1|4|7(0)|00:00:01|

|*1|TABLEACCESSFULL|T1|1|4|7(0)|00:00:01|

--------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

1-filter("N"=1)

2-UsingSQLProfile

--------------------

Planhashvalue:1369807930

---------------------------------------------------------------------------

|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|

---------------------------------------------------------------------------

|0|SELECTSTATEMENT||1|4|1(0)|00:00:01|

|*1|INDEXRANGESCAN|IDX_T1|1|4|1(0)|00:00:01|

---------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

1-access("N"=1)

-------------------------------------------------------------------------------

11>executedbms_sqltune.accept_sql_profile(task_name=>'my_sql_tuning_task_2',task_owner=>'SYS',replace=>TRUE);

12>再次执行select/*+no_index(t1idx_t1)*/*fromt1wheren=1;

select*fromtable(dbms_xplan.display_cursor(null,null,'advanced'));

原来走全表现在走索引范围range扫描了，起到了变更作用，但是，一旦sql参数值或其他变化就会改变这个已调整的automaticsqlprofile

13>验证参数值发生改变，又回到全表扫描了

select/*+no_index(t1idx_t1)*/*fromt1wheren=2;

select*fromtable(dbms_xplan.display_cursor(null,null,'advanced'));

SQL_IDc4j6hxkqudj1s,childnumber0

-------------------------------------

select/*+no_index(t1idx_t1)*/*fromt1wheren=2

Planhashvalue:3617692013

--------------------------------------------------------------------------

|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|

--------------------------------------------------------------------------

|0|SELECTSTATEMENT||||7(100)||

|*1|TABLEACCESSFULL|T1|1|4|7(0)|00:00:01|

--------------------------------------------------------------------------

QueryBlockName/ObjectAlias(identifiedbyoperationid):

-------------------------------------------------------------

1-SEL$1/T1@SEL$1

OutlineData

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1""T1"@"SEL$1")

END_OUTLINE_DATA

*/

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

1-filter("N"=2)

ColumnProjectionInformation(identifiedbyoperationid):

-----------------------------------------------------------

1-"N"[NUMBER,22]

14>让automaticprofile永久生效添加force_match=true，默认force_match=false

executedbms_sqltune.accept_sql_profile(task_name=>'my_sql_tuning_task_2',task_owner=>'SYS',replace=>TRUE,force_match=>true);

注意SYS_SQLPROF_0154228b55fe000是否一样

SQL_IDfd5p89b5jz0ct,childnumber0

-------------------------------------

select/*+no_index(t1idx_t1)*/*fromt1wheren=4

Planhashvalue:1369807930

---------------------------------------------------------------------------

|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|

---------------------------------------------------------------------------

|0|SELECTSTATEMENT||||1(100)||

|*1|INDEXRANGESCAN|IDX_T1|1|4|1(0)|00:00:01|

---------------------------------------------------------------------------

QueryBlockName/ObjectAlias(identifiedbyoperationid):

-------------------------------------------------------------

1-SEL$1/T1@SEL$1

OutlineData

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1""T1"@"SEL$1"("T1"."N"))

END_OUTLINE_DATA

*/

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

1-access("N"=4)

ColumnProjectionInformation(identifiedbyoperationid):

-----------------------------------------------------------

1-"N"[NUMBER,22]

Note

-----

-SQLprofileSYS_SQLPROF_0154228b55fe0001usedforthisstatement

已选择46行。

SQL>select/*+no_index(t1idx_t1)*/*fromt1wheren=5;

N

----------

5

SQL>select*fromtable(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID6u34k01s3c4rg,childnumber0

-------------------------------------

select/*+no_index(t1idx_t1)*/*fromt1wheren=5

Planhashvalue:1369807930

---------------------------------------------------------------------------

|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|

---------------------------------------------------------------------------

|0|SELECTSTATEMENT||||1(100)||

|*1|INDEXRANGESCAN|IDX_T1|1|4|1(0)|00:00:01|

---------------------------------------------------------------------------

QueryBlockName/ObjectAlias(identifiedbyoperationid):

-------------------------------------------------------------

1-SEL$1/T1@SEL$1

OutlineData

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1""T1"@"SEL$1"("T1"."N"))

END_OUTLINE_DATA

*/

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

1-access("N"=5)

ColumnProjectionInformation(identifiedbyoperationid):

-----------------------------------------------------------

1-"N"[NUMBER,22]

Note

-----

-SQLprofileSYS_SQLPROF_0154228b55fe0001usedforthisstatement

已选择46行。