Understanding V$SQL Views to Optimize Oracle Performance

When working with SQL queries in Oracle, especially for performance tuning and troubleshooting, various dynamic performance views help in understanding the execution details of SQL statements. Among them, V$SQL, V$SQLTEXT, V$SQLAREA, and V$SQLSTATS and VSQLTEXT_WITH_NEWLINES are crucial for monitoring and analyzing SQL execution. While all those relate to SQL statements, they offer different information and serve distinct purposes. Understanding their nuances is key to effective performance tuning and troubleshooting.

Oracle DBAs also frequently rely on the dynamic performance views (also known as dynamic views or V$ views) to monitor and analyze SQL statement execution. You should also review oracle active sessions for system performance monitoring.

1. V$SQL

V$SQL provides a high-level snapshot of SQL statements currently residing in the shared SQL area. It’s your go-to view for performance analysis, offering aggregated execution statistics, parsing information and memory usage details.

Key Features:

• Stores parsed SQL statements in the shared SQL area.
• Contains aggregated execution statistics such as execution count, CPU time, elapsed time, buffer gets, and disk reads.
• Tracks child cursors when SQL statements are re-executed with different execution plans.
• Helps identify SQL queries consuming excessive resources.

Key Columns:

• SQL_ID: Unique identifier for a SQL statement.
• SQL_TEXT: Partial SQL text (up to 1000 characters).
• EXECUTIONS: Number of times the query has been executed.
• DISK_READS: Number of disk reads performed.
• BUFFER_GETS: Number of logical reads performed.
• ELAPSED_TIME: Total elapsed time of the statement execution.

Usage Example:

SELECT sql_id, sql_text, executions, disk_reads, buffer_gets, elapsed_time
  FROM v$sql
 WHERE executions > 100;

2. V$SQLTEXT

V$SQLTEXT stores the complete SQL text of statements executed in the database but in a fragmented manner, where each row contains a portion of the full statement.

Key Features:

• Stores the entire SQL text but broken into multiple rows.
• Useful for retrieving full SQL statements when V$SQL.SQL_TEXT truncates at 1000 characters.
• SQL text is stored in the PIECE column, requiring ORDER BY PIECE to reconstruct the full statement.

Key Columns:

• SQL_ID: Unique identifier for the SQL statement.
• PIECE: Sequence number for ordering the SQL text fragments.
• SQL_TEXT: A portion of the SQL statement (up to 64 characters per row).

Usage Example:

SELECT sql_id, 
       LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS full_sql_text
  FROM v$sqltext
 WHERE sql_id = 'g9v6yq52z709a'
GROUP BY sql_id;

3. V$SQLTEXT_WITH_NEWLINES

V$SQLTEXT_WITH_NEWLINES is similar to V$SQLTEXT but preserves the original formatting, including newlines and whitespace.

Key Features:

• Stores full SQL text with original formatting.
• Each row contains a portion of the SQL statement, but retains newlines, making it easier to read.
• Used for retrieving the exact formatting of a SQL statement as written by the user.

Key Columns:

• SQL_ID: Identifies the SQL statement.
• PIECE: Order of the SQL text fragment.
• SQL_TEXT: A portion of the SQL statement (formatted with newlines).

Usage Example:

SELECT sql_id, LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS full_sql_text
FROM v$sqltext_with_newlines
WHERE sql_id = 'g9v6yq52z709a'
GROUP BY sql_id;

4. V$SQLAREA

V$SQLAREA aggregates statistics for all executions of a SQL statement. Unlike V$SQL, it provides a single row per unique statement, summarizing its total performance.

Key Features:

• Aggregated Statistics: Combines metrics from all executions of a query.
• Simplified View: Provides one row per SQL statement.
• Historical Data: May retain statistics after statements are aged out of the shared pool.

SELECT * FROM employees WHERE employee_id = :id;

V$SQLAREA will show one row with the combined statistics of all executions.

Use V$SQLAREA to identify frequently executed queries and analyze overall performance trends.

5. V$SQLSTATS

Introduced in Oracle 10g, V$SQLSTATS is similar to V$SQLAREA but offers better performance and longer data retention.

Key Features:

• Efficient and Scalable: Designed to improve performance compared to V$SQLAREA.
• Longer Data Retention: Retains statistics even after statements are removed from the shared pool.
• Complete SQL Text: Unlike V$SQL and V$SQLAREA, it preserves the full SQL text.

SELECT * FROM employees WHERE employee_id = :id;

V$SQLSTATS will also show one row, but with extended retention and the complete SQL text.

Use V$SQLSTATS to monitor high-impact queries and track SQL performance over time.

Summary Table

Performance Monitoring Using V$SQL views

Imagine an EBS application that retrieves customer order information. Let’s say a user reports slow performance when viewing order details.

1. Identifying the Problematic SQL

The first step is to pinpoint the slow SQL statement. Common methods include:

• Application Monitoring Tools – Oracle EBS has built-in performance monitoring tools that can highlight slow SQL queries.
• AWR Reports (Automatic Workload Repository) – These capture performance metrics and can help identify SQL statements with high resource consumption.
• SQL Tracing (Using TKPROF or DBMS_MONITOR) – Enables tracing for a specific user session to capture the exact SQL executed and its performance metrics.

Let’s assume we identified the following SQL statement (simplified for demonstration):

SELECT ooh.order_number,
       hp.party_name customer_name,
       ool.line_number,
       ool.shipment_number,
       ool.ordered_item,
       ool.ordered_quantity,  
       ooh.ordered_date
  FROM oe_order_headers_all ooh, 
       oe_order_lines_all ool,
       hz_cust_accounts hca,
       hz_parties hp
 WHERE ooh.header_id = ool.header_id
   AND ooh.sold_to_org_id = hca.cust_account_id  
   AND hp.party_id=hca.party_id
   AND ooh.order_number = :order_number;

2. Gathering SQL Performance Data from V$SQL

Once the SQL statement is identified, we retrieve its performance metrics using the V$SQL view. We use the SQL_ID, which can be obtained from AWR reports or SQL tracing. Assume our SQL_ID is ‘8bwu2mc6cn74x’.

SELECT sql_id,
       sql_text,  -- Might be truncated
       executions,
       elapsed_time / 1e6 AS elapsed_time_sec,  --microseconds to seconds
       cpu_time / 1e6 AS cpu_time_sec,  --microseconds to seconds
      buffer_gets,
      disk_reads
 FROM v$sql
WHERE sql_id = '8bwu2mc6cn74x';

Key Metrics to Analyze:

• EXECUTIONS – High execution count with poor performance indicates an inefficient, frequently used query.
• ELAPSED_TIME and CPU_TIME – If CPU time is much lower than elapsed time, the query might be waiting on I/O or locks.
• BUFFER_GETS and DISK_READS – High values suggest excessive logical and physical I/O, which could indicate missing indexes or suboptimal joins.

3. Retrieving the Full SQL Text from V$SQLTEXT

Since v$SQL.SQL_TEXT may be truncated, we use V$SQLTEXT to reconstruct the complete SQL statement:

SELECT LISTAGG(sql_text, '') 
       WITHIN GROUP (ORDER BY piece) AS full_sql_text 
  FROM   v$sqltext 
 WHERE     sql_id = '8bwu2mc6cn74x' 
 GROUP BY sql_id;

4. Examining Execution Plans with V$SQLAREA and V$SQLSTATS

The v$sqlarea and v$sqlstats views provide deeper insights into execution plans. v$sqlarea is useful for active queries, while v$sqlstats aggregates statistics over time.

SELECT plan_hash_value,
       executions,
       elapsed_time / 1e6 AS elapsed_time_sec,
       cpu_time / 1e6 AS cpu_time_sec,
       buffer_gets,
       disk_reads
  FROM v$sqlarea --Use v$sqlstats for historical data
 WHERE sql_id = '8bwu2mc6cn74x';

Why plan_hash_value Matters?

• Different plan_hash_values indicate the optimizer is generating multiple execution plans for the same SQL, possibly due to changes in bind variable values or outdated statistics.
• If multiple plans exist, consider enforcing a stable plan using SQL Plan Baselines.

5. Generating the Execution Plan

To analyze how the database is executing the query, use EXPLAIN PLAN:

EXPLAIN PLAN FOR 
SELECT ooh.order_number,
       hp.party_name customer_name,
       ool.line_number,
       ool.shipment_number,
       ool.ordered_item,
       ool.ordered_quantity,  
       ooh.ordered_date
  FROM oe_order_headers_all ooh, 
       oe_order_lines_all ool,
       hz_cust_accounts hca,
       hz_parties hp
 WHERE ooh.header_id = ool.header_id
   AND ooh.sold_to_org_id = hca.cust_account_id  
   AND hp.party_id=hca.party_id
   AND ooh.order_number = :order_number;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                 | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                      |     5 |   545 |    14   (0)|
|   1 |  NESTED LOOPS                               |                      |     5 |   545 |    14   (0)|
|   2 |   NESTED LOOPS                              |                      |     5 |   545 |    14   (0)|
|*  3 |    HASH JOIN                                |                      |     1 |    81 |     9   (0)|
|   4 |     NESTED LOOPS                            |                      |     1 |    81 |     9   (0)|
|   5 |      STATISTICS COLLECTOR                   |                      |       |       |            |
|*  6 |       HASH JOIN                             |                      |     1 |    39 |     7   (0)|
|   7 |        NESTED LOOPS                         |                      |     1 |    39 |     7   (0)|
|   8 |         STATISTICS COLLECTOR                |                      |       |       |            |
|   9 |          TABLE ACCESS BY INDEX ROWID BATCHED| OE_ORDER_HEADERS_ALL |     1 |    27 |     5   (0)|
|* 10 |           INDEX RANGE SCAN                  | OE_ORDER_HEADERS_U2  |     1 |       |     3   (0)|
|  11 |         TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCOUNTS     |     1 |    12 |     2   (0)|
|* 12 |          INDEX UNIQUE SCAN                  | HZ_CUST_ACCOUNTS_U1  |     1 |       |     1   (0)|
|  13 |        TABLE ACCESS STORAGE FULL            | HZ_CUST_ACCOUNTS     |     1 |    12 |     2   (0)|
|  14 |      TABLE ACCESS BY INDEX ROWID            | HZ_PARTIES           |     1 |    42 |     2   (0)|
|* 15 |       INDEX UNIQUE SCAN                     | HZ_PARTIES_U1        |     1 |       |     1   (0)|
|  16 |     TABLE ACCESS STORAGE FULL               | HZ_PARTIES           |     1 |    42 |     2   (0)|
|* 17 |    INDEX RANGE SCAN                         | OE_ORDER_LINES_N1    |     3 |       |     3   (0)|
|  18 |   TABLE ACCESS BY INDEX ROWID               | OE_ORDER_LINES_ALL   |     3 |    84 |     5   (0)|
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("HP"."PARTY_ID"="HCA"."PARTY_ID")
   6 - access("OOH"."SOLD_TO_ORG_ID"="HCA"."CUST_ACCOUNT_ID")
  10 - access("OOH"."ORDER_NUMBER"=TO_NUMBER(:ORDER_NUMBER))
  12 - access("OOH"."SOLD_TO_ORG_ID"="HCA"."CUST_ACCOUNT_ID")
  15 - access("HP"."PARTY_ID"="HCA"."PARTY_ID")
  17 - access("OOH"."HEADER_ID"="OOL"."HEADER_ID")
 

What to Look for in the Execution Plan?

• Full Table Scans: If unexpected, they might indicate missing indexes.
• Nested Loops vs. Hash Joins: Depending on data volume, a hash join might be more efficient.
• Index Usage: Ensure appropriate indexes are being used.

6. Preserving SQL Formatting (v$sqltext_with_newlines)

For debugging, v$sqltext_with_newlines retains the original SQL format:

SELECT LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS 
       formatted_sql_text
  FROM v$sqltext_with_newlines
 WHERE sql_id = '8bwu2mc6cn74x'
GROUP BY sql_id;

7. Analyzing and Optimizing Performance

Based on our analysis, we can implement performance improvements:

a. Identifying Missing Indexes

If a full table scan is detected on oe_order_headers, and order_number is frequently queried, adding an index can improve performance:

CREATE INDEX idx_order_header_order_number ON oe_order_heades (order_number);

b. Ensuring Bind Variables Are Used

• Always use bind variables (:order_number) instead of literals to reduce hard parsing and improve execution plan stability.

c. Updating Table Statistics

• Stale statistics can lead to inefficient execution plans. Refresh them using:

EXEC DBMS_STATS.GATHER_TABLE_STATS('OE', 'OE_ORDER_HEADERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('OE', 'OE_ORDER_LINES');

d. Rewriting the Query (if needed)

If excessive joins or subqueries are causing performance bottlenecks, consider rewriting the query using:

• Materialized Views for pre-aggregated data.
• WITH Clause (Common Table Expressions) for better readability and performance.

e. Using Optimizer Hints (as a last resort)

If the optimizer chooses a suboptimal plan, hints like INDEX, PARALLEL, or LEADING can be used cautiously.

SELECT /*+ INDEX(ooh idx_order_order_order_number) */
       ooh.order_number,
       hp.party_name customer_name,
       ool.line_number,
       ool.shipment_number,
       ool.ordered_item,
       ool.ordered_quantity,  
       ooh.ordered_date
  FROM oe_order_headers_all ooh, 
       oe_order_lines_all ool,
       hz_cust_accounts hca,
       hz_parties hp
 WHERE ooh.header_id = ool.header_id
   AND ooh.sold_to_org_id = hca.cust_account_id  
   AND hp.party_id=hca.party_id
   AND ooh.order_number = :order_number;

8. Validating Improvements

After applying changes, verify performance gains by re-running:

• Queries on v$sql, V$sqlarea, and v$sqlstats.
• EXPLAIN PLAN to confirm execution plan improvements.

Final Recommendations

• Test all optimizations in a non-production environment first.
• Monitor impact over time using AWR reports.
• Ensure statistics collection is automated for continued performance stability.

Conclusion

• Use V$SQL to analyze SQL execution statistics and performance.
• Use V$SQLAREA or V$SQLSTATS for an overview of a SQL statement’s overall performance.
• Prefer V$SQLSTATS over V$SQLAREA for better performance and longer data retention.
• Use V$SQLTEXT to retrieve the complete SQL text without formatting.
• Use V$SQLTEXT_WITH_NEWLINES to retrieve SQL text while preserving original formatting.

Understanding these views helps DBAs and developers diagnose performance issues, tune SQL statements, and retrieve complete SQL queries executed in the database.