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> 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.

创建索引：

tpcc@toaddb> create index t2_idx on t2(object_id);
index created.

收集统计信息：

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

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

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

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

运行存储过程如下：

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的名字

tpcc@toaddb> print tuning_task;
tuning_task
----------------------------------------------------------------------------------------------------
task_114

查看执行报告

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")
-------------------------------------------------------------------------------

接受分析建议

报告中给出了执行方法，如上红色部分

接受报告的建议，验证一下如下：

tpcc@toaddb> execute dbms_sqltune.accept_sql_profile(task_name=> 'task_114',task_owner => 'tpcc', replace => true);
pl/sql procedure successfully completed.

执行测试

再执行原先命令如下：

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.  维护操作

禁用命令
如下：

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>select task_name,status fromuser_advisor_tasks ;

删除profile

begin 
 dbms_sqltune.drop_sql_profile(name => 'sys_sqlprof_01534b8309b90000'); 
end; 

总结

以上所述是小编给大家介绍的oracle固定执行计划之sql profile概要文件 ，希望对大家有所帮助