Query the Underlying Advisor Views to Scale-Up Your Tuning Capability
In this Oracle Blog series, I plan to cover my work (which is the public domain) related to scaling up your use of the Oracle’s Advisor Framework by querying the (mostly undocumented content) of the DBA_ADVISOR% views in order to get at performance tuning recommendations much more quickly.
[N.B. The use of the Advisors I’m referring to is a licensed product under the Diagnostics and Tuning Pack license.]
So, if you are tired of grinding away to try to come up with a database tuning action plan, this series is for you, as it will detail how to get to database tuning actions more quickly by aggregating findings and recommendations across multiple Advisor runs. I have reverse engineered the Advisor framework and have built simple custom SQL queries that expose many of its undocumented features. I’ve come up with a way to condense the Advisor data which is historically persisted from multiple automatic or manual runs into a useful format. In the DBA or developer’s hands, these custom queries can be used to massively scale-up your ability to action tuning opportunities exposed by the various Advisors. The [arguably*] excellent findings and recommendations that come out of the various Advisors such as the SQL Tuning Advisor, the Automated Database Diagnostics Monitor (ADDM), the Segment Tuning Advisor, and the SQL Access Advisor will be of particular focus in this series.
*[arguably] … i.e. the elephant in the room: I understand that there are some professionals who don’t like or rely very much on the various advisors. The way I look at it is, the advisors provide additional information on which to base your root cause analysis and can also help you verify/validate your tuning action plan. I don’t think I would use the advisors recommendations carte blanche (which can backfire** on you), but rather, make expert use of the information and recommendations they provide; remember, you, the database tuner, is the expert, not, say, ADDM or the SQL Tuning Advisor. In my long career in tuning Oracle databases and applications, I’ve made it my ambition to know the tools, their capabilities, strengths and limitations, and leverage that knowledge along with expert use of custom tools to deliver high impact performance improvements for Oracle databases.
**backfire: I heard a war story recently where a 70 TB database crashed because of a single SQL statement that was using a parallel profile recommended by the SQL Tuning Advisor (STA). I’ve had some STA profiles actually degrade performance, but that one is the worst war story on the STA I’ve ever heard. The STA does warn you that parallel SQL Profiles will increase the CPU consumption, but I’m not sure if that was the root cause of this particular crash. Me, personally, I try to stay away from parallel profiles unless I’m convinced there is plenty of CPU and the SQL won’t run long at high CPU consumption. I had a colleague who always lowered the CPU count temporarily when accepting the parallel SQL Profile (ostensibly to be sure it would not overly parallelize the query), then would bump the CPU count back up.
Reverse Engineering the Advisor Framework
Ok, by reverse engineering I don’t mean some illegal process to steal Oracle’s code related to the Advisor Framework. After all, don’t mess with big tech or you might find yourself on the wrong end of an ice pick [just kidding]. I don’t support illegal reverse engineering, it’s just not worth going to jail for.
What I did was to embark on an 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]. Having developed 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 Oracle database tuning.
The reason behind doing this was to repurpose the Advisor content for various every day performance problem solving use cases.
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
This 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
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.
