Lesson 3. SQL Explorer and SQL Statistics-Based Tuning.
You should already be familiar with the v$sql view. This view provides cumulative statistics for SQL statements. Traditionally, this view has been used for finding top SQL statements in different categories: reads, gets (logical reads), elapsed time (response time), CPU time etc. This tuning method is widely used and has been very valuable (the SQL Area window in Lab128 covers this type of tuning). The obvious drawback of this method is that it doesn't show when the load happened. This shortcoming can be partially worked around by taking snapshots of v$sql before and after suspected activity or even flushing the shared pool to reset counters in v$sql view. These tricks are not eloquent; they are tedious and lack time precision.
Lab128 uses the same method to take snapshots of v$sql; although this method has been taken to an extreme: the snapshots are collected every 15-30 seconds. This generates enormous amounts of data, so Lab128 stores it in compressed form. The result of this technique is astonishing! Now, any time period in the past can be analyzed, the top SQL statements can be identified, and statistical charts of individual statements can be drawn and compared to other key performance data.
How does this technique compare to ASH (Active Session History) analysis? Both v$sql snapshots and ASH methods target expensive SQL statements. You can use either method and the results should be close. In our experience, v$sql snapshots provide more complete and accurate data when it comes to pure SQL statistics (version 10g Release 2 and later). On the other hand, ASH methods have wider scope, since they provide additional data, such as nature of waits, database objects involved etc. Therefore, these two techniques complement each other. In fact, the SQL Explorer window blends data from both techniques in one place, making it a very powerful tool.
Let's take a tour of the SQL Explorer. There are many ways to open SQL Explorer window. For now, let's open it using main menu: View | SQL Explorer, or by selecting the SQL Explorer icon in the tool bar. There are three sections in this window: upper - the graphical charts; middle - the tabular view of system wait values, and lower - Active Session History (ASH).
The upper section shows graphical charts of selected SQL statistics summarized for all SQL statements. These charts provide the "big" picture of activity in the database. Use the check boxes on the right to select the statistics you wish to use. Typically, "Elapsed Time", "CPU Time", "Disk Reads", and "Buffer Gets" are used most often. For frequent and short queries, "Executions" may also be useful. If you suspect a locking issue, check "Application Wait Time"; for index contention and other concurrency issues, check "Concurrency Wait Time". Don't select too many charts at the same time; select enough to provide the information you need while keeping the display uncluttered. In most cases, the single chart "Elapsed Time" (relating to response time) is sufficient.
In the upper section, highlight the time interval around interesting activity by highlighting with the mouse. Time selection is important because the other two sections show data for the selected time interval.
The tabular view in the middle section presents delta values for the selected time interval. This data is shown for individual SQL statements. Click on the column headers to sort rows in descending order. As with charts, you will be most interested in the "Elapsed Time", "CPU Time", "Disk Reads", and "Buffer Gets" statistics. Click on those to see the top SQL statements in each category. If you are interested in a particular statement, select it. If you want to plot this individual statement in the Charts section, right-click and select "Show Graph for selected SQL". Select OK in the "Select SQL Statistics" window and the chart will be added to the Charts section. This chart shows the dynamics of statement execution and identifies exact moments when the statement used peak system resources.
Select another row in the tabular view and add this SQL statement to the charts section. What if you want to locate the row corresponding to the first statement's graph? It has cryptic IDs, so it is difficult to find the row again. Try this: right click on the graph in question and select "Locate SQL for this Graph". The corresponding row will be selected again. The tabular view is searchable too. Ctrl-F will call a "Find" dialog box. All columns and rows in the view are searchable and you can search forwards or backwards.
Sometimes, the Charts section will get crowded and you will want to remove charts. It can be done through the right-click menu, or by using the "Remove Extra Charts" link at the end of the statistics list on the right of the Charts section (you may need to scroll down a bit).
Let's assume you are interested in only one SQL statement and want to closely examine it by changing the time selection and viewing the statement's statistics. Use the "Selected Row only" check box. Only one row will be shown in the tabular view. You can then change time selection and see how the numbers change for the selected row. "Selected Row only" makes it much simpler to follow a particular SQL statement. You can always uncheck this option to have all rows back.
The lower section of the SQL Explorer window contains the already familiar ASH section. As you select a particular statement, the ASH section updates to show data for that statement. As mentioned above, ASH complements SQL statistics by providing additional data. The ASH section behaves exactly the same as in the Activity Explorer, described in Lesson 2.
In the lower right corner, you can find SQL text. The SQL_ID and Plan Hash Value fields on top of the SQL text box are links to the SQL Statement Details window. Click on a link to open this window. The SQL Statement Details window gets data from the same source as the SQL Explorer. It has all the available information about a particular SQL Statement. This window behaves similarly to the SQL Explorer. Once you get familiar with the SQL Explorer, the SQL Statement Details window should become familiar too. You can close this window to return to the SQL Explorer. The link can be used to quickly check the individual SQL charts. The SQL Statement Details window has its own merits, but this is a topic for another lesson.
Just to remind you, all data shown in SQL Explorer pertains to the selected period of time. If you want to explore the same time period in other windows, Lab128 can switch to other windows and keep the same time selection.
For example, you may decide to examine the same period of time in the Activity Explorer. As soon a time period is selected in any graphical chart, there is an option in the right-click menu to call the Activity Explorer. Another useful feature is the ability to add more statistics from outside windows into a graphical section. The reverse action is also available; any or all graphs can be merged into other windows. See Lesson 4, which provides more information on graphical charts.
Lesson summary. Lab128 uses frequent snapshots of v$sql (or v$sqlstats in 10gR2 and later) to power the SQL Explorer. All important SQL statistics for individual statements can be explored and plotted in the charts. All data presented in this window pertains to the selected time period, so it is critical to make the selection around interesting activity in the graphical chart section. Alternatively, the SQL Explorer can be called from other windows while preserving the time selection from the calling window. The middle section presents data for individual statements. Click on column headers to sort rows and find top SQL statements in each statistic category. The most important statistics have been suggested in this lesson. Individual statement charts can be viewed by selecting the statement and using the right-click menu. Alternatively, a link is available to open the SQL Statement detail window, which provides charts and other information on the selected statement. Finally, the SQL Explorer blends SQL data with ASH data, presented in the lower section.