如何检验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 BetaWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 2000SQL> 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 f3v7dxj4bggvqTune the sql~~~~~~~~~~~~GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : TASK_226Tuning Task Owner : SYSWorkload Type : Single SQL StatementScope : COMPREHENSIVETime Limit(seconds): 1800Completion Status : COMPLETEDStarted at : 11/30/2012 13:13:27Completed at : 11/30/2012 13:13:30-------------------------------------------------------------------------------Schema Name : SYSContainer Name: CDB$ROOTSQL ID : f3v7dxj4bggvqSQL 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 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 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: 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)2- Using SQL Profile--------------------Plan hash value: 2974300728----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 113 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST | 1 | 113 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=5060)------------------------------------------------------------------------------- execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_226',task_owner => 'SYS', replace => TRUE,category=>'MACLEAN_TEST'); SQL> set autotrace on;SQL> select /*+ FULL( profile_test) */ * from profile_test where 2 object_id=5060;1 row selected.Execution Plan----------------------------------------------------------Plan hash value: 2974300728----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 113 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST | 1 | 113 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=5060)Note----- - SQL profile "SYS_SQLPROF_013b5177cf260000" used for this statementStatistics---------------------------------------------------------- 275 recursive calls 0 db block gets 130 consistent gets 1 physical reads 0 redo size 1783 bytes sent via SQL*Net to client 543 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 27 sorts (memory) 0 sorts (disk) 1 rows processedSQL> SQL> alter session set sqltune_category=DEFAULT;Session altered.SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;1 row selected.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---------------------------------------------------------- 1 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 以上我们通过sqltune_category将SQL PROFILE的作用域限定在session级别，实现了对SQL PROFILE的性能测试。