SQL Access Advisor in Oracle Database 10g

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Access Advisor in Oracle Database 10g，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5238字，纯文字阅读大概需要8分钟。

内容图文

The SQL Access Advisor makes suggestions about indexes and materialized views which might improve system performance. This article describes how to use the SQL Access Advisor in Oracle 10g.

• Enterprise Manager
• DBMS_ADVISOR
• Quick Tune
• Related Views

Related articles.

• SQL Access Advisor in Oracle Database 11g Release 1

Enterprise Manager

The SQL Access Advisor is accessible from Enterprise Manager. Specific reports can be produced by clicking on the "Advisor Central" link, then the "SQL Access Advisor" link. The resulting page allows you to create a workload and a SQL Access Advisor task. Once the task has completed you can view information about the findings and recommendations.

DBMS_ADVISOR

The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including SQL Access Advisor tasks. The following example shows how it is used to create, execute and display a typical SQL Access Advisor script for the current workload.

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 a SQL Access 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;

  IF l_num_found = 0 THEN
    DBMS_ADVISOR.create_sqlwkld(workload_name => l_wkld_name);
  END IF;

  -- 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;
  
  IF l_num_found = 0 THEN
    DBMS_ADVISOR.add_sqlwkld_ref(
      task_name     => l_taskname,
      workload_name => l_wkld_name);
  END IF;
  
  -- 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 resulting script.
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 command should be adjusted to allow the whole script to be displayed.

Quick Tune

If you just want to tune an individual statement you can use the QUICK_TUNE procedure as follows.

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 correct task name specified.

Related Views

The following views can be used to display the SQL Access Advisor output without using Enterprise Manager or the get_task_script function:

• DBA_ADVISOR_TASKS - Basic information about existing tasks.
• DBA_ADVISOR_LOG - Status information about existing tasks.
• DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
• DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.

For more information see:

• DBMS_ADVISOR
• SQL Access Advisor in Oracle Database 11g Release 1

Hope this helps. Regards Tim...

Back to the Top.

SQL Access Advisor in Oracle Database 10g

标签：optimizer   int   ica   parameter   viso   mode   recommend   limited   stat   

本文系统来源：http://www.cnblogs.com/zfox2017/p/7567814.html

内容总结

以上是互联网集市为您收集整理的SQL Access Advisor in Oracle Database 10g全部内容，希望文章能够帮你解决SQL Access Advisor in Oracle Database 10g所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---