Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs

From: "Ken Naim" <kennaim_at_gmail.com>
Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs Date: Mon, 20 Nov 2006 13:10:36 -0500

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.

Best guess on the comments I've seen so far. You're running with automatic workarea sizing. During the daytime tests nothing is consuming much in the was of pga memory. During the batch there are a number of other 'large memory' jobs going on that have an impact on the "aggregate PGA auto target", so your process that does the insert gets a lot less memory for its joins.

Add a line to your trap that is capturing v$session_wait for the session so it traps v$mystat/v$statname at the same time, and you will probably see it recording some

    workarea executions - onepass
or

    workarea executions - multipass

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

--
http://www.freelists.org/webpage/oracle-l