Oracle’s Advisor Framework – Part 3 – Table Structure

In Part 1 of this series, I introduced the concept of querying the underlying Advisor views with the purpose of scaling up your tuning capability.

In Part 2, I explained what the Advisor Framework is and why you would want to scale-up your use of the Advisor Framework.

Part 3: The Advisor Table Structure

In Part 3, I describe the view structure for the Advisor framework and provide a description as to what is stored in the Advisor tables.

As I stated in Part 2 of this series, my experience with the data under the Advisor covers stems from my in-depth examination of the underlying structure and content of the various views that form the Advisor Framework [along with some trial-and-error querying of the data].  With a comprehensive understanding of the Advisor framework and data, I wrote simple SQL queries to easily expose that content to the DBA or Developer interested in solving every day performance problem use cases.

Views Undergird the Advisor Framework?

As is typical with Oracle products, there are a number of dictionary views that are part of the Advisor framework.  For example, you can get a list of all the advisors by querying DBA_ADVISOR_DEFINITIONS, as in the following example:

select advisor_name from DBA_ADVISOR_DEFINITIONS;

The above listing is from an earlier version of Oracle.  Under 19c there is a “Statistics Advisor” (not really sure when it was introduced).

I cover in this series primarily the content of the ADDM Advisor, the SQL Tuning Advisor, the Segment Advisor and the SQL Access Advisor.

To see how the advisors are setup you can query DBA_ADVISOR_DEF_PARAMETERS, for example:

select advisor_name, parameter_name, parameter_value, description

from DBA_ADVISOR_DEF_PARAMETERS

;

Aah yes, the key-value-pair (KVP) data structure.  I’ve heard some people say that they steer clear of this data structure (usually because you often have to pivot the data out since most people like to see their data in a multi column format).  Not me, I use the KVP structure often, and have used it for decades [how do you think we could have developed an application that could house all the case report for data for a major pharma company for 70 medical development programs without having to do DDL to add a new case report for section (reference War Stories, Episode 003 – A Single SQL Statement Crashes a DB Host Machine)].  If you are interested in the KVP  data structure I speak of this extensively in my blog post on Tuning SQL Using Feature Engineering and Feature Selection - Part I.

Oracle also records the usage of the advisors in DBA_ADVISOR_USAGE, for example:

select advisor_name, last_exec_time, num_execs

, num_db_reports, first_report_time, last_report_time

from DBA_ADVISOR_USAGE

;

The Advisor Views:

There are 41 views in the Advisor framework:

select view_name 

from dba_views 

where view_name like 'DBA_ADVISOR%' ;  

The following table has partial output from running the above query.

For the purposes of this series I’m primarily looking at the highlighted views, plus  DBA_ADVISOR_SQLA_WK_STMTS and DBA_ADVISOR_USAGE.

What is stored in the advisor tables?

Here I will describe the kinds of data persisted in the advisor tables [NB: I use the term tables and views interchangeably because from a data perspective tables and views can be considered synonymous, technically these are views (which are stored SQL statements), not actual tables (if you want the real table names, you can examine the view text)].

DBA_ADVISOR_Findings

This table contains a description about what was found as a potential performance issue.  All the advisors us this same view to persist the findings (e.g. SQL Tuning Task, ADDM, …).  The impact of the finding is expressed in DB Time which is used by Oracle as a “common currency” for performance analysis.  As a reminder:

DB Time = CPU Time + Wait Time (non-idle)

Another common metric you’ll encounter is Average Active Sessions (AAS):

AAS = Sum(DB Time) / Elapsed Time

Having the impact expressed in DB Time gives more or less weight to a finding, because, obviously (hopefully), you will get more bang for your buck implementing a solution for a finding with a greater impact.

DBA_ADVISOR_Recommendations

This view has the resulting analysis from running an advisor.  It describes the type of a finding and the projected percent benefit of implementing a recommendation, as well as ranking the recommendations.

DBA_ADVISOR_Actions

This view contains the tuning actions recommended for a finding.

Each action is specified by the COMMAND* column and attributes 1-6 columns, ATTR1 - ATTR6.

*COMMAND defines how the attribute columns will be used.

DBA_ADVISOR_Rationale

This is the type and benefit of a recommendation and explains the rationale for a particular recommendation; in other words the reasoning behind a recommendation, or the justification for a recommendation.

DBA_ADVISOR_Objects

The Advisor Objects view is used when we want to see, for example, the SQL id's for a SQL Tuning Advisor run.  This also uses ATTR1-n columns; the values of the columns depend on the advisor that created the object row [polymorphic overloading at its finest 😊].  For example, for SQL objects, the column ATTR1 contains the SQL ID and the column ATTR4 persists the SQL text.

Housekeeping Views:

There are several views that are used for what I call housekeeping functions:

DBA_ADVISOR_Tasks

Everything is run as a task, so for example you will have tasks for SQL Tuning, Segment Tuning, ADDM, ….  For example, to setup a task for the SQL Tuning Advisor one would run DBMS_SQLTUNE.create_tuning_task; see  the following code snippet for an example for creating a task for running the STA:

DBMS_SQLTUNE.create_tuning_task (

begin_snap  => to_number(:BEGIN_SNAP_VAR) ,

end_snap    => to_number(:END_SNAP_VAR) ,

sql_id      => :sql_id_var,

plan_hash_value => to_number(:plan_hash_value),

scope       => DBMS_SQLTUNE.scope_comprehensive,

time_limit  => 1200, -- in seconds

task_name   => :task_name,

description => 'Tuning task for statement '|| :sql_id_var || ' hash: ' || :plan_hash_value|| ' in AWR ' || :BEGIN_SNAP_VAR || ' - ' || :END_SNAP_VAR || ' .');

The SQL Access advisor task setup looks something like this:

/* Create Task */

dbms_advisor.create_task(advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR

, task_name => taskname 

, task_desc => taskdesc

, task_id  => taskid);

There is plenty on-line for running the advisors, so I won’t repeat that here and direct the reader to the Oracle doc’s or other on-line sources.

DBA_ADVISOR_Log

This has the current status, progress, error messages, execution times, etc, for an advisor task.

DBA_ADVISOR_Journal

This details the progress of, say, the SQL Access advisor execution.

Advisor Entity-Relationship Diagram:

An advisor Task will have one or more Findings.

A Finding will have one or more Recommendations.

A Recommendation will have one or more recommended Actions and Rationales for the recommendations.

The Actions and Rationales may reference Objects.

Example Usage of Task_Name from DBA_ADVISOR_TASKS

For example, if you have the task name for an ADDM run, you can extract the ADDM Report.

select task_name, description, created

from dba_advisor_tasks

where advisor_name = ‘ADDM’

order by created desc;

So, one could use the task name to extract the ADDM report as follows:

What’s next:

The above usage example was to wet your whistle for queries against the DBA_ADVISOR views. In the upcoming Part 4 I will describe and provide various useful queries against various Advisor views. The full series outline is repeated below for your convenience.

Series Outline

If you have been following along. there is a lot of content in this topic, and I’m breaking up the blog posts up into a more easily digestible 6-part series.

Part 1: Series Introduction

The Series Introduction is Part 1, where I introduce the concept of querying the underlying Advisor views with the purpose of scaling up your tuning capability.

Part 2: What is the Advisor Framework?

In Part 2, I wrote on what the Advisor Framework is and why you would want to scale-up your use of the Advisor Framework.

Part 3: Advisor tables structure.

In Part 3, (this post) is about the view structure for the Advisor framework and provide a description as to what is stored in the Advisor tables.

Part 4: Querying the advisor framework

In Part 4, I will describe and provide various useful queries against various Advisor views.

Part 5: Case Studies / Advisor framework in use

In Part 5, I will provide several case studies of the Advisor framework in use, and hopefully show how useful these custom queries are.

Part 6: Jet Tour Through the Catalog of SQL Tools

In the last part of the series, I plan to provide a catalog of the SQL tools I’ve developed.  The SQL I use is in the public domain.

Update Your Bag-Of-Tricks

Again, stick around for this month’s series and get actual working SQL code to put in your bag-of-tricks, and hopefully use and improve your Oracle database tuning skills.

I like the bag-of-tricks motif because it reminds me that, as a kid, I was impressed by Felix the Cat (cartoon character) [I guess I’m dating myself and admitting I watched too much TV]. Felix would get himself into some kind of trouble, and at the last moment he would reach into his bag-of-tricks and come up with a solution to save the day.  Early in my Oracle career [goes back to Oracle V4 in 1985], I would learn of various SQL queries against the data dictionary and other ways to work your way through what’s going on under the covers in Oracle.  I was struggling for what to call the directory where I kept this code, a light bulb goes on and I decided on using Felix’s bag-of-tricks as a metaphor.  So, all new capabilities for introspecting the database, documents, … go into my bag-of-tricks.