RE: Tracking Temp Segment Usage and Event 10046
Title: Tracking Temp Segment Usage and Event 10046 For more information about 10053, see Wolfgang Breitlings http://www.hotsos.com/dnloads/1/10053/Breitling2002.pdf. Wolfgang will present this and another new 10053 paper at our Symposium in Dallas next February (http://www.hotsos.com/events/symposium). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni, Rajendra Sent: Wednesday, September 18, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Subject: RE: Tracking Temp Segment Usage and Event 10046 I spent last two days working on that SQR and finally nailed it. Developers were using DISTINCT as a rule and CBO was choosing incorrect indexes. I had to do following to make it work ... 1.we put hints in appropriate places 2.created 3 new indexes to help the queries 3. Created 1 MV 4. Change the pl/sql code to be more efficient 5. Remove unnecessary distinct conditions. After all this said and done, I finally have the SQR taking around 375 seconds compared to about 1500 seconds. Even if I run all 8 of them in parallel it doesn't blow the TEMP segments. Now comes the difficult part, to explain the development the things that we had to do and why we had to do them. I think I am really starting to see the power of event 10046 ... I just wish I should learn more on event 10053 as well, but that will come too eventually. I am still going to track the temp segment usage though ... Thanks everyone for all your kind suggestions, I am not done yet, there is more work to be done. 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: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: Tracking Temp Segment Usage and Event 10046 I'd start looking at the execution plans first and examine if there is any Cartesian joins and the order the tables get joined. Waleed
RE: Tracking Temp Segment Usage and Event 10046
Title: Tracking Temp Segment Usage and Event 10046 Thanks Cary One thing I noticed is some parameters referenced in that document are different in 9iR2 ... I guess Wolfgang is probably working on the 9iR2 version ... It is a very nice paper ... 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: Cary Millsap [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 19, 2002 5:24 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Tracking Temp Segment Usage and Event 10046 For more information about 10053, see Wolfgang Breitlings http://www.hotsos.com/dnloads/1/10053/Breitling2002.pdf. Wolfgang will present this and another new 10053 paper at our Symposium in Dallas next February (http://www.hotsos.com/events/symposium). Cary MillsapHotsos Enterprises, Ltd.http://www.hotsos.comUpcoming events:- Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu- 2003 Hotsos Symposium on Oracle® System Performance, Feb 912 Dallas- Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark 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
RE: Tracking Temp Segment Usage and Event 10046
Title: Tracking Temp Segment Usage and Event 10046 I'd start looking at the execution plans first and examine if there is any Cartesian joins and the order the tables get joined. Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 17, 2002 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: 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!
RE: Tracking Temp Segment Usage and Event 10046
aren't Cartesian joins are unlikely since all was well in the RBO world? sounds to me like the RBO was simply selecting better query plans than the CBO is. Raj - have you tried playing around with the OPTIMIZER_MODE parameter or OPTIMIZER_INDEX_CACHING; and OPTIMIZER_INDEX_COST_ADJ as described in Tim Gorman's paper? http://www.evdbt.com/SearchIntelligenceCBO.doc http://www.evdbt.com/SearchIntelligenceCBO.doc -Original Message- Sent: Wednesday, September 18, 2002 10:03 AM To: Multiple recipients of list ORACLE-L I'd start looking at the execution plans first and examine if there is any Cartesian joins and the order the tables get joined. Waleed -Original Message- Sent: Tuesday, September 17, 2002 10:34 AM To: Multiple recipients of list ORACLE-L 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tracking Temp Segment Usage and Event 10046
Title: Tracking Temp Segment Usage and Event 10046 I spent last two days working on that SQR and finally nailed it. Developers were using DISTINCT as a rule and CBO was choosing incorrect indexes. I had to do following to make it work ... 1.we put hints in appropriate places 2.created 3 new indexes to help the queries 3. Created 1 MV 4. Change the pl/sql code to be more efficient 5. Remove unnecessary distinct conditions. After all this said and done, I finally have the SQR taking around 375 seconds compared to about 1500 seconds. Even if I run all 8 of them in parallel it doesn't blow the TEMP segments. Now comes the difficult part, to explain the development the things that we had to do and why we had to do them. I think I am really starting to see the power of event 10046 ... I just wish I should learn more on event 10053 as well, but that will come too eventually. I am still going to track the temp segment usage though ... Thanks everyone for all your kind suggestions, I am not done yet, there is more work to be done. 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: Khedr, Waleed [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 18, 2002 11:03 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Tracking Temp Segment Usage and Event 10046 I'd start looking at the execution plans first and examine if there is any Cartesian joins and the order the tables get joined. Waleed 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
RE: Tracking Temp Segment Usage and Event 10046
v$sort_usage and v$sort_segment are always a good start hth connor --- Khedr, Waleed [EMAIL PROTECTED] wrote: I'd start looking at the execution plans first and examine if there is any Cartesian joins and the order the tables get joined. Waleed -Original Message- Sent: Tuesday, September 17, 2002 10:34 AM To: Multiple recipients of list ORACLE-L 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! = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tracking Temp Segment Usage and Event 10046
Title: Tracking Temp Segment Usage and Event 10046 Raj: You will be able to identify the processes (along with the SQLs and number of extents) using the 10046 level 8 trace. If you use the temp files you see the file# ( in the direct path read/write events) as db_files+1 or MAXDBFILES+1 or similar values. DOn't try to map the FILE# with the dictionary or X$KCFIO. THis is the only catch. KG -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, RajendraSent: Tuesday, September 17, 2002 7:34 AMTo: Multiple recipients of list ORACLE-LSubject: 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!
RE: Tracking Temp Segment Usage and Event 10046
Title: RE: Tracking Temp Segment Usage and Event 10046 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
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- Sent: Tuesday, September 17, 2002 9:34 AM To: Multiple recipients of list ORACLE-L 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
