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