[20130626]11GR2 SQL Tuning Advisor.txt
11GR2加入了sql tuning advisor,缺省是打开的,我发现一些dba建议安装11G后,直接关闭它,好像因为消耗资源.
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
实际上,如果能看看里面信息,对于优化还是有许多帮助的.
--可以获得信息.
--如果想知道如何建议,执行如下:
--我喜欢显示信息是英文的,定义环境变量NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.
--抽取一段里面的信息,我看到都是访问系统表的一些信息:
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 6465
Schema Name: SCOTT
SQL ID : cbmzd29dvsx8j
SQL Text : SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 AND EXISTS
(SELECT owner# FROM sys.obj$ o
WHERE o.OWNER# = u.USER# AND (o.TYPE# 5)
AND NOT ((o.TYPE# = 2) AND (BITAND(o.flags, 2) = 2))
AND NOT ((o.TYPE# = 1) AND (BITAND(o.flags, 2) =
2))) ORDER BY 1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
The SQL profile was not automatically created because auto-creation was
disabled. Set task parameter ACCEPT_SQL_PROFILES to TRUE to enable
auto-creation.
Recommendation (estimated benefit: 92.21%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'SYS_AUTO_SQL_TUNING_TASK', object_id => 6465, 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.
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
--也许是这些消耗资源!!要做10次以上.试想如果语句本来就很慢,再这样做有点可怕!
-------------------------------------------------------------------------------
3.拿上面的例子做一个测试:
按照建议执行如下:
--可以发现逻辑读147,明显减少.sql显示使用sql profile来控制执行计划.
--至少这些对于优化经验不足的人还是有帮助,并且能获得一些oracle的建议,机器毕竟比人聪明.
--我越来越喜欢11G了.
11GR2加入了sql tuning advisor,缺省是打开的,我发现一些dba建议安装11G后,直接关闭它,好像因为消耗资源.
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
实际上,如果能看看里面信息,对于优化还是有许多帮助的.
SQL> select client_name, status,attributes,service_name from dba_autotask_client;
CLIENT_NAME STATUS ATTRIBUTES SERVICE_NAME
---------------------------------------- -------- ------------------------------------------------------------ --------------
auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
select * from dba_advisor_findings where task_name like 'SYS_AUTO_SQL_TUNING_TASK';
--可以获得信息.
--如果想知道如何建议,执行如下:
--我喜欢显示信息是英文的,定义环境变量NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.
spool suggest.txt
select dbms_sqltune.report_auto_tuning_task(
(select min(execution_name) from dba_advisor_findings
where task_name like 'SYS_AUTO_SQL_TUNING_TASK'),
(select max(execution_name) from dba_advisor_findings
where task_name like 'SYS_AUTO_SQL_TUNING_TASK')
) from dual;
spool off
--抽取一段里面的信息,我看到都是访问系统表的一些信息:
-------------------------------------------------------------------------------
SQLs with SQL Profile Findings Ordered by Maximum Benefit, Object ID
-------------------------------------------------------------------------------
execution name object ID SQL ID benefit
------------------------------ ---------- ------------- --------
EXEC_10224 6517 fpf9ztf0yw0fw 96.91%
EXEC_10177 6462 g10n36gghz1rr 93.80%
EXEC_10177 6465 cbmzd29dvsx8j 92.21%
EXEC_10177 6479 7ptuqb2nxxzrv 92.02%
EXEC_10177 6456 1s3w0r29yv8cv 90.92%
EXEC_10112 6437 d0cdpbm2v4ubu 89.95%
EXEC_10177 6466 2m5atu0grvtmq 84.04%
EXEC_10112 6442 0kyb2cb1ra5aq 84.02%
EXEC_10177 6469 c9fckvj9d4muu 82.77%
EXEC_10112 6439 72yf8srrpkwmh 80.15%
....
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 6465
Schema Name: SCOTT
SQL ID : cbmzd29dvsx8j
SQL Text : SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 AND EXISTS
(SELECT owner# FROM sys.obj$ o
WHERE o.OWNER# = u.USER# AND (o.TYPE# 5)
AND NOT ((o.TYPE# = 2) AND (BITAND(o.flags, 2) = 2))
AND NOT ((o.TYPE# = 1) AND (BITAND(o.flags, 2) =
2))) ORDER BY 1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
The SQL profile was not automatically created because auto-creation was
disabled. Set task parameter ACCEPT_SQL_PROFILES to TRUE to enable
auto-creation.
Recommendation (estimated benefit: 92.21%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'SYS_AUTO_SQL_TUNING_TASK', object_id => 6465, 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): .065773 .000795 98.79 %
CPU Time (s): .06569 .000799 98.78 %
User I/O Time (s): .000178 0 100 %
Buffer Gets: 1973 154 92.19 %
Physical Read Requests: 10 0 100 %
Physical Write Requests: 0 0
Physical Read Bytes: 82739 0 100 %
Physical Write Bytes: 0 0
Rows Processed: 28 28
Fetches: 28 28
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.
--也许是这些消耗资源!!要做10次以上.试想如果语句本来就很慢,再这样做有点可怕!
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 977673319
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 40 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | USER$ | 2 | 40 | 2 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | I_USER2 | 2 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 10 | 3 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | I_OBJ4 | 1518 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("U"."TYPE#"=1)
filter("U"."TYPE#"=1 AND EXISTS (SELECT /*+ PUSH_SUBQ INDEX_SS ("O"
"I_OBJ4") */ 0 FROM "SYS"."OBJ$" "O" WHERE ("O"."TYPE#"1 OR
BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2 OR BITAND("O"."FLAGS",2)2) AND
"O"."OWNER#"=:B1 AND "O"."TYPE#"5))
4 - filter(("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2 OR
BITAND("O"."FLAGS",2)2))
5 - access("O"."OWNER#"=:B1)
filter("O"."OWNER#"=:B1 AND "O"."TYPE#"5)
2- Using SQL Profile
--------------------
Plan hash value: 2080390714
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 82 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 20 | 82 (2)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | USER$ | 39 | 780 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 10 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_OBJ2 | | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "SYS"."OBJ$" "O" WHERE "O"."OWNER#"=:B1
AND ("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2 OR
BITAND("O"."FLAGS",2)2) AND "O"."TYPE#"5))
3 - filter("U"."TYPE#"=1)
4 - filter(("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2
OR BITAND("O"."FLAGS",2)2))
5 - access("O"."OWNER#"=:B1)
filter("O"."TYPE#"5)
3.拿上面的例子做一个测试:
SQL> set autot traceonly ;
SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 AND EXISTS
(SELECT owner# FROM sys.obj$ o
WHERE o.OWNER# = u.USER# AND (o.TYPE# 5)
AND NOT ((o.TYPE# = 2) AND (BITAND(o.flags, 2) = 2))
AND NOT ((o.TYPE# = 1) AND (BITAND(o.flags, 2) =
2))) ORDER BY 1;
28 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 977673319
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 61 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 20 | 61 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | USER$ | 2 | 40 | 2 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | I_USER2 | 2 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 10 | 3 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | I_OBJ4 | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("U"."TYPE#"=1)
filter("U"."TYPE#"=1 AND EXISTS (SELECT 0 FROM "SYS"."OBJ$" "O" WHERE
("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2 OR
BITAND("O"."FLAGS",2)2) AND "O"."OWNER#"=:B1 AND "O"."TYPE#"5))
4 - filter(("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2 OR
BITAND("O"."FLAGS",2)2))
5 - access("O"."OWNER#"=:B1)
filter("O"."OWNER#"=:B1 AND "O"."TYPE#"5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2062 consistent gets
0 physical reads
0 redo size
883 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
28 rows processed---逻辑读2062.
按照建议执行如下:
execute dbms_sqltune.accept_sql_profile(task_name =>'SYS_AUTO_SQL_TUNING_TASK', object_id => 6465, task_owner =>'SYS', replace => TRUE);
Execution Plan
----------------------------------------------------------
Plan hash value: 2080390714
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 82 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 20 | 82 (2)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | USER$ | 39 | 780 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 10 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_OBJ2 | | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "SYS"."OBJ$" "O" WHERE "O"."OWNER#"=:B1
AND ("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2 OR
BITAND("O"."FLAGS",2)2) AND "O"."TYPE#"5))
3 - filter("U"."TYPE#"=1)
4 - filter(("O"."TYPE#"1 OR BITAND("O"."FLAGS",2)2) AND ("O"."TYPE#"2
OR BITAND("O"."FLAGS",2)2))
5 - access("O"."OWNER#"=:B1)
filter("O"."TYPE#"5)
Note
-----
- SQL profile "SYS_SQLPROF_013f7fba46910000" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
157 consistent gets
0 physical reads
0 redo size
883 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
28 rows processed
--可以发现逻辑读147,明显减少.sql显示使用sql profile来控制执行计划.
--至少这些对于优化经验不足的人还是有帮助,并且能获得一些oracle的建议,机器毕竟比人聪明.
--我越来越喜欢11G了.
