使用dbms_advisor來執行sql access advisor
使用dbms_advisor來執行sql access advisor主要有以下幾個步驟:
1.建立任務
2.定義工作量
3.生成建議
4.檢視與實現建議
步驟1 建立任務
在任務建議被生成之前，必須建立一個任務。任務很重要因為它是與建議程式相關的所有資訊的居住 地，包括建議處理的結果。如果使用dbms_advisor.quick_tune過程，那麼會自動建立任務。在所有 其它的情況下，必須手動執行dbms_advisor.create_task過程來建立任務。透過使用dbms_adviosr.set_task_parameter過程來定義引數可以控制任務的執行。
步驟2 定義工作量
工作量是SQL Access Advisor的主要輸入，並且它由一個或多個SQL語句組成，並且加上用來完全描 述每個SQL語句的各種統計資訊與屬性。如果工作量包含了一個目標業務系統的所有SQL語句，那麼這 個工作量可以認為是一個完全工作量，如果工作量只包含了一部分SQL語句，那麼就認為是部分工作 量。完全與部分工作量之間的差別是，對於完全工作量，SQL Access Advisor如果發現有些現有的物 化檢視與索引沒有被有效的使用，可能會建議刪除特定的現有物化檢視與索引。
通常來說，SQL Access Adviosr使用工作量作為所有分析活雲貴的基礎。雖然工作量可能包含各種各 樣的SQL語句，它將根據特定的統計資訊，業務重要性或統計資訊與業務重要性的組合來仔細排列條 目。這種排列是關鍵，因為它能讓SQL Access Advisor使用更少的業務影響來處理最重要的SQL語句
對於資料集合可以認為是一個有效的工作量，SQL Access Advisor可能需要存在特定的屬性。如果丟 失某些條目雖然可以執行分析，那麼建議的質量將會大幅度的降低。例如，SQL Access Advisor請求 一個包含SQL查詢與誰執行這個查詢的工作量。所有其它屬性都是可選項，然而，如果工作量還包含 了I/O與CPU資訊，那麼SQL Access Advisor可能會更好的對語句評估當前的效率。工作量作為一個單 獨的物件被儲存，它是由dbms_advisor.create_sqlwkld過程所建立，並且可以很容易的在多個指導 任務之間共享記憶體。因為工作量是獨立的，它必須使用dbms_advisor.add_sqlwkld_ref過程來連結到 一個指導任務。一旦這種連結被建立，直到所有指導任務刪除了對工作量的依賴性之前，這種連結是 不能被刪除的。當一個父指導任務被刪除或者當工作量引用由使用者執行 dbms_advisor.delete_sqlwkld_ref過程手動從指導任務中刪除後，工作量引用也將被冊子。
也可以在不使用工作量的情況下使用SQL Access Advisor，然而，為了最佳的結果，工作量必須以用 戶提供的表形式被提供，SQL Tuning Set或從SQL Cache中匯入。如果沒有提供工作量，SQL Access Advisor可以生成並使用基於你使用者方案所定義規模的假想工作量。
一旦工作量被載入到檔案庫或在生成建議時載入，可以對工作量應用過濾來限制分析什麼。這提供了 一種基於不同工作量生成不同建議的能力。
建議處理與工作量的定製是由SQL Access Advisor引數來控制的。引數在任務或工作量物件的生命周 期內仍然是生效的。當使用dbms_advisor.set_task_parameter過程設定引數後，直到重新設定之前 引數值是不會發生改變的。
步驟3 生成建議
一旦任務建立並且工作量被連結到任務，且還設定了合適的引數，可以使用 dbms_advisor_execute_task過程來生成建議。這些建議會儲存在SQL Access Advisor檔案庫中。
建議處理機制會生成一些建議並且每個建議將由一個或多個操作組成。例如，建立物化檢視，然後為 了收集統計資訊而對其進行分析。
任務建議可能是一個複雜解決方案的簡單建議，它要求實現一組資料庫物件，比如索引，物化檢視與 物化檢視日誌。當一個指導任務被執行時，它將仔細分析收集的資料與使用者調整的任務引數。SQL Access Advisor將試圖基於內建的機制來格式化解決方案。解決方案然後會被精緻的以結構化建議的 形式被儲存可以由使用者檢視與實現。
步驟4 檢視與實現建議
檢視建議有兩種方式:使用目錄檢視或使用dbms_advisor.get_task_script過程來生成指令碼。不是所有的建議你都需要接受，並且可以在建議指令碼中標記你要接受的建議。最後的步驟就是實現建 議然後驗證查詢效能是否有所提高。
SQL Access Advisor檔案庫
SQL Access Advisor所需要與所生成的所有資訊都儲存在SQL Access Advisor檔案庫中，它是資料庫 資料字典的一部分。使用檔案庫有以下優點:
.為SQL Access Advisor收集完整的工作量
.支援歷史資料
.由伺服器進行管理
使用SQL Access Advisor需要的系統許可權
需要有advisor許可權來管理或使用SQL Access Advisor。當處理一個工作量時，SQL Access Advisor 為了試圖驗證每個語句需要識別引用的表與列。透過處理每個語句就像原始使用者執行語句一樣來完成 驗證。如果使用者對於特定的表沒有select許可權，SQL Access Advisor將跳過語句所引用的表。這可能 造成許多語句從分析操作變成了執行操作。如果SQL Access Advisor在一個工作量中執行所有語句， 工作量將會失效並且SQL Access Advisor將會返回如下資訊:
QSM-00774, there are no SQL statements to process for task TASK_NAME
為了避免丟失關鍵的工作量查詢，當前資料庫使用者必須有對被分析的物化檢視所引用的目標表有 select許可權。對於這些表，select許可權不能透過角色被獲得。
設定任務與模板
1.建立任務
任務是你所定義需要分析什麼以及分析結果將儲存在什麼地方。使用者可以建立任意數量的任務，每個 有特定的設定。都是基於相同指導任務模型並且共享相同檔案庫的。使用dbms_advisor.create_task 過程建立任務的語法如下:
DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_id OUT NUMBER, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL); DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL);
下面是建立任務的一示例:
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name:='JYTASK';
PL/SQL procedure successfully completed
task_name
---------
JYTASK
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:task_id,:task_name);
PL/SQL procedure successfully completed
task_id
---------
45354
task_name
---------
JYTASK
使用模板
當對一個任務或工作量識別一個空閒配置後，這個配置可以被作為模板進行儲存。將來的任務可以基 於這個模板進行建立。
透過設定模板，當執行調整分析時可以節省時間。還能對業務操作制定一種合 適的調整分析。
為了使用模板建立任務，當建立新任務時需要指定要使用的模板。這時，SQL Access Advisor將從模 板中複製資料與引數到新建立的任務中。當建立任務時或使用dbms_advisor.update_task_attribute 過程來設定模板屬性時可以將現有的任務設定為模板。
為了使用任務作為模板，可以告訴SQL Access Advisor當建立新任務時指定要使用的任務。在這時， SQL Access Advisor將複製任務模板的資料與引數設定到新建立的任務。可以透過設定模板屬性將現 有的任務設定為模板。
工作量物件也可以用來作為模板來建立新的工作量物件。
建立模板
1.建立一個名叫my_template的模板
SQL> variable template_id number;
SQL> variable template_name varchar2(255);
SQL> execute :template_name := 'my_template';
PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:template_id,:template_name, is_template => 'true');
PL/SQL procedure successfully completed
template_id
---------
45357
template_name
---------
my_template
2.設定模板引數
SQL> -- set naming conventions for recommended indexes/mvs SQL> execute dbms_advisor.set_task_parameter(:template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_'); PL/SQL procedure successfully completed template_name --------- my_template SQL> -- set default tablespace for recommended indexes/mvs SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_INDEX_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_MVIEW_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template
3.使用模板來建立任務
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name := 'mytask';
PL/SQL procedure successfully completed
task_name
---------
mytask
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id,:task_name, template=>'my_template');
PL/SQL procedure successfully completed
task_id
---------
45376
task_name
---------
mytask
下面的例子使用預先定義的模板SQLACCESS_WAREHOUSE來建立任務
execute dbms_advisor.create_task('SQL Access Advisor', :task_id, :task_name, template=>'SQLACCESS_WAREHOUSE');
管理工作量
管理工作量主要從以下方面進行:
.工作量物件
.使用工作量
.連結任務與工作量
.定義工作量內容
.向工作量增加SQL語句
.從工作量中刪除SQL語句
.改變工作量中的SQL語句
.維護工作量
.刪除工作量
工作量物件
因為工作量作為單獨的工作量物件被儲存，它可以很容易的在多個指導任務之間被共享。一旦一個工 作量物件被一個指導任務所引用，那麼直到所有指導任務刪除它們所依賴的資料之前，工作量是不能 被刪除或修改的。當父指導任務被刪除或者當工作量引用被手動從指導任務中被刪除時，工作量引用 將會被刪除。
當工作量可以使用時，SQL Access Advisor會執行的最好。SQL Access Workload檔案庫能夠儲存多 個工作量，因此真實世界資料倉儲或事務處理環境中的不同使用者可以在很長一段時間內檢視並且可以 踴躍資料庫的重啟。
在工作量的真實SQL語句被定義之前，工作量必須使用dbms_advisor.create_sqlwkld過程來進行建立
然後，使用合適的import_sqlwkld過程來載入工作量。一個特定的工作量可以透過呼叫 dbms_advisor.delete_sqlwkld過程來進行刪除。為了刪除當前使用者的所有工作量，可以在呼叫 dbms_advisor.delete_sqlwkld過程時傳遞一個常量advisor_all或%。
使用工作量
dbms_advisor.create_sqlwkld過程用來建立工作量並且它必須在執行任何其它工作量操作之前存在 ，比如匯入或更新SQL語句。透過工作量名來識別工作量，因此應該定義一個唯一名稱來標識操作。
語法如下:
dbms_advisor.create_sqlwkld ( workload_name in out varchar2, description in varchar2 := null, template in varchar2 := null, is_template in varchar2 := 'false');
建立工作量
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'myworkload'; PL/SQL procedure successfully completed workload_name --------- myworkload SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload'); PL/SQL procedure successfully completed workload_name --------- myworkload
使用模板來建立工作量
1.建立變數
SQL> variable template_id number; SQL> variable template_name varchar2(255);
2.建立名叫my_wk_template的模板
SQL> execute :template_name := 'my_wk_template'; PL/SQL procedure successfully completed template_name --------- my_wk_template SQL> execute dbms_advisor.create_sqlwkld(:template_name, is_template=>'true'); PL/SQL procedure successfully completed template_name --------- my_wk_template
3.設定模板引數。下面設定過濾只有insur_changde方案中的表被最佳化:
SQL> -- set USERNAME_LIST filter to insur_changde SQL> execute dbms_advisor.set_sqlwkld_parameter(:template_name, 'USERNAME_LIST', 'insur_changde'); PL/SQL procedure successfully completed template_name --------- my_wk_template
4.使用模板來建立工作量
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'myworkload'; PL/SQL procedure successfully completed workload_name --------- myworkload SQL> execute dbms_advisor.create_sqlwkld (:workload_name, 'this is my first workload', 'my_wk_template'); PL/SQL procedure successfully completed workload_name --------- myworkload
將任務與工作量進行連結
在建議程式開始之前，任務必須被連結到一個工作量。可以透過呼叫 dbms_advisor.add_sqklwkld_ref過程，透過使用它們的名字來將任務與工作量進行連結。這個過程 會在指導任務與工作量之間建立一個連結。一旦在指導任務與工作量之間建立了連結，工作量將會受 保護從而避免被刪除。語法如下:
dbms_advisor.add_sqlwkld_ref (task_name in varchar2,workload_name in varchar2);
下面將任務mytask與工作量myworkload進行連結.
SQL> execute dbms_advisor.add_sqlwkld_ref('mytask', 'myworkload');
PL/SQL procedure successfully completed
定義工作量的內容
一旦工作量被建立，它必須要載入一些資訊。理想情況下，一個工作量將由SQL語句（除非它是一個 假想的工作量）組成，它們是在資料庫中正在執行的SQL語句。SQL Access Advisor可以從以下來源 獲得工作量:
.SQL調優集
.載入使用者定義的工作量
.載入SQL快取工作量
.使用假想工作量
.使用匯總的9i工作量
SQL調優集
SQL調優集是工作量檔案中的一種工作量。可以使用SQL調整集作為SQL Access Advisor的工作量透過 dbms_advisor.import_workload_sts過程進行匯入。下面是使用語法:
dbms_advisor.import_sqlwkld_sts (workload_name in varchar2, sts_owner in varchar2, sts_name in varchar2, import_mode in varchar2 := 'new', priority in number := 2, saved_rows out number, failed_rows out number); dbms_advisor.import_sqlwkld_sts (workload_name in varchar2, sts_name in varchar2, import_mode in varchar2 := 'new', priority in number := 2, saved_rows out number, failed_rows out number);
在工作量被收集並且過濾語句後，SQL Access Advisor使用工作量中的DML語句來計算使用統計資料 ，下面的例子使用SQL調優集建立一個名叫my_sts_workload的工作量
SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 cur dbms_sqltune.sqlset_cursor;
3 begin
4 open cur for
5 select value(p)
6 from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE'' and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and command_type<>2',
7 null,
8 null,
9 null,
10 null,
11 1,
12 null,
13 'all')) p;
14 dbms_sqltune.load_sqlset(sqlset_name => 'MY_STS_WORKLOAD',
15 populate_cursor => cur);
16 end;
17 /
PL/SQL procedure successfully completed
SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'my_sts_workload';
PL/SQL procedure successfully completed
sqlsetname
---------
my_sts_workload
SQL> execute :workload_name := 'my_workload';
PL/SQL procedure successfully completed
workload_name
---------
my_workload
SQL> execute dbms_advisor.create_sqlwkld (:workload_name);
PL/SQL procedure successfully completed
workload_name
---------
my_workload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW', 1,:saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
my_workload
sqlsetname
---------
MY_STS_WORKLOAD
saved_stmts
---------
1219
failed_stmts
---------
13
載入使用者定義工作量
為了載入使用者定義工傷腦筋量，使用dbms_advisor.import_sqlwkld_user過程。這個過程從使用者結構 表或檢視中收集應用程式工作量並將其儲存在指導檔案庫中。owner_name與table_name兩個引數識別 表是從那種型別的工作量中獲得的。對於工作量儲存在那個方案，表名，或有多少個使用者定義的表存 在都沒有限制。唯一的要求就是使用者表的結構必須與USER_WORKLOAD相關，並且使用者要對工作量表或 檢視有select訪問許可權。語法如下:
dbms_advisor.import_sqlwkld_user ( workload_name in varchar2, import_mode in varchar2 := 'NEW', owner_name in varchar2, table_name in varchar2, saved_rows out number, failed_rows out number);
下面的例子載入之前建立的工作量MYWORKLOAD，使用使用者表SH.USER_WORKLOAD。假設表 USER_WORKLOAD已經載入了SQL語句，並且它的結構與USER_WORKLOAD Table Format相符
variable saved_stmts number; variable failed_stmts number; execute dbms_advisor.import_sqlwkld_user( 'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);
載入SQL Cache工作量
可以使用dbms_advisor.import_sqlwkld_sqlcache過程來獲得SQL Cache工作量。在呼叫這個過程的 時候，SQL Cache中的當前內容將會被分析並且載入到工作量中。 dbms_advisor.import_sqlwkld_sqlcache過程從SQL Cache中載入SQL工作量，語法如下:
dbms_advisor.import_sqlwkld_sqlcache ( workload_name in varchar2, import_mode in varchar2, priority in number := 2, saved_rows out number, failed_rows out number);
下面的例子從SQL Cache中載入之前建立的工作量MYWORKLOAD。載入工作量語句的優先順序為2:
variable saved_stmts number;
variable failed_stmts number;
execute dbms_advisor.import_sqlwkld_sqlcache('MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);
SQL Access Advisor可以從SQL Cache中檢索工作量資訊。如果收集的資料是從例項引數 cursor_sharing設定為similar或force的伺服器中所檢索到的，那麼使用文字值的查詢將會被轉換為 包含系統生成變數的語句。如果使用SQL Access Advisor來建議物化檢視，那麼伺服器應該將引數 cursor_sharing設定為exact，因此有where子句的物化檢視會被建議。
使用假想工作量
在許多情況下，應用程式工作量是不存在的。 在這種情況下，SQL Access Advisor可以檢查當前邏 輯方案設計與基於表之間所定義的關係的格式化建議。這種型別的工作量也稱為假想工作量。SQL Access Advisor可以產生一組初始化建議，並且將會成為最佳化應用程式的堅實基礎。
使用假想工作量的優點:
.只需要方案與表關係
.對於假定場景建模是有效的
使用假想工作量的缺點:
.只有定義了維度才能工作
.在建議訪問結構方面沒有提供關於影響DML操作的任何資訊
.不一定是完整的
為了成功匯入一個假想工作量，目標方案必須包含維度資訊。可以使用 dbms_advisor.import_sqlwkld_schema過程，語法如下:
dbms_advisor.import_sqlwkld_schema ( workload_name in varchar2, import_mode in varchar2 := 'NEW', priority in number := 2, saved_rows out number, failed_rows out number);
下面的例子建立一個名叫SCHEMA_WKLD的假想工作量，將valid_table_list設定為sh,並呼叫 dbms_advisor.import_sqlwkld_schema過程來生成假想工作量。
variable workload_name varchar2(255); variable saved_stmts number; variable failed_stmts number; execute :workload_name := 'SCHEMA_WKLD'; execute dbms_advisor.create_sqlwkld(:workload_name); execute dbms_advisor.set_sqlwkld_parameter(:workload_name,VALID_TABLE_LIST, 'SH'); execute dbms_advisor.import_sqlwkld_schema(:workload_name, 'NEW', 2, :saved_stmts, :failed_stmts);
當使用dbms_advisor.import_sqlwkld_schema過程時，valid_table_list引數不能包含萬用字元比如 SCO%或SCOTT.EMP%。只支援唯一的萬用字元格式SCOTT.%，它將指定使用者方案中的所有表。
使用9i彙總指導工作量
使用9i彙總工作量可以建立工作量。這些工作量可以被SQL Access Advisor使用，透過使用 dbms_advisor.import_sqlwld_sumadv過程來進行匯入。為了使用這個過程，必須知道Oracle 9i的工 作量ID。
這個過程從彙總指導工作量中收集SQL工作量。這個過程的目標是為了幫助Oracle 9i彙總指導使用者遷 移到SQL Access Advisor。語法如下:
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, sumadv_id IN NUMBER, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
下面的例子從Oracle 9i彙總指導工作量來建立一個SQL工作量。oracle 9i的工作量workload_id為 777
1.建立一些變數
variable workload_name varchar2(255); variable saved_stmts number; variable failed_stmts number;
2.建立工作量WKLD_9I
execute :workload_name := 'WKLD_9I'; execute dbms_advisor.create_sqlwkld(:workload_name);
3.從Oracle 9i彙總指導工作量匯入SQL工作量
execute dbms_advisor.import_sqlwkld_sumadv (:workload_name, 'NEW', 2, 777, :saved_stmts, :failed_stmts);
SQL Access Advisor工作量引數
一個SQL工作量可以透過dbms_advisor.set_sqlwkld_parameter過程設定一個或多個引數來在載入時 進行過濾操作。
下面的例子設定了SQL工作量引數。將SQL_LIMIT設定為3,ORDER_LIST設定為OPTIMIZER_COST。當匯入 工作量時，這意味著，語句將透過OPTIMIZER_COST來進行排序並且前三個語句將會被保留。
-- Order statements by OPTIMIZER_COST
execute dbms_advisor.set_sqlwkld_parameter ('MYWORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST');
-- Max number of statements 3
execute dbms_advisor.set_sqlwkld_parameter('MYWORKLOAD', 'SQL_LIMIT', 3);
向工作量增加SQL語句
一種替匯入工作量的方法是手動指定SQL語句並且使用dbms_advisor.add_sqlwkld_statement過程將 它們加入工作量。這個過程允許向指定的工作量增加SQL語句。語法如下:
dbms_advisor.add_sqlwkld_statement ( workload_name in varchar2, module in varchar2, action in varchar2, cpu_time in number := 0, elapsed_time in number := 0, disk_reads in number := 0, buffer_gets in number := 0, rows_processed in number := 0, optimizer_cost in number := 0, executions in number := 1, priority in number := 2, last_execution_date in date := 'SYSDATE', stat_period in number := 0, username in varchar2, sql_text in clob);
下面的例子向MYWORKLOAD工作量增加一個單獨的SQL語句:
variable sql_text varchar2(400);
execute :sql_text := 'select avg(amount_sold) from sales';
execute dbms_advisor.add_sqlwkld_statement ('MYWORKLOAD', 'MONTHLY', 'ROLLUP', priority=>1, executions=>10,username => 'SH', sql_text => :sql_text);
從工作量中刪除SQL語句
使用dbms_advisor.delete_sqlwkld_statement過程可以從指定的工作量中刪除SQL語句。在刪除SQL 語句時需要指定sql_id。
dbms_advisor.delete_sqlwkld_statement (workload_name in varchar2,sql_id in number);
下面的例子將從MYWORKLOAD工作量中從郵sql_id為10的SQL語句:
execute dbms_advisor.delete_sqlwkld_statement('MYWORKLOAD', 10);
如果工作量當前被一個活動任務所引用，那麼工作量是不能被修改或刪除的。如果工作量不是處於初 始狀態，那麼就可以認為工作量處於活動狀態。dbms_advisor.reset_task過程可以將工作量設定為 初始狀態。
改變工作量中的SQL語句
可以透過dbms_advisor.update_sqlwkld_statement過程來修改工作量中的SQL語句。這個過程將會更 新指定工作量中的現有SQL語句。透過指定sql_id來更新SQL語句，語法如下:
dbms_advisor.update_sqlwkld_statement ( workload_name in varchar2, sql_id in number, application in varchar2 := null, action in varchar2 := null, priority in number := null, username in varchar2 := null); dbms_advisor.update_sqlwkld_statement ( workload_name in varchar2, search in varchar2, updated out number, application in varchar2 := null, action in varchar2 := null, priority in number := null, username in varchar2 := null);
下面的例子將sql_id為10的SQL語句的優先順序修改為3
維護工作量
有以下幾種操作可以用來維護工作量:
.設定工作量屬性
.重設工作量
.刪除工作量與任務之間的連結
設定工作量屬性
dbms_advisor.update_sqlwkld_attributes過程可以用來修改工作量物件或模板的各種屬性。有些這 樣的屬性是用來描述的，比如描述它是否是一個模板或是隻讀。語法如下:
dbms_advisor.update_sqlwkld_attributes ( workload_name in varchar2, new_name in varchar2 := null, description in varchar2 := null, read_only in varchar2 := null, is_template in varchar2 := null, how_created in varchar2 := null);
下面的例子將MYWORKLOAD工作量修改為只讀:
execute dbms_advisor.update_sqlwkld_attributes('myworkload', read_only=> 'true');
重設工作量
dbms_advisor.reset_sqlwkld過程可以將一個工作量設定為初始狀態。當工作量資料保持原樣時這將 會刪除所有的日記和日誌資訊，並重新計算易波動的統計資訊。這個過程應該在任何工作量被調整後 ，比如增加或刪除SQL語句後執行這個過程。下面的例子將重新設定MYWORKLOAD工作量.
execute dbms_advisor.reset_sqlwkld('myworkload');
刪除工作量與任務之間的連結
在任務或工作量被刪除之前，如果它被分別連結到一個工作量或任務，那麼在任務與工作量之間的鏈 接必須使用delete_sqlwkld_ref過程來進行刪除。下面的例子將會把任務MYTASK與SQL工作量 MYWORKLOAD之間的連結刪除。
execute dbms_advisor.delete_sqlwkld_ref('mytask', 'myworkload');
刪除工作量
當工作量不再需要時，可以使用dbms_advisor.delete_sqlwkld過程來將其刪除。可以刪除所有工作 量或者一個特定的集合，但如果工作量仍然被連結到一個任務，那麼這個工作量將不會被刪除。
下面的例子用來刪除一個特定的工作量。
dbms_advisor.delete_sqlwkld (workload_name in varchar2);
execute dbms_advisor.delete_sqlwkld('myworkload');
處理建議
處理建議包含以下方面的內容:
.建議選項
.評估模式
.生成建議
.檢視建議
.SQL工作量日記
.停止建議處理
.標記建議
.修改建議
.生成SQL指令碼
.何時將不再需要建議
建議選項
在建議生成之前，任務的引數首先必須使用dbms_advisor.set_task_parameter過程來進行定義。如 果沒有定義引數，那麼將會使用預設值。使用dbms_advisor.set_task_parameter過程設定引數的語 法如下:
dbms_advisor.set_task_parameter ( task_name in varchar2, parameter in varchar2, value in [varchar2 | number]);
在下面的例子中，將任務MYTASK的儲存大小修改為100MB。這將指示對於建議將有額外的100MB空間。 如果設定為0,說明沒有額外的空間被分配。如果設定為一個負值，則說明指導必須試圖削減當前空間 利用的大小，其大小是引數所指定。
execute dbms_advisor.set_task_parameter('mytask','storage_change', 100000000);
在下面的例子中，將使用valid_table_list引數來過濾掉所有不包括sh.sales和sh.customers表的查詢。
execute dbms_advisor.set_task_parameter('MYTASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');
評估模式
當執行一個任務時，SQL Access Advisor有兩種操作模式:問題解決與評估。預設情況下，SQL Access Advisor將試圖透過尋找索引結構，物化檢視與物化檢視日誌的改進來解決訪問方法的問題。 當只進行評估操作時，SQL Access Advisor將只會給出提供什麼訪問結構讓工作量使用的見意。例如 ，一個問題可能可以透過建立一個新索引，增加一個新列到物化檢視日誌，等等的方法來解決。而當 進行評估操作時，只會生成比如保留索引，保留物化檢視等建議。當進行評估操作時不會考慮對訪問 方法的調整。它是一種嚴格的方法用來檢視現有訪問方法結構和它們如何被提供給工作量所使用。
生成建議
透過執行dbms_advisor_execute_task過程並指定任務名來生成建議。在這個過程執行完成後，可以 檢查dba_advisor_log表來檢查真實的執行狀態與生成的建議數量與運算元。可以使用任務名來查詢
{dba,user}_advisor_recommendations檢視來檢視建議，查詢{dba,user}_advisor_actions檢視來查 看這些建議的操作。
dbms_advisor.execute_task
dbms_advisor.execute_task過程用來對特定任務執行SQL Access Advisor分析或評估。執行任務是 一個同步操作，所以直到操作完成之前不會將控制返回給使用者。任務在執行時或執行完成後，可以檢 查dba_advisor_log表來檢視真實的執行狀態。
執行dbms_advisor.execute_task過程來生成建議，建議是由一個或多個操作組成，比如建立物化視 圖日誌或物化檢視。語法如下:
dbms_advisor.execute_task (task_name in varchar2);
下面的例子執行任務MYTASK:
execute dbms_advisor.execute_task('MYTASK');
檢視建議
由SQL Access Advisor生成的每個建議可以使用目錄檢視來進行檢視，比如{dba,user} _advisor_recommendations檢視。然而，最簡單的方法是使用dbms_advisor.get_task_script過程或 使用EM中的SQL Access Advisor，它是一個圖形工具來顯示建議並且提供了一個超連結來快速檢視那 個語句將會受益於這個建議。由SQL Access Advisor生成的每個建議被連結到受益於它的SQL語句。
下面顯了由SQL Access Advisor由生成的建議(rec_id)，以及它們的排名與總收益。排名是一種測量 標準判斷建議對於查詢的重要性。收益是所有查詢使用建議後它的執行成本（最佳化器成本）的提高總量。
variable workload_name varchar2(255);
variable task_name varchar2(255);
execute :task_name := 'MYTASK';
execute :workload_name := 'MYWORKLOAD';
select rec_id, rank, benefit
from user_advisor_recommendations where task_name = :task_name;
REC_ID RANK BENEFIT
---------- ---------- ----------
1 2 2754
2 3 1222
3 1 5499
4 4 594
為了識別那個查詢受益於那個建議，可以使用dba_*與user_advisor_sqla_wk_stmts。precost與 postcost是分別對不使用與使用建議的發生改變的訪問結構來評估最佳化器成本的專案(在explain plan中所示)。為了檢視每個查詢，執行以下語句:
select sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost as percent_benefit from user_advisor_sqla_wk_stmts where task_name = :task_name and workload_name = :workload_name; SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT ---------- ---------- ---------- ---------- --------------- 121 1 3003 249 91.7082917 122 2 1404 182 87.037037 123 3 5503 4 99.9273124 124 4 730 136 81.369863
每一個建議都是由一個或多個操作組成，這些操作必須都執行才能受益於建議。SQL Access Advisor 提供了以下型別的操作:
.create|drop|retain materialized view .create|alter|retain materialized view log .create|drop|retain index .gather stats
create操作關聯新的訪問結構，retain建議指示現有的訪問結構必須保留，drop建議只有在 workload_scope引數設定為full時才會生成。gather stats操作將生成呼叫dbms_stats過程的語句用 來對新生成的訪問結構收集統計資訊。需要注意的是可能多個建議引用了相同操作，然後在生成的建 議指令碼中，對於每個操作只會看到一次。
在下面的例子中，可以看到對於這組建議有許多不同的操作。
select 'action count', count(distinct action_id) cnt
from user_advisor_actions where task_name = :task_name;
'ACTIONCOUNT CNT
------------ ----------
Action Count 20
-- see the actions for each recommendations
select rec_id, action_id, substr(command,1,30) as command
from user_advisor_actions where task_name = :task_name
order by rec_id, action_id;
REC_ID ACTION_ID COMMAND
---------- ---------- ------------------------------
1 5 CREATE MATERIALIZED VIEW LOG
1 6 ALTER MATERIALIZED VIEW LOG
1 7 CREATE MATERIALIZED VIEW LOG
1 8 ALTER MATERIALIZED VIEW LOG
1 9 CREATE MATERIALIZED VIEW LOG
1 10 ALTER MATERIALIZED VIEW LOG
1 11 CREATE MATERIALIZED VIEW
1 12 GATHER TABLE STATISTICS
1 19 CREATE INDEX
1 20 GATHER INDEX STATISTICS
2 5 CREATE MATERIALIZED VIEW LOG
2 6 ALTER MATERIALIZED VIEW LOG
2 9 CREATE MATERIALIZED VIEW LOG
...
每個操作有多個屬性，它是關於訪問結構的屬性。每個訪問結構的名稱與表空間被分別儲存在 dba_advisor_actions檢視中的attr1與attr2列中。每個新訪問結構所佔用的空間大小儲存在 dba_advisor_actions檢視中的num_attr1列中。每個操作的所有其它屬性是不同的。
下面的PL/SQL過程可以用來列印建議的一些屬性.
create or replace procedure show_recm (in_task_name in varchar2) is
cursor curs is
select distinct action_id, command, attr1, attr2, attr3, attr4
from user_advisor_actions
where task_name = in_task_name
order by action_id;
v_action number;
v_command varchar2(32);
v_attr1 varchar2(4000);
v_attr2 varchar2(4000);
v_attr3 varchar2(4000);
v_attr4 varchar2(4000);
v_attr5 varchar2(4000);
begin
open curs;
dbms_output.put_line('=========================================');
dbms_output.put_line('Task_name = ' || in_task_name);
loop
fetch curs into
v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
exit when curs%notfound;
dbms_output.put_line('Action ID: ' || v_action);
dbms_output.put_line('Command : ' || v_command);
dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
dbms_output.put_line('Attr4 : ' || v_attr4);
dbms_output.put_line('Attr5 : ' || v_attr5);
dbms_output.put_line('----------------------------------------');
end loop;
close curs;
dbms_output.put_line('=========end recommendations============');
end show_recm;
/
-- see what the actions are using sample procedure
set serveroutput on size 99999
execute show_recm(:task_name);
A fragment of a sample output from this procedure is as follows:
Task_name = MYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "SH"."CUSTOMERS"
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "SH"."SH_MV$$_0004"
Attr2 (tablespace): "SH_MVIEWS"
Attr3 : REFRESH FAST WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 19
Command : CREATE INDEX
Attr1 (name) : "SH"."SH_IDX$$_0013"
Attr2 (tablespace): "SH_INDEXES"
Attr3 : "SH"."SH_MV$$_0002"
Attr4 : BITMAP
Attr5 :
SQL工作量日記
在執行分析處理(execute_task)時，SQL Access Advisor儲存了關於分析日記的有用資訊。可以使用
user_advisor_journal檢視來檢視日記。輸出的資訊量依賴於任務引數journaling的設定。
在匯入工作量時，各種資訊被匯入到SQL工作量日記中。它們可以使用user_advisor_sqlw_journal視 圖來進行檢視。例如，如果一個特定的SQL語句引用了無效的表，表丟失統計資訊或有許可權錯誤，這 些資訊會被記錄在日記中。輸出的資訊量可以透過journaling。
為了關閉日記，執行以下語句:
execute dbms_advisor.set_task_parameter('MYTASK', 'JOURNALING', 0);
為了檢視資訊，執行以下語句:
execute dbms_advisor.set_task_parameter('MYTASK', 'JOURNALING', 4);
為了檢視致命資訊，執行以下語句:
execute dbms_advisor.set_sqlwkld_parameter('MYWORKLOAD', 'JOURNALING', 1);
停止建議處理
如果SQL Access Advisor執行dbms_advisor.execute_task過程花了太長時間來生成建議，可以執行 dbms_advisor.cancel_task過程並傳入任務名來進行停止處理。如果執行dbms_advisor.cancel_task 過程，將不會生成建議。另外可以執行dbms_advisor.interrupt_task過程來終止建議處理。
dbms_advisor.interrupt_task過程會造成一個Advisor操作被終止就像正常結束一樣。因此使用者可以 看到到終止時間點已經格式化的任何建議。
一個終止的任務不能被重啟。語法如下:
dbms_advisor.interrupt_task(task_name in varchar2);:
終止任務MY_TASK
execute dbms_advisor.interrupt_task ('my_task');
dbms_advisor.cancel_task過程會造成一個當前執行操作被終止。一個Advisor操作可能花費了幾秒 來響應這個呼叫。因為所有Advisor任務過程是同步進行的，為了取消一個操作，必須使用一個單獨 的資料庫會話。
取消命令有效的將任務還原到它啟動取消操作開始的條件狀態。因此，取消的任務或資料物件不需要重啟。
dbms_advisor.cancel_task (task_name in varchar2);
取消任務MYTASK:
execute dbms_advisor.cancel_task('MYTASK');
標記建議
預設情況下，所有SQL Access Advisor建議已經準備好被實現了，然而，使用者可以透過執行 dbms_advisor.mark_recommendation過程來選擇跳過或執行所選擇的建議。 dbms_advisor.mark_recommendation過程允許使用者使用reject或ignore設定來註釋一個建議，當生成 實現過程時它將造成dbms_advisor.get_task_script過程來跳過它。語法如下:
dbms_advisor.mark_recommendation ( task_name in varchar2 id in number, action in varchar2);
下面的例子將ID為2的建議標記為reject。這個建議與任何依賴的建議將不會出現在指令碼中。
execute dbms_advisor.mark_recommendation('MYTASK', 2, 'REJECT');
修改建議
可以使用dbms_advisor.update_rec_attributes過程，SQL Access Advisor名字與指派給新物件的關 系，比如在分析操作時的索引與物化檢視。然而，它不是必須要選擇合適的名字，因此可以手動設定 所有者，名字與新物件的表空間名。對於建議引用的現有資料庫物件，所有者與名字不會改變。語法 如下:
dbms_advisor.update_rec_attributes ( task_name in varchar2 rec_id in number, action_id in number, attribute_name in varchar2, value in varchar2);
其中,attribute_name引數可以有以下引數值:
.owner:指定建議物件的所有者
.name:指定建議物件名字
.tablespace:指定建議物件表空間
下面的用來修改SH_MVIEWS操作ID為1,建議ID為1的tablespace屬性
execute dbms_advisor.update_rec_attributes('MYTASK', 1, 1,'TABLESPACE', 'SH_MVIEWS');
生成SQL指令碼
一種檢視建議的替代方法就是查詢後設資料，它將使用dbms_advisor.task_script過程來為SQL語句創 建建議指令碼。最終指令碼是一個可以執行的SQL檔案，它可能包含drop,create和alter語句。對於新對 象，物化檢視名字，物化檢視日誌名與使用使用者定義模板自動生成的索引。你也能在試圖執行它之前 生成SQL指令碼。
這裡有四個任務引數控制著命名規則(mview_name_template與index_name_template),這些物件的所 有者(def_index_owner與def_mview_owner)與表空間(def_mview_tablespace與 def_index_tablespace)。
下面的例子顯示瞭如何生成包含建議的CLOB:
execute dbms_advisor.create_file(dbms_advisor.get_task_script ('MYTASK'),'ADVISOR_RESULTS', 'advscript.sql');
為了儲存指令碼檔案，必須提供目錄路徑，因此dbms_advisor.create_file過程需要知道指令碼儲存位置 。另外，必須要對這個目錄持有讀寫許可權。下面的例子顯示瞭如何儲存一個CLOB型別的指導指令碼檔案
SQL> create directory advisor_results as '/bak';
Directory created
SQL> grant read,write on directory advisor_results to public;
Grant succeeded
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('JYTASK'),'ADVISOR_RESULTS', 'jy_advscript.sql');
PL/SQL procedure successfully completed
下面是由這個過程生成的指令碼內容的一部分。這個指令碼包含了對建議訪問結構收集統計資訊的呼叫並 且在最後將建議標記為implemented。
Rem Access Advisor V10.1.0.0.0 - Production
Rem
Rem Username: SH
Rem Task: MYTASK
Rem Execution date: 15/04/2005 11:35
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
create materialized view log on "sh"."products"
with rowid, sequence("prod_id","prod_subcategory")
including new values;
alter materialized view log force on "sh"."products"
add rowid, sequence("prod_id","prod_subcategory")
including new values;
..
create materialized view "sh"."mv$$_00510002"
refresh fast with rowid
enable query rewrite
as select sh.customers.cust_state_province c1, count(*) m1 from
sh.customers where (sh.customers.cust_state_province = 'ca') group
by sh.customers.cust_state_province;
begin
dbms_stats.gather_table_stats('"sh"', '"mv$$_00510002"', null,
dbms_stats.auto_sample_size);
end;
/
如何不再需要建議
dbms_advisor.reset_task過程可以將一個任務重新設定為初始啟動狀態。這將會刪除所有建議和任 務的中間資料。任務的實際狀態被設定為initial。語法如下:
dbms_advisor.reset_task (task_name in varchar2);
下面的例子將任務MYTASK進行重設定:
execute dbms_advisor.reset_task('mytask');
執行快速最佳化
如果只想要最佳化單個SQL語句，dbms_advisor.quick_tune過程將接受任務名與一個SQL語句作為引數 來執行。它將建立一個任務與工作量，並且執行任務。使用dbms_advisor.quick_tune過程所得到的 結果沒有差異。它們實際上與使用dbms_advisor.execute_task過程得到的結果是一樣的，但是對於 只最佳化一個SQL語句來說，快速最佳化這種方法更容易。其語法如下:
dbms_advisor.quick_tune ( advisor_name in varchar2, task_name in varchar2, attr1 in clob, attr2 in varchar2 := null, attr3 in number := null, task_or_template in varchar2 := null);
下面是快速最佳化一個SQL語句的例子:
SQL> variable task_name varchar2(255);
SQL> variable sql_stmt varchar2(4000);
SQL> execute :sql_stmt := 'select count(1) cs from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h, bs_insured j where a.hospital_id = b.hospital_id and b.center_id = g.center_id and b.biz_type = g.biz_type and b.center_id = h.center_id(+) and b.valid_flag = ''1'' and b.indi_id = j.indi_id and (j.urban_type = ''all'' or ''all'' = ''all'') and (b.district_code = b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat = ''2'' and b.biz_type <> ''52'' and b.biz_type <> ''17'' and b.pers_type_detail in (2) and nvl(b.finish_flag, ''0'') = ''1'' and b.end_date between to_date(''2014-01-01 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'') and to_date(''2014-12-31 23:59:59'', ''yyyy-mm- dd hh24:mi:ss'')';
PL/SQL procedure successfully completed
sql_stmt
---------
select count(1) cs from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h, bs_insured j where a.hospital_id = b.hospital_id and b.center_id = g.center_id and b.biz_type = g.biz_type and b.center_id = h.center_id(+) and b.valid_flag = '1' and b.indi_id = j.indi_id and (j.urban_type = 'all' or 'all' = 'all') and (b.district_code = b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat = '2' and b.biz_type <> '52' and b.biz_type <> '17' and b.pers_type_detail in (2) and nvl(b.finish_flag, '0') = '1' and b.end_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2014-12- 31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
SQL> execute :task_name := 'my_quicktune_task';
PL/SQL procedure successfully completed
task_name
---------
my_quicktune_task
SQL> execute dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,:task_name, :sql_stmt);
PL/SQL procedure successfully completed
task_name
---------
my_quicktune_task
sql_stmt
---------
select count(1) cs from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h, bs_insured j where a.hospital_id = b.hospital_id and b.center_id = g.center_id and b.biz_type = g.biz_type and b.center_id = h.center_id(+) and b.valid_flag = '1' and b.indi_id = j.indi_id and (j.urban_type = 'all' or 'all' = 'all') and (b.district_code = b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat = '2' and b.biz_type <> '52' and b.biz_type <> '17' and b.pers_type_detail in (2) and nvl(b.finish_flag, '0') = '1' and b.end_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2014-12- 31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
SQL> create or replace procedure show_recm (in_task_name in varchar2) is
2 cursor curs is
3 select distinct action_id, command, attr1, attr2, attr3, attr4
4 from user_advisor_actions
5 where task_name = in_task_name
6 order by action_id;
7
8 v_action number;
9 v_command varchar2(32);
10 v_attr1 varchar2(4000);
11 v_attr2 varchar2(4000);
12 v_attr3 varchar2(4000);
13 v_attr4 varchar2(4000);
14 v_attr5 varchar2(4000);
15 begin
16 open curs;
17 dbms_output.put_line('=========================================');
18 dbms_output.put_line('Task_name = ' || in_task_name);
19 loop
20 fetch curs into
21 v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
22 exit when curs%notfound;
23 dbms_output.put_line('Action ID: ' || v_action);
24 dbms_output.put_line('Command : ' || v_command);
25 dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
26 dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
27 dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
28 dbms_output.put_line('Attr4 : ' || v_attr4);
29 dbms_output.put_line('Attr5 : ' || v_attr5);
30 dbms_output.put_line('----------------------------------------');
31 end loop;
32 close curs;
33 dbms_output.put_line('=========end recommendations============');
34 end show_recm;
35 /
Procedure created
SQL> set serveroutput on size 99999
SQL> execute show_recm('my_quicktune_task');
=========================================
Task_name = my_quicktune_task
Action ID: 1
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "INSUR_CHANGDE"."MV$$_B1D10000
Attr2 (tablespace):
Attr3 : REFRESH FORCE WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
Action ID: 2
Command : GATHER TABLE STATISTICS
Attr1 (name) : "INSUR_CHANGDE"."MV$$_B1D10000
Attr2 (tablespace):
Attr3 : -1
Attr4 :
Attr5 :
----------------------------------------
=========end recommendations============
PL/SQL procedure successfully completed
管理任務
每次建議被生成，任務被建立，並且除非對這些任務執行一些維護操作，它們將會隨著時間而增長並 且將會佔用儲存空間。有些任務可以想要保留並且防止意外刪除。因此對於任務有多個管理操作可以 執行。
.更新任務屬性
.刪除任務
.設定days_to_expire引數
更新任務屬性
使用dbms_advisor.update_task_attributes過程，可以執行以下操作:
.改變任務名稱
.指定任務描述
.設定任務為只讀狀態，因上不能被修改
.將任務設定為模板讓其它任務可以依賴它進行定義
.改變任務或任務模板的各種屬性
其語法如下:
dbms_advisor.update_task_attributes ( task_name in varchar2 new_name in varchar2 := null, description in varchar2 := null, read_only in varchar2 := null, is_template in varchar2 := null, how_created in varchar2 := null);
下面的例子將任務名稱MYTASK更新為TUNING1:
execute dbms_advisor.update_task_attributes('MYTASK', 'TUNING1');
下面的例子將任務TUNING1標記為只讀:
execute dbms_advisor.update_task_attributes('TUNING1', read_only => 'TRUE');
下面的例子將把任務MYTASK標記為模板
execute dbms_advisor.update_task_attributes('TUNING1', is_template=>'TRUE');
刪除任務
dbms_advisor.delete_task過程將從檔案庫中刪除指導任務。其語法如下:
dbms_advisor.delete_task(task_name in varchar2);
下面的例子將任務MYTASK刪除:
execute dbms_advisor.delete_task('MYTASK');
設定DAYS_TO_EXPIRE引數
當一個任務或工作量被建立後，引數days_to_expire被設定為30天。這個引數指示直到建立時間達到 指定天數時任務或物件將會由系統自動刪除。如果你想要無限期的儲存一個任務或工作量，引數 days_to_expire應該被設定為advisor_unlimited。
使用SQL Access Advisor進行SQL最佳化的例子
使用SQL調優集來載入工作量執行SQL Access Advisor
1.建立任務
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name:='JYTASK';
PL/SQL procedure successfully completed
task_name
---------
JYTASK
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:task_id,:task_name);
PL/SQL procedure successfully completed
task_id
---------
45354
task_name
---------
JYTASK
2.建立工作量
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'myworkload'; PL/SQL procedure successfully completed workload_name --------- myworkload SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload'); PL/SQL procedure successfully completed workload_name --------- myworkload
3.將任務與工作量進行連結
SQL> execute dbms_advisor.add_sqlwkld_ref('JYTASK', 'myworkload');
PL/SQL procedure successfully completed
4.從SQL調優集載入工作量
SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 cur dbms_sqltune.sqlset_cursor;
3 begin
4 open cur for
5 select value(p)
6 from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE'' and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and command_type<>2',
7 null,
8 null,
9 null,
10 null,
11 1,
12 null,
13 'all')) p;
14 dbms_sqltune.load_sqlset(sqlset_name => 'MY_STS_WORKLOAD',
15 populate_cursor => cur);
16 end;
17 /
PL/SQL procedure successfully completed
SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'MY_STS_WORKLOAD';
PL/SQL procedure successfully completed
sqlsetname
---------
MY_STS_WORKLOAD
SQL> execute :workload_name := 'myworkload';
PL/SQL procedure successfully completed
workload_name
---------
myworkload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW',1,:saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
myworkload
sqlsetname
---------
MY_STS_WORKLOAD
saved_stmts
---------
1219
failed_stmts
---------
13
5.執行任務
SQL> exec dbms_advisor.execute_task('JYTASK');
PL/SQL procedure successfully completed
6.檢視建議
SQL> select rec_id, rank, benefit from user_advisor_recommendations where task_name ='JYTASK';
REC_ID RANK BENEFIT
---------- ---------- ----------
1 1 0
2 2 511856
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
8 8 17712
9 9 0
10 10 171589
11 11 352280
12 12 0
13 13 35201233
14 14 249249
15 15 0
16 16 0
17 17 0
18 18 0
19 19 67650
20 20 24901
21 21 0
22 22 45756
23 23 0
24 24 0
25 25 0
26 26 44170
SQL> select sql_id, rec_id, precost, postcost,
2 decode((precost-postcost),0,0,round((precost-postcost)*100/precost,2)) as percent_benefit
3 from user_advisor_sqla_wk_stmts
4 where task_name ='JYTASK' and workload_name ='myworkload';
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------------
2450 5 4440 4440 0
2451 13 19012 13580 28.57
2452 1 2908 2908 0
2453 3 4907 4907 0
2454 9 2665 2665 0
2455 4 7180 7180 0
2456 12 4230 4230 0
2457 9 1521 1521 0
2458 85 14322 434 96.97
2459 27 9809 9809 0
2460 2 31086 8478 72.73
2461 5 4050 4050 0
2462 6 15432 15432 0
2463 1 935 935 0
2464 307 14490 420 97.1
2465 20 2900 2175 25
2466 5 1401 1401 0
2467 7 1980 1980 0
2468 128 224180 67254 70
2469 23 8098 8098 0
下面的PL/SQL過程可以用來列印建議的一些屬性.
SQL> create or replace procedure show_recm (in_task_name in varchar2) is
2 cursor curs is
3 select distinct action_id, command, attr1, attr2, attr3, attr4
4 from user_advisor_actions
5 where task_name = in_task_name
6 order by action_id;
7
8 v_action number;
9 v_command varchar2(32);
10 v_attr1 varchar2(4000);
11 v_attr2 varchar2(4000);
12 v_attr3 varchar2(4000);
13 v_attr4 varchar2(4000);
14 v_attr5 varchar2(4000);
15 begin
16 open curs;
17 dbms_output.put_line('=========================================');
18 dbms_output.put_line('Task_name = ' || in_task_name);
19 loop
20 fetch curs into
21 v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
22 exit when curs%notfound;
23 dbms_output.put_line('Action ID: ' || v_action);
24 dbms_output.put_line('Command : ' || v_command);
25 dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
26 dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
27 dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
28 dbms_output.put_line('Attr4 : ' || v_attr4);
29 dbms_output.put_line('Attr5 : ' || v_attr5);
30 dbms_output.put_line('----------------------------------------');
31 end loop;
32 close curs;
33 dbms_output.put_line('=========end recommendations============');
34 end show_recm;
35 /
Procedure created
SQL> set serveroutput on size 9999999
SQL> execute show_recm('JYTASK');
=========================================
Task_name = JYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SPECIAL_DI
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 3
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."FC_BIZ_POLICY
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES
Attr5 :
----------------------------------------
Action ID: 5
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_TREAT_TYPE
Attr2 (tablespace):
Attr3 : ROWID, PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 7
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_PERSON_TYP
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 9
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."PFS_INSUR_DET
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 11
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SPECIAL_DI
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 13
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_STAC"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CULTURE_ST
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 17
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CALLING"
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 19
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OCCUPATION
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 21
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_BIZ_LICE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 23
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 25
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SALARYSYS"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 27
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_JOIN_STA"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 29
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_REVEN
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 31
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_MEDI_ITEM_
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 33
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_HIRED_TYPE
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 35
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_CHARG
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 37
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_ECON_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 39
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."CG_INDI_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 41
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_FINAL
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 43
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_LOWINSR"
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 45
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SUB_CONN"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 47
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OFFICIAL_I
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 49
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OFFICIAL"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 51
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CHARGE_COD
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :ant succeeded
SQL> create directory advisor_results as '/bak';
Directory created
SQL> grant read,write on directory advisor_results to public;
Grant succeeded
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('JYTASK'),'ADVISOR_RESULTS', 'jy_advscript.sql');
PL/SQL procedure successfully completed
[IBMP740-1:root:/bak]#more jy_advscript.sql
Rem SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem
Rem Username: INSUR_CHANGDE
Rem Task: JYTASK
Rem Execution date: 06/09/2016 09:48
Rem
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SPECIAL_DISEASE_RANGE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."FC_BIZ_POLICY"
WITH ROWID, SEQUENCE ("POLICY_CODE","POLICY_VALUE","CENTER_OR_HOSP","VALID_FLAG","CENTER_ID")
INCLUDING NEW VALUES;
...省略...
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."MV$$_B12A0069"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT A.SERIAL_MATCH, A.HOSPITAL_ID, A.MATCH_TYPE, A.ITEM_CODE, A.ITEM_NAME,
A.HOSP_CODE, A.HOSP_NAME, A.MODEL AS MODEL_NAME, A.HOSP_MODEL, TO_CHAR (A.EDIT_DATE,
'yyyy-mm-dd') AS EDIT_DATE, A.EDIT_STAFF, A.EDIT_MAN, A.VALID_FLAG, NVL (B.SP_FLAG,
'0') AS SP_FLAG, TO_CHAR(A.EFFECT_DATE, 'yyyy-mm-dd') AS EFFECT_DATE,
TO_CHAR(A.EXPIRE_DATE, 'yyyy-mm-dd') AS EXPIRE_DATE, A.AUDIT_FLAG, TO_CHAR (A.AUDIT_DATE,
'yyyy-mm-dd') AS AUDIT_DATE, A.AUDIT_STAFF, A.AUDIT_MAN, B.CLINIC_FLAG,
NVL(B.DEFRAY_TYPE_CJ,B.DEFRAY_TYPE) AS DEFRAY_TYPE_CJ, B.STAPLE_FLAG,
B.SELF_SCALE, NVL(A.HOSP_PRICE, 0) AS PRICE, B.STAT_TYPE AS STAT_TYPE,
DECODE(SUBSTR('110',1,1),'5', DECODE(B.BO_FLAG,1,DECODE(SUBSTR (B.DEFRAY_TYPE,1,3),
'Bed', B.DEFRAY_TYPE, 'A000_00'),'A000_100'),'4', DECODE(B.WL_FLAG,1,DECODE (B.DEFRAY_TYPE,
'A001','A001','A000_00'),'A000_100'), B.DEFRAY_TYPE) AS DEFRAY_TYPE, DECODE(NVL (C.TREATMENT_TYPE,
'000'), '161', '161', '162', '162', '000') AS TREATMENT_TYPE FROM BS_CATALOG_MATCH
A, BS_ITEM B, BS_SPECIAL_ITEM_RANGE C ,BS_CENTER D WHERE A.CENTER_ID =
'430701' AND A.HOSPITAL_ID = '4307000001' AND A.HOSP_CODE = '111965**'
AND A.MATCH_TYPE = '0' AND A.VALID_FLAG = '1' AND A.EFFECT_DATE < = TO_DATE( '2016-09-02','yyyy-mm-dd') AND (A.EXPIRE_DATE >= TO_DATE('2016-09-02'
,'yyyy-mm-dd') OR A.EXPIRE_DATE IS NULL) AND A.CENTER_ID = D.CENTER_ID
AND NVL(D.CATALOG_CENTER,D.CENTER_ID) = B.CENTER_ID AND TO_CHAR(A.ITEM_CODE)
= (B.ITEM_CODE) AND B.VALID_FLAG = '1' AND B.EFFECT_DATE < = TO_DATE('2016-09-02' ,'yyyy-mm-dd') AND (B.EXPIRE_DATE >= TO_DATE('2016-09-02','yyyy-mm-dd'
) OR B.EXPIRE_DATE IS NULL) AND A.CENTER_ID = C.CENTER_ID(+) AND A.MATCH_TYPE
= C.MEDI_ITEM_TYPE(+) AND A.ITEM_CODE = C.MEDI_ITEM_CODE(+) AND '1' =
C.VALID_FLAG(+) AND TO_DATE('2016-09-02','yyyy-mm-dd') >= C.EFFECT_DATE(+)
AND TO_DATE('2016-09-02','yyyy-mm-dd') < = NVL(C.EXPIRE_DATE(+), TO_DATE(
'2099-12-31', 'yyyy-mm-dd')) AND '131' <> C.TREATMENT_TYPE(+);
begin
dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"MV$ $_B12A0069"',NULL,dbms_stats.auto_sample_size);
end;
/
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."MV$$_B12A006A"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT A.SERIAL_MATCH, A.HOSPITAL_ID, A.MATCH_TYPE, A.ITEM_CODE, A.ITEM_NAME,
A.HOSP_CODE, A.HOSP_NAME, A.MODEL AS MODEL_NAME, A.HOSP_MODEL, TO_CHAR (A.EDIT_DATE,
'yyyy-mm-dd') AS EDIT_DATE, A.EDIT_STAFF, A.EDIT_MAN, A.VALID_FLAG, NVL (B.SP_FLAG,
'0') AS SP_FLAG, TO_CHAR(A.EFFECT_DATE, 'yyyy-mm-dd') AS EFFECT_DATE,
TO_CHAR(A.EXPIRE_DATE, 'yyyy-mm-dd') AS EXPIRE_DATE, A.AUDIT_FLAG, TO_CHAR (A.AUDIT_DATE,
'yyyy-mm-dd') AS AUDIT_DATE, A.AUDIT_STAFF, A.AUDIT_MAN, B.CLINIC_FLAG,
NVL(B.DEFRAY_TYPE_CJ,B.DEFRAY_TYPE) AS DEFRAY_TYPE_CJ, B.STAPLE_FLAG,
B.SELF_SCALE, NVL(A.HOSP_PRICE, 0) AS PRICE, B.STAT_TYPE AS STAT_TYPE,
DECODE(SUBSTR('120',1,1),'5', DECODE(B.BO_FLAG,1,DECODE(SUBSTR (B.DEFRAY_TYPE,1,3),
'Bed', B.DEFRAY_TYPE, 'A000_00'),'A000_100'),'4', DECODE(B.WL_FLAG,1,DECODE (B.DEFRAY_TYPE,
'A001','A001','A000_00'),'A000_100'), B.DEFRAY_TYPE) AS DEFRAY_TYPE, DECODE(NVL (C.TREATMENT_TYPE,
'000'), '161', '161', '162', '162', '000') AS TREATMENT_TYPE FROM BS_CATALOG_MATCH
A, BS_ITEM B, BS_SPECIAL_ITEM_RANGE C ,BS_CENTER D WHERE A.CENTER_ID =
'430722' AND A.HOSPITAL_ID = '4307220004' AND A.HOSP_CODE = '00358' AND
A.MATCH_TYPE = '0' AND A.VALID_FLAG = '1' AND A.EFFECT_DATE < = TO_DATE( '2016-09-01','yyyy-mm-dd') AND (A.EXPIRE_DATE >= TO_DATE('2016-09-01'
,'yyyy-mm-dd') OR A.EXPIRE_DATE IS NULL) AND A.CENTER_ID = D.CENTER_ID
AND NVL(D.CATALOG_CENTER,D.CENTER_ID) = B.CENTER_ID AND TO_CHAR(A.ITEM_CODE)
= (B.ITEM_CODE) AND B.VALID_FLAG = '1' AND B.EFFECT_DATE < = TO_DATE('2016-09-01' ,'yyyy-mm-dd') AND (B.EXPIRE_DATE >= TO_DATE('2016-09-01','yyyy-mm-dd'
) OR B.EXPIRE_DATE IS NULL) AND A.CENTER_ID = C.CENTER_ID(+) AND A.MATCH_TYPE
= C.MEDI_ITEM_TYPE(+) AND A.ITEM_CODE = C.MEDI_ITEM_CODE(+) AND '1' =
C.VALID_FLAG(+) AND TO_DATE('2016-09-01','yyyy-mm-dd') >= C.EFFECT_DATE(+)
AND TO_DATE('2016-09-01','yyyy-mm-dd') < = NVL(C.EXPIRE_DATE(+), TO_DATE(
'2099-12-31', 'yyyy-mm-dd')) AND '131' <> C.TREATMENT_TYPE(+);
begin
dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"MV$ $_B12A006A"',NULL,dbms_stats.auto_sample_size);
end;
/
...省略...
使用任務模板來載入工作量執行SQL Access Advisor
1.建立任務模板my_template
SQL> variable template_id number;
SQL> variable template_name varchar2(255);
SQL> execute :template_name := 'my_template';
PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:template_id,:template_name, is_template => 'true');
PL/SQL procedure successfully completed
template_id
---------
45357
template_name
---------
my_template
2.設定模板引數
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_'); PL/SQL procedure successfully completed template_name --------- my_template SQL> -- set default tablespace for recommended indexes/mvs SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_INDEX_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_MVIEW_TABLESPACE', 'USERS'); PL/SQL procedure successfully completed template_name --------- my_template
3.使用模板來建立任務mytask
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name := 'mytask';
PL/SQL procedure successfully completed
task_name
---------
mytask
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id,:task_name, template=>'my_template');
PL/SQL procedure successfully completed
task_id
---------
45376
task_name
---------
mytask
4.建立工作量my_template_workload
SQL> variable workload_name varchar2(255); SQL> execute :workload_name := 'my_template_workload'; PL/SQL procedure successfully completed workload_name --------- my_template_workload SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload'); PL/SQL procedure successfully completed workload_name --------- my_template_workload
5.從SQL調優集載入工作量
SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY__TEMPLATE_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
SQL> declare
2 cur dbms_sqltune.sqlset_cursor;
3 begin
4 open cur for
5 select value(p)
6 from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE''
7
8 and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and
9
10 command_type<>2',
11 null,
12 null,
13 null,
14 null,
15 1,
16 null,
17 'all')) p;
18 dbms_sqltune.load_sqlset(sqlset_name => 'MY__TEMPLATE_STS_WORKLOAD',
19 populate_cursor => cur);
20 end;
21 /
PL/SQL procedure successfully completed
SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'MY__TEMPLATE_STS_WORKLOAD';
PL/SQL procedure successfully completed
sqlsetname
---------
MY__TEMPLATE_STS_WORKLOAD
SQL> execute :workload_name := 'my_template_workload';
PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW',1,:saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
sqlsetname
---------
MY__TEMPLATE_STS_WORKLOAD
saved_stmts
---------
650
failed_stmts
---------
38
6.建立任務與工作量之間的連結
SQL> execute dbms_advisor.add_sqlwkld_ref('mytask','my_template_workload');
PL/SQL procedure successfully completed
7.執行任務
SQL> execute dbms_advisor.execute_task('mytask');
PL/SQL procedure successfully completed
8.生成建議指令碼
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('mytask'),'ADVISOR_RESULTS', 'jy_mytask_advscript.sql');
PL/SQL procedure successfully completed
[IBMP740-1:root:/bak]# more jy_mytask_advscript.sql
Rem SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem
Rem Username: INSUR_CHANGDE
Rem Task: mytask
Rem Execution date: 06/09/2016 10:42
Rem
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_PERSON_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HIRED_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSP_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_OFFICIAL_IDEN"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_TOWNS"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_OFFICIAL"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_VILLAGE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SPECIAL"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_BANK_INFO"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_FOLK"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_TAX_INFO"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_STREET"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_FUND_TYPE"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SEX"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_OCCUPATION"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_COMMUNITY"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_POSITION"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_KINDRED"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."CM_OUTHOS_CIRCS"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_DISTRICT"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."CG_INDI_REASON"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."SYS_QUESTIONNAIRE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_GROUP"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSP_BIZ_PERS"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSPITAL_COLLATE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_HOSPITAL"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_BIZTYPE"
WITH ROWID, PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."CG_INDI_ALT_DATA"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."MS_RECEIVE_SQL"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."MQ_SQL"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."LV_PRD"
WITH ROWID, SEQUENCE("CALC_PRD","CURR_YEAR","CENTER_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_TREAT_TYPE"
WITH ROWID, PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_SPECIAL_DISEASE_RANGE"
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."BS_DISEASE"
WITH ROWID, PRIMARY KEY ;
CREATE MATERIALIZED VIEW LOG ON
"INSUR_CHANGDE"."APPLICATION_VERSION"
WITH PRIMARY KEY ;
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."SH_MV$$_0008"
TABLESPACE "USERS"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SUM(CITY_CODE) CITY_CODE FROM ( SELECT '1' CITY_CODE FROM BS_OUT O WHERE
O.LEAVE_TYPE <> '2' AND O.LEAVE_STATUS = '1' AND O.INDI_ID = '208227'
AND TO_CHAR(O.LEAVE_DATE, 'yyyy-mm-dd') < = '2016-09-06' UNION SELECT
'1' CITY_CODE FROM BS_OUT O, (SELECT BOC.LEAVE_NO, BOC.INPUT_DATE FROM
BS_OUT_CHANGE BOC, BS_OUT O WHERE BOC.LEAVE_NO = O.LEAVE_NO AND BOC.VALUE_AFTER
= '異地正式取消' AND O.INDI_ID = '208227') C WHERE O.LEAVE_NO = C.LEAVE_NO(+)
AND O.LEAVE_TYPE <> '2' AND O.LEAVE_STATUS = '4' AND O.INDI_ID = '208227'
AND TO_CHAR(O.LEAVE_DATE, 'yyyy-mm-dd') < = '2016-09-06' AND TO_CHAR(C.INPUT_DATE, 'yyyy-mm-dd') > '2016-09-06' UNION SELECT NVL(BI.CITY_CODE, 0) CITY_CODE
FROM BS_INSURED BI WHERE BI.INDI_ID = '208227' AND NOT EXISTS (SELECT
1 FROM BS_OUT BO WHERE BO.INDI_ID = BI.INDI_ID AND BO.LEAVE_TYPE <> 2
AND BO.LEAVE_STATUS = 1) );
begin
dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"SH_MV$ $_0008"',NULL,dbms_stats.auto_sample_size);
end;
/
使用SQL Caceh載入工作量來呼叫SQL Access Advisor
1.建立名為my_cache_workload的工作量
SQL> execute :workload_name := 'my_cache_workload'; PL/SQL procedure successfully completed workload_name --------- my_cache_workload SQL> execute dbms_advisor.create_sqlwkld(:workload_name); PL/SQL procedure successfully completed workload_name --------- my_cache_workload
2.設定過濾
只載入只包含INSUR_CHANGDE使用者所擁有表的SQL語句
SQL> execute dbms_advisor.set_sqlwkld_parameter('my_cache_workload', 'USERNAME_LIST', 'INSUR_CHANGDE');
PL/SQL procedure successfully completed
3.從SQL Cache中載入工作量
SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> variable workload_name varchar2(255);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :workload_name := 'my_cache_workload';
PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload
SQL> EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (:workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts);
PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload
saved_stmts
---------
120672
failed_stmts
---------
1515
SQL> SELECT num_select_stmt, create_date
2 FROM user_advisor_sqlw_sum
3 WHERE workload_name = :workload_name;
NUM_SELECT_STMT CREATE_DATE
--------------- -----------
84547 2016/9/6 11
workload_name
---------
my_cache_workload
SQL> SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30)
2 FROM user_advisor_sqlw_stmts
3 WHERE workload_name ='my_cache_workload'
4 ORDER BY sql_id;
SQL_ID USERNAME OPTIMIZER_COST SUBSTR(SQL_TEXT,1,30)
---------- ------------------------------ -------------- --------------------------------------------------------------------------------
4319 INSUR_CHANGDE 0 insert into bs_indi_freeze_his
4320 INSUR_CHANGDE 0 update bs_pres_insur set
4321 INSUR_CHANGDE 0 select a.serial_match,
4322 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4323 INSUR_CHANGDE 0 select nvl(catalog_center,cent
4324 INSUR_CHANGDE 0 select scene_value from mt_biz
4325 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4326 INSUR_CHANGDE 0 insert into bs_mdi_indi_acc( I
4327 INSUR_CHANGDE 0 select biz_type,center_id from
4328 INSUR_CHANGDE 0 select bi.indi_id,bi.pers_type
4329 INSUR_CHANGDE 0 select nvl(cp.indi_join_flag,0
4330 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4331 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4332 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4333 INSUR_CHANGDE 0 select a.dataobj_name from
4334 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4335 INSUR_CHANGDE 0 select password from bs_cards
4336 INSUR_CHANGDE 0 select t.old_value,t.new_value
4337 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4338 INSUR_CHANGDE 0 update mt_serial set cur_num_n
4.建立名為my_cache_task的任務
SQL> execute :task_name := 'my_cache_task';
PL/SQL procedure successfully completed
task_name
---------
my_cache_task
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id, :task_name);
PL/SQL procedure successfully completed
task_id
---------
45556
task_name
---------
my_cache_task
5.在任務與工作量之間建立連結
SQL> execute dbms_advisor.add_sqlwkld_ref('my_cache_task','my_cache_workload');
PL/SQL procedure successfully completed
6.執行任務
SQL> execute dbms_advisor.execute_task('my_cache_task');
PL/SQL procedure successfully completed
7.生成指令碼
SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('my_cache_task'),'ADVISOR_RESULTS', 'my_cache_workload_script.sql');
PL/SQL procedure successfully completed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2124598/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 手工執行sql tuning advisor和sql access advisorSQL
- SQL Access Advisor!SQL
- SQL Access Advisor(zt)SQL
- SQL Access Advisor 概要SQL
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- SQL Access Advisor的 DBMS_ADVISOR.QUICK_TUNE 使用SQLUI
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- 使用dbms_advisor優化物化檢視優化
- sql tuning advisor和sql access advisor區別SQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- 在EM中使用SQL Access Advisor（SQL訪問建議）優化SQLSQL優化
- 使用dbms_advisor模擬表碎片化及解決過程
- Access學習要點2----Access2010中如何執行SQL執行SQL語句SQL
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- Oracle OCP IZ0-053 Q232(sql access advisor)OracleSQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle OCP 1Z0 053 Q123(SQL Access Advisor)OracleSQL
- Sql Tuning Advisor 使用方法SQL
- SQL Tuning Advisor使用例項SQL
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- SQL Repair AdvisorSQLAI
- 用sql profile來固定執行計劃SQL
- Laravel 使用 sql 語句 和 sql 檔案 來建立執行資料庫遷移LaravelSQL資料庫
- 使用Pipe來執行RMAN命令
- SQL TUNING ADVISORSQL
- 使用sql profile固定執行計劃SQL
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- [原創]ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- 在EM中使用SQL Tuning Advisor（SQL優化建議）優化SQLSQL優化
- 使用PL/SQL檢視執行計劃SQL
- 使用sqltrace跟蹤session執行的sqlSQLSession
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- 使用RMAN來PDB執行完全恢復
- MyBatis SQL執行MyBatisSQL
- [ORACLE] SQL執行OracleSQL
- SQL的執行SQL
- 使用SQL TRACE和TKPROF觀察SQL語句執行結果SQL