如何验证SQL PROFILE的性能?

如何检验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的性能测试。

  1. “Tune the sql” 以后部分的内容是怎么产生的. 能不能把命令行给出来?
    这样我自己就可以测试了.

    还有, set sqltune_category=DEFAULT 以前, sqltune_category的取值是什么? 文中没有提.

    Thanks,
    木匠

  2. re 木匠:估计测试的时候,将category=>’MACLEAN_TEST’,测试完成之后,在session级set sqltune_category=DEFAULT回去。

    to maclean:呵呵,最好将版本号遮挡一下吧。听说查的很严。

  3. @Maclean Liu : sqltrpt.sql 简单易于, 在提示出来以后,输入SQL_ID就行了. 我用几个SQL_ID试用了一下,给出的建议,价值还行.

    @小荷, 这下测试案例就完整了. 多谢.

    – 木匠

  4. 6:41:13 saup@PRI10G> alter session set sqltune_category=’MACLEAN_TEST';

    Session altered.

    16:41:35 saup@PRI10G> set autotrace traceonly
    16:41:39 saup@PRI10G> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

    Execution Plan
    ———————————————————-
    Plan hash value: 938196023

    ——————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 95 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| PROFILE_TEST | 1 | 95 | 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_014eda8e75b70002″ used for this statement

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    4 consistent gets
    0 physical reads
    0 redo size
    1418 bytes sent via SQL*Net to client
    492 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed