Access Path Scientific Analysis Part VII – Using an 11G DBConsole to Identify Resource Consuming SQL

We continue our discussion on the various tools that DBAs can use to identify high resource consuming SQL statements.  We started by learning that there are several data dictionary tables that we can access using SQL statements to retrieve information we need.     The next blog focused on the 10G DB Console.   We found that the 10G DB Console provides numerous mechanisms that we can use to identify the top resource consumers.    In this blog, we’ll use the 11G DB Console to achieve the same result.    We’ll find  that although the navigation is pretty much the same as its 10G counterpart, 11G does offer some pretty interesting new features.

Finding the Top Resource Consumers Using the 11G DB Console
The intent of this discussion is to identify the easiest ways to identify high resource-consuming SQL using the 11G DBConsole.   In order to prevent this blog from being too large, I’ll attempt to focus my discussion on the topic at hand.   We’ll look at the various tools, advisors and utilities that 11G’s DB Console provides in upcoming blogs.

Once an Oracle database is built, loaded with data and tuned, the only real variables that can affect it on a daily basis is either workload/data volume changes or the SQL statements being executed in it.   Of course, we can always tweak the OS parameters, add hardware, etc., but these changes aren’t made on a daily basis (we hope).  The number of concurrent users can fluctuate depending on peak time periods, economic conditions, etc. and disk utilization can also increase based on a whole host of reasons.

But the real dynamic of any database environment is the access paths that SQL statements take.   Those of us that have been in the database administration field for any lengths of time understand that a single SQL statement’s access path change can transform a database that was providing acceptable performance to one that is “performance challenged”.

Our challenge is then to find the statement that is causing the trouble, determining the resources it is utilizing (or dominating) and identifying the access path that it is taking.    Like its 10G DB Console counterpart, the 11G DB Console makes finding these offending statements much easier than before the consoles were available.   That was when DBAs where DBAs, you had a command line interface.   You had no “SGTs” (sissy GUI tools) available.  The DBA ran SQL statements against the database and pored over reams of output – line by line, by line, by line…..    Trust me when I say that I do NOT classify those times as “the good old days”.   You actually had to take the data “offline” to perform many of the administrative tasks that can be done online today.    Needles to say, I am a huge fan of the SGTs and using them is an absolute requirement here at Remote DBA Experts.

Let’s start our analysis of the 11G DB Console.

We begin our investigation by logging into the 11G’s DB Console.    This home page is not much different than its 11G counterpart.  At the top of the home page we see the tabs we can use to navigate our way through the various console panels.   Like its 10G database counterpart, the 11G database console provides a host CPU graphic in the middle of the screen.     If we click on the host name contained in the CPU graphic or the CPU link in the Active Sessions graphic just to the right of it, the 11G DB Console will respond by displaying the Top Activity Page.

You’ll notice  that the consoles supplied with both 10G and 11G provide numerous links to the Top Activity Page.  We’ll also learn later in this blog that there are links that will allow us to view key database resources and the top SQL statements that are consuming them. There’s a reason for that.   As I stated earlier in this blog, identifying the SQL being executed (and the associated resources the statements consume) plays a key role in any performance problem analysis effort.

The Top Activity Page displays a running history of the top resource consuming SQL statements and sessions.  There is a drop down menu in the upper page that allows us to view the top resource consumers historically.   This historical option finally allows administrators to answer the age-old question posed by various business personnel and application developers “My job ran long two days ago and I think it’s the database’s fault.   What happened?”.     Now we can find out exactly what happened.

When we click on the SQL ID link on the Top Activity Page, the 11G DB Console will respond by displaying the SQL Details Activity Page. The SQL Details Activity Page provides us with information on how active this statement is  (how many sessions are running  this particular SQL statement). The same navigation  links for statistics, activity, plan, tuning history and SQL monitoring are displayed on all of the SQL Details drill down panels.   They allow the user to drill down and analyze information that is pertinent to the SQL tuning process.

You will also notice that the DB Console provides numerous buttons that allow you to activate the SQL Tuning Advisor.  Actually, you’ll find these links everywhere and there’s a reason for that.   Oracle wants you to leverage the advantages that the SQL Tuning Advisor provides.   The more mature these advisors become, the better I like them.    In my next blog, we’ll deviate from our technical discussion and discuss the future of database tuning.  That future will include an increased dependency on automated tools and less of a dependency on a deep knowledge of Oracle optimization and Oracle internals.

One of my favorite panels is the first one on the navigation list.   The SQL Statistics Page provides a wealth of information on the SQL statement we are reviewing.   On the left hand side of the screen, the DB Console is telling us that a SQL Profile is implemented for this statement.  On the right hand side, all of the pertinent resource consumption information is displayed.   Virtually every piece of information needed to evaluate the statements resource consumption is provided including executions, elapsed time, CPU time, buffer gets, disk reads and rows returned.   The console also displays both the total resource utilization for all executions of this statement as well as per execution.   As we learned in previous discussions, if a SQL statement is consuming 300,000 disk reads total but it is run 300,000 times, it probably doesn’t need tuning.

If we click on the Plan navigation tab, the DBConsole responds by displaying information on the SQL Statement’s Access path. I really like the graphical version display that  this tool provides.    In a future blog, I’ll show you how to interpret EVERY SQL statement access path output that I use – from a dump of the plan table to this console display.    If you want to know more information about the particular operation contained in the graphical display, you can click on the operation and the console will respond by displaying a definition of that operation in the Solution Details box.  If you like a more traditional display,  there is a bullet on the left side of the panel that allows you to switch from a graphical depiction of the access path  to a more traditional table display output.

The Plan Control Page displays information on SQL Plan Baselines and SQL Profiles.   We don’t want to delve too deep now into SQL Baselines, but you can rest assure we’ll spend some dedicated time on this feature.   The results that we are receiving with SQL Plan Baselines are making me “cautiously optimistic”.   I love that term.  The optimizer is intelligently deciding to add access paths that are better into the baseline and allowing us to activate them.    All I can say is that it seems to be working as Oracle describes it.

The Tuning History Page provides us with historical information on past tuning changes. When we finally take Oracle’s advice and run the SQL Tuning Advisor, it will display any  profile we implement on this page. If you have implemented several different profiles in the past, DBConsole will provide a listing of them on the Tuning History Page. You then have the option of deactivating one profile and reactivating a past profile if you desire.

Let’s continue our investigation by using the navigation tab on the top left hand side on the Database Home Page to navigate to the Database Performance Home Page. This panel certainly makes it easy for us to determine what resources are being utilized. We can click on the different colors to drill down into specific resources.    Here’s a drilldown page for all of the sessions that are waiting on User I/O.    Once again, the 11G DB Console makes it easy for us to find the individual SQL Statements, as well as the sessions that are the top generators of User I/O waits.  Clicking on the SQL statement on the lower left side of the page will display the SQL Statistics Page. Right back where we started this discussion.   We can easily review the SQL statment’s statistics, access paths, tuning history….

As we have found, the 10G DBConsole provides many different features that allow us to identify the top resource consumers in the database it is monitoring. With time and experience, you’ll be able to quickly determine what sessions; SQL and components are the top resource consumers. In addition, you will also be able to determine what resource the top consumers are utilizing and the SQL they are executing.

In my next blog, we’ll deviate from our technical discussion and talk about the future of database tuning.  Here’s my prediction – The future will not include us being required to become “at one” with the Oracle optimizer.  We’ll be reviewing and implementing decisions made by the intelligent advisors.

Thanks for Reading,

Chris Foot
Oracle Ace
Director Of Service Delivery