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

Comments

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

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

    Thanks,
    木匠

  2. roger says

    test on 12c ?

  3. 小荷 says

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

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

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

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

    – 木匠

  5. 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

  6. 笑话,刘大是谁

  7. 11r2要这样才能禁用掉PROFILEbeginDBMS_SQLTUNE.ALTER_SQL_PROFILE(‘SQL_PROFILE’,’STATUS’,’DISABLED’);end;/

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号