RE: SQL in PL/SQL Loop

The only 'small' problem is that you need to modify the PL/SQL code to profile. On production system it is just be a problem and unhapilly this is the place where there is the most load, so the more uses for a profiler.  

I would like that Oracle implements something like the set trace in session for DBMS_PROFILER, based on username or SID and package name:  

DBMS_PROFILER.set_profile_in_session(SID , <PACKAGE_NAME>)
DBMS_PROFILER.set_profile_for_username(<USERNAME> , <PACKAGE_NAME>)
DBMS_PROFILER.set_profile_for_MODULE(<MODULE_NAME> , <PACKAGE_NAME>)
 

Retrieve trace in UDUMP.  

Maybe next version.  

bp

From: Ric Van Dyke [mailto:ric.van.dyke_at_hotsos.com] Sent: Thursday, 19 October, 2006 12:45 PM To: arvind.kumar2_at_siemens.com
Cc: oracle-l_at_freelists.org
Subject: RE: SQL in PL/SQL Loop

You may want to try to profile the run on the block with DBMS_PROFILER to see where the time is going.  

Check the PL/SQL Packages and Types Reference and the PL/SQL User's Guide and Reference guides for details on the package, also see Metalink Support Note 243755.1 for a nifty little report script.  

Ric Van Dyke

Hotsos Enterprises

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 19 2006 - 08:09:30 CDT