Oracle SQL Access Advisor 说明
?
一.說明
1.1 相關內(nèi)容
在如下官方文檔中提到了一些優(yōu)化工具的說明：
Monitoring and Tuning the Database
http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm
?
Using Advisors to Optimize DatabasePerformance
Oracle Databaseincludes a set of?advisors?to help you manage and tune your database.This section contains background information about these advisors andinstructions for their use. The following topics are covered:
(1)????AboutAdvisors
(2)????Aboutthe SQL Advisors
(3)????Aboutthe Automatic SQL Tuning Advisor
(4)????Configuringthe Automatic SQL Tuning Advisor
(5)????ViewingAutomatic SQL Tuning Results
(6)????Runningthe SQL Tuning Advisor
(7)????Runningthe SQL Access Advisor
(8)????OptimizingMemory Usage with the Memory Advisors
?
其中的部分內(nèi)容之前也有整理，參考：
如何用 SQL Tuning Advisor (STA) 優(yōu)化SQL語句
http://blog.csdn.net/tianlesoftware/article/details/5630888
?
Oracle ADDM 自動診斷監(jiān)視工具 介紹
http://blog.csdn.net/tianlesoftware/article/details/5630942
?
Oracle ASH(Active SessionHistory) 說明
http://blog.csdn.net/tianlesoftware/article/details/6448765
?
Oracle AWR 介紹
http://blog.csdn.net/tianlesoftware/article/details/4682300
?
這里我們看一下SQLAccess Advisor的說明：
?
The SQL AccessAdvisor tunes a schema to a given SQL workload. For example, the SQL AccessAdvisor can provide recommendations for creating indexes, materialized views,or partitioned tables for a given workload.?
?
有關在OEM中配置SQL Access Advisor的方法參考：
Monitoring and Tuning the Database
http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm
?
1.2 SQL Access Advisor 概述
在另篇里有SQLAccess Advisor 的詳細說明：
?SQL AccessAdvisor
http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm
?
Materialized views, partitions, and indexes are essential when tuning a database to achieveoptimum performance for complex, data-intensive queries.?SQL AccessAdvisor helps you achieve your performance goals by recommending the proper setof materialized views, materialized view logs, partitions, and indexes for agiven workload.
--數(shù)據(jù)庫對負責查詢的優(yōu)化的本質(zhì)是對物化視圖，分區(qū)和索引的優(yōu)化，而SQL Access Advisor 可以幫助我們實現(xiàn)優(yōu)化，其可以提供物化視圖，分區(qū)和索引的推薦值。
?
Understanding and using these structures is essential when optimizing SQL as they can resultin significant performance improvements in data retrieval. The advantages,however, do not come without a cost. Creation and maintenance of these objectscan be time consuming, and space requirements can be significant. In particular,partitioning of an unpartitioned base table is a complex operation that must beplanned carefully.
--在優(yōu)化SQL時，理解和使用這些結(jié)構(gòu)可以大幅的提高數(shù)據(jù)檢索的性能，但是這也是有代價的，創(chuàng)建和維護這些對象也需要消耗時間，空間，特別是對非分區(qū)表進行分區(qū)是一個復雜的操作，需要慎重考慮。
?
SQL AccessAdvisor index recommendations include bitmap, function-based, and B-treeindexes. A bitmap index offers a reduced response time for many types of ad hocqueries and reduced storage requirements compared to other indexing techniques.B-tree indexes are most commonly used in a data warehouse to index unique ornear-unique keys.
? ? ? ? --SQL Access Advisor 索引的推薦包括bitmap，function-based，B-tree 索引。
?
SQL AccessAdvisor materialized view recommendations include fast refreshable and fullrefreshable MVs, for either general rewrite or exact text match rewrite.
???????--SQL Access Advisor 物化視圖推薦包括fast refreshable和fullrefreshable。
?
SQL Access Advisor,using the?TUNE_MVIEW?procedure, also recommends how to optimizematerialized views so that they can be fast refreshable and take advantage ofgeneral query rewrite.
??????--SQL Access Advisor 使用TUNE_MVIEW過程來建議如何優(yōu)化物化視圖。
?
In addition, SQLAccess Advisor can recommend partitioning on an existing unpartitioned basetable to improve performance. Furthermore, it may recommend new indexes andmaterialized views that are themselves partitioned. While creating newpartitioned indexes and materialized view is no different from theunpartitioned case, partitioning existing base tables should be executed withcare. This is especially true when indexes, views, constraints, or triggers aredefined on the table. See?"SpecialConsiderations when Script Includes Partitioning Recommendations"?fora list of issues involving base table partitioning for performing this taskonline.
?????-- SQL Access Advisor 可以建議在非分區(qū)表上使用分區(qū)來提高性能。此外可能建議在表上（分區(qū)和非分區(qū)表）創(chuàng)建新的索引和物化視圖。
?
You can run SQLAccess Advisor from Oracle Enterprise Manager (accessible from the AdvisorCentral page) using SQL Access Advisor Wizard or by invoking the?DBMS_ADVISOR?package.The?DBMS_ADVISORpackage consists of a collection of analysis and advisoryfunctions and procedures callable from any PL/SQL program.
--也可以從OEM中調(diào)用DBMS_ADVISOR包來運行SQL Access Advisor。
?
Figure18-1?illustrates how SQL Access Advisor recommends access structuresfor a given workload obtained from a user-defined table or the SQL cache. If aworkload is not provided, then it can generate and use a hypothetical workloadalso, provided the user schema contains dimensions defined by the?CREATE?DIMENSION?keyword.
--下圖給出了SQL Access Advisor 訪問用戶表或者SQL 緩存中的一個架構(gòu)圖，如果沒有提供workload,那么它會假設一個workload。
?
Using SQL Access Advisor in EnterpriseManager or API, you can do the following:
--在EM中使用SQLAccess Advisor或者API，可以實現(xiàn)如下功能：
（1）Recommend materialized views andindexes based on collected, user-supplied, or hypothetical workloadinformation.
（2）Recommend partitioning of tables,indexes, and materialized views.
（3）Mark, update, and removerecommendations.
?
In addition, you can use SQL Access AdvisorAPI to do the following:
--使用API還可以實現(xiàn)：
（1）Perform a quick tune using a singleSQL statement.
（2）Show how to make a materializedview fast refreshable.
（3）Show how to change a materializedview so that general query rewrite is possible.
?
To make recommendations, SQL Access Advisor relies on structural statistics about tableand index cardinalities of dimension level columns,?JOIN?KEY?columns,and fact table key columns. You can gather either exact or estimated statisticswith the?DBMS_STATS?package.Because gathering statistics is time-consuming and full statistical accuracy isnot required, it is generally preferable to estimate statistics. Withoutgathering statistics on a given table, queries referencing this table aremarked as invalid in the workload, resulting in no recommendations being madefor those queries. It is also recommended that all existing indexes andmaterialized views have been analyzed. See?OracleDatabase PL/SQL Packages and Types Reference?for more informationabout the?DBMS_STATS?package.
? ? ? --為了實現(xiàn)建議的功能，SQL Access Advisor 依賴于結(jié)構(gòu)的統(tǒng)計信息，如表和索引的基數(shù)。可以使用DBMS_STATS包來收集統(tǒng)計信息，因為收集統(tǒng)計統(tǒng)計信息需要消耗時間，索引不需要精確的收集，一般都是收集大概的統(tǒng)計信息。 如果查詢的表上沒有統(tǒng)計信息，那么查詢時，在workload里的表就會標記為無效，那么就不能提供相關的優(yōu)化建議。同時，對于已經(jīng)存在的索引和物化視圖也是建議進行分析。
?
1.3 SQL Access Advisor 使用概述
An easy way touse SQL Access Advisor is to invoke its wizard, which is available inEnterprise Manager from the Advisor Central page. If you prefer to use SQLAccess Advisor through the?DBMS_ADVISORpackage, then this sectiondescribes the basic components and the sequence in which you must call theprocedures.
--使用SQL Access Advisor 最簡單的方法是在OEM中調(diào)用向?qū)?#xff0c;當然也可以使用DBMS_ADVISOR 包，具體的調(diào)用方法有如下4步。
Createa task
Definethe workload
Generatethe recommendations
Viewand implement the recommendations
?
1.3.1?Create a task
An?advisortask?is a container in the data dictionary that stores the inputs to andthe results of an intelligent advisor analysis run. All information relating tothe recommendation operation, including the results, resides in the task.
? ? ? --Advisor task 是數(shù)據(jù)字典里的一個容器，其用來存儲輸入和advisor 分析的結(jié)果。 所有的于recommendation 操作相關的信息和結(jié)果都存儲在task里。
???????????
Before SQLAccess Advisor can make?recommendations, you mustcreate a task using either of the following:
--在使用SQL Access Advisor 之前需要使用如下方法創(chuàng)建一個task：
（1）The wizard inOracle Enterprise Manager or the?DBMS_ADVISOR.QUICK_TUNE?procedure,which creates the task automatically。
（2）The?DBMS_ADVISOR.CREATE_TASK?procedure
?
You can controlwhat a task does by defining parameters for the task using the?DBMS_ADVISOR.SET_TASK_PARAMETER?procedure.
--可以使用DBMS_ADVISOR 過程來設置task的參數(shù)。
?
1.3.2 ?Definethe workload
A?workload?consistsof one or more SQL statements, plus statistics and attributes that fullydescribe each statement. A?full workload?contains all SQL statementsfrom a target business application. A partial workload?contains a subsetof SQL statements. The difference is that for full workloads SQL Access Advisormay recommend dropping unused materialized views and indexes.
? ? ? ?--Wordload 包含一個或者多個SQL 語句，及其統(tǒng)計信息和每個語句的屬性。Full workload 包含目標應用中的所有SQL 語句，Partialworkload包含SQL statements的一個子集。
?
You cannot useSQL Access Advisor without a workload. A workload may contain a variety ofstatements. SQL Access Advisor ranks the entries according to a specificstatistic, business importance, or combination of the two, which enables theadvisor to process the most important SQL statements first.
? ? ? ? --只有創(chuàng)建了workload 才可以使用SQL Access Advisor。 Workload里包含了各種SQL語句，SQL Access Advisor 根據(jù)統(tǒng)計信息,重要性或者2者的結(jié)合對這些SQL進行排序，越重要的SQL 會被advisor 優(yōu)先處理。
?
SQL AccessAdvisor may require particular attributes to be present in a valid workload.Although the advisor can perform analysis when items are missing, the qualityof the recommendations may be lower. For example, SQL Access Advisor requires aworkload to contain a SQL query and the user who ran the query, with otherattributes as optional. However, if the workload also contains I/O and CPUdata, then SQL Access Advisor can better evaluate statement efficiency.
? ? ? --SQL Access Advisor 需要指定特別的屬性到一個有效的workload，雖然在沒有指定的情況下也可以運行，但是此時的recommendations的質(zhì)量就會低很多。
?
The databasestores a workload as a SQL tuning set. You can access the workload with the?DBMS_SQLTUNE?packageand share it among many Advisor tasks. Because the workload is independent, youmust link it to a task using the?DBMS_ADVISOR.ADD_STS_REF?procedure.After this link has been established, you cannot delete or modify the workloaduntil all advisor tasks have removed their dependency on the workload. Aworkload reference is removed when a user deletes a parent advisor task ormanually removes the workload reference from the task by using the?DBMS_ADVISOR.DELETE_STS_REF?procedure.
? ? ? ?--數(shù)據(jù)庫已SQL tuning set的方式存儲workload，所以我們可以使用DBMS_SQLTUNE包來訪問workload，并將workload 于advisor tasks 分享。因為workload是獨立的，所以我們必須使用DBMS_ADVISOR.ADD_STS_REF過長將workloadlink到task上。 Link 建立之后，我們不能刪除或修改workload，知道所有的advisortask 從關聯(lián)的workload中移除。
? ? ? ? ?當用戶刪除一個parent advisor task 或者使用DBMS_ADVISOR.DELETE_STS_REF過程手工的移除workload相關的task，此時workload reference 也會移除。
?
You can create ahypothetical workload from a schema by analyzing dimensions and constraints.For best results, provide a workload as a SQL tuning set. The?DBMS_SQLTUNE?packageprovides several helper functions that can create SQL tuning sets from commonworkload sources, such as the SQL cache, a user-defined workload stored in atable, and a hypothetical workload.
? ? ? ?--我們可以使用analyze dimensions 和constraint 從schema中創(chuàng)建一個虛擬的workload。為了更準確的結(jié)構(gòu)，已SQL tuning set 的方式提供一個workload，DBMS_SQLTUNE 包提供了一些幫助函數(shù)，其可以從common workload source中創(chuàng)建SQL tuning sets，如SQLcache，用戶自定義存儲在table里的workload，虛擬機的workload等。
?
At the time therecommendations are generated, you can apply a filter to the workload torestrict what is analyzed. This restriction provides the ability to generatedifferent sets of recommendations based on different workload scenarios. SQLAccess Advisor parameters control the recommendation process and customizationof the workload. These parameters control various aspects of the process, suchas the type of recommendation required and the naming conventions for what itrecommends.
? ? ? ?--在生成recommendations時，可以應用filter 來過濾workload，從而限制其analyze。 這個限制提供了根據(jù)不同的workload來生成不同recommendations的能力。 SQL Access Advisor 參數(shù)控制recommendation進程和定制workload。 這些參數(shù)控制各方面的進程。
?
To set theseparameters, use the?SET_TASK_PARAMETER?procedure. Parameters arepersistent in that they remain set for the life span of the task. When aparameter value is set using SET_TASK_PARAMETER, it does not change until youmake another call to this procedure.
--可以使用SET_TASK_PARAMETER 過程來設置這些參數(shù)。
?
1.3.3???Generatethe recommendations
After a task?exists and a workload is linked to the task and theappropriate parameters are set, you can generate recommendations using the?DBMS_ADVISOR.EXECUTE_TASK?procedure.These recommendations are stored in SQL Access Advisor Repository.
? ? ? --創(chuàng)建了task，并將workload link到task之后，在設置好相關的參數(shù)，就可以使用DBMS_ADVISOR.EXECUTE_TASK 過程來生成recommendations。 這個recommendations存儲在SQL Access Advisor Repository里。
?
Therecommendation process generates several recommendations. Each recommendationspecifies one or more actions. For example, a recommendation could be to createseveral materialized view logs, create a materialized view, and then analyze itto gather statistics.
? ? ? ?--recommendations 進程生成recommendations，每個recommendation指定一個或者多個actions。 例如：一個recommendation可以創(chuàng)建一些物化視圖日志，創(chuàng)建物化視圖，和收集統(tǒng)計信息。
?
A task recommendation can range from a simple suggestion to a complex solution thatrequires partitioning a set of existing base tables and implementing a set ofdatabase objects such as indexes, materialized views, and materialized viewlogs. When an advisor task is executed, SQL Access Advisor carefully analyzescollected data and user-adjusted task parameters. It then forms a structuredrecommendation that the user can view and implement.
? ? ? ?--Task recommendation 是一個范圍，從簡單的建議到復雜的解決方案。當advisortask 執(zhí)行時，SQL Access Advisor 會仔細分析收集數(shù)據(jù)和用戶定義的參數(shù)。
?
1.3.4???Viewand implement the recommendations
You can view therecommendations from SQL Access Advisor in either of the following ways:
--可以使用如下2種方法來查看recommendation的內(nèi)容：
（1）Using thecatalog views
（2）Generating ascript using the?DBMS_ADVISOR.GET_TASK_SCRIPT?procedure
?
In EnterpriseManager, you may display the recommendations after SQL Access Advisor processhas completed. See?"ViewingRecommendations"?for a description of using the catalog views toview the recommendations. See?"GeneratingSQL Scripts"?to see how to create a script.
??????????? --在OEM中，在SQL Access Advisor 進程處理完畢后會自動顯示recommendation。
?
You need notaccept all recommendations. You can mark the ones to be included in therecommendation script. However, when base table partitioning is recommended,some recommendations depend on others. For example, you cannot implement alocal index if you do not also implement the partitioning recommendation on theindex base table.
???????????
The final stepis then implementing the recommendations and verifying that query performancehas improved.
?
1.3.5 SQLAccess Advisor Repository
All theinformation needed and generated by SQL Access Advisor resides in the Advisorrepository, which is a part of the database dictionary. The benefits of usingthe repository are that it:
--Advisor 生成的所有信息都存放在Advisor repository中，其是數(shù)據(jù)字典的一部分，使用repository有如下好處：
(1)????Collects a complete workloadfor SQL Access Advisor.
(2)????Supports historical data.
(3)????Is managed by the server.
?
1.3.6 使用SQLAccess Advisor需要的權(quán)限
You must have the?ADVISOR?privilege tomanage or use SQL Access Advisor. When processing a workload, SQL AccessAdvisor attempts to validate each statement to identify table and columnreferences. The database achieves validation by processing each statement as ifit were being executed by the statement's original user.
--必須需要有ADVISOR權(quán)限
?
If the user doesnot have?SELECT?privileges to a particular table, then SQL AccessAdvisor bypasses the statement referencing the table. This behavior can causemany statements to be excluded from analysis. If SQL Access Advisor excludesall statements in a workload, then the workload is invalid. SQL Access Advisorreturns the following message:
QSM-00774, thereare no SQL statements to process for task TASK_NAME
??????????? --必須需要有指定表的select 的權(quán)限，否則會報QSM-774錯誤。
?
To avoid missingcritical workload queries, the current database user must have?SELECT?privilegeson the tables targeted for materialized view analysis. For these tables, these?SELECT?privilegescannot be obtained through a role.
?
Additionally,you must have the?ADMINISTER SQL TUNING SET?privilege to create andmanage workloads in SQL tuning set objects. To run the Advisor on SQL tuningsets owned by other users, you must have the ADMINISTER ANY SQL TUNING SET?privilege.
--還需要?ADMINISTER SQL TUNING SET的權(quán)限來創(chuàng)建和管理workload。
?
?
二.手工生成SQLAccess Advisor 示例
From:
http://www.oracle-base.com/articles/10g/SQLAccessAdvisor10g.php
?
2.1 DBMS_ADVISOR
The?DBMS_ADVISOR?packagecan be used to create and execute any advisor tasks, including SQL AccessAdvisor tasks. The following example shows how it is used to create, executeand display a typical SQL Access Advisor script for the current workload.
--DBMS_ADVISOR 包可以用來創(chuàng)建和執(zhí)行advisor 任務。
?
DECLARE
?l_taskname?? ??VARCHAR2(30)??:= 'test_sql_access_task';
?l_task_desc??? VARCHAR2(128)? := 'Test SQL Access Task';
?l_wkld_name??? VARCHAR2(30)?? := 'test_work_load';
?l_saved_rows?? NUMBER???????? := 0;
?l_failed_rows? NUMBER???????? := 0;
?l_num_found??? NUMBER;
BEGIN
??-- Create an SQLAccess Advisor task.
?DBMS_ADVISOR.create_task (
???advisor_name => DBMS_ADVISOR.sqlaccess_advisor,
???task_name??? => l_taskname,
???task_desc??? => l_task_desc);
???
? -- Reset the task.
?DBMS_ADVISOR.reset_task(task_name => l_taskname);
?
? -- Create a workload.
?SELECT COUNT(*)
?INTO?? l_num_found
?FROM?? user_advisor_sqlw_sum
?WHERE? workload_name =l_wkld_name;
?
? IFl_num_found = 0 THEN
???DBMS_ADVISOR.create_sqlwkld(workload_name => l_wkld_name);
? ENDIF;
?
? -- Link the workload to the task.
?SELECT count(*)
?INTO?? l_num_found
?FROM?? user_advisor_sqla_wk_map
?WHERE? task_name???? = l_taskname
?AND??? workload_name =l_wkld_name;
?
? IFl_num_found = 0 THEN
???DBMS_ADVISOR.add_sqlwkld_ref(
?????task_name???? => l_taskname,
?????workload_name => l_wkld_name);
? ENDIF;
?
? -- Set workload parameters.
?DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
? DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name,'MODULE_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);
?DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
?DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ORDER_LIST', 'PRIORITY,OPTIMIZER_COST');
?DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
?DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
?
?DBMS_ADVISOR.import_sqlwkld_sqlcache(l_wkld_name, 'REPLACE', 2,l_saved_rows, l_failed_rows);
?
? -- Set task parameters.
?DBMS_ADVISOR.set_task_parameter(l_taskname, '_MARK_IMPLEMENTATION','FALSE');
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'EXECUTION_TYPE','INDEX_ONLY');
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'MODE', 'COMPREHENSIVE');
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'DML_VOLATILITY', 'TRUE');
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'WORKLOAD_SCOPE','PARTIAL');
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
?DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_OWNER', DBMS_ADVISOR.ADVISOR_UNUSED);
?
? -- Execute the task.
?DBMS_ADVISOR.execute_task(task_name => l_taskname);
END;
/
?
-- Display the resultingscript.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('test_sql_access_task') AS script FROM??dual;
SET PAGESIZE 24
?
The value for the?SET LONG?commandshould be adjusted to allow the whole script to be displayed.
?
在我測試環(huán)境上的輸入結(jié)果如下：
PL/SQL procedure successfully completed.
?
SCRIPT?????????????????????????????????????????????????????????????????????????
--------------------------------------------------------------------------------
Rem? SQL AccessAdvisor: Version 10.2.0.4.0 - Production???????????????????????
Rem???? ????????????????????????????????????????????????????????????????????????
Rem? Username:??????? SYS??????????????????????????????????????????????????????
Rem? Task:??????????? test_sql_access_task?????????????????????????????????????
Rem? Executiondate: ?31/01/2012 21:50?????????????????????????????????????????
Rem????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????
CREATE BITMAP INDEX "QSOA"."DATA_OA_MESSAGE_IDX$$_167F0001"????????????????????
??? ON"QSOA"."DATA_OA_MESSAGE"????????????????????????????????????????????????
???("MESS_TYPE")??????????????????????????????????????????????????????????????
??? COMPUTESTATISTICS;??????????????????????? ?????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????
CREATE INDEX"ZHAOKA"."CFG_GAME_AREA_S_IDX$$_167F0004"?????????????????????????
??? ON "ZHAOKA"."CFG_GAME_AREA_SERVER"?????????????????????????????????????????
???("AREA_ID","AREA_NAME","SERVER_ID","SERVER_NAME")??????????????????????????
??? COMPUTESTATISTICS;????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????
….???????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
?
2.2 Quick Tune
If you just wantto tune an individual statement you can use the?QUICK_TUNE?procedureas follows.
--如果僅僅是調(diào)整一個獨立的語句，可以使用QUICK_TUNE過程：
?
BEGIN
?DBMS_ADVISOR.quick_tune(
???advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
???task_name??? =>'emp_quick_tune',
???attr1??????? => 'SELECT e.*FROM emp e WHERE UPPER(e.ename) = ''SMITH''');
END;
/
Any recommendations can then be displayed using the previous query with the correcttask name specified.
查詢輸出結(jié)果和之前的一樣，使用:
Select DBMS_ADVISOR.get_task_script(‘emp_quick_tune’) fromdual;
?
2.3 Related Views
The followingviews can be used to display the SQL Access Advisor output without usingEnterprise Manager or the?get_task_script?function:
--可以使用以下視圖來查看advisor的輸出：
(1)????DBA_ADVISOR_TASKS：Basic information about existingtasks.
(2)????DBA_ADVISOR_LOG?：Status information about existingtasks.
(3)????DBA_ADVISOR_FINDINGS?： Findings identified for anexisting task.
(4)????DBA_ADVISOR_RECOMMENDATIONS?： Recommendations for the problemsidentified by an existing task.
?
更多的示例參考官網(wǎng)：
?SQL Access Advisor
http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm
?
?
?
?
?
-------------------------------------------------------------------------------------------------------
版權(quán)所有，文章允許轉(zhuǎn)載，但必須以鏈接方式注明源地址，否則追究法律責任!
Blog:?? http://blog.csdn.net/tianlesoftware
Weibo: ?????????? http://weibo.com/tianlesoftware
Email: ??????????? tianlesoftware@gmail.com
Skype: ?????????? tianlesoftware
?
-------加群需要在備注說明Oracle表空間和數(shù)據(jù)文件的關系，否則拒絕申請----
DBA1 群：62697716(滿);?? DBA2 群：62697977(滿)??DBA3 群：62697850(滿)??
DBA 超級群：63306533(滿);? DBA4 群：83829929? ?DBA5群： 142216823
DBA6 群：158654907? ??DBA7 群：172855474 ???DBA總?cè)?#xff1a;104207940
總結(jié)
以上是生活随笔為你收集整理的Oracle SQL Access Advisor 说明的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 摩斯密码是什么？(在线摩斯密码加密)
- 下一篇： Linux 指令大全(3)