Management Advisory Framework

************* add more information on each advisor **********************

Oracle includes a number advisors that provide you with automatic performance details about various subsystems of the database. The advisors can help with tuning the database and identify bottlenecks, suggest optimal sizing for key database resources. Each of these advisors bases its actions on a specific oracle PL/SQL packages like the DBMS_ADVISOR. ADDM and automatic segment advisor are the only advisors that are scheduled to run automatically, the management advisory framework allows you to use similar methods to invoke all the advisors and the report are in a consistent format as well. The frameworks main function is to help with improving database performance.

There are a number of advisors available which with a specific function

SQL tuning advisor can use the following as input Top SQL, SQL tuning sets, snapshots and preserved snapshots. The advisor then will recommend in four area’s optimizer statistics, SQL profiling, access path analysis, SQL structure analysis.

SQL access advisor can use the following as input SGA, snapshot or a SQL tuning set. The advisor then will recommend scripts that can be used to generate any suggested indexes or materialized views.

Memory advisor normally deals with three area’s shared pool, , large pool, db buffer cache and PGA. If you have SGA_TARGET set then you will not be able to use these as Oracle will work what is best.

MTTR advisor makes sure that the number of dirty buffers are written out to disk according to algorithms designed to maximize performance. Making this to low can effect the performance on the database, making to high increases the time to recover after a crash.

MTTR select recovery_estimated_ios, actual_redo_blks redo, target_mttr, estimated_mttr, writes_mttr from v$instance_recovery;
MTTR optimal logfile size select optimal_logfile_size from v$instance_recovery;

Segment advisor can predict a table or index size, can growth trend estimates and recommend if a table or index should be shrunk (it uses the historical info from the AWR to see if it should be shrunk or not or best kept).

Undo advisor will make sure that it is large enough to guarantee that transactions have enough space for their undo data, and additional space to enable read consistency requirements are meant. Remember active undo will never be over written

Available Undo Blocks select begin_time, end_time, undoblks, maxquerylen, ssolderrcnt, nospaceerrcnt from v$undostat;

Note: should have zero’s in ssolderrcnt (snapshot to old – read consistency) and nospaceerrcnt (no space errors – transactions), otherwise you have add

Tuned undo retention select begin_time, end_time, tuned_undoretention from v$undostat;

Note: above query will show in 10 minute intervals how old the oldest block of inactive undo data was, the bigger the tablespace the further back tuned_undo retention will be.

DBMS_ADVISOR

You can use the dbms_advisor package to create and manage tasks for each of the management advisors.

create task dbms_advisor.create_task(‘Segment Advisor’, :task_id, task_name, ‘Free space in emp’, null);
create object dbms_advisor.create_object( task_name, ‘TABLE’, ‘TEST01’, ‘EMP’, null, null, object_id);
set task parameter dbms_advisor.set_task_parameter( task_name, ‘recommend_all’, ‘true’);
execute task dbms_advisor.execute_task(task_name);
delete task dbms_advisor.delete_task(task_name);
cancel task dbms_advisor.cancel_task(task_name);
display task print task_id
display recommendations

select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id = ??
select rec_id, rank, benefit, from dba_advisor_recommendations where task_name = 'task_name';

Display actions select task_id, task_name, command, attrl from dba_advisor_actions where task_id = ???;
Useful Views
DBA_ADVISOR_TASKS displays information about the task like name, frequency, etc
DBA_ADVISOR_PARAMETERS displays the name and values of all parameters for all tasks
DBA_ADVISOR_FINDINGS shows the findings reported by all the advisors
DBA_ADVISOR_RECOMMENDATIONS contains an analysis of all the recommendations in the database i.e benefits, ranking
DBA_ADVISOR_ACTIONS shows the remedial actions associated with each advisor recommendation
DBA_ADVISOR_RATIONALE show you the rationale behind each recommendation

SQL Tuning Advisor

Using the tuning advisor on bad SQL it can help with

The optimizer can run in two modes normal (normal tuning) or in-depth (tuning mode), the in-depth mode means the optimizer carries out in-depth analysis to come up with ways to optimize execution plans, however this does have a impact on resources. Running in tuning mode is called automatic tuning optimizer (ATO) and it performs the following tasks

Statistics analysis The ATO makes sure that you have up to date statistics for all objects in the SQL statement, if you don't it suggests that you collect them, it will also collect other statistics and can correct stale statistics
SQL profiling

The ATO tries to verify the validity of its estimates of factors such as column selectivity and cardinality of database objects, it can use three methods

  • dynamic data sampling - uses sample data to check its estimates
  • partial execution - carries a partial execution of the sql statement, to see ifs it estimates are correct
  • past execution history statistics - uses SQL statement history to help with its work

If there is enough information from statistics analysis or SQL profiling it asks you to create a profile which is stored in the data dictionary and is used in normal mode, the profile contains the optimal execution path.

Access path analysis

The advisor can change the access path by checking the following

  • If an index is effective it will advise you to create it
  • advise you to run the SQL Access Advisor to analyze the wisdom of adding a new index
SQL structure analysis

ATO can advisor you to change the structure (both the syntax and semantics) of poorly performing SQL statements, it will consider

  • Design mistakes, like performing full tables scans because you did not create any indexes
  • Using inefficient SQL; for example, the NOT IN construct, which is known to be much slower than the NOT EXISTS construct in general

The tuning advisor will recommend the following

You can access the SQL tuning advisor in two ways admass package or OEM

Create the task

DECLARE
  my _task_name varchar2(30);
  my_sql text CLOB;
BEGIN
  my_sql text := 'select * from employees where emptied = :bind_v ar';

  my _task_name := admass (
    sql _test => my_sql text,
    bi nd_list => sql _binds(anhydrate(90)),
    us er_name => 'HR',
    scope => 'comprehensive',
    tim e_limit => 60,
    task _name => 'my _sql_tuning_task',
    description => 'Task to tune employees'
  );
END

Execute the task admass (task _name => 'my _sql_tuning_task');
Accept task dbms_sqltune.accept_sql_profile (
  task_name => 'my_sql_tuning_task',
  name => 'my_sql_tuning_profile'
);
Drop the task dbms_sqltune.drop_tuning_task (task_name => 'my_sql_tuning_task');
Tuning report set long 1000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task( 'my_sql_tuning_task') from dual;
Alter the profile dbms_sqltune.alter_sql_profile (
  name => 'my_sql_tuning_profile',
  attribute_name => 'status',
  value => 'disabled'
);
Drop the profile dbms_sqltune.drop_sql_profile (
  name => 'my_sql_tuning_profile',
  ignore => true
);
Useful Views
DBA_ADVISOR_TASKS displays information about the task like name, frequency, etc
DBA_ADVISOR_FINDINGS shows the findings reported by all the advisors
DBA_ADVISOR_RECOMMENDATIONS contains an analysis of all the recommendations in the database i.e benefits, ranking
DBA_ADVISOR_RATIONALE show you the rationale behind each recommendation
DBA_SQLTUNE_STATISTICS displays statistics associated with all SQL statements in the database
DBA_SQLTUNE_PLANS displays information about the execution plans generated for all SQL statements in the database during a SQL tuning session
DBA_SQLSET_BINDS displays the bind values associated with all SQL tuning sets in the database
DBA_SQLSET_STATEMENTS displays information about the SQL statements, along with their statistics, that form all SQL tuning sets in the database
DBA_SQLSET_REFERENCES describes whether or not all SQL tuning sets in the database are active
DBA_SQL_PROFILES displays information about SQL profiles currently created for specific SQL statements

Oracle manages the profiles into categories, when the user logs in they are assigned a category, the category is obtain from the the system or session parameter sqltune_category

Instance alter system set sqltune_category = PROD;
Session alter session set sqltune_category = DEV;