Dennis,
I did, but the problem is this is a SQR report, and it ain't one sql. SQR reports are like giant cursor loops but un structured. There are about 100 different sql statements (not including the recursive ones).
In RBO, eight such reports could run parallel and not affect anything and finish in approx 25 minutes. In CBO they crash in about 14 minutes into the report and out of eight only about 3/4 manage to finish.
Performance is my second priority, reducing its temp space usage is my first priority. The trace files is about 400MB+ in size.
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 17, 2002 10:11 AM
To: '[EMAIL PROTECTED]'
Cc: Jamadagni, Rajendra
Subject: RE: Tracking Temp Segment Usage and Event 10046
Raj - I personally favor using the simpler diagnostics first. Why not run
your SQL through EXPLAIN PLAN and see which SQL is performing sorts? My
immediate guess is that under RBO an index was being used that isn't now.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 17, 2002 9:34 AM
To: Multiple recipients of list ORACLE-L
Subject: Tracking Temp Segment Usage and Event 10046
I have a problem with a process and its temp segment usage.
Previously, in RBO (8161) we used to run 8 reports with slightly different
parameters in parallel and it used to work fine with all other load on the
system and the total TEMP space was 6GB
Since we migrated to CBO (all tables & indexes are analyzed using estimate),
these processes fail with unable to extent temp segment error. Currently we
have allocated 12GB to temp tablespace.
I also have performance issues so I am doing a 10046 trace at level 8 on
this one. Is there a way to identify when this process used temp space, how
much and when does it de-allocate?
Would this information be available in 10046 trace or is there something
else that I could do to track that ...
Thanks in advance
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
