oracle 10g可以使优化器运行在优化模式收集额外的信息，并且对当前sql做出一些建议，使其运行的更好。使用STA一定要保证优化器是CBO模式下。
在优化模式下，优化
器可以给出以下的一些建议：
- Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
- SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the
OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it's sensible to update profiles periodically. The SQL profiling is not performed when the tuining optimizer is run in limited mode. - Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path. It's index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
- SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.
执行
DBMS_SQLTUNE
包进行
sql
优化需要有
advisor
的权限：
CONN sys/password AS SYSDBA；
GRANT ADVISOR TO scott;
CONN scott/tiger；
我们使用dba_objects 来创建两张表，一张大表，一张小表，不去收集统计信息，不去创建索引。
其中t1为小表，t为大表
SQL> select count(*) from t1;
COUNT(*)
----------
753888
SQL> select count(*) from t;
COUNT(*)
----------
1884641
SQL> select count(*) from t,t1 where t.object_id=t1.object_id;
已用时间: 00: 00: 29.92
执行计划
----------------------------------------------------------
Plan hash value: 949044725
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 33921 (1)|999:5959 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN | | 940M| 22G| 184M| 33921 (1)|999:59 59 |
| 3 | TABLE ACCESS FULL| T1 | 7751K| 96M| | 3716 (0)|210:1045 |
| 4 | TABLE ACCESS FULL| T | 10M| 124M| | 9156 (0)|517:5206|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
2 recursive calls
2 db block gets
37632 consistent gets
10603 physical reads
352 redo size
530 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1.创建一个新的tuning task，使用到的函数为
CREATE_TUNING_TASK function
创建任务有以下几种方式：
SQL text format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text IN CLOB, bind_list IN sql_binds := NULL, user_name IN VARCHAR2 := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
SQL ID format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
Workload Repository format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
SQLSET format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, rank1 IN VARCHAR2 := NULL, rank2 IN VARCHAR2 := NULL, rank3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := NULL, result_limit IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME', sqlset_owner IN VARCHAR2 := NULL) RETURN VARCHAR2;
SQL Performance Analyzer format:
DBMS_SQLTUNE.CREATE_TUNING_TASK( spa_task_name IN VARCHAR2, spa_task_owner IN VARCHAR2 := NULL, spa_compare_exec IN VARCHAR2 := NULL, basic_filter IN VARCHAR2 := NULL, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL) RETURN VARCHAR2;
oracle提供了以上几种方式，具体内容请参照Oracle官方文档，在这儿我们只使用第一条
DECLARE
MY_TASK_NAME VARCHAR2(30);
MY_SQLTEXT CLOB;
BEGIN
MY_SQLTEXT := ' select count(*) from t,t1 where t.object_id=t1.object_id';
MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => MY_SQLTEXT,
USER_NAME => 'SCOTT',
SCOPE => 'COMPREHENSIVE',
TIME_LIMIT => 60,
TASK_NAME => 'tuning_sql_test',
DESCRIPTION => 'Task to tune a query on a specified table');
END;
2.执行优化过程使用
EXECUTE_TUNING_TASK
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 过程已成功完成。
已用时间: 00: 00: 06.20
有时候你可能想暂停，取消或者重新启动任务，你可以使用以下的方式
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'tuning_sql_test');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'tuning_sql_test');
-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'tuning_sql_test');
-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'tuning_sql_test');
3.查看任务是否结束
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
4.查看优化结果
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('tuning_sql_test') AS recommendations FROM dual;
SET PAGESIZE 24
其中内容如下：oracle给了很详细的建议。
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST3')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test3
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_1088
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 10/28/2013 16:30:49
Completed at : 10/28/2013 16:31:33
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : gmc689fch5gr4
SQL Text : select count(*) from t,t1 where t.object_id=t1.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "SCOTT"."T1"。
Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。
2- Statistics Finding
---------------------
尚未分析表 "SCOTT"."T"。
Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 86.96%)
------------------------------------------
- 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_sql_test3',
task_owner => 'SCOTT', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
与 DOP 8 并行执行此查询会使原始计划上的响应时间缩短 86.97%。但是, 启用并行执行
时要付出一些代价。它将增加语句的资源消耗 (预计为
4.25%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源,
因此如果没有足够可用的硬件容量,
并发语句的响应时间将受到负面影响。
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 949044725
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 26 | | 33921 (1)|999:59:
59 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|
|* 2 | HASH JOIN | | 940M| 22G| 184M| 33921 (1)|999:59:
59 |
| 3 | TABLE ACCESS FULL| T1 | 7751K| 96M| | 3716 (0)|210:10:
45 |
| 4 | TABLE ACCESS FULL| T | 10M| 124M| | 9156 (0)|517:52:
0
6 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
2- Using Parallel Execution
---------------------------
Plan hash value: 3647253920
--------------------------------------------------------------------------------
-----------------------------
--------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CP
U)| Time | TQ |IN
-OUT| PQ Distrib |
--------------------------------------------------------------------------------
---------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 4420 (
0)|250:00:56 |
| | |
| 1 | SORT AGGREGATE | | 1 | 26 | |
|
| | | |
| 2 | PX COORDINATOR | | | | |
|
| | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 26 | |
|
| Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | |
| | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 940M| 22G| 23M| 4420 (
0)|250:00:56 | Q1,02 | PCWP |
|
| 6 | PX RECEIVE | | 7751K| 96M| | 516 (
0)| 29:11:30 | Q1,02 | PCWP |
|
| 7 | PX SEND HASH | :TQ10000 | 7751K| 96M| | 516 (
0)| 29:11:30 | Q1,00 | P->P
| HASH |
| 8 | PX BLOCK ITERATOR | | 7751K| 96M| | 516 (
0)| 29:11:30 | Q1,00 | P
CWC | |
| 9 | TABLE ACCESS FULL| T1 | 7751K| 96M| | 516 (
0)| 29:11:30 | Q1,00
| PCWP | |
| 10 | PX RECEIVE | | 10M| 124M| | 1272 (
0)| 71:55:35 | Q
1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 10M| 124M| | 1272 (
0)| 71:55:35
| Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 10M| 124M| | 1272 (
0)| 71:55
:35 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| T | 10M| 124M| | 1272 (
0)| 7
1:55:35 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------
-
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
-------------------------------------------------------------------------------
5.删除任务
EXEC
DBMS_SQLTUNE.DROP_TUNING_TASK('tuning_sql_test');
参照 ：
Oracle官方文档
扫一扫
397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
