When one user reports that the application is slow, you need statistics and traces for that user's work, not the whole database. DBMS_MONITOR enables statistics gathering and SQL tracing for a client identifier, a service, module, and action, or a single session, so you measure exactly the work in question.
Code for This Guide
The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_monitor.client_id_stat_enable(client_id); -- statistics, in V$CLIENT_STATS dbms_monitor.client_id_trace_enable(client_id, waits => true, binds => false); dbms_monitor.serv_mod_act_stat_enable(service, module, action); dbms_monitor.session_trace_enable(session_id, serial_num, waits, binds); -- and the matching _disable procedures
DBMS_MONITOR is for DBAs: it needs EXECUTE on the package, usually through the DBA role.
Statistics for One End User
Run as a DBA. The session sets the client identifier agent_42, as an application would for its logged-in user, and statistics are enabled for that identifier.
Example:
begin
dbms_session.set_identifier('agent_42'); -- the end user behind a session
dbms_monitor.client_id_stat_enable('agent_42'); -- collect statistics for it
end;
/
select count(*) from nimbus.bookings where status = 'CONFIRMED';
select count(*) from nimbus.tickets where cabin = 'BUSINESS';
select stat_name, case when value > 0 then 'yes' end as recorded
from v$client_stats
where client_identifier = 'agent_42'
and stat_name in ('user calls', 'parse count (total)', 'execute count', 'DB time')
order by stat_name;
begin
dbms_monitor.client_id_stat_disable('agent_42');
dbms_session.clear_identifier;
end;
/Output:
PL/SQL procedure successfully completed.
COUNT(*)
___________
171
COUNT(*)
___________
270
STAT_NAME RECORDED
______________________ ___________
DB time yes
execute count yes
parse count (total) yes
user calls yes
PL/SQL procedure successfully completed.After two queries, V$CLIENT_STATS has values for agent_42: user calls, parses, executions, and DB time. The block at the end disables the statistics and clears the identifier.
Things to Know
- Client identifiers follow the end user across pooled connections, which session-level tracing cannot do.
- Traces go to trace files on the database server; TKPROF or trcsess summarize them.
- Always disable tracing when done; trace files grow quickly.
Related Guides
Conclusion
DBMS_MONITOR collects statistics and SQL traces for one client identifier, service, module, action, or session. Have applications set identifiers, enable monitoring for the user or step in question, and disable it when the measurement is done.
