Skip to Main Content
  • Questions

  • How to find the SQL_ID of the sql statements running in a stored procedure?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saket.

Asked: February 16, 2018 - 9:36 am UTC

Last updated: August 13, 2021 - 12:28 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Team,
I have scenario in which I need to check which of my procedures(they run in batch) are slowing down the operations.
The procedure consist of two main tasks:
1.) Get data from multiple table (has multiple joins and vast data)
2.) insert the obtained data from task 1 into some table.
Part 1 consist of multiple sql statements, which I think may be slowing down the process. I need to find which queries exactly are causing trouble and also to which procedure do they belong to. Thanks in advance.

and Chris said...

Trace your session!

alter session set sql_trace = true;
exec  your_procedures;
alter session set sql_trace = false;


Then, when you format the resulting trace file, you'll see what all the statements are, how long they took, etc.

With TKPROF you can format the file so the slowest are at the top, making it easy to see where the problems are.

For more on this, read:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Worked for me!

Saket Parab, February 16, 2018 - 3:17 pm UTC

Hey Chris! That worked! Thanks a lot.
Chris Saxon
February 16, 2018 - 4:31 pm UTC

Great :)

Nitin Jain, August 11, 2021 - 3:06 pm UTC

I’m some cases, procedure executes by application , in that scenarios , how can we trace it?
Connor McDonald
August 13, 2021 - 12:28 am UTC

If you take a look at at DBMS_MONITOR there are various mechanisms to hone in on particular sessions, services, modules etc...

SQL> desc dbms_monitor
PROCEDURE CLIENT_ID_STAT_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_ID                      VARCHAR2                IN
PROCEDURE CLIENT_ID_STAT_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_ID                      VARCHAR2                IN
PROCEDURE CLIENT_ID_TRACE_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_ID                      VARCHAR2                IN
PROCEDURE CLIENT_ID_TRACE_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_ID                      VARCHAR2                IN
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
 PLAN_STAT                      VARCHAR2                IN     DEFAULT
PROCEDURE DATABASE_TRACE_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 INSTANCE_NAME                  VARCHAR2                IN     DEFAULT
PROCEDURE DATABASE_TRACE_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
 INSTANCE_NAME                  VARCHAR2                IN     DEFAULT
 PLAN_STAT                      VARCHAR2                IN     DEFAULT
PROCEDURE SERV_MOD_ACT_STAT_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SERVICE_NAME                   VARCHAR2                IN
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE SERV_MOD_ACT_STAT_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SERVICE_NAME                   VARCHAR2                IN
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE SERV_MOD_ACT_TRACE_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SERVICE_NAME                   VARCHAR2                IN
 MODULE_NAME                    VARCHAR2                IN     DEFAULT
 ACTION_NAME                    VARCHAR2                IN     DEFAULT
 INSTANCE_NAME                  VARCHAR2                IN     DEFAULT
PROCEDURE SERV_MOD_ACT_TRACE_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SERVICE_NAME                   VARCHAR2                IN
 MODULE_NAME                    VARCHAR2                IN     DEFAULT
 ACTION_NAME                    VARCHAR2                IN     DEFAULT
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
 INSTANCE_NAME                  VARCHAR2                IN     DEFAULT
 PLAN_STAT                      VARCHAR2                IN     DEFAULT
PROCEDURE SESSION_TRACE_DISABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     BINARY_INTEGER          IN     DEFAULT
 SERIAL_NUM                     BINARY_INTEGER          IN     DEFAULT
PROCEDURE SESSION_TRACE_ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SESSION_ID                     BINARY_INTEGER          IN     DEFAULT
 SERIAL_NUM                     BINARY_INTEGER          IN     DEFAULT
 WAITS                          BOOLEAN                 IN     DEFAULT
 BINDS                          BOOLEAN                 IN     DEFAULT
 PLAN_STAT                      VARCHAR2                IN     DEFAULT


Even if your app uses connection pooling, you can collect multiple trace files and then use trcsess to consolidate them

https://docs.oracle.com/database/121/TGSQL/tgsql_trace.htm#TGSQL791
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database