Oracle Advisory

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

• Memory - provides recommendations regarding the optimal sizing of the SGA and PGA memory area's.
• MTTR - lets you configure instance recovery by enabling you to adjust the mean time to recovery setting for an instance
• SQLTuning - analyzes complex SQL statements and recommends ways to improve them
• SQL Access - provides advise on creating new indexes, materialized views or materialized view logs.
• Segment - allows you to perform growth-trend analyses on various database objects i.e. shrinking
• Undo - bases its activities on system usage statistics, including the length of the queries as well as the rate of undo generation.

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

• Advice on improving the execution plan
• Reasons for the SQL improvement (recommendation)
• Benefits you can expect by following the advisors advice
• Details of the commands to tune the misbehaving SQL statements

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

• Creating indexes will speed up access paths
• Using SQL profiles will allow you to generate a better execution plan
• Gathering optimizer statistics for objects that do not have any, or renewing stale statistics will be benefit
• Rewriting SQL as advised will improve performance

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;