Before tuning anything, you need to know which statements cost the most. Oracle keeps statistics for every SQL statement in the shared pool, and two dynamic performance views expose them: V$SQLSTATS, with one row per statement and plan, and V$SQL, with one row per child cursor and more detail. Ranking statements by elapsed time, CPU, or logical reads, in total and per execution, points straight at the queries worth tuning. This guide runs a small known workload and then finds its slow statements with both views.
What You Need
Querying V$ views needs the SELECT_CATALOG_ROLE role or SELECT privileges on the views. The examples run as an application schema that has the role.
Sample Table and Workload
Example:
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
customer_id number not null,
order_date date not null,
amount number(10,2) not null);
insert into demo_orders
select level, mod(level * 7, 5000) + 1,
date '2025-01-01' + mod(level, 600), mod(level * 13, 500) + 10
from dual connect by level <= 200000;
commit;
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')Output:
Table DEMO_ORDERS created. 200,000 rows inserted. Commit complete. PL/SQL procedure successfully completed.
The workload runs three statements, each with a comment that makes it easy to find: a report that scans the table 50 times, a lookup by primary key 5,000 times, and a large sort 5 times:
Example:
-- a small workload: three statements, run different numbers of times
declare
l_total number;
begin
for i in 1 .. 50 loop -- a report: full scan, 50 times
execute immediate
'select /* demo_report */ sum(amount) from demo_orders where amount > :1'
into l_total using i;
end loop;
for i in 1 .. 5000 loop -- a lookup by primary key, 5,000 times
execute immediate 'select /* demo_lookup */ amount from demo_orders where order_id = :1'
into l_total using i;
end loop;
for i in 1 .. 5 loop -- a large sort, 5 times
execute immediate 'select /* demo_sort */ max(rn) from (select row_number() over '
|| '(order by amount desc, order_date) rn from demo_orders)'
into l_total;
end loop;
end;
/Output:
PL/SQL procedure successfully completed.
Find the Statements with the Most Total Time
Example:
-- the statements that used the most time in total
select sql_id, executions as execs,
round(elapsed_time / 1e6, 2) as elapsed_s,
round(cpu_time / 1e6, 2) as cpu_s,
buffer_gets,
substr(sql_text, 1, 30) as sql_text
from v$sqlstats
where sql_text like 'select /* demo_%'
order by elapsed_time desc;Output:
SQL_ID EXECS ELAPSED_S CPU_S BUFFER_GETS SQL_TEXT ________________ ________ ____________ ________ ______________ _________________________________ 6b539tagqa6d4 50 0.17 0.17 37616 select /* demo_report */ sum(a 7nd0t2v2k5krg 5 0.12 0.12 3781 select /* demo_sort */ max(rn) addvtafg0y0u5 5000 0.01 0.01 15007 select /* demo_lookup */ amoun
- ELAPSED_TIME and CPU_TIME are in microseconds, so dividing by a million gives seconds.
- BUFFER_GETS counts logical reads, blocks read from memory, a good measure of the work a statement does.
- The report uses the most time in total: each run is quick, but it ran 50 times and read 37,616 blocks altogether.
- The lookup ran 5,000 times and still used almost no time.
In a real database, leave out the WHERE clause and add FETCH FIRST 10 ROWS ONLY to see the top ten statements of the whole instance.
Rank by Time per Execution
Totals show where the database spends its time; per-execution values show which single run is slow, which is what a user waiting for a page notices:
Example:
-- the same statements, per execution
select sql_id, executions as execs,
round(elapsed_time / executions / 1000, 3) as ms_per_exec,
round(buffer_gets / executions) as gets_per_exec,
substr(sql_text, 1, 30) as sql_text
from v$sqlstats
where sql_text like 'select /* demo_%'
order by elapsed_time / executions desc;Output:
SQL_ID EXECS MS_PER_EXEC GETS_PER_EXEC SQL_TEXT ________________ ________ ______________ ________________ _________________________________ 7nd0t2v2k5krg 5 23.484 756 select /* demo_sort */ max(rn) 6b539tagqa6d4 50 3.491 752 select /* demo_report */ sum(a addvtafg0y0u5 5000 0.002 3 select /* demo_lookup */ amoun
- The sort is the slowest statement per run: about 23 ms, seven times the report, although it reads a similar number of blocks. Its time goes into sorting, which costs CPU rather than reads.
- The lookup reads 3 blocks per run, about 0.002 ms. It needs no tuning, however often it runs.
Look at both rankings: a statement at the top of the totals is worth tuning for the whole system, and one at the top per execution for its users.
V$SQL: One Row per Child Cursor
V$SQL has a row for every child cursor, a version of a statement with its own plan or environment, and adds details such as the parsing schema and the last time it ran:
Example:
-- V$SQL: one row per child cursor, with the parsing schema and plan
select sql_id, child_number as child, plan_hash_value, parsing_schema_name as parsed_by,
executions as execs, to_char(last_active_time, 'HH24:MI:SS') as last_active
from v$sql
where sql_text like 'select /* demo_%'
order by sql_id, child_number;Output:
SQL_ID CHILD PLAN_HASH_VALUE PARSED_BY EXECS LAST_ACTIVE ________________ ________ __________________ ____________ ________ ______________ 6b539tagqa6d4 0 284121710 NIMBUS 50 01:45:36 7nd0t2v2k5krg 0 1893555825 NIMBUS 5 01:45:36 addvtafg0y0u5 0 969379040 NIMBUS 5000 01:45:36
- Each statement has one child here. Several children for one SQL_ID mean the statement got different cursors, for example for different bind types or optimizer settings.
- PLAN_HASH_VALUE identifies the plan. When it changes for a statement, its plan changed.
V$SQLSTATS keeps one row per SQL_ID and plan, is cheaper to query on a busy system, and keeps its statistics for a while after the cursor has left the shared pool. Use V$SQLSTATS for rankings and V$SQL for the details of one statement.
Show the Plan of the Top Statement
The SQL_ID leads to the plan. Here SQLcl's COLUMN ... NEW_VALUE stores the top SQL_ID in a substitution variable for DBMS_XPLAN.DISPLAY_CURSOR:
Example:
-- the plan of the top statement, by SQL_ID
set define on
column sql_id new_value top_sql_id noprint
select sql_id from v$sqlstats
where sql_text like 'select /* demo_%'
order by elapsed_time desc
fetch first 1 row only;
select * from dbms_xplan.display_cursor(sql_id => '&top_sql_id',
format => 'BASIC +ROWS +COST +PREDICATE');Output:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* demo_report */ sum(amount) from demo_orders where amount > :1
Plan hash value: 284121710
---------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 205 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| DEMO_ORDERS | 200K| 205 (1)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("AMOUNT">:1)
19 rows selected.The report reads the whole table to sum the amounts above a value. That makes it a candidate for an index on AMOUNT, or for fewer runs, since it ran 50 times.
Things to Know
- The statistics are cumulative since the statement was loaded into the shared pool. To measure a change, compare two snapshots of the values, or use AWR when it is licensed.
- Statements in PL/SQL appear as PL/SQL sends them: static SQL in uppercase and without comments, dynamic SQL as written. The comments in the workload work because it uses EXECUTE IMMEDIATE.
- Other useful columns are DISK_READS for physical reads, ROWS_PROCESSED, and SQL_FULLTEXT for the complete text of long statements.
Related Guides
Conclusion
V$SQLSTATS ranks statements by elapsed time, CPU, and logical reads, in total and per execution, and V$SQL adds the details of each child cursor. Find the top statements both ways, take their SQL_ID to DBMS_XPLAN.DISPLAY_CURSOR, and tune the plans that do the most work.
