Oracle固定执行计划之SQL PROFILE概要文件详解
1. 引子
Oracle系统为了合理分配和使用系统的资源提出了概要文件的概念。所谓概要文件，就是一份描述如何使用系统的资源(主要是CPU资源)的配置文件。将概要文件赋予某个数据库用户，在用户连接并访问数据库服务器时，系统就按照概要文件给他分配资源。
包括:
1、管理数据库系统资源。
利用Profile来分配资源限额，必须把初始化参数resource_limit设置为true默认是TRUE的。
2、管理数据库口令及验证方式。
默认给用户分配的是DEFAULT概要文件，将该文件赋予了每个创建的用户。但该文件对资源没有任何限制，因此管理员常常需要根据自己数据库系统的环境自行建立概要文件。
2. 概要文件限制
概要文件主要可以对数据库系统如下指标进行限制。
1）用户的最大并发会话数（SESSION_PER_USER)
2）每个会话的CPU时钟限制（CPU_PER_SESSION）
3）每次调用的CPU时钟限制，调用包含解析、执行命令和获取数据等等。（CPU_PER_CALL）
4）最长连接时间。一个会话的连接时间超过指定时间之后，Oracle会自动的断开连接（CONNECT_TIME）
5）最长空闲时间。如果一个会话处于空闲状态超过指定时间，Oracle会自动断开连接（IDLE_TIME）
6）每个会话可以读取的最大数据块数量（LOGICAL_READS_PER_SESSION）
7）每次调用可以读取的最大数据块数量（LOGICAL_READS_PER_CALL）
8）SGA私有区域的最大容量（PRIVATE_SGA）
概要文件对口令的定义和限制如下：
1）登录失败的最大尝试次数（FAILED_LOGIN_ATTEMPTS）
2）口令的最长有效期(PASSWORD_LIFE_TIME)
3）口令在可以重用之前必须修改的次数(PASSWORD_REUSE_MAX)
4）口令在可以重用之前必须经过的天数(PASSWORD_REUSE_TIME)
5）超过登录失败的最大允许尝试次数后，账户被锁定的天数
6）指定用于判断口令复杂度的函数名
在指定概要文件之后，DBA可以手工的将概要文件赋予每个用户。但是概要文件不是立即生效，而是要将初始化参数文件中的参数RESOURCE_LIMIT设置为TRUE之后，概要文件才会生效。
3. SQLPROFILE
SQLPROFILE在ORACLE10g中引入，主要目的侧重于SQL优化，弥补了存储概要的缺点.
DBA可以使用SQL调整顾问(STA)或SQL访问顾问(SAA)来识别可以得到更好性能的SQL语句，
这些语句可以保存在SQL调整集、一个AWR快照或保存在当前的库缓存中，一旦识别出调整候选者，这些顾问程序就开始分析捕获到的语句以期获得更好的性能，然后生成专用的语句扩展(就叫做SQL配置文件)并重写SQL语句以在执行期间获取更佳的性能。
与存储概要类似，一个SQL配置文件提供了使用更好的执行计划的能力(如果这个执行计
划是可用的)，SQL配置文件也可以象存储概要一样分阶段执行，或限制到对特定会话才能执行该SQL配置文件，但是大多数重要的改进超过了存储概要.
SQLProfile对于一下类型语句有效：
SELECT语句；
UPDATE语句；
INSERT语句（仅当使用SELECT子句时有效）；
DELETE语句；
CREATE语句（仅当使用SELECT子句时有效）；
MERGE语句（仅当作UPDATE和INSERT操作时有效）。
另外，使用SQLProfile还必须有CREATEANYSQLPROFILE、DROPANYSQLPROFILE和ALTERANYSQLPROFILE等系统权限。
4. 测试一
创建表
tpcc@TOADDB>createtablet1asselectobject_id,object_namefromdba_objectswhererownum<=50000; Tablecreated. tpcc@TOADDB>createtablet2asselect*fromdba_objects; Tablecreated.
创建索引：
tpcc@TOADDB>createindext2_idxont2(object_id); Indexcreated.
收集统计信息：
tpcc@TOADDB>execdbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'forallcolumnssize1'); PL/SQLproceduresuccessfullycompleted. tpcc@TOADDB>execdbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'forallcolumnssize1'); PL/SQLproceduresuccessfullycompleted.
执行无HINT的SQL
tpcc@TOADDB>setautotraceon
tpcc@TOADDB>selectt1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'andt1.object_id=t2.object_id;
42rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:1838229974
---------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
|0|SELECTSTATEMENT||2500|97K|498(1)|00:00:01|
|*1|HASHJOIN||2500|97K|498(1)|00:00:01|
|*2|TABLEACCESSFULL|T1|2500|72500|68(0)|00:00:01|
|3|TABLEACCESSFULL|T2|92021|988K|430(1)|00:00:01|
---------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2-filter("T1"."OBJECT_NAME"LIKE'%T1%'AND"T1"."OBJECT_NAME"IS
NOTNULL)
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
1789consistentgets
0physicalreads
0redosize
2350bytessentviaSQL*Nettoclient
573bytesreceivedviaSQL*Netfromclient
4SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
42rowsprocessed
执行带Hint的SQL
SQL>select/*+use_nl(t1t2)index(t2)*/t1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'andt1.object_id=t2.object_id;
42rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:1022743391
---------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||2500|97K|5069(1)|00:00:01|
|1|NESTEDLOOPS||2500|97K|5069(1)|00:00:01|
|2|NESTEDLOOPS||2500|97K|5069(1)|00:00:01|
|*3|TABLEACCESSFULL|T1|2500|72500|68(0)|00:00:01|
|*4|INDEXRANGESCAN|T2_IDX|1||1(0)|00:00:01|
|5|TABLEACCESSBYINDEXROWID|T2|1|11|2(0)|00:00:01|
---------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
3-filter("T1"."OBJECT_NAME"LIKE'%T1%'AND"T1"."OBJECT_NAME"ISNOT
NULL)
4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
304consistentgets
24physicalreads
0redosize
2350bytessentviaSQL*Nettoclient
573bytesreceivedviaSQL*Netfromclient
4SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
42rowsprocessed
使用SQLPROFILE
查找执行SQL的SQL_ID
tpcc@TOADDB>selectsql_id,sql_textfromv$sqlwheresql_textlike'%t1.object_name%'; SQL_ID ------------- SQL_TEXT ---------------------------------------------------------------------------------------------------- 4zbqykx89yc8v selectt1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'andt1.object_id=t2.object_id 18bphz37dajq9 select/*+use_nl(t1t2)index(t2)*/t1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'and t1.object_id=t2.object_id
运行存储过程如下：
vartuning_taskvarchar2(100); DECLARE l_sql_idv$session.prev_sql_id%TYPE; l_tuning_taskVARCHAR2(30); BEGIN l_sql_id:='4zbqykx89yc8v'; l_tuning_task:=dbms_sqltune.create_tuning_task(sql_id=>l_sql_id); :tuning_task:=l_tuning_task; dbms_sqltune.execute_tuning_task(l_tuning_task); dbms_output.put_line(l_tuning_task); END; / TASK_114 PL/SQLproceduresuccessfullycompleted.
查看task的名字
tpcc@TOADDB>printtuning_task; TUNING_TASK ---------------------------------------------------------------------------------------------------- TASK_114
查看执行报告
setlong99999
colcommentsformata200
SELECTdbms_sqltune.report_tuning_task(:tuning_task)COMMENTSFROMdual;
COMMENTS
----------------------------------------------------------------------------------------------------
GENERALINFORMATIONSECTION
-------------------------------------------------------------------------------
TuningTaskName:TASK_114
TuningTaskOwner:TPCC
WorkloadType:SingleSQLStatement
Scope:COMPREHENSIVE
TimeLimit(seconds):1800
CompletionStatus:COMPLETED
Startedat:03/06/201605:27:21
Completedat:03/06/201605:27:24
-------------------------------------------------------------------------------
SchemaName:TPCC
SQLID:4zbqykx89yc8v
SQLText:selectt1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'
andt1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGSSECTION(1finding)
-------------------------------------------------------------------------------
1-SQLProfileFinding(seeexplainplanssectionbelow)
--------------------------------------------------------
Apotentiallybetterexecutionplanwasfoundforthisstatement.
Recommendation(estimatedbenefit:83.08%)
------------------------------------------
-ConsideracceptingtherecommendedSQLprofile.
executedbms_sqltune.accept_sql_profile(task_name=>'TASK_114',
task_owner=>'TPCC',replace=>TRUE);
Validationresults
------------------
TheSQLprofilewastestedbyexecutingbothitsplanandtheoriginalplan
andmeasuringtheirrespectiveexecutionstatistics.Aplanmayhavebeen
onlypartiallyexecutediftheothercouldberuntocompletioninlesstime.
OriginalPlanWithSQLProfile%Improved
---------------------------------------
CompletionStatus:COMPLETECOMPLETE
ElapsedTime(s):.012865.00455664.58%
CPUTime(s):.0124.004563.7%
UserI/OTime(s):00
BufferGets:178730283.1%
PhysicalReadRequests:00
PhysicalWriteRequests:00
PhysicalReadBytes:00
PhysicalWriteBytes:00
RowsProcessed:4242
Fetches:4242
Executions:11
Notes
-----
1.Statisticsfortheoriginalplanwereaveragedover10executions.
2.StatisticsfortheSQLprofileplanwereaveragedover10executions.
-------------------------------------------------------------------------------
EXPLAINPLANSSECTION
-------------------------------------------------------------------------------
1-OriginalWithAdjustedCost
------------------------------
Planhashvalue:1838229974
---------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
|0|SELECTSTATEMENT||42|1680|498(1)|00:00:01|
|*1|HASHJOIN||42|1680|498(1)|00:00:01|
|*2|TABLEACCESSFULL|T1|42|1218|68(0)|00:00:01|
|3|TABLEACCESSFULL|T2|92021|988K|430(1)|00:00:01|
---------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2-filter("T1"."OBJECT_NAME"LIKE'%T1%'AND"T1"."OBJECT_NAME"IS
NOTNULL)
2-UsingSQLProfile
--------------------
Planhashvalue:1022743391
---------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||42|1680|152(0)|00:00:01|
|1|NESTEDLOOPS||42|1680|152(0)|00:00:01|
|2|NESTEDLOOPS||42|1680|152(0)|00:00:01|
|*3|TABLEACCESSFULL|T1|42|1218|68(0)|00:00:01|
|*4|INDEXRANGESCAN|T2_IDX|1||1(0)|00:00:01|
|5|TABLEACCESSBYINDEXROWID|T2|1|11|2(0)|00:00:01|
---------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
3-filter("T1"."OBJECT_NAME"LIKE'%T1%'AND"T1"."OBJECT_NAME"ISNOT
NULL)
4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
接受分析建议
报告中给出了执行方法，如上红色部分
接受报告的建议，验证一下如下：
tpcc@TOADDB>executedbms_sqltune.accept_sql_profile(task_name=>'TASK_114',task_owner=>'TPCC',replace=>TRUE); PL/SQLproceduresuccessfullycompleted.
执行测试
再执行原先命令如下：
tpcc@TOADDB>selectt1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'andt1.object_id=t2.object_id;
42rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:1022743391
---------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||42|1680|152(0)|00:00:01|
|1|NESTEDLOOPS||42|1680|152(0)|00:00:01|
|2|NESTEDLOOPS||42|1680|152(0)|00:00:01|
|*3|TABLEACCESSFULL|T1|42|1218|68(0)|00:00:01|
|*4|INDEXRANGESCAN|T2_IDX|1||1(0)|00:00:01|
|5|TABLEACCESSBYINDEXROWID|T2|1|11|2(0)|00:00:01|
---------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
3-filter("T1"."OBJECT_NAME"LIKE'%T1%'AND"T1"."OBJECT_NAME"ISNOT
NULL)
4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
-SQLprofile"SYS_SQLPROF_01534b8309b90000"usedforthisstatement
-thisisanadaptiveplan
Statistics
----------------------------------------------------------
35recursivecalls
0dbblockgets
317consistentgets
1physicalreads
0redosize
2350bytessentviaSQL*Nettoclient
573bytesreceivedviaSQL*Netfromclient
4SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
42rowsprocessed
启用了PROFILE，PS：如果执行中多加几个空格，并不会影响PROFILE的生效的。
5. 维护操作
禁用命令
如下：
begin dbms_sqltune.alter_sql_profile( name=>'SYS_SQLPROF_01534b8309b90000', attribute_name=>'status', value=>'disabled'); end; /
启用命令
如下：
begin dbms_sqltune.alter_sql_profile( name=>'SYS_SQLPROF_01534b8309b90000', attribute_name=>'status', value=>'enabled'); end; /
查看使用的PROFILE
如下：
SQL>SELECTtask_name,statusFROMUSER_ADVISOR_TASKS;
删除PROFILE
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name=>'SYS_SQLPROF_01534b8309b90000'); END;
总结
以上所述是小编给大家介绍的Oracle固定执行计划之SQLPROFILE概要文件，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！