Access Path Scientific Analysis Part V – Identifying Top Resource Consumers Using SQL Statements

Now that we are ready to continue our discussion on access path scientific analysis, we need to begin identifying some representative queries that we can monitor and tune.  There are numerous ways to identify the top resource consumers for a given instance.

We’ll learn that both the10G and 11G DB Consoles are both able to quickly provide us with all of the information we need to identify the top resource consumers, what resources they are consuming and the SQL they are executing.   We can also fall back to a couple of SQL statements that I have been using for years.  They still do the job when I need them to.  That task is identifying SQL statements that are high resource consumers.

This week, we will cover my couple of SQL statements, next week, we’ll cover the 10G DBConsole and we’ll end up with a discussion on using the 11G Console to identify high resource consuming statements.

Dynamic Performance Views

The Oracle database maintains a set of views that records current database activity.  The Oracle 11G manual states that these views are “often called dynamic performance views because they are continuously updated while a database is open and in use.”   DBAs often use the terms “V$ Views” or ”V$ Tables” because they are prefixed with the characters “V$”.   Administrators are able to use these tables to retrieve information about activities occurring in the Oracle instance.   The key word is dynamic.   It means that the information is always changing and, based on certain conditions, the information you are looking for may not be available.

There are dozens (and dozens) of V$ views available to DBAs.   They can provide us with a wealth of information on virtually any activity that is occurring in the Oracle instance.    Let’s keep this simple and focus on a couple of tables that can provide us with some pertinent information on SQL statements and the resources they are consuming.   Three of my personal favorite V$ tables of ALL TIME are V$SQLAREA, V$SQLSTATS  and V$SQL_PLAN.  We’ll take a quick peak at V$SQLAREA, V$SQLSTATS in this blog and review V$SQL_PLAN in-depth in upcoming discussions.

V$SQLAREA, V$SQLSTATS, V$SQLTEXT and V$SQLTEXT_WITH_NEWLINES
Let’s take a high level look at some of the views we will use to gather information on SQL statements contained in the library cache. We’ll need this information in later discussions to help us determine what resources the statements are consuming and what access paths they are taking.

• V$SQLAREA - This dynamic performance view contains statistics on SQL statements that are in the shared pool, parsed and ready for execution.   This view is often an aggregate of the contents of the V$SQL view.  We’ll have to remember this when we run queries against the table.   If I’m tuning a statement, I want to see the total impact it has on the environment.   That aggregate information is important to me.   Like any transient memory area, SQL statements in the shared pool (and related statistical information in this table) may get aged out.   If that is the case, we’ll have to wait for the SQL to run again (or run the statement ourselves if we can) to get the statement back into memory and the statistics available in V$SQLAREA.   If the statement is executed a lot, we won’t have any problem finding information on it in V$SQLAREA.   V$SQLAREA displays statistics on shared SQL areas and contains one row per SQL string.   V$SQL’s SQL_TEXT column provides the first 1,000 characters of the text of the SQL statement and a SQL_ID column that we can use to as an identifier when we query the other V$ dynamic performance views. V$SQL’s SQL_FULLTEXT is a CLOB column that contains the entire SQL statement.  V$SQLAREA also provides an abundant amount of information on resource utilization including sorts, memory utilization, disk reads, buffer gets, rows processed.   We will review some of these columns later in this blog.
• V$SQLSTATS - Oracle 10G R2′s data dictionary contains this new view that Oracle describes as being a faster, more efficient way for a DBA to retrieve statistical information about SQL statements contained in memory.   The Oracle manual also states that this dynamic performance view will also provide a greater retention for statistics (even after the statement is aged out) than its V$SQLAREA counterpart.
• V$SQLTEXT – Although V$SQLAREA and V$SQLSTATS does provide a CLOB column that contains the full text of the SQl statement, this view provides an easy way to retrieve the entire text of the SQL statement. If the text of the SQL statement looks like it has been truncated in V$SQLAREA’s SQL_TEXT column, use the value in the SQL_ID column from V$SQLAREA to query V$SQLTEXT to get the entire statement. If the text of the SQL statement contains any newlines or control characters, they will be replaced with whitespace.
• V$SQLTEXT_WITH_NEWLINES – Contains the text of the SQL statement without the newlines or control characters being replaced by whitespace.

Let’s take a look at some of the columns that are contained in V$SQLAREA and V$SQLSTATS.  These tables contain a wealth of resource utilization information!

• SQL_TEXT VARCHAR2(1000) – First thousand characters of the SQL text for the current cursor
• SQL_ID VARCHAR2(13) –  SQL identifier that we can use to identify this statement
• SORTS NUMBER  – Sum of the number of sorts
• FETCHES NUMBER – Number of fetches
• EXECUTIONS NUMBER – Total number of executions
• DISK_READS NUMBER – Sum of the number of disk  reads
• BUFFER_GETS NUMBER – Sum of buffer gets
• ROWS_PROCESSED NUMBER - Total number of rows processed on behalf of this SQL statement
• OPTIMIZER_MODE VARCHAR2(10) (V$SQLAREA ONLY) – Mode under which the SQL statement was executed
• OPTIMIZER_COST NUMBER (V$SQLAREA ONLY) -Cost of this query given by the optimizer
• SQL_PROFILE VARCHAR2(64) (V$SQLAREA ONLY) -SQL profile used for this statement, if any

As stated previously, the information for specific SQL statements contained in the V$ performance views provided above could be flushed from the system based on workload and memory allocations. Although you will always find data for currently executing queries, you may, or may not, find information pertaining to statements that were executed some time in the past. If a SQL statement has been aged out of the library cache, you may not be able to find information on it.

Finding the Top Resource Consuming Queries
If I want to perform a traditional “top down” tuning approach and tune the highest resource consuming SQL, I’ll use the statements below to identify the top resource consuming queries.

The following query identifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, address, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;

The following query identifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, address, sql_text FROM v$sqlarea WHERE buffer_gets > 10000 ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 — for the disk read report and
BREAK ON buffer_gets SKIP 2 — for the buffer get report

It’s common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 10,000. I used these numbers just as an example but I do personally use them as a starting point from time-to-time. Based on their output, I’ll then adjust the numbers up or down accordingly. The numbers also depend on the system I’m reviewing. I’ll use different numbers for OLTP environments than I would for data warehouses.

You’ll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn’t need tuning. But the more it runs, the more emphasis I will place on tuning it. If you can reduce the buffer gets generated by a query that runs thousands of times of day by even small amount, you are still reducing the total workload the system has to process.

Heavy disk reads per statement execution usually means a lack of proper indexing, poor selection criteria, etc.. Heavy buffer reads sometimes means the exact opposite – indexes are being used when they shouldn’t be. But I’m personally most interested in workload, that’s why I always use the buffer hits in my initial queries.  The more times a statement is hitting the buffers, the greater the workload it is exerting upon the database environment.

Thanks for Reading,

Chris Foot
Oracle Ace
Director Of Service Delivery