Access Paths VI – 10G Grid Control SQL Details Panels

Introduction
The intent of this first series of blogs is educate ourselves in the various tools we can use to identify Oracle access paths. In my next series, I'll provide you with links to information provided by various Oracle experts on access paths. Finally, we'll discuss how we can influence access paths when we think our choice of access path is better than Oracle's.

All Roads in 10G Grid Control Lead to the SQL Details Panels
10G Grid Control's primary display tool for providing information on specific SQL statements is the SQL Details Panels. It seems like no matter where you start your investigation in 10G Grid Control, sooner or later, you'll end up with a panel that contains a link to the SQL Details Panels. Let's take a look at a few of the more popular ways of finding our way to them.

Database Performance Home Page
The Database Performance Home page provides a couple of links that lead to the SQL Details Panels. If we start from the top, we'll see the Sessions: Waiting and Working Chart in the middle of the screen. This display allows us to determine exactly what resource our disgruntled users are waiting for.

Clicking on one of the colored areas of the Sessions: Waiting and Working Chart allows us to drill down to the specific resource being waited on. Clicking on any of the colored areas will display a drill down panel that provides details on that specific resource. For example, if we click on the purple I/O color on our chart, we'll navigate to the I/O drill down page.

No matter what resource you drill down into, they all have the same look and feel. All of the resource panels display the particular resource's historical utilization and two pie charts on the bottom of the screen that allow administrators to drill down into the top sessions and SQL statements utilizing that resource. If we click on the links listed under the Top Waiting SQL: User I/O heading, 10G Grid Control will respond by displaying the SQL Details Panels. If we click on the links under the Top Waiting Sessions: User I/O heading, 10G Grid Control will display the Session Information panel. The Session Information panel contains a link to the current SQL being executed which is displayed using the SQL Details Panels.

If you are interested in learning more about the Database Performance Home Page, please turn to my blog titled "Database Tuning Using Oracle 10G Grid Control".

Top Sessions Panel
If we return to our Database Performance Home Page, we need to scroll down to see the Top Sessions and Top SQL links. When we click on the Top Sessions link, 10G Grid Control will respond by displaying the Top Session display panel. If you look at the red box, you'll see that the Top Sessions panel is actually part of the Top Consumers set of display panels.

We navigate to the Session Details Panels for each session in our report by clicking on the link under the SID column heading. The Session Details Panel provides a link to the current SQL being executed which 10G Grid control displays using the SQL Details Panels.

Top SQL Panel
Let's return to our Database Performance Home Page. The link below Top Sessions is the Top SQL. Clicking on the link displays the Top SQL panel. The Top SQL panel has to be one of my favorite display panels in 10G Grid Control because it allows us to view SQL performance historically. The Top SQL panel provides a listing of SQL Details links that provide us with access to the SQL Details Panel.

Viewing Performance Historically
10G Grid Control allows us to take the database "back in time" and review performance historically. This screenshot shows the Database Performance Home Page in historical mode. Historical mode is activated by the drop down menu (red box) that is displayed in the upper right hand corner of the page.

The historical mode display provides a slider that allows us to go back in time and view performance statistics captured during a specific time period. When I drag the slider to a time in the past, 10G Grid control displays the Database Performance Home page statistics for the chosen time period. If we click on the Top SQL link at the bottom of the page, 10G Grid Control responds by displaying the Top SQL Period display panel. This panel provides links to the SQL Details panels for the top SQL statements that were active during the chosen time period.

SQL Details Panels
Now that we have learned how to navigate to the SQL Details Panels, let's take a look at the information they provide. The first screen displayed is the Execution Plan panel.

Execution Plan
The Execution Plan panel provides us with the SQL statement's access path taken during execution. The top section will display the entire text of the SQL statement. The next section displays where the panel retrieved this information from (cursor cache), the optimizer mode, data capture time, plan hash value and optimizer mode.

The bottom section provides us with all of the basic information we need to identify the access path this query is taking. It also includes key performance indicators including the number of rows that were used in each step, the time to perform the step and CPU and I/O cost indicators.

Current Statistics
Let's take a look at the Current Statistics Panel and the wealth of tuning information it provides. The panel will tell us if this SQL statement is being influenced by a SQL Profile. We'll have an entire discussion on SQL Profiles in an upcoming blog. But for the sake of this discussion, a SQL Profile is a stored access path that we can generate using the SQL Tuning Advisor.

Time Model information provides key performance indicators including elapsed time per execution, CPU time per execution and the statement's wait ratio. The Execution Statistics includes the number of executions, parse calls, rows per fetch, rows per execution and executions per parse call.

Execution History
The Execution History panel provides us with another key set of performance indicators. It shows us the statement's performance historically. The top chart displays the number of seconds the statement took to execute. This allows us to quickly determine if a statement's spike in elapsed and CPU times can be attributed to an event that occurred during a specific time in the past.

The Executions chart shows us how many times a statement was executed historically. This allows us to determine if an overall degradation in database performance can be attributed to an increase in the number of executions of this statement. I have used this panel to find a problem in a program that was causing it to loop and execute a particular statement millions of times.

Tuning History
The Tuning History Panel displays information on past tuning exercises for this statement. If you have generated any SQL Profiles, this panel will display information on each SQL Profile generated. It will also provide information on which SQL Profile is currently being used. The panel allows users to activate and deactivate each of the SQL Profiles that were generated for this statement. As I stated, we'll focus on SQL Profiles in an upcoming blog and I'll show you how to use this panel to switch between the different SQL Profiles created for a given statement.

Wrapup
I hope you enjoyed this blog on the SQL Details panels. I think you'll agree that it provides us with a good set of information to begin our SQL performance tuning activities.

Thanks for Reading,

Chris Foot
Oracle Ace