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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1782223/