使用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')

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