如何检验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 %
  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 statement

Statistics
----------------------------------------------------------
        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 processed

SQL> 
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的性能测试。