DBMS_PROFILER not recording time spent in Execute Immediate

From: Christopher Boyle <cboyle@xxxxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Tue, 1 Dec 2009 16:44:11 -0500

Good Day,
   Oracle 11 g standard edition on Windows Server.  I  am working on a
developed in house etl package that  makes frequent use of execute immediate
to perform DDL such as Rename Table and Drop/Create Indexes, etc.  I have
been using DBMS_PROFILER to gather data about which lines of code are the
most executed and which consume the most time.  Pretty standard so far but
when the process reaches a line like "Execute Immediate Create
MultiColumnIndex on 100 million row table" the profiler reports that less
than 3 milliseconds are required but it will also show a reasonable amount
of time for other DDL statements, including create index.   I have googled,
Kyted and binged but not found anything that addresses how DBMS_PROFILER
interacts with Execute Immediate or DDL  anywhere.  Can anyone point me
towards a resource that would clarify this for me?  The amount of data being
processed as well as the run time are increasing. We are months away from
being able to do a full rewrite.  Even so, I will gladly accept any
suggestions on how to restructure the ETL process to reduce the processing
time.  Yes, Bulk Collects and foralls are being used.   A temp table is
created as select the unchanged records, the changed records are loaded into
the temp table, the indexes are dropped on the original and created on the
temp table which is finally renamed to take the place of the original.

Thanks,
Chris

DBMS_PROFILER not recording time spent in Execute Immediate

Other related posts: