Tuning issue, 10046 trace and Scheduled Jobs

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 20 Nov 2006 12:20:59 -0500

I have a pl/sql batch process running on 10g (using scheduled
programs/schedules/jobs) on windows that runs nightly starting at 3 am and
part of it calls 2 similar procedures; 3 inserts into global temporary
tables, followed by a call to dbms_stats to set the number of records,
followed by an insert as select full outer joining the 3 GTT's (~155k narrow
records each) with a bit of logic into a permanent table. 

These insert statements when run at night as part of the batch take 35+
minutes, when they are run at any other time they take less than 10 seconds
(210 times slower) whether by calling the package or just running the insert

I have looked into everything I can think of. I have checked the explain
plans on the 2 queries and they are optimal, statistics and indexes are

Yesterday I added 10046 tracing around the 2 procedure calls to see what was
taking so long within those queries however no trace file was created. When
I just ran the package, it ran quickly and the trace file was generated.

1. Has anyone ever seen a 10046 not generate a trace file when called by a
scheduled job?
2. Any ideas on why these "insert as selects" from the 3 GTT's would perform
differently when run as a schedule job?



Other related posts: