Gather query tuning data via Oracle's dynamic performance views

The dynamic performance views supplied with the Oracle database include several that make the process of gathering tuning information easier. This tip describes four of them: V$SQL, V$SQLAREA, V$SQLTEXT, and a new one, V$SQLSTATS.

For several releases now, DBAs and developers have had access to three dynamic performance views -- V$SQL, V$SQLAREA, and V$SQLTEXT -- that can be used to collect statistics about the performance of their SQL statements. In Oracle 10g, Release 2, a fourth dynamic performance view -- V$SQLSTATS -- has been added that is a more convenient way to access this data.

Unlike the static data dictionary views (ie, ones that begin with USER_, ALL_, or DBA_ prefixes), the dynamic performance views are updated constantly as the system is running. This makes it possible to watch the performance of a SQL statement while it is executing.

Like the static views, you'll need permissions to use them. The SELECT_CATALOG_ROLE can be granted to non-DBA users (typically in development environments) to enable users to SELECT from them.

Here are some brief descriptions of what each of these views gives you. For more information, consult the Oracle Database 10g Reference, Chapter 7 (http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm).

• V$SQL: This view gives you the full text of the SQL statement in a character large object (CLOB) column, plus the first 1,000 characters of it in a convenient VARCHAR2 column. The statistics available are extensive: number of parses and invalidations, disk reads and writes, execution times, wait times, and optimiser data. You can also tell the user and schema that created the statement, and how many users are currently executing it.
• V$SQLAREA: This view contains many of the same statistics as V$SQL. However, V$SQL contains one row for each child of an original SQL statement, whereas this view contains only one row for each SQL string entered.
• V$SQLSTATS: New in 10gR2, this view is faster and more convenient than V$SQL and V$SQLAREA; however, it contains only a subset of the other views' columns. In particular, it does not link to user information. There is one row for each combination of SQL and execution plan hash value. As a bonus, the entries in this view may persist longer than the other views. You may find a statement here even after it has aged out of the shared pool (and, therefore, it disappears from V$SQLAREA).
• V$SQLTEXT: There are times that you want each line of a SQL statement separately, instead of one big object as in V$SQL; V$SQLTEXT gives you this. The COMMAND_TYPE column is a code you can use to select certain statement types (ie, only INSERT or SELECT). The SQL_TEXT column has one piece of the text, and the PIECE column numbers them for sorting via ORDER BY. A variant, V$SQLTEXT_WITH_NEWLINES, keeps the original linefeeds to improve readability (V$SQLTEXT replaces them with spaces). Neither V$SQLTEXT nor V$SQLTEXT_WITH_NEWLINES tells you who executed the statement or provides you with any statistics. To get that information, you'll have to join back to V$SQL or V$SQLAREA using the ADDRESS and HASH_VALUE columns.

Taken together, these four tools give you the raw material you need to detect how well your SQL is executing.