SQL调整顾问可以通过SQL*PLUS或EM调用。
一般分为3个步骤:
1. 创建调整任务。
2. 执行调整任务。
3. 查看结果。
创建调整任务:
执行调整任务
上个步骤可能会耗时很长。
查看结果
观察执行结果,调整顾问会给出该语句的调整建议(上例是创建索引),并给出当前和使用调整建议后不同的执行计划。
一般分为3个步骤:
1. 创建调整任务。
2. 执行调整任务。
3. 查看结果。
创建调整任务:
点击(此处)折叠或打开
- DECLARE
- tuning_task_name VARCHAR2(30);
- tuning_sqltext CLOB;
- BEGIN
- tuning_sqltext := 'SELECT id FROM BOM WHERE MOD3=3';
- tuning_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
- sql_text => tuning_sqltext,
- bind_list => NULL,
- user_name => 'HK',
- scope => 'COMPREHENSIVE',
- time_limit => 60,
- task_name => 'first_tuning_task50',
- description => 'Tune T50 count');
- END;
- /
点击(此处)折叠或打开
- BEGIN
- DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'first_tuning_task50' );
- END;
- /
上个步骤可能会耗时很长。
查看结果
点击(此处)折叠或打开
- SET SERVEROUTPUT ON
- BEGIN
- DBMS_OUTPUT.PUT_LINE(dbms_sqltune.report_tuning_task('first_tuning_task60'));
- END;
- /
"DBMS_SQLTUNE.REPORT_TUNING_TAS"
"GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : first_tuning_task50
Tuning Task Owner : HK
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 09/26/2014 17:58:41
Completed at : 09/26/2014 17:59:14
Number of Statistic Findings : 1
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: HK
SQL ID : bx8zw8mfvh7u1
SQL Text : SELECT id FROM BOM WHERE MOD3=3
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table ""HK"".""BOM"" and its indices were not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'HK', tabname => 'BOM',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HK.IDX$$_00FE0001 on HK.BOM('MOD3');
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run ""Access Advisor""
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 258718033
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6008K| 148M| 242K (1)| 00:48:31 |
|* 1 | TABLE ACCESS FULL| BOM | 6008K| 148M| 242K (1)| 00:48:31 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(""MOD3""=:SYS_B_0)
2- Using New Indices
--------------------
Plan hash value: 964494434
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6008K| 148M| 30552 (2)| 00:06:07 |
|* 1 | VIEW | index$_join$_001 | 6008K| 148M| 30552 (2)| 00:06:07 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX$$_00FE0001 | 6008K| 148M| 2797 (1)| 00:00:34 |
| 4 | INDEX FAST FULL SCAN| BOM_PK_ID | 6008K| 148M| 20992 (2)| 00:04:12 |
-------------------------------------------------..."
"GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : first_tuning_task50
Tuning Task Owner : HK
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 09/26/2014 17:58:41
Completed at : 09/26/2014 17:59:14
Number of Statistic Findings : 1
Number of Index Findings : 1
-------------------------------------------------------------------------------
Schema Name: HK
SQL ID : bx8zw8mfvh7u1
SQL Text : SELECT id FROM BOM WHERE MOD3=3
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table ""HK"".""BOM"" and its indices were not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'HK', tabname => 'BOM',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HK.IDX$$_00FE0001 on HK.BOM('MOD3');
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run ""Access Advisor""
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 258718033
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6008K| 148M| 242K (1)| 00:48:31 |
|* 1 | TABLE ACCESS FULL| BOM | 6008K| 148M| 242K (1)| 00:48:31 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(""MOD3""=:SYS_B_0)
2- Using New Indices
--------------------
Plan hash value: 964494434
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6008K| 148M| 30552 (2)| 00:06:07 |
|* 1 | VIEW | index$_join$_001 | 6008K| 148M| 30552 (2)| 00:06:07 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX$$_00FE0001 | 6008K| 148M| 2797 (1)| 00:00:34 |
| 4 | INDEX FAST FULL SCAN| BOM_PK_ID | 6008K| 148M| 20992 (2)| 00:04:12 |
-------------------------------------------------..."
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1282023/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-1282023/