SQL Access Advisor in Oracle Database 10g

Source: Internet
Author: User

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 to 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

DBMS_ADVISORThe package can is used to create and execute any advisor tasks, including SQL Access advisor tasks. The following example shows how it's used to create, execute and display a typical SQL Access Advisor script for the Curr ENT workload.

DECLARE l_taskname VARCHAR2 (+): = ' test_sql_access_task ';  L_task_desc VARCHAR2: = ' Test SQL Access task ';  L_wkld_name VARCHAR2 (+): = ' 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_n    ame = 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 100000SET PAGESIZE 50000SELECT dbms_advisor.get_task_script (' Test_sql_access_task ') as Scriptfrom dual; SET PAGESIZE24 

The value for the SET LONG command should is adjusted to allow the whole script to be displayed.

Quick Tune

If you just want to tune a 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 and is displayed using the previous query with the correct task name specified.

Related views

The following views can is used to display the SQL Access Advisor output without using an Enterprise Manager or the fun get_task_script Ction:

    • 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 to 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.