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_ADVISOR
The 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