Subject: RE: Tuning issue, 10046 trace and Scheduled Jobs
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Nov 2006 07:43:38 -0000
============================================
From: "Ken Naim" <kennaim@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
