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

Utilize Sql Tuning Advisor from Script

Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具,大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor;但如果系统中没有配置dbconsole或者Grid Control的话,我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本,具体脚本:

begin
DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid := '&sqlid';
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => '&task_name',
description => 'comment'                               
);
END;
/
BEGIN
dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
set pages 60
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;

具体使用示例:

SQL> begin
DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
Enter value for task_name: newtask
old   3:   DBMS_SQLTUNE.drop_tuning_task('&task_name');
new   3:   DBMS_SQLTUNE.drop_tuning_task('newtask');
begin
*
ERROR at line 1:
ORA-13605: The specified task or object newtask does not exist for the current
user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2307
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at "SYS.DBMS_SQLTUNE", line 751
ORA-06512: at line 3
SQL> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid := '&sqlid';
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => '&task_name',
description => 'comment'                               
);
END;
/ 
Enter value for sqlid: 17usubxchdf2w
old  11:   my_sqlid := '&sqlid';
new  11:   my_sqlid := '17usubxchdf2w';
Enter value for task_name: new_task 
old  19:						   task_name => '&task_name',
new  19:						   task_name => 'new_task',
SQL> BEGIN
dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/  
Enter value for task_name: new_task
old   3:   dbms_sqltune.execute_tuning_task(task_name => '&task_name');
new   3:   dbms_sqltune.execute_tuning_task(task_name => 'new_task');
PL/SQL procedure successfully completed.
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
Enter value for task_name: new_task
old   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name'
new   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'new_task'
STATUS
-----------
COMPLETED
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;
Enter value for task_name: new_task
old   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL
new   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('new_task') FROM DUAL
DBMS_SQLTUNE.REPORT_TUNING_TASK('NEW_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : new_task
Tuning Task Owner  : SYS
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at	   : 03/25/2011 00:14:41
Completed at	   : 03/25/2011 00:14:45
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID	   : 17usubxchdf2w
SQL Text   : select count(t1) from hashtab
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SYS"."HASHTAB" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'HASHTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 485915006
------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	   1 |	  13 |	   2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	     |	   1 |	  13 |		  |	     |
|   2 |   TABLE ACCESS FULL| HASHTAB |	 102 |	1326 |	   2   (0)| 00:00:01 |
------------------------------------------------------------------------------

沪ICP备14014813号

沪公网安备 31010802001379号