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.
Part 2: What is the Advisor Framework?
In Part 2, I explain what the Advisor Framework is and why you would want to scale-up your use of the Advisor Framework.
“… Database, heal thy self …”
The Advisor Framework is the infrastructure to support Oracle’s suite of database manageability tools. It is as if Oracle is to say, “… Database, heal thy self. …”. Well, there may be a bit of “markitecture” in that statement [for more musings on “markitecture” reference War Stories, Episode 001 - Markitecture vs Architecture]; actually, I think I coined the phrase “… Database, heal thy self. …”, so I can’t say that is from Oracle Corp., but I think that is the general drift with the Advisor framework.
The ADDM Advisor, for example, builds on the kind of data captured in the AWR, although I don’t think they are tightly coupled because I recall seeing ADDM data when AWR was not being captured. ADDM is basically rules-based diagnostics that also identified particular problems and how they might be solved. So, if you use ADDM regularly, it may flag up for you problems before they become noticeable to your end-users. One of the great advantages to people like me who like to dive deep under the covers is that the Advisors use a common data structure to historically persist and expose the run results, so they can be accessed by the off the shelf tools (and in my case, custom tools).
The public interface for the Advisors is the PL/SQL package DBMS_ADVISOR; it can also be accessed via OEM, but I don’t cover that here.
The tuning related advisors are a subset of all advisors and some of the advisors have been available since version 9i of the Oracle Database.
The focus of this series will be exploring the persisted information from the tuning related advisors.
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 more comprehensive understanding of the Advisor framework, I wrote simple SQL queries to easily expose that content to the DBA or Developer interested in solving every day performance problem use cases.
Why scale-up your use of the Advisor Framework?
My basic reason for pursuing a deeper understanding of the Advisor framework was to help me solve ordinary performance problems more quickly. Also, I’ve sought to improve my tuning capability though this knowledge (probably a bit more serendipity than planning was involved here). I think really since I’m a fanatic about talking SQL to my data, I thought, why not harvest the information from periodic automated runs of the ADDM Advisor, the Segment Tuning Advisor, and the SQL Tuning Advisor. By harvesting the historically persisted information (I believe it is kept around for 30 days by default) I can save time because (in many cases) there is no need to re-run these Advisors. The ADDM Advisor runs hourly by default (technically, after each snapshot); both the Segment Tuning Advisor and the SQL Tuning Advisor run daily.
Not only that, by going directly to the persisted Advisor data, you can more quickly assess the run results from typical on-demand executions of the SQL Access Advisor and SQL Tuning Advisor. Further, by going directly to the persisted Advisor data, you can massively scale up your ability to action many tuning opportunities. I do this by running custom SQL to evaluate advice across many runs.
What’s next:
Part 3 will cover the view structure for the Advisor framework and provide a description as to what is stored in the Advisor tables. The full series outline is repeated below for your convenience.
Series Outline
There is a lot of content in this topic, so I plan to break 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 will write 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, I will write on 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.
