Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajesh.

Asked: October 01, 2024 - 1:10 am UTC

Last updated: October 02, 2024 - 12:44 am UTC

Version: 21c

Viewed 100+ times

You Asked

How to tune procedure, function, package while running slow?

and Connor said...

Like anything

a) add instrumentation https://asktom.oracle.com/ords/asktom.search?tag=instrumentation-overhead
b) find where the most time is being lost https://carymillsap.blogspot.com/2009/12/my-whole-system-is-slow-now-what.html

The most common thing I see when PLSQL is slow is the SQL calls within it are slow, so standard tracing can also be useful

- exec dbms_monitor.session_trace_enable(waits=>true)
- run your proc
- exec dbms_monitor.session_trace_disable
- get the tracefile name from v$diag_info https://youtu.be/Xoq3lvxUTmY
- run tkprof to summarise the results

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library