PL/SQL Developer / DBMS_SQL

[Frank Menne]

Hello !

I often use the Profiler function in the
PL/SQL Developer. Very good tuning tool.

Im my Projects I very often use Dynamic SQL.
So a huge amount of processing time is spent
in the DBMS_SQL module. But this information
is rather useless for me. I need the content
of the dynamic statement or at least the line
in my own procedure which causes the dynamic
statement.

Any Idea how to get this information / how to
modify my code, etc... ???

 

[Marco Kalter]

That's the problem with dynamic SQL: you generate your (PL/)SQL code at runtime, which is invisible to the profiler functionality. With SQL Trace you should be able to obtain the information you need, but it's obviously not as convenient as the profiler.

 

[amaury]

Did you try using "Native Dynamic SQL" (EXECUTE IMMEDIATE...) instead of DBMS_SQL?

Since this is a built-in instruction and not a function call, the time spent executing the query appears on the correct line.

Maybe the time to rewrite your code...

Amaury.