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. 测试一
创建表
|
1 2 3 4 |
tpcc@TOADDB> create table t1 as selectobject_id,object_name from dba_objects where rownum<=50000; Table created. tpcc@TOADDB> create table t2 as select * fromdba_objects; Table created. |
创建索引：
|
1 2 |
tpcc@TOADDB> create index t2_idx on t2(object_id); Index created. |
收集统计信息：
|
1 2 3 4 |
tpcc@TOADDB> execdbms_stats.gather_table_stats( user , 't1' , cascade => true ,method_opt=> 'forall columns size 1' ); PL/SQL procedure successfully completed. tpcc@TOADDB> execdbms_stats.gather_table_stats( user , 't1' , cascade => true ,method_opt=> 'forall columns size 1' ); PL/SQL procedure successfully completed. |
执行无HINT的SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
tpcc@TOADDB> set autotrace on tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id; 42 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1838229974 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 2500 | 97K| 498 (1)| 00:00:01 | |* 1| HASH JOIN | | 2500 | 97K| 498 (1)| 00:00:01 | |* 2| TABLE ACCESS FULL | T1 | 2500 | 72500 | 68 (0)| 00:00:01 | | 3| TABLE ACCESS FULL | T2 | 92021 | 988K| 430 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 1-access( "T1" . "OBJECT_ID" = "T2" . "OBJECT_ID" ) 2- filter( "T1" . "OBJECT_NAME" LIKE '%T1%' AND "T1" . "OBJECT_NAME" IS NOT NULL ) Statistics ---------------------------------------------------------- 1 recursive calls 0 dbblock gets 1789 consistent gets 0 physical reads 0 redosize 2350 bytes sent via SQL*Net toclient 573 bytes received via SQL*Net from client 4 SQL*Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 42 rowsprocessed |
执行带Hint的SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SQL> select /*+ use_nl(t1 t2) index (t2)*/ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id; 42 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1022743391 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 2500 | 97K| 5069 (1)|00:00:01 | | 1| NESTED LOOPS | | 2500| 97K| 5069 (1)| 00:00:01 | | 2| NESTED LOOPS | | 2500 | 97K| 5069 (1)| 00:00:01 | |* 3| TABLE ACCESS FULL | T1 | 2500 | 72500 | 68 (0)|00:00:01 | |* 4| INDEX RANGE SCAN |T2_IDX | 1 | | 1 (0)| 00:00:01 | | 5| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 3- filter( "T1" . "OBJECT_NAME" LIKE '%T1%' AND "T1" . "OBJECT_NAME" IS NOT NULL ) 4-access( "T1" . "OBJECT_ID" = "T2" . "OBJECT_ID" ) Statistics ---------------------------------------------------------- 1 recursive calls 0 dbblock gets 304 consistent gets 24 physical reads 0 redosize 2350 bytes sent via SQL*Net toclient 573 bytes received via SQL*Net from client 4 SQL*Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 42 rowsprocessed |
使用SQL PROFILE
查找执行SQL的SQL_ID
|
1 2 3 4 5 6 7 8 9 10 |
tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like '%t1.object_name%' ; SQL_ID ------------- SQL_TEXT ---------------------------------------------------------------------------------------------------- 4zbqykx89yc8v select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id 18bphz37dajq9 select /*+ use_nl(t1 t2) index (t2) */t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id |
运行存储过程如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
var tuning_task varchar2(100); DECLARE l_sql_id v$session.prev_sql_id%TYPE; l_tuning_task VARCHAR2(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/SQL procedure successfully completed. |
查看task的名字
|
1 2 3 4 |
tpcc@TOADDB> print tuning_task; TUNING_TASK ---------------------------------------------------------------------------------------------------- TASK_114 |
查看执行报告
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
set long 99999 col comments format a200 SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual; COMMENTS ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_114 Tuning Task Owner : TPCC Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 03/06/2016 05:27:21 Completed at : 03/06/2016 05:27:24 ------------------------------------------------------------------------------- Schema Name : TPCC SQL ID : 4zbqykx89yc8v SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain planssection below) -------------------------------------------------------- Apotentially better execution plan was found for this statement. Recommendation (estimated benefit: 83.08%) ------------------------------------------ -Consider accepting the recommended SQL profile. executedbms_sqltune.accept_sql_profile(task_name => 'TASK_114' , task_owner => 'TPCC' , replace => TRUE ); Validation results ------------------ TheSQL profile was tested by executing both its plan and the original plan andmeasuring their respective execution statistics . A plan may have been only partially executed if the other could be run to completion in lesstime. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .012865 .004556 64.58 % CPUTime (s): .0124 .0045 63.7% User I/O Time (s): 0 0 Buffer Gets: 1787 302 83.1% Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 42 42 Fetches: 42 42 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1838229974 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 42 | 1680 | 498 (1)| 00:00:01 | |* 1| HASH JOIN | | 42 | 1680 | 498 (1)| 00:00:01 | |* 2| TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0)| 00:00:01 | | 3| TABLE ACCESS FULL | T2 | 92021 | 988K| 430 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 1-access( "T1" . "OBJECT_ID" = "T2" . "OBJECT_ID" ) 2- filter( "T1" . "OBJECT_NAME" LIKE '%T1%' AND "T1" . "OBJECT_NAME" IS NOT NULL ) 2- Using SQL Profile -------------------- Plan hash value: 1022743391 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 42 | 1680 | 152 (0)|00:00:01 | | 1| NESTED LOOPS | | 42 | 1680 | 152 (0)| 00:00:01 | | 2| NESTED LOOPS | | 42 | 1680 | 152 (0)| 00:00:01 | |* 3| TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0)| 00:00:01 | |* 4| INDEX RANGE SCAN |T2_IDX | 1 | | 1 (0)| 00:00:01 | | 5| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 3- filter( "T1" . "OBJECT_NAME" LIKE '%T1%' AND "T1" . "OBJECT_NAME" IS NOT NULL ) 4-access( "T1" . "OBJECT_ID" = "T2" . "OBJECT_ID" ) ------------------------------------------------------------------------------- |
接受分析建议
报告中给出了执行方法，如上红色部分
接受报告的建议，验证一下如下：
|
1 2 |
tpcc@TOADDB> execute dbms_sqltune.accept_sql_profile(task_name=> 'TASK_114' ,task_owner => 'TPCC' , replace => TRUE ); PL/SQL procedure successfully completed. |
执行测试
再执行原先命令如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id; 42 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1022743391 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 42 | 1680 | 152 (0)|00:00:01 | | 1| NESTED LOOPS | | 42| 1680 | 152 (0)|00:00:01 | | 2| NESTED LOOPS | | 42 | 1680 | 152 (0)| 00:00:01 | |* 3| TABLE ACCESS FULL | T1 | 42 | 1218 | 68 (0)| 00:00:01 | |* 4| INDEX RANGE SCAN |T2_IDX | 1 | | 1 (0)| 00:00:01 | | 5| TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 3- filter( "T1" . "OBJECT_NAME" LIKE '%T1%' AND "T1" . "OBJECT_NAME" IS NOT NULL ) 4-access( "T1" . "OBJECT_ID" = "T2" . "OBJECT_ID" ) Note ----- - SQL profile "SYS_SQLPROF_01534b8309b90000" used for this statement - this is an adaptive plan Statistics ---------------------------------------------------------- 35 recursive calls 0 dbblock gets 317 consistent gets 1 physical reads 0 redosize 2350 bytes sent via SQL*Net toclient 573 bytes received via SQL*Net from client 4 SQL*Net roundtrips to / from client 1 sorts (memory) 0 sorts (disk) 42 rowsprocessed |
启用了PROFILE，PS：如果执行中多加几个空格，并不会影响PROFILE的生效的。
5. 维护操作
禁用命令
如下：
|
1 2 3 4 5 6 7 |
begin dbms_sqltune.alter_sql_profile( name => 'SYS_SQLPROF_01534b8309b90000' , attribute_name => 'status' , value => 'disabled' ); end ; / |
启用命令
如下：
|
1 2 3 4 5 6 7 |
begin dbms_sqltune.alter_sql_profile( name => 'SYS_SQLPROF_01534b8309b90000' , attribute_name => 'status' , value => 'enabled' ); end ; / |
查看使用的PROFILE
如下：
|
1 |
SQL> SELECT task_name,status FROMUSER_ADVISOR_TASKS ; |
删除PROFILE
|
1 2 3 |
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE( name => 'SYS_SQLPROF_01534b8309b90000' ); END ; |
总结
以上所述是小编给大家介绍的Oracle固定执行计划之SQL PROFILE概要文件 ，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
原文链接：https://www.cnblogs.com/DataArt/archive/2018/10/31/9881852.html
查看更多关于Oracle固定执行计划之SQL PROFILE概要文件详解的详细内容...