Oracle Sql advisors - ghdrako/doc_snipets GitHub Wiki

SQL Tunning Advisor

SQL Tuning Advisor can be run on individual SQL statements or SQL tuning sets. SQL Tuning Advisor requires a separate license as part of the Oracle Database Tuning Pack.

The USER_ADVISOR_TASKS and V$ADVISOR_PROGRESS data dictionary views can be queried to monitor the progress of a SQL Tuning Advisor task.

Creating and Executing a SQL Tuning Advisor Task in SQL*Plus

DECLARE
  sqlstmt   CLOB;
  taskname  VARCHAR2(30);
BEGIN
  sqlstmt  :=    'select r.region,s.totalsales from sales s, regions r where

                 s.region_no = r.region_no group by r.region_no';

  taskname :=    DBMS_SQLTUNE.CREATE_TUNING_TASK (

              sql_text    => sqlstmt,

              bind_list   => sql_binds(anydata.ConvertNumber(100)),
              user_name   => 'tfm',
              scope       => 'COMPREHENSIVE',
              time_limit  => 60,
              task_name   => 'totalsales_sql_sta_task',
              description => 'Individual Query - SQL Tuning Advisor Task');
END;
/

BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'totalsales_sql_sta_task');
END;

/

SET LONGCHUNKSIZE 1500

SET LINESIZE 250

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'totalsales_sql_sta_task' ) FROM DUAL;

SQL Access Advisor

Analyze SQL workloads and provide performance improvement by suggesting recommendations for materialized views, partitioning, and indexes.

SQL Access Advisor can be run on the following SQL workloads:

Current and recent SQL activity
SQL tuning set
Hypothetical workload

SQL Access Advisor also provides recommendations for dropping unused indexes. In addition to covering full, refreshable materialized views, SQL Access Advisor can be leveraged to make materialized views fast-refreshable for an optimal implementation. SQL Access Advisor is part of the SQL Tuning Pack licensed option.

The USER_ADVISOR_SQLA_WK_STMTS and USER_ADVISOR_ACTIONS data dictionary views can be queried to view the recommendations generated by the SQL Access Advisor.

DECLARE
  taskname varchar2(30)         := 'SQL_ACCESS_9940';
  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)     := 'STS_CUSTOM_9940';
  sts_owner varchar2(30)     := 'SYS';
BEGIN
  -- Create the SQL Access Advisor Task 
  dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
     task_id,taskname,task_desc,task_or_template);
  -- Reset the SQL Access Advisor 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_sts_ref(taskname, sts_owner, sts_name);
  END IF;
  -- Link STS Workload to Task
  dbms_advisor.add_sts_ref(taskname,sts_owner, sts_name);

  -- Set the 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',
DBMS_ADVISOR.ADVISOR_UNLIMITED);
  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',
'COMPREHENSIVE');
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 the SQL Access Advisor Task
  dbms_advisor.execute_task(taskname);
END;

SQL Repair Advisor

SQL Repair Advisor is leveraged for repairing SQL statements at the database server tier that end up in critical failure. SQL Repair Advisor performs a comprehensive analysis on the failed SQL statement and may provide a SQL patch recommendation that fixes the failure issue by instructing the Query Optimizer to select a different execution plan that avoids failure.