`

Oracle 11g SQL Access Advisor Enhancements(原创)

 
阅读更多

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;

 

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

             http://www.oracle-base.com/articles/11g/sql-access-advisor-11gr1.php

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

0
0
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics