Yuan Yao, An Oracle ACE's Blog

Oracle Database provides three dynamic views for querying execution statistics of all SQL statements currently cached in the Library Cache of the Shared Pool. They are V$SQL, V$SQLAREA and V$SQLSTATS. This article explores the differences between them.

V$SQL has one row for each different version of a SQL statement. This means that each child has its own execution statistics, resulting in multiple rows for a single SQL statement.

V$SQLAREA And V$SQLSTATS present a single row for each unique SQL string ,i.e., each parent cursor. This means that the statistics for all child cursors, i.e., different versions of this cursor, are aggregated.

The example below demonstrates the difference.

First, we execute the same SQL statement twice, by changing a session variable between the two executions, we create two versions of the same SQL string:

SQL> select /* sql_version */ last_name from employees where EMPLOYEE_ID<10;
no rows selected

SQL> alter session set optimizer_index_cost_adj=101;
Session altered.

SQL> select /* sql_version */ last_name from employees where EMPLOYEE_ID<10;
no rows selected

Next, we query the execution statistics of the SQL in the V$SQL, V$SQLAREA, and V$SQLSTATS.

SQL> select sql_id,substr(sql_text,1,50),buffer_gets,ELAPSED_TIME,child_number,CHILD_ADDRESS from v$sql where sql_id='8qxn9jfkd7348';
SQL_ID          SUBSTR(SQL_TEXT,1,50)                                  BUFFER_GETS   ELAPSED_TIME   CHILD_NUMBER CHILD_ADDRESS
8qxn9jfkd7348   select /* sql_version */ last_name from employees                1           2571              0 0000000095FBF368
8qxn9jfkd7348   select /* sql_version */ last_name from employees                1           2679              1 0000000096560ED8

SQL> select sql_id,substr(sql_text,1,50),buffer_gets,ELAPSED_TIME from v$sqlarea where sql_id='8qxn9jfkd7348';
SQL_ID          SUBSTR(SQL_TEXT,1,50)                                  BUFFER_GETS   ELAPSED_TIME
8qxn9jfkd7348   select /* sql_version */ last_name from employees                2           5250

SQL> select sql_id,substr(sql_text,1,50),buffer_gets,ELAPSED_TIME from v$sqlstats where sql_id='8qxn9jfkd7348';
SQL_ID          SUBSTR(SQL_TEXT,1,50)                                  BUFFER_GETS   ELAPSED_TIME
8qxn9jfkd7348   select /* sql_version */ last_name from employees                2           5250

As shown, V$SQL displays two rows for the SQL statement, while the V$SQLAREA and V$SQLSTATS each show one row with aggregated execution statistics like BUFFER_GETS and ELAPSED_TIME.

V$SQLAREA was a favored view for SQL tuning until the introduction of V$SQLSTATS in Oracle 10g R2. Although sharing similar column definitions, V$SQLSTATS offers several advantages:

• Complete SQL Text: V$SQLSTATS contains the entire text of the SQL statement, eliminating the need to reference both V$SQLAREA and V$SQLTEXT to obtain the necessary information.
• Performance: Oracle states that V$SQLSTATS is faster, and more scalable. BY querying V$SQLSTATS, you are steering clear of a critical shared memory structure that any session wishing to parse or execute SQL will be competing for. However, that being said, this isn’t a leave pass to excessively query V$SQLSTATS.
• Data Retention: V$SQLSTATS provides a longer access window, the statistics may still appear in this view, even after the cursor has been aged out of the Shared Pool.

In an upcoming blog, I will provide you with a few ready-made queries for identifying top SQLs for tuning using V$SQLSTATS.