sql的优化，我一直都是加索引，加hint，改sql，收集统计信息，每次都是这些步骤，今天听说了STA，所以想记录一下。应该说STA算是比较智能的东西，如果我们表上没有索引，它可能会建议我们加上索引。但首先得创建优化任务，然后再执行优化任务，最后才能出优化结果，感觉和addm有点像。
一：创建优化任务
SQL> create user user1 identified by user1 account unlock;
User created.
SQL> grant connect,resource,advisor to user1;
Grant succeeded.
SQL> grant select on dba_objects to user1;
Grant succeeded.
SQL> conn user1/user1
Connected.
SQL> create table t1 as select object_id,object_name from dba_objects;
Table created.
SQL> insert into t1 select * from t1;
75886 rows created.
SQL> /
151772 rows created.
SQL> /
303544 rows created.
SQL> /
607088 rows created.
SQL> /
1214176 rows created.
SQL> /
2428352 rows created.
SQL> commit;
Commit complete.
SQL> create table t2 as select * from t1;
Table created.
SQL> select t1.object_id,t2.object_name from t1,t2 where t1.object_id=t2.object_id and t1.object_id=1;
no rows selected
Elapsed: 00:00:00.44
Execution Plan
----------------------------------------------------------
Plan hash value: 1819147781
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65702 | 5902K| 12794 (2)| 00:02:34 |
|* 1 | HASH JOIN | | 65702 | 5902K| 12794 (2)| 00:02:34 |
|* 2 | TABLE ACCESS FULL| T1 | 257 | 3341 | 6426 (2)| 00:01:18 |
|* 3 | TABLE ACCESS FULL| T2 | 256 | 20224 | 6366 (2)| 00:01:17 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_ID"=1)
3 - filter("T2"."OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
30 recursive calls
2 db block gets
23651 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
0 rows processed
下面通过STA给出的建议来优化这个sql。
一：创建优化任务
SQL> DECLARE
2 task_name VARCHAR2(40);
3 BEGIN
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '1rft7rqy2ha66',
plan_hash_value => 1819147781,
4 5 6 7 scope => 'COMPREHENSIVE',
8 time_limit => 60,
9 task_name => 'sql_maomao'
10 );
11 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_maomao');
12 END;
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:41.81
以下为DBMS_SQLTUNE.CREATE_TUNING_TASK的sql_id的格式，所以需要plan_hash_value
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;
二：执行优化任务
SQL> exec dbms_sqltune.execute_tuning_task('sql_maomao');
PL/SQL procedure successfully completed.
Elapsed: 00:00:42.63
三：检查优化任务状态
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='sql_maomao';
TASK_NAME STATUS
------------------------------ -----------
sql_maomao COMPLETED
四：查优化结果
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_maomao') from DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_maomao
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_2195
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/25/2013 10:27:58
Completed at : 12/25/2013 10:28:40
-------------------------------------------------------------------------------
Schema Name: USER1
SQL ID : 1rft7rqy2ha66
SQL Text : select t1.object_id,t2.object_name from t1,t2 where
t1.object_id=t2.object_id and t1.object_id=1
-------------------------------------------------------------------------------
FINDINGS SECTION (4 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "USER1"."T2" was not analyzed.---先是建议我分析下表
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'USER1', tabname =>
'T2', 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.
2- Statistics Finding
---------------------
Table "USER1"."T1" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'USER1', tabname =>
'T1', 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.
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 95.37%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_maomao',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 24 will improve its response time
95.38% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 10.98% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
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
4- 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: 99.97%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index USER1.IDX$$_086B0001 on USER1.T1("OBJECT_ID"); --建议创建索引
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index USER1.IDX$$_086B0002 on USER1.T2("OBJECT_ID","OBJECT_NAME"); ------建议创建索引
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: 1819147781------------这个是之前的执行计划
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65702 | 5902K| 12794 (2)| 00:02:34 |
|* 1 | HASH JOIN | | 65702 | 5902K| 12794 (2)| 00:02:34 |
|* 2 | TABLE ACCESS FULL| T1 | 257 | 3341 | 6426 (2)| 00:01:18 |
|* 3 | TABLE ACCESS FULL| T2 | 256 | 20224 | 6366 (2)| 00:01:17 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_ID"=1)
3 - filter("T2"."OBJECT_ID"=1)
2- Using New Indices
--------------------
Plan hash value: 132872914----优化后的执行计划
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 3 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 92 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_086B0001 | 1 | 13 | 1 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 79 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_086B0002 | 1 | 79 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"=1)
4 - access("T2"."OBJECT_ID"=1)
3- Using Parallel Execution
---------------------------
Plan hash value: 1540970267-------------------采用并行的执行计划
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65702 | 5902K| 592 (2)| 00:00:08 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 65702 | 5902K| 592 (2)| 00:00:08 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 65702 | 5902K| 592 (2)| 00:00:08 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 256 | 20224 | 294 (2)| 00:00:04 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 256 | 20224 | 294 (2)| 00:00:04 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 256 | 20224 | 294 (2)| 00:00:04 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| T2 | 256 | 20224 | 294 (2)| 00:00:04 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 257 | 3341 | 297 (2)| 00:00:04 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 257 | 3341 | 297 (2)| 00:00:04 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 257 | 3341 | 297 (2)| 00:00:04 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS FULL| T1 | 257 | 3341 | 297 (2)| 00:00:04 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
7 - filter("T2"."OBJECT_ID"=1)
11 - filter("T1"."OBJECT_ID"=1)
-------------------------------------------------------------------------------
五：执行优化建议
SQL> create index USER1.IDX$$_086B0001 on USER1.T1("OBJECT_ID");
Index created.
Elapsed: 00:00:27.51
SQL> create index USER1.IDX$$_086B0002 on USER1.T2("OBJECT_ID","OBJECT_NAME");
Index created.
Elapsed: 00:01:46.03
重新执行再看下时间和执行计划：
SQL> select t1.object_id,t2.object_name from t1,t2 where
2 t1.object_id=t2.object_id and t1.object_id=1;
no rows selected
Elapsed: 00:00:00.07---之前是0.44
Execution Plan
----------------------------------------------------------
Plan hash value: 132872914
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 92 | 3 (0)| 00:
00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 92 | 3 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_086B0001 | 1 | 13 | 2 (0)| 00:
00:01 |
| 3 | BUFFER SORT | | 1 | 79 | 1 (0)| 00:
00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_086B0002 | 1 | 79 | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"=1)
4 - access("T2"."OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
17 recursive calls
1 db block gets
330 consistent gets
10 physical reads
0 redo size
410 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以发现走了索引，而且时间也变快了。
六：删除优化任务
SQL> exec dbms_sqltune.drop_tuning_task('sql_maomao');
PL/SQL procedure successfully completed.
七：小结
实验中由于我的sql比较简单，所以STA可以很好的给出建议，但如果是复杂的sql，那可能就没有那么明显的建议了，即使他给出建议，其实也不一定会好用。除了这个实验，我又做了一个相对复杂的sql，这个sql呢，我已经调过，就是将其中一个表上的索引去掉，执行就很快，但cost很高，但最后STA给出的建议是在创建4个索引在不同的表上。最后我按照他的建议创建了索引，但速度还是没有改变，反而更慢了。STA给出的建议只是在原有sql的基础上给出，并不会只能的去改变sql的写法，如果这里有hint的话，它也许会通过减少hint来给出优化。虽然它相对智能，但还是没有人判断的好。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1064236/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24500180/viewspace-1064236/
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
