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

I am running automatic workarea during the day but the batch process changes its session to Manual and then increases its sort and hash area sizes. When the batch runs it is the only process running and it is only these two queries that are slow. I was wrong about the wait events, there seems there are none, as all the time is consumed by cpu. I will implement your suggestion of trapping v$mystat/v$statname and see what it shows.

Thanks,
Ken

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, November 21, 2006 2:44 AM To: oracle-l_at_freelists.org
Subject: 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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 21 2006 - 10:20:18 CST