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

/  
———————
作者:badman250
来源:csdn
原文:https://blog.csdn.net/notbaron/article/details/50830910
版权声明:本文为博主原创文章,转载请附上博文链接!

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐