SQL Access Advisor
            
獲得有關(guān)基于實(shí)際頻率和使用類型（而非數(shù)據(jù)類型）進(jìn)行分區(qū)、索引和創(chuàng)建物化視圖以改進(jìn)模式設(shè)計(jì)的建議。
            
                
                    
                        
                        
                        
下載 Oracle 數(shù)據(jù)庫(kù) 11g
                    
                
            
            Oracle 數(shù)據(jù)庫(kù) 10g 提供了大量幫助程序（或“顧問程序”），可幫助您決定最佳操作流程。其中一個(gè)示例是 SQL Tuning Advisor，它可以提供有關(guān)查詢調(diào)整以及在流程中延長(zhǎng)整個(gè)優(yōu)化過程的建議。            
 
            
但請(qǐng)考慮以下調(diào)整案例：假設(shè)一個(gè)索引確實(shí)有助于某個(gè)查詢，但該查詢只執(zhí)行一次。這樣，即使該查詢可以得益于此索引，但創(chuàng)建索引的成本也會(huì)超出其帶來的好處。要按這種方式分析案例，您需要了解查詢的訪問頻率和原因。 
            另一個(gè)顧問程序 (SQL Access Advisor) 可執(zhí)行這種類型的分析。除了像在 Oracle 數(shù)據(jù)庫(kù) 10g 中一樣可以分析索引、物化視圖等，Oracle 數(shù)據(jù)庫(kù) 11g 中的 SQL Access Advisor 還可以分析表和查詢以識(shí)別可能的分區(qū)策略 — 這在設(shè)計(jì)最佳模式時(shí)可以提供很大幫助。在 Oracle 數(shù)據(jù)庫(kù) 11g 中，SQL Access Advisor 現(xiàn)在可以提供與整個(gè)負(fù)載相關(guān)的建議，包括考慮創(chuàng)建成本和維護(hù)訪問結(jié)構(gòu)。            
在本文中，您將了解新的 SQL Access Advisor 如何解決常見問題。（注：出于演示目的，我們將通過一個(gè)語(yǔ)句演示這個(gè)功能；但是，Oracle 建議使用 SQL Access Advisor 來幫助調(diào)整整個(gè)負(fù)載，而不只是一個(gè) SQL 語(yǔ)句。）
            
問題
            
下面是一個(gè)典型問題。應(yīng)用程序發(fā)出了以下 SQL 語(yǔ)句。該查詢似乎要消耗大量資源并且速度很慢。            
            
            select store_id, guest_id, count(1) cnt            from res r, trans t            where r.res_id between 2 and 40            and t.res_id = r.res_id            group by store_id, guest_id            /            
            該 SQL 涉及兩個(gè)表，即 RES 和 TRANS；后者是前者的子表。您需要找到提高查詢性能的解決方案 — SQL Access Advisor 正是最合適的工具。            
 
            
您可以通過命令行或 Oracle 企業(yè)管理器數(shù)據(jù)庫(kù)控制與顧問程序進(jìn)行交互，但使用 GUI 可以提供更好的值（GUI 可讓您將解決方案可視化，并將許多任務(wù)簡(jiǎn)化為簡(jiǎn)單的點(diǎn)擊操作）。 
            
要使用企業(yè)管理器中的 SQL Access Advisor 解決 SQL 中的問題，請(qǐng)遵循以下步驟。            
            
                
當(dāng)然，第一個(gè)任務(wù)是啟動(dòng)企業(yè)管理器。在 Database 主頁(yè)上，向下滾動(dòng)到頁(yè)面底部，您將在這里看到幾個(gè)超鏈接，如下圖所示：                
 
                
                
                
                
在該菜單中，單擊 Advisor Central，這將顯示一個(gè)與下圖類似的屏幕。下面僅顯示了該屏幕的頂部。                
 
                
                
                
                
單擊 SQL Advisors，這將顯示一個(gè)與下圖類似的屏幕。                
 
                
                
                
                
在該屏幕中，您可以計(jì)劃 SQL Access Advisor 會(huì)話，并指定其選項(xiàng)。顧問程序必須收集一些要使用的 SQL 語(yǔ)句。最簡(jiǎn)單的選項(xiàng)就是通過 Current and Recent SQL Activity 從共享池獲取它們。選擇該選項(xiàng)，您可以獲取共享池中緩存的所有 SQL 語(yǔ)句來進(jìn)行分析。                
但是，在某些情況下，您并不需要共享池中的所有語(yǔ)句；而僅需要其中的一組特定語(yǔ)句。為此，您需要在另一個(gè)屏幕上創(chuàng)建一個(gè)“SQL 調(diào)整工具集”，然后在這里（即，該屏幕中）引用集合名。
                
此外，您可能希望根據(jù)理論上預(yù)期會(huì)發(fā)生的情況來運(yùn)行復(fù)合負(fù)載。這些類型的 SQL 語(yǔ)句將不會(huì)位于共享池中，因?yàn)樗鼈兩形刺幚?。相反，您需要?jiǎng)?chuàng)建這些語(yǔ)句并將其存儲(chǔ)在一個(gè)特殊表中。在第三個(gè)選項(xiàng) (Create a Hypothetical Workload...) 中，您需要提供該表的名稱以及模式名。
                
對(duì)于本文，假設(shè)您希望從共享池中獲取 SQL。因此，選擇第一個(gè)選項(xiàng)（即默認(rèn)選項(xiàng)），如屏幕所示。
                
                
但是，您可能并不需要所有語(yǔ)句，而只需要一些關(guān)鍵語(yǔ)句。例如，您可能只希望分析用戶 SCOTT（即應(yīng)用程序用戶）執(zhí)行的 SQL。所有其他用戶可能會(huì)執(zhí)行即席 SQL 語(yǔ)句，但您希望在分析中排除它們。在這種情況下，單擊 Filter Options 前面的“+”號(hào)，如下圖所示。                
 
                
                
                
                
在該屏幕中，在要求您輸入用戶的文本框中輸入 SCOTT，然后選擇單選按鈕 Include only SQL...（默認(rèn)選項(xiàng)）。同樣，您也可以排除某些用戶。例如，您希望捕獲數(shù)據(jù)庫(kù)中的所有活動(dòng)，除了用戶 SYS、SYSTEM 和 SYSMAN。您可以在文本框中輸入這些用戶，然后單擊按鈕 Exclude all SQL statements...。
 
                
                
您可以按 Module Id、Action 甚至 SQL 語(yǔ)句中的特定字符串來過濾語(yǔ)句中訪問的表。其目的是確保只分析感興趣的語(yǔ)句。選擇整個(gè) SQL 緩存的小型子集可以加快分析速度。在本例中，我們假設(shè)用戶 SCOTT 僅執(zhí)行了一個(gè)語(yǔ)句。如果不是這樣，您可以施加額外的過濾條件，將分析集合減少到只有一個(gè) SQL（即，原始問題語(yǔ)句中提到的那個(gè) SQL）。
 
                
                
單擊 Next。這將顯示以下屏幕（僅顯示了頂部）：                
 
                
                
                
                
在該屏幕中，您可以指定應(yīng)該搜索哪些類型的建議。例如，在本例中，我們希望顧問程序查找潛在的索引、物化視圖和分區(qū)，因此應(yīng)選中這些項(xiàng)旁邊的所有復(fù)選框。對(duì)于 Advisor Mode，您可以進(jìn)行選擇；默認(rèn)選項(xiàng) Limited Mode 僅處理高成本 SQL 語(yǔ)句。當(dāng)然，這可以加快速度并獲得更好的結(jié)果集。要分析所有 SQL，應(yīng)使用 Comprehensive Mode。（在本例中，模式的選擇無(wú)關(guān)緊要，因?yàn)槟挥幸粋€(gè) SQL。）
 
                
                
屏幕的后半部分顯示了高級(jí)選項(xiàng)，例如，應(yīng)該如何確定 SQL 語(yǔ)句的優(yōu)先順序、所使用的表空間等等。您可以保留默認(rèn)項(xiàng)為標(biāo)記狀態(tài)（稍后將描述更多內(nèi)容）。單擊 Next，這將顯示計(jì)劃屏幕。選擇 Run Immediately，并單擊 Next。
                
單擊 Submit。這將創(chuàng)建一個(gè) Scheduler 作業(yè)。您可以單擊該屏幕中顯示的作業(yè)超鏈接，它們位于頁(yè)面頂部。作業(yè)將顯示為 Running。
 
                
                
反復(fù)單擊 Refresh 直到您看到 Last Run Status 列下方的值更改為 SUCCEEDED。
 
                
                
現(xiàn)在，返回 Database 主頁(yè)并單擊 Advisor Central，正如您在第一步中所做的那樣?，F(xiàn)在，您將看到 SQL Access Advisor 行，如下圖所示：                
 
                
                
                
                
該屏幕表明 SQL Access Advisor 任務(wù)已經(jīng) COMPLETED?，F(xiàn)在，單擊按鈕 View Result。屏幕顯示如下：                
 
                
                
                
                
該屏幕說明了一切！SQL Access Advisor 分析了 SQL 語(yǔ)句，并發(fā)現(xiàn)某些解決方案可以將查詢性能提高十倍。要查看提供了哪些具體建議，單擊 Recommendations 選項(xiàng)卡，這將顯示詳細(xì)信息屏幕，如下所示。                
 
                
                
                
                
從較高級(jí)別看，該屏幕提供了許多很好的信息。例如，對(duì)于 ID = 1 的語(yǔ)句，Actions 列下方有兩個(gè)建議操作。下一列 Action Types 顯示了操作類型，由彩色方塊表示。根據(jù)下方的圖標(biāo)指南，您可以了解這兩個(gè)操作分別針對(duì)索引和分區(qū)。它們可以共同將性能提高幾個(gè)數(shù)量級(jí)。                
要確切了解可以提高哪個(gè) SQL 語(yǔ)句，單擊 ID，這將顯示以下屏幕。當(dāng)然，該分析只有一個(gè)語(yǔ)句，因此這里只顯示一項(xiàng)內(nèi)容。如果您有多個(gè)語(yǔ)句，應(yīng)該可以看到所有內(nèi)容。
                
 
                
                
                
                
在上面的屏幕上，請(qǐng)注意 Recommendation ID 列。單擊超鏈接將顯示詳細(xì)建議，如下所示：                
 
                
                
                
                
該屏幕將提供非常清楚的解決方案描述。它提出了兩個(gè)建議：創(chuàng)建分區(qū)表和使用索引。隨后，它發(fā)現(xiàn)索引已經(jīng)存在，因此建議保留該索引。                
如果您單擊 Action 列下方的 PARTITION TABLE，將看到 Oracle 為使其成為分區(qū)表而生成的實(shí)際腳本。但是，在單擊之前，在文本框中填入表空間名稱。這將允許 SQL Access Advisor 在構(gòu)建該腳本時(shí)使用該表空間：
                
Rem                Rem Repartitioning table "SCOTT"."TRANS"                Rem                SET SERVEROUTPUT ON                SET ECHO ON                Rem                Rem Creating new partitioned table                Rem                CREATE TABLE "SCOTT"."TRANS1"                (    "TRANS_ID" NUMBER,                "RES_ID" NUMBER,                "TRANS_DATE" DATE,                "AMT" NUMBER,                "STORE_ID" NUMBER(3,0)                ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING                TABLESPACE "USERS"                PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)                );                begin                dbms_stats.gather_table_stats(‘"SCOTT"‘, ‘"TRANS1"‘, NULL, dbms_stats.auto_sample_size);                end;                /                Rem                Rem Copying constraints to new partitioned table                Rem                ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);                Rem                Rem Copying referential constraints to new partitioned table                Rem                ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")                REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;                Rem                Rem Populating new partitioned table with data from original table                Rem                INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"                SELECT * FROM "SCOTT"."TRANS";                COMMIT;                Rem                Rem Renaming tables to give new partitioned table the original table name                Rem                ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11";                ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS";                
                腳本實(shí)際上將構(gòu)建一個(gè)新表，然后將其重命名以匹配原始表。
 
                
                
最后一個(gè)選項(xiàng)卡 Details 將顯示有關(guān)任務(wù)的某些有趣的詳細(xì)信息。盡管它們對(duì)于分析并不重要，但可以提供有關(guān)顧問程序如何得出這些結(jié)論的有價(jià)值線索，從而有助于您自己的思考過程。該屏幕分為兩部分，第一個(gè)部分是 Workload and Task Options，如下所示。                
 
                
                
                
                
屏幕的后半部分顯示任務(wù)的運(yùn)行日志。有時(shí)，顧問程序無(wú)法處理所有 SQL 語(yǔ)句。如果某些 SQL 語(yǔ)句被舍棄，就會(huì)在這里顯示，并計(jì)入 Invalid SQL String:Statements discarded 計(jì)數(shù)。如果您不明白為什么只分析了數(shù)個(gè) SQL 語(yǔ)句，下面就是原因。                
 
                
                
                
            
            
高級(jí)選項(xiàng)
            
在上面的第 10 步中，我使用了一個(gè)對(duì)高級(jí)設(shè)置的引用。我們來看看這些設(shè)置的作用。
            
單擊 Advanced Options 左側(cè)的加號(hào)，這將顯示一個(gè)屏幕，如下所示：
            
 
            
            
            
 
            
該屏幕允許您輸入將在其中創(chuàng)建索引的表空間的名稱、索引的創(chuàng)建模式等。對(duì)于分區(qū)建議，您可以指定實(shí)現(xiàn)分區(qū)的表空間等。
            
看來，最重要的元素是 Consider access structures creation costs recommendations 復(fù)選框。如果您選中該復(fù)選框，SQL Access Advisor 將考慮索引本身的創(chuàng)建成本。例如，是否應(yīng)該創(chuàng)建 10 個(gè)新索引，相關(guān)成本可能會(huì)導(dǎo)致 SQL Access Advisor 建議不創(chuàng)建它們。
            
您還可以在該屏幕中指定索引的最大大小。
            
與 SQL Tuning Advisor 的差異
            
在簡(jiǎn)介中，我只簡(jiǎn)單描述了該工具與 SQL Tuning Advisor 的不同，下面我們來詳細(xì)說明它們之間的差異。一個(gè)簡(jiǎn)單演示可以最好地說明這些差異。 
            
從 SQL Advisors 屏幕中，選擇 SQL Tuning Advisor 并運(yùn)行。完成后，下面是顯示結(jié)果的屏幕部分：
            
 
            
            
            
 
            
現(xiàn)在，如果您單擊 View 查看建議，將顯示一個(gè)如下所示的屏幕：
            
 
            
            
            
 
            仔細(xì)查看建議：它將根據(jù) RES_ID 列上的 TRANS 創(chuàng)建一個(gè)索引。但是，SQL Access Advisor 沒有執(zhí)行該建議。相反，它建議將表分區(qū)，原因如下：根據(jù)訪問模式和可用數(shù)據(jù)，SQL Access Advisor 確定分區(qū)比在列上構(gòu)建索引更加高效。與 SQL Tuning Advisor 提供的建議相比，這是一個(gè)更“實(shí)際”的建議。            
SQL Tuning Advisor 提出的建議只對(duì)應(yīng)以下四個(gè)目標(biāo)之一：
            
                
為統(tǒng)計(jì)信息丟失或失效的對(duì)象收集統(tǒng)計(jì)信息 
                
考慮優(yōu)化器的任何數(shù)據(jù)偏差、復(fù)雜謂詞或失效的統(tǒng)計(jì)信息 
                
重新構(gòu)建 SQL 以優(yōu)化性能
                
提出新索引建議
            
            這些建議僅與單個(gè)語(yǔ)句（而非整個(gè)負(fù)載）相關(guān)。因此，只能將 SQL Tuning Advisor 偶爾用于高負(fù)載或關(guān)鍵業(yè)務(wù)查詢。注意，與 SQL Access Advisor 相比（其標(biāo)準(zhǔn)更加寬松），該顧問程序只建議能夠提供重大性能改進(jìn)的索引。當(dāng)然，SQL Tuning Advisor 沒有分區(qū)顧問程序。            
用例
            SQL Access Advisor 對(duì)于調(diào)整模式（而不僅僅是查詢）很有用。作為一個(gè)最佳實(shí)踐，您可以使用該策略來開發(fā)高效的 SQL 調(diào)整計(jì)劃：            
                
搜索高成本 SQL 語(yǔ)句，或者（更好的是）評(píng)估整個(gè)負(fù)載。
                
將可疑語(yǔ)句放入 SQL 調(diào)整工具集。
                
使用 SQL Tuning Advisor 和 SQL Access Advisor 對(duì)其進(jìn)行分析。
                
得到分析結(jié)果；記錄建議。
                
將建議插入 SQL Performance Analyzer（參見本文）。
                
在 SQL Performance Analyzer 中檢查更改前后的情況，并得出最佳解決方案。
                
重復(fù)上述操作，直到獲得最佳模式設(shè)計(jì)。
                
獲得最佳模式設(shè)計(jì)之后，您可能希望使用 SQL 計(jì)劃管理基準(zhǔn)鎖定該計(jì)劃（如本文所述）。
            
            
結(jié)論
            
調(diào)整數(shù)據(jù)庫(kù)結(jié)構(gòu)是最費(fèi)時(shí)費(fèi)力的棘手任務(wù)之一，同時(shí)也是最有成效的任務(wù)之一。同樣，分區(qū)是一個(gè)非常有效的調(diào)整工具，但分區(qū)的選擇很難輕松決定。SQL Access Advisor 在這些過程中提供了一個(gè)非常有用的幫助。
            返回到“Oracle 數(shù)據(jù)庫(kù) 11g：面向 DBA 和開發(fā)人員的重要特性”主頁(yè)            
            
  Arup Nanda (arup@proligence.com) 擔(dān)任專職 Oracle DBA 已愈 12 年，他擁有 Oracle 數(shù)據(jù)庫(kù)技術(shù)各個(gè)領(lǐng)域的經(jīng)驗(yàn)，2003 年被 Oracle 雜志授予“年度 DBA”稱號(hào)。Arup 經(jīng)常在 Oracle 相關(guān)的活動(dòng)中發(fā)表演講并為 Oracle 相關(guān)刊物撰寫文章，他還是一位 Oracle ACE 總監(jiān)。他與其他人合作編寫了四本書，其中包括《RMAN Recipes for Oracle Database 11g:A Problem Solution Approach》。