sql tuning set/sql tuning advisor(待完善)

建立sql tuning set

 

sql tuning set是效能調整中重要的一個環節。以下我們先看該功能的使用，再來說明其用途。

 

（1）建立：

[oracle@localhost ~]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 22 21:48:06 2012

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> conn scott/scott

Connected.

SQL> begin

  2  dbms_sqltune.create_sqlset(sqlset_name=>'my_sql_tuningset',

  3  description=>'i/o intensive workload');

  4  end;

  5  /

 

PL/SQL procedure successfully completed.

 

(2)顯示：

SQL>select * from table(dbms_sqltune.select_sqlset('my_sql_tuningset','(disk_reads/buffer_gets)>=0.75'));

 

no rows selected

 

SQL> select * from dba_sqlset;

 

ID    NAME          OWNER       DESCRIPTI     CREATED   LAST_MODI STATEMENT_COUNT

---------- ---------------------- -------------- ---------------------------  ---------------- --------------------- ---------------------------

     1 my_seq_tuning_set   SCOTT  I/O intensive workload 22-DEC-12    22-DEC-12              0

     2 my_sql_tuningset    SCOTT  i/o intensive workload  22-DEC-12   22-DEC-12              0

注：ID為1的記錄是以前建立的。

 

這時，dba_sqlset_statements和dba_sqlset_binds表中並沒有相關記錄

 

（3）刪除：

SQL> begin  

  2   dbms_sqltune.drop_sqlset(sqlset_name=>'my_seq_tuning_set');

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

SQL> select * from dba_sqlset;

--再次查詢

ID    NAME          OWNER       DESCRIPTI     CREATED   LAST_MODI STATEMENT_COUNT

---------- ---------------------- -------------- ---------------------------  ---------------- --------------------- ---------------------------

     2 my_sql_tuningset    SCOTT  i/o intensive workload  22-DEC-12   22-DEC-12              0

 

 

（4）載入：

load_sqlset過程將sql語句放入sql tuning set（簡稱STS）中。

可以放入STS的資源包括負載報告（workload repository）、其他STS或者共享sql區（shared SQL area）。

對於負載報告和STS，預定義的表函式可以從資源表中選擇列並放入新的STS中。

 

下面這個例子中，呼叫名字為peak baseline的AWR基線載入到my_sql_tuning_set中。

資料按照花費時間排序，只選擇前三十個sql語句。

未操作：

DECLARE

baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;--宣告遊標型別

BEGIN

 

OPEN baseline_cursor FOR--將負載報告內容放入該遊標中

SELECT VALUE(p)

FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

'peak baseline',NULL, NULL,'elapsed_time',NULL, NULL, NULL,30)) p;--

 

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sql_tuningset',

populate_cursor => baseline_cursor);--將負載報告的內容放入已經定義的STS中

 

END;

/

 

修改：

BEGIN

DBMS_SQLTUNE.DELETE_SQLSET(

sqlset_name => 'my_sql_tuning_set',

basic_filter => 'executions < 50');

END;

/

 

轉換：

BEGIN

DBMS_SQLTUNE.create_stgtab_sqlset(

table_name => 'my_10g_staging_table',

schema_name => 'dba1',

db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );

END;

/

 

BEGIN

DBMS_SQLTUNE.pack_stgtab_sqlset(

sqlset_name => 'my_sts',

sqlset_owner => 'hr',

staging_table_name => 'my_10g_staging_table',

staging_schema_owner => 'dba1',

db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );

END;

/

 

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

sqlset_name => '%',

replace => TRUE,

staging_table_name => 'my_10g_staging_table');

END;

/

 

Sql tuning advisor

我們都知道，對於一條提交的語句，oracle會使用優化器來生成執行計劃。而優化器其實是有兩種模式的。在正常模式下，優化器編譯sql語句並生成執行計劃，時間上的限制是非常嚴格的，通常不到一秒鐘完成；

在調優模式下，優化器會進行額外的操作來判斷接收到的語句是否還有優化的空間，這時候優化器輸出的不是執行計劃，而是一系列的操作以及這些操作之間的關係，目的在於產生更好的執行計劃。在調優模式下，優化器可以被稱為自動調優優化器。

輸入與輸出總是成比例的，既然使用調優模式希望生成更優的計劃，就會花費更多的時間和資源，而且每次操作都是硬解析。因此，不要在系統上輕易開啟調優模式，生產系統幾乎不允許。可以在測試環境下比較後再上線。

 

自動效能優化器主要通過統計資訊的分析、sql profiling、執行路徑分析、sql結構分析以及當前與歷史效能資料的對比分析來優化效能。

 

優化器是根據統計資料來生成執行計劃的，因此需要保證統計資料的準確性和完整性。自動效能優化器會在獲取語句後判斷統計資訊是否足夠、“新鮮”，並根據判斷來決定是否需要收集統計資訊、使用輔助統計資料。

Sql profile是一條語句的一系列輔助資料（可能是一個表或一個索引的統計資料），資料庫可以使用這些資訊來改善執行計劃。

執行路徑方面，如果自動效能優化器發現建立一個新的索引能夠提高查詢效能，就會推薦建立索引。但由於自動效能優化器不能對建立索引對整個sql負載做出評價，推薦不一定準確，因此推薦在正常負載情況下（有代表性的時段）對語句執行sql access advisor，該工具能根據建立新索引對整個sql的影響來做出推薦。

語句結構方面，oracle判斷語句在語法、語義上是否有改進的可能。比如對不可能出現重複值的兩個查詢使用union，效能優化器可能就會建議改成使用union all。

如果優化器發現新生成的計劃與原來的計劃不一樣，就會根據其成本做出選擇。如果不希望優化器這麼做，而是告訴它出現新的執行計劃時仍使用原來的執行計劃，可以通過基線來實現。

 

自動效能調優是在系統維護視窗（特定的時間段）對AWR報告中的高負載的sql自動進行的，維護視窗的起止時間、頻率等資訊是可以手工調節的，通常不會多於一小時。對於效能相差三倍以上的語句，自動sql優化建議器給出提示，如果這還是sql profiles造成的效能差異，且accept_sql_profiles設定為true，則自動建立profile。

 

自動sql調優在維護視窗自動執行，啟停方法為：

啟用：

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

禁用：

DBMS_AUTO_TASK_ADMIN package:

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

注：dbms_auto_sqltune包的執行需要dba許可權，

EXECUTE_AUTO_TUNING_TASK的執行只能是sys使用者，版本要求為11.2.0.2及以上

 

檢視最近一次語句分析後，所有的sql語句建議：

VARIABLE my_rept CLOB;

BEGIN

:my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(

begin_exec => NULL,

end_exec => NULL,

type => 'TEXT',

level => 'TYPICAL',

section => 'ALL',

object_id => NULL,

result_limit => NULL);

END;

/

PRINT :my_rept

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-753168/，如需轉載，請註明出處，否則將追究法律責任。