Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning issue, 10046 trace and Scheduled Jobs

RE: Tuning issue, 10046 trace and Scheduled Jobs

From: Ken Naim <>
Date: Mon, 20 Nov 2006 13:10:36 -0500
Message-ID: <00e301c70ccf$2ee2d650$96b016ac@KenHome>


Seems the tracefile went to the bdump dir instead of udump and my tracefile_identifier was ignored (thanks Laimutis). One mystery solved. I will analyze it shortly.

I have also been able to replicate the issue by schedule the procedure calls directly. V$session_wait shows the wait event causing the issue to be direct path read temp waiting 635 seconds already followed by a 139+ second wait for direct path write temp.

-----Original Message-----
From: Ken Naim [] Sent: Monday, November 20, 2006 12:21 PM To: ''
Subject: Tuning issue, 10046 trace and Scheduled Jobs

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 statements.

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 fine.

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?


Received on Mon Nov 20 2006 - 12:10:36 CST

Original text of this message