[20130626]11GR2 SQL Tuning Advisor.txt
[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了.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-764886/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql tuningSQL
- sql tuning set/sql tuning advisor(待完善)SQL
- sql tuning setSQL
- 熟悉SQL tuningSQL
- sql tuning task和sql profileSQL
- Automatic SQL Tuning and SQL ProfilesSQL
- 【筆記】SQL tuning筆記SQL
- Oracle SQL Perfomance TuningOracleSQL
- SQL TUNING ADVISORSQL
- Automatic SQL Tuning 原理SQL
- sql tuning之變通SQL
- SPA_SQL Performance Analyzer_SQL Tuning SetSQLORM
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- 快速SQL TUNING——1分鐘搞定超慢SQLSQL
- oracle實用sql(3)--sql tuning advisorOracleSQL
- SQL Tuning Advisor簡介SQL
- sql tuning 第一章SQL
- 轉貼_push_subq_sql tuningSQL
- oracle sql tuning 14 --10046OracleSQL
- 二個SQL tuning例子(使用case)SQL
- 【筆記】sql tuning advidor筆記SQL
- ORACLE SQL Tuning Health-Check(SQLHC)OracleSQL
- sql tuning advisor(STA) 建議 建立sql profileSQL
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- Sql Tuning Advisor 使用方法SQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- oracle sql tuning 2--調優工具OracleSQL
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- Oracle SQL最佳化之sql tuning advisor(STA)OracleSQL
- sql監控與調優(sql monitoring and tuning) （轉載）SQL
- Automatic SQL Tuning in Oracle Database 11gSQLOracleDatabase
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Guideline of SQL Tuning AdvisorGUIIDESQL