Skip to main content
Question

How to see currently executed SQL in PL/VSQL stored procedure

  • March 6, 2023
  • 4 replies
  • 4 views

Sergey_Cherepan_1
Forum|alt.badge.img+2

Hi,

I am running PL/vSQL stored procedure.

In sessions system table, I can see only call to stored procedure.

How I can see currently executing SQL (if any) for session that is running stored procedure?

Thank you
Sergey

4 replies

SruthiA
Forum|alt.badge.img+1
  • Participating Frequently
  • March 6, 2023

@Sergey_Cherepan_1 ; It is getting logged in dc_requests_issued but as a part of different txn id

I have run below to call the stored procedure. as you can see it logged the query, but as a part of other txn id.

eonv1203=> call result1();

result1

   0

(1 row)

select node_name, transaction_id, statement_id,request from dc_requests_issued order by time desc;

v_eonv1203_node0001 | 45035996273735120 | 1 | select ($1)::int
v_eonv1203_node0001 | 45035996273735118 | 1 | SELECT * FROM t1 ORDER BY b DESC
v_eonv1203_node0001 | 45035996273735117 | 1 | call result1();
v_eonv1203_node0001 | 45035996273735116 | 1 | CREATE PROCEDURE result1() LANGUAGE PLvSQL AS $$
DECLARE
x int;
BEGIN
x <- SELECT * FROM t1 ORDER BY b DESC; -- x is now assigned the first row returned by the SELECT query
RETURN;
END;
$$;


Sergey_Cherepan_1
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • March 6, 2023

Thanks for looking!
Yes I found that SQL is logged in dc requests issued under different transaction Id.
Question here - how I can monitor SQL executed in stored procedure.
I want to see what SQL is being executed right now, and how long it is running.


Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • March 7, 2023

Stored procedures run in an internal session that you can view with "select * from vs_sessions;"


Sergey_Cherepan_1
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • March 7, 2023

Interesting... thanks for hint, will investigate how it works.