Oracle中之SQL PROFILE概要文件的示例分析

2025-03-25 技术教程 151

这篇文章给大家分享的是有关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. SQL PROFILE

SQL PROFILE在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操作时有效）。

另外，使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。

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>setautotraceontpcc@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"ISNOTNULL)Statistics----------------------------------------------------------1recursivecalls0dbblockgets1789consistentgets0physicalreads0redosize2350bytessentviaSQL*Nettoclient573bytesreceivedviaSQL*Netfromclient4SQL*Netroundtripsto/fromclient0sorts(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"ISNOTNULL)4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Statistics----------------------------------------------------------1recursivecalls0dbblockgets304consistentgets24physicalreads0redosize2350bytessentviaSQL*Nettoclient573bytesreceivedviaSQL*Netfromclient4SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)42rowsprocessed

使用SQL PROFILE

查找执行SQL的SQL_ID

tpcc@TOADDB>selectsql_id,sql_textfromv$sqlwheresql_textlike'%t1.object_name%';SQL_ID-------------SQL_TEXT----------------------------------------------------------------------------------------------------4zbqykx89yc8vselectt1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'andt1.object_id=t2.object_id18bphz37dajq9select/*+use_nl(t1t2)index(t2)*/t1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T1%'andt1.object_id=t2.object_id

运行存储过程如下：

vartuning_taskvarchar2(100);DECLAREl_sql_idv$session.prev_sql_id%TYPE;l_tuning_taskVARCHAR2(30);BEGINl_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_114PL/SQLproceduresuccessfullycompleted.

查看task的名字

tpcc@TOADDB>printtuning_task;TUNING_TASK----------------------------------------------------------------------------------------------------TASK_114

查看执行报告

setlong99999colcommentsformata200SELECTdbms_sqltune.report_tuning_task(:tuning_task)COMMENTSFROMdual;COMMENTS----------------------------------------------------------------------------------------------------GENERALINFORMATIONSECTION-------------------------------------------------------------------------------TuningTaskName:TASK_114TuningTaskOwner:TPCCWorkloadType:SingleSQLStatementScope:COMPREHENSIVETimeLimit(seconds):1800CompletionStatus:COMPLETEDStartedat:03/06/201605:27:21Completedat:03/06/201605:27:24-------------------------------------------------------------------------------SchemaName:TPCCSQLID:4zbqykx89yc8vSQLText: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------------------TheSQLprofilewastestedbyexecutingbothitsplanandtheoriginalplanandmeasuringtheirrespectiveexecutionstatistics.Aplanmayhavebeenonlypartiallyexecutediftheothercouldberuntocompletioninlesstime.OriginalPlanWithSQLProfile%Improved---------------------------------------CompletionStatus:COMPLETECOMPLETEElapsedTime(s):.012865.00455664.58%CPUTime(s):.0124.004563.7%UserI/OTime(s):00BufferGets:178730283.1%PhysicalReadRequests:00PhysicalWriteRequests:00PhysicalReadBytes:00PhysicalWriteBytes:00RowsProcessed:4242Fetches:4242Executions:11Notes-----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"ISNOTNULL)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"ISNOTNULL)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"ISNOTNULL)4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Note------SQLprofile"SYS_SQLPROF_01534b8309b90000"usedforthisstatement-thisisanadaptiveplanStatistics----------------------------------------------------------35recursivecalls0dbblockgets317consistentgets1physicalreads0redosize2350bytessentviaSQL*Nettoclient573bytesreceivedviaSQL*Netfromclient4SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)42rowsprocessed

启用了PROFILE，PS：如果执行中多加几个空格，并不会影响PROFILE的生效的。

5. 维护操作

禁用命令
如下：

begindbms_sqltune.alter_sql_profile(name=>'SYS_SQLPROF_01534b8309b90000',attribute_name=>'status',value=>'disabled');end;/

启用命令
如下：

begindbms_sqltune.alter_sql_profile(name=>'SYS_SQLPROF_01534b8309b90000',attribute_name=>'status',value=>'enabled');end;/

查看使用的PROFILE

如下：

SQL>SELECTtask_name,statusFROMUSER_ADVISOR_TASKS;

删除PROFILE

BEGINDBMS_SQLTUNE.DROP_SQL_PROFILE(name=>'SYS_SQLPROF_01534b8309b90000');END;

感谢各位的阅读！关于“Oracle中之SQL PROFILE概要文件的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！