Overview SQL Access Advisor
Oracle introduced the SQL Access Advisor in the Oracle database 10g Release to help you create efficient access structures to optimize SQL queries. The advisor accepted either an actual workload as input (from the cursor cache or an STS from the automatic workload repository) or used a hypothetical workload created by you to recommend which indexes, materialized views, or materialized view logs to create and drop to improve SQL performance. In Oracle Database 11g, there are several enhancements to the SQL Access Advisor:
- New procedures.
- In addition to its usual table, index, materialized view, and materialized view log recommendations, the advisor now also recommends partitioning of tables, indexes, and materialized views.
- Reporting of intermediate results at publish points.
New Procedures
Although you can use other sources for the workload, in Oracle Database 11g, Oracle seems to suggest using a SQL Tuning Set (STS) as the source for the SQL Access Advisor. An STS has the benefit of being a separate identity and can be shared by various Advisor tasks, not just the Advisor that creates it. Once an Advisor task references an STS, that STS can’t be deleted or changed until all Advisor tasks remove their dependency on it. An STS also lets you capture and store different types of SQL workload together as a persistent object in the database. As in Oracle Database 10g, you use the DBMS_SQLTUNE package to create the initial empty STS. In Oracle Database 10g, you used a SQL Workload object to capture the workload, which you can create by using the CREATE_SQLWKLOAD procedure. Because the SQL Workload is independent, you used the ADD_SQLWKLD_REF procedure to link the SQL Workload object to a SQL Advisor task to protect the workload from being removed or modified. Finally, you used the DELETE_SQLWKLD_REF procedure to remove the workload reference manually from the Advisor task (you could also drop the Advisor task to make the SQL Workload go away).
When using the SQL Access Advisor, you can choose your workload from one of the following sources: Current and Recent SQL Activity, an existing STS, or a hypothetical workload.
Partitioning Recommendations
In addition to its usual table, index, and materialized view recommendations, the SQL Access Advisor makes the following recommendations in Oracle Database 11g.
- Partition a table: The partitioning methods are range, interval, list, hash, range-hash, and range-list.
- Partition an index: The partitioning methods are local, range, and hash.
- Partition materialized view: The partitioning schemes are range, interval, list, hash, range-hash, and range-list.
The SQL Access Advisor invokes the DBMS_REDEFINITION package to implement its partition recommendations online. The SQL Access Advisor can make partitioning recommendations both under the limited tuning option and the comprehensive tuning option. The comprehensive tuning option uses SQL profiles and also allows you to specify a time limit for the tuning task, which is 30 minutes, by default. Partitioning is usually a more time-consuming and complex task when compared to, say, the creation of a simple index. Follow these guidelines when using the SQL Access Advisor in Oracle Database 11g, if you want to get good partitioning recommendations:
- The tables must be large, with a minimum of 10,000 rows.
- If the base tables have a bitmap index defined on them, you must remove them before running the advisor recommendation script because you can’t migrate the bitmap indexes correctly. After the advisor creates a partitioned table from an unpartitioned table, you can re-create the dropped bitmap index.
- The advisor will generate partitioning recommendations for only columns of type DATE and NUMBER. If a SQL statement in the workload doesn’t use a predicate or join with these types of columns, the database won’t make any partitioning recommendations.
- Interval is the default partitioning mode. Hash partitioning is offered only to facilitate partition-wise joins.
- You must ensure that you have enough space to hold the original table and a copy of it because the DBMS_REDEFINITION packages make a temporary copy of the source table.
- If the recommendations include a partitioning recommendation along with other types of recommendations such as creating a new index, it’s not a good idea to decide to only create the index and leave the table as is, without partitioning it. If you can’t partition the table for some reason, you must run the SQL Access Advisor again, this time with the partitioning option disabled. The reason for this is that the index recommendation in this case was predicated on the existence of a partitioned table. Therefore, skipping the partitioning recommendation but accepting the indexing recommendations isn’t a correct choice.
Back up your database before starting the advisor session because that’s probably the quickest and easiest way to undo a major table partitioning task.
Publish Points
Previously, once the SQL Access Advisor started its recommendation analysis, the only way to access the results was to wait until the processing was completed or to interrupt the task. In Oracle Database 11g, you can access the results even before the advisor task is completed. This offers you the potential to save considerable time in implementing key advisor recommendations because you can interrupt a long running task and glean an idea about the recommendations. You can break down a large workload into smaller chunks using your own criteria and have the advisor analyze each chunk of the workload and report its intermediate results at the publish points. Once you interrupt a task, the advisor will mark the task as INTERRRUPTED, and you can view the intermediate results and generate the recommendation scripts. You also have the choice to tell the advisor to resume the task that you interrupted. A word of caution, however: in order to make any base table partitioning recommendations, the SQL Access Advisor needs to analyze almost the entire workload. If you interrupt a task early, you probably won’t see any type of partition-related recommendations. However, a late stage intermediate result may quite possibly yield partition recommendations if the advisor figures out it’s beneficial to do so.
QUICK_TUNE Procedure
This procedure performs an analysis and generates recommendations for a single SQL statement.
This provides a shortcut method of all necessary operations to analyze the specified SQL statement. The operation creates a task using the specified task name. The task will be created using a specified Advisor task template. Finally, the task will be executed and the results will be saved in the repository.
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);
- advisor_name: Name of the Advisor that will perform the analysis.
- task_name: Name of the task.
- attr1: the single SQL statement to tune.
- attr2: For the SQL Access Advisor, attr2 is the user who would execute the single statement. If omitted, the current user will be used.
- attr3: Advisor-specific attribute in the form of a NUMBER.
- task_or_template: An optional task name of an existing task or task template.
If indicated by the user, the final recommendations can be implemented by the procedure.
The task will be created using either a specified SQL Access task template or the built-in default template of SQLACCESS_GENERAL. The workload will only contain the specified statement, and all task parameters will be defaulted.
Examples
DECLARE
task_name VARCHAR2(30);
BEGIN
task_name := 'My Task';
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,
'SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=10');
END;
/
Running a SQL Access Advisor Job Using PL/SQL
DECLARE
taskname varchar2(30) := 'SQLACCESS3638195';
task_desc varchar2(256) := 'SQL Access Advisor';
task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
task_id number := 0;
num_found number;
sts_name varchar2(256) := 'SQLACCESS3638195_sts';
sts_cursor dbms_sqltune.sqlset_cursor;
BEGIN
/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_id,
taskname,
task_desc,
task_or_template);
/* Reset Task */
dbms_advisor.reset_task(taskname);
/* Delete Previous STS Workload Task Link */
select count(*)
into num_found
from user_advisor_sqla_wk_map
where task_name = taskname
and workload_name = sts_name;
IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld_ref(taskname,sts_name,1);
END IF;
/* Delete Previous STS */
select count(*)
into num_found
from user_advisor_sqlw_sum
where workload_name = sts_name;
IF num_found > 0 THEN
dbms_sqltune.delete_sqlset(sts_name);
END IF;
/* Create STS */
dbms_sqltune.create_sqlset(sts_name, 'Obtain workload from cursor cache');
/* Select all statements in the cursor cache. */
OPEN sts_cursor FOR
SELECT VALUE(P)
FROM TABLE(dbms_sqltune.select_cursor_cache) P;
/* Load the statements into STS. */
dbms_sqltune.load_sqlset(sts_name, sts_cursor);
CLOSE sts_cursor;
/* Link STS Workload to Task */
dbms_advisor.add_sqlwkld_ref(taskname,sts_name,1);
/* Set STS Workload Parameters */
dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT','25');
dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');
/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');
dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');
dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');
/* Execute Task */
dbms_advisor.execute_task(taskname);
END;
/
The "GET_TASK_SCRIPT" function allows you to display the recommendations once the task is complete.
-- Display the resulting script.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('SQLACCESS3638195') AS script
FROM dual;
相关推荐
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例,有9个步骤, 后面 其他语句,都按照这个步骤来做就可以了。
oracle 10gADDM 和 SQL Tuning Advisor
关于oracle的SQL Tuning Advisor全面的使用介绍,包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set,sql_profile的操作。里面涉及到的内容都有可执行的代码。
SQL调整工具集(SQL Tuning Set,STS)是Oracle 10g的SQL Tuning Advisor特性的一个组成部分。每个调整工具集都包含一个或几个SQL语句,以及正确解释它们所需的上下文信息。SQL Tuning Advisor用一个调整工具集作为...
Oracle+10G+最佳20位新特性:SQL+Advisor+和+ADDM
SQLAdvisor是由美团点评公司技术工程部DBA团队(北京)开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。目前SQL...
SQL Access Advisor 获得关于基于表(而不仅仅是数据)的实际使用的最佳表设计的建议。 PL/SQL:高效的编码 在不同的事件处触发几次的触发器以及迫使相同类型的触发器排队的能力是一些新增亮点。 RMAN 探究 Data ...
基本信息 出版社: 清华大学出版社; 第1版 (2009年1月1日) 平装: 594页 语种: 简体中文 开本: 16 ... 使用最新的Oracle Database 11g工具——Oracle Total Recall和Oracle Flashback Data Archive等。
OCPOCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-052,1Z0-053) 共2部分:此为第002部分 基本信息 原书名: OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052...
基本信息 出版社: 清华大学出版社; 第1版 (2009年1月1日) 平装: 594页 语种: 简体中文 开本: 16 ... 使用最新的Oracle Database 11g工具——Oracle Total Recall和Oracle Flashback Data Archive等。
SQL Access Advisor E. Automatic Database Diagnostic Monitor(ADDM) Answer: A 你发现你的数据高速缓存区(Database Buffer Cache)不够用了,可以使用 Memory Advisor 来确定其大小,自动数据库诊断监视器 ...
解压密码:123 ||SQL Sentry Performance Advisor是一款出色的数据库监控工具,它允许您快速、轻松地跟踪和识别SQL服务器之间的性能问题。它是一个可靠和强大的应用程序,提供了各种智能工具,以帮助您立即识别和...
Speed up the execution of important database queries by making good choices about which indexes to create. Choose correct index types ...Chapter 9: SQL Tuning Advisor Chapter 10: In-Memory Column Store
Speed up the execution of important database queries by making good choices about which indexes to create. Choose correct index types for ... SQL Tuning Advisor Chapter 10. In Memory Column Store
Oracle10g性能优化,内容: • 使用适合于可用工具的Oracle 数据库优化方法 • 利用数据库Advisor 主动优化Oracle 数据库 • 使用基于自动工作量资料档案库的工具优化数据库 • 使用Statspack 报表优化数据库 • ...
官方资料:Oracle白皮书_Oracle数据库11g中的分区 分区的优势:分区的基本知识;使用分区提高可管理性;使用分区提岛性能;使用分区提高可用性;分区一为业务建模:基本分区策略;分区扩展;PARTITION ADVISOR;分区策略和...
美团技术团队出品的用来优化sql的工具,可以对SQL语句进行优化,分析瓶颈
IYSQL - 基于SQLAdvisor与Soar的开源Web分析工具,用于改进你的SQL.
SQLAdvisor是美团开源的一款SQL索引优化建议工具, 是由美团点评公司技术工程部DBA团队(北京)开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合分析SQL中的where条件、聚合条件、...