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 = ?? |
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
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
|
SQL structure analysis | ATO can advisor you to change the structure (both the syntax and semantics) of poorly performing SQL statements, it will consider
|
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 := admass ( |
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; |