
Optimizing Oracle SQL query performance is essential if you are in charge of maintaining Oracle databases. This article will demonstrate how to use a script to find top SQL in library cache, which enhances the database’s overall performance.
Parsed SQL queries are stored in Oracle’s library cache, which enables quicker execution of those queries when they are reused. However, some SQL statements can potentially use more resources than others.
Table of Contents
Source: V$SQLSTATS / V$SQLAREA
Purpose: Finds SQL statements that are currently in the shared SQL area of the Library Cache (real-time).
Scope: Only considers SQL queries currently cached in the database’s shared pool.
Use Case:
Helps in identifying queries that are consuming excessive resources right now.
Useful for debugging slow queries in real-time sessions.
Does not require AWR or an additional license.
SQL Script to Find Top SQL:
This script will help you pull the top 10 most expensive SQL queries from the Oracle library cache based on elapsed time.
-- Script to retrieve the top 10 most expensive SQL statements based on elapsed time
set long 1000000;
set longchunksize 1000;
set feedback off;
set veri off;
column elapsed format 99,990.90;
variable newl varchar2(64);
begin
:newl := '';
end;
/
select * from (select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,:newl,''),1,55) as sql_text_fragment from V$SQLSTATS order by elapsed_time desc) where ROWNUM <= 10;
This script lists the top 10 SQL queries that consume the most resources by ordering them based on their elapsed time.
Sample Output:
Once you run the above script, it will display the SQL ID, elapsed time (in seconds), and a fragment of the SQL query text.

Using the provided script, you can easily find and optimize the top SQL queries from Oracle’s library cache, improving your Oracle SQL Query performance. Always keep monitoring and tuning the most resource-intensive queries for consistent performance gains.
Oracle SQL Tuning Guide: Link to Oracle Documentation
You can go through my other article, How to Run SQL Tuning Advisor for an SQL_ID.
Pingback: Top 10 Expensive SQL Queries in Oracle database: Easy Guide
Pingback: Retrieve Full SQL text for a specific SQL ID: Easy Guide
Pingback: Find SQL details from AWR for a specific SQL_ID: Easy Guide
Pingback: Find SQL Details from Library Cache with 1 Easy Script