如何验证SQL PROFILE的性能?

　　10g以后的sql tuning advisor(可以通过Enterprise Manager或DBMS_SQLTUNE包访问)会给出对于SQL的建议包括以下四种：

　　1. 收集最新的统计信息

　　2. 彻底重构该SQL语句

　　3. 创建推荐的索引

　　4. 启用SQL TUNING ADVISOR找到的SQL PROFILE

　　这里我们要注意的是在production环境中显然不可能让我们在没有充分测试的前提下随意为SQL接受一个PROFILE，因为这可能为本来就性能糟糕而需要调优的系统引来变化。 但是如果恰巧没有合适的TEST环境，而你的SQL PROFILE又可能是性能压力的救命稻草时，我们可以使用以下方法在production环境中局部测试SQL PROFILE，仅在session级别生效：

　　Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta

　　With the Partitioning, OLAP, Data Mining and Real Application Testing options

　　SQL> create table profile_test tablespace users as select * from dba_objects;

　　Table created.

　　SQL> create index ix_objd on profile_test(object_id);

　　Index created.

　　SQL> set linesize 200 pagesize 2000

　　SQL> exec dbms_stats.gather_table_stats('','PROFILE_TEST');

　　PL/SQL procedure successfully completed.

　　SQL> set autotrace traceonly;

　　SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

　　Execution Plan

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

　　Plan hash value: 663678050

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

　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

　　| 0 | SELECT STATEMENT | | 1 | 113 | 408 (1)| 00:00:01 |

　　|* 1 | TABLE ACCESS FULL| PROFILE_TEST | 1 | 113 | 408 (1)| 00:00:01 |

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

　　Predicate Information (identified by operation id):

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

　　1 - filter("OBJECT_ID"=5060)

　　Statistics

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

　　0 recursive calls

　　0 db block gets

　　1471 consistent gets

　　0 physical reads

　　0 redo size

　　1779 bytes sent via SQL*Net to client

　　543 bytes received via SQL*Net from client

　　2 SQL*Net roundtrips to/from client

　　0 sorts (memory)

　　0 sorts (disk)

　　1 rows processed

　　f3v7dxj4bggvq

　　Tune the sql

　　~~~~~~~~~~~~

　　GENERAL INFORMATION SECTION

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

　　Tuning Task Name : TASK_226

　　Tuning Task Owner : SYS

　　Workload Type : Single SQL Statement

　　Scope : COMPREHENSIVE

　　Time Limit(seconds): 1800

　　Completion Status : COMPLETED

　　Started at : 11/30/2012 13:13:27

　　Completed at : 11/30/2012 13:13:30

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

　　Schema Name : SYS

　　Container Name: CDB$ROOT

　　SQL ID : f3v7dxj4bggvq

　　SQL Text : select /*+ FULL( profile_test) */ * from profile_test where

　　object_id=5060

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

　　FINDINGS SECTION (1 finding)

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

　　1- SQL Profile Finding (see explain plans section below)

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

　　A potentially better execution plan was found for this statement.

　　Recommendation (estimated benefit: 99.79%)

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

　　- Consider accepting the recommended SQL profile.

　　execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_226',

　　task_owner => 'SYS', replace => TRUE);

　　Validation results

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

　　The SQL profile was tested by executing both its plan and the original plan

　　and measuring their respective execution statistics. A plan may have been

　　only partially executed if the other could be run to completion in less time.

　　Original Plan With SQL Profile % Improved

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

　　Completion Status: COMPLETE COMPLETE

　　Elapsed Time (s): .005407 .000034 99.37 %

　　CPU Time (s): .004599 0 100 %

　　User I/O Time (s): 0 0

　　Buffer Gets: 1470 3 99.79 %

原文转自：http://blogread.cn/it/article/6041