14.1 How do I…Identify potentially inefficient queries with the V$SQLAREA view?

Problem

My database serves many users and applications. I want to review the SQL that they issue against Oracle8, particularly those queries which are generating large amounts of I/O. How do I review the SQL statements that my database is processing?

Technique

The V$SQLAREA and V$SQLTEXT_WITH_NEWLINES dynamic performance views are the key to identifying the queries, if any, that are monopolizing database resources. The structures of these views appear in Tables 14.1 and 14.2.

Table 14.1 Part of the V$SQLAREA dynamic performance view

Column Column Description
SQL_TEXT First 80 characters of the SQL statement
DISK_READS Number of disk reads generated by SQL_TEXT
BUFFER_GETS Number of buffer gets generated by SQL_TEXT
ADDRESS Address of the cursor for SQL_TEXT

Table 14.2 The structure of the V$SQLTEXT_WITH_NEWLINES dynamic performance table

Column Column Description
ADDRESS Cursor address
HASH_VALUE Hash function value for a cached cursor
COMMAND_TYPE Type of command (coded):
insert = 2 select = 3
update = 6 delete = 7
PIECE Statement portion number (used to order SQL_TEXT pieces)
SQL_TEXT Long version of the SQL statement containing line breaks

The V$SQLAREA view contains one row for every SQL statement that is ready for execution and residing in the shared pool. It is dynamic in the sense that any given SQL statement will appear and disappear from V$SQLAREA as the statement resides in and ages out of the shared pool. The V$SQLTEXT_WITH_NEWLINES lists a longer version of the SQL statement than the 80 character version residing in V$SQLAREA. The ADDRESS column in both views provides a way to join them.

It is not necessary to tune every SQL statement issued against a database; a horrific SQL statement that no user or application ever executes causes no real harm. No DBA or developer has time to examine execution paths for every SQL statement, anyway. The trick is to identify which SQL statements are both bad performers and generators of considerable database activity. The DISK_READS column in the V$SQLAREA table can help with this task.

Steps

1. Start an SQL*Plus session, log in as the WAITE user, and use the START command to load and execute the script CHP14_1.SQL.

2. The script is parameterized to include SQL statements generating more than some number of disk reads. The script asks you to supply this number. In Figure 14.1, showing script CHP14_1.SQL and its output, 250 is the disk read’s cutoff for report inclusion.

The script uses a substitution variable in line 5 to prompt the user for the number of disk reads to serve as the cutoff criterion for inclusion in the report. In Figure 14.1, this criterion is 250 disk reads. The script uses an ORDER BY clause to present the offending SQL statements in descending order of generated disk reads. The SQL_TEXT column from the V$SQLTEXT_WITH_NEWLINES dynamic data dictionary view contains 64 byte pieces of SQL statements. So, one SQL statement can be broken across multiple rows in this view. The SQL_TEXT column in the V$SQLAREA clause has a 1-to-1 relationship with SQL statements, but statements in this view are truncated at 1,000 bytes. The PIECE column appears in the ORDER BY clause in the CHP14_1.SQL script to present the pieces of each SQL statement in the correct order.

How It Works

The script in Step 1 queries the V$SQLAREA and V$SQLTEST_ WITH_NEWLINES dynamic performance views with a user-specified criterion for inclusion in the result set.

Comments

The results from the query in Step 1 provide a starting point for future tuning operations by helping you identify, out of the thousands of queries running against the database, which ones are generating the most work. For the purposes of this example, Step 1 returns all SQL statements responsible for 200 or more disk reads. For most production systems, the cut off for inclusion in the report should be at least 10,000 disk reads. In most environments, only two to three percent of the SQL statements issued against a database need additional tuning.