ITPub博客

首页 > 数据库 > Oracle > 使用SQL PROFILE 给出合理的执行计划

使用SQL PROFILE 给出合理的执行计划

原创 Oracle 作者:pxbibm 时间:2015-08-25 15:20:03 0 删除 编辑
Oracle数据库10g使用了一个叫做SQL配置文件的新方法弥补了存储概要的缺点,  
DBA可以使用SQL调整顾问(STA)或SQL访问顾问(SAA)来识别可以得到更好性能的SQL语句,  
这些语句可以保存在SQL调整集、一个AWR快照或保存在当前的库缓存中,一旦识别出调整候 
选者, 这些顾问程序就开始分析捕获到的语句以期获得更好的性能,然后生成专用的语句扩展(就叫 
做SQL配置文件)并重写SQL语句以在执行期间获取更佳的性能。 
 
与存储概要类似,一个SQL配置文件提供了使用更好的执行计划的能力(如果这个执行计 
划是可用的),SQL配置文件也可以象存储概要一样分阶段执行,或限制到对特定会话才能执 
行该SQL配置文件,但是大多数重要的改进超过了存储概要, 
至少有两方面: 
(a)自我调整的能力保障了SQL配置文件能提供最好的执行计划 
(b)检查SQL配置文件不再有效的能力(因此ADDM建议需要生成一个新的SQL配置文件) 
 
 
 
SQL Profile对于一下类型语句有效: 
     SELECT语句; 
     UPDATE语句; 
     INSERT语句(仅当使用SELECT子句时有效); 
     DELETE语句; 
     CREATE语句(仅当使用SELECT子句时有效); 
     MERGE语句(仅当作UPDATE和INSERT操作时有效)。 
另外,使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。 

SQL PROFILE 实验:
1.创建测试表

SQL> drop table t purge;                     

Table dropped.

SQL> create table t as select object_id,object_name from dba_objects;

Table created.

SQL> insert into t select * from t;

14140 rows created.

SQL> /

28280 rows created.

SQL> /

56560 rows created.

SQL> /

113120 rows created.

SQL> update t set object_name='T';

226240 rows updated.

SQL> UPDATE T SET OBJECT_NAME='T1' WHERE ROWNUM=1;

1 row updated.

SQL> COMMIT;

Commit complete.
SQL>declare
  my_task_name varchar2(30);
  mysqltext    clob;
begin
  mysqltext    := 'select * from t where object_NAME=''T1''';
  my_task_name := dbms_sqltune.create_tuning_task(sql_text  => mysqltext,
                                                  user_name => 'SCOTT',
                                                  scope     => 'COMPREHENSIVE',
                                                  task_name => 'sql_tuning_test');
end;
/

PL/SQL procedure successfully completed.

SQL>  exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

PL/SQL procedure successfully completed.

SQL> SET LONG 999999
SQL> SET LINESIZE 1000
SQL> set serveroutput on size 999999
SQL> select dbms_sqltune.report_tuning_task('sql_tuning_test') from dual;

 

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sql_tuning_test
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 08/25/2015 15:09:49
Completed at       : 08/25/2015 15:09:50

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 2qkpfwtzpdc0d
SQL Text   : select * from t where object_NAME='T1'

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  尚未分析表 "SCOTT"."T"。

  Recommendation
  --------------
  - 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',
            estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
            'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要此表的最新统计信息。

2- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。

  Recommendation (estimated benefit: 99.1%)
  -----------------------------------------
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index SCOTT.IDX$$_07230001 on SCOTT.T("OBJECT_NAME","OBJECT_ID");

  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |   869 |   224   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |    11 |   869 |   224   (1)| 00:00:03 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OBJECT_NAME"='T1')

2- Using New Indices
--------------------
Plan hash value: 3722711773

-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    79 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX$$_07230001 |     1 |    79 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OBJECT_NAME"='T1')

-------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12798004/viewspace-1782223/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    240
  • 访问量
    2155468