Re: latch
Hi! Shared pool latch protects memory allocation and de-allocation in shared pool. Thus probably you have a very volatile shared pool currently. You have several options, first of them would be to check whether you'r application is using bind variables. If it is mostly using literal values in SQL, then each sql call with different parameters has to be parsed and stored in library cache along with it's execution plan etc.. Maybe you have too *big* shared pool? I'm not sure about 9i, but in 8i you could have a lot of shared pool LRU scanning for recreatable chunks when loading/parsing in a new object. If literal values in SQL are your case, then either change the app or use cursor_sharing = force parameter. Check www.ixora.com.au Q+A section under shared pool, there is more information about shared pool tuning. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, July 28, 2003 8:34 PM > can someone please tell me what is shared pool > latch(latch# 155) and how do we prevent this. > > i am facing this in 9i very frequently. > > thanks > sai > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Sai Selvaganesan > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: latch wait
Try v$latch_children -Original Message- Sent: Monday, November 12, 2001 2:30 PM To: Multiple recipients of list ORACLE-L I have a puzzling problem: a session is apparently waiting on a "cache buffers chains latch" forever (well, for 40 minutes and counting, so far); seq# in v$session_wait is static. "tries" is 0. latch free address C4708BC0 number 66 tries 0 00 When examining the latch in question (using the address from v$session_wait.p1raw), I'm not seeing any change in the get, misses, etc. statistics. It's as though a process has the latch and won't let go. Any ideas from the list as to where I look next? TIA, Paul Baumgartel __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: latch wait
Paul , try joining the v$session with v$sqlarea to find offending sql harassing the cache buffers chains.. here is a sample sql u could use .. select sql_text from v$sqlarea, v$session where v$sqlarea.address= v$session.sql_address and v$sqlarea.hash_value = v$session.sql_hash_value and sid='Your Sid From V$session wait for latch#66'; find the offending sql and tune it .. cache buffer chains latch means that your SQL is badly written and is performing a considerably higher # of LIO's .. explian plan the sql and see if you can use indexes .. also analyze your table and indexes .. post dump from v$session wait for wait=0 (waiting state) .. so other list members an give you more suggestions Deepak --- "Gogala, Mladen" <[EMAIL PROTECTED]> wrote: > Try v$latch_children > > -Original Message- > Sent: Monday, November 12, 2001 2:30 PM > To: Multiple recipients of list ORACLE-L > > > I have a puzzling problem: a session is apparently > waiting on a "cache > buffers chains latch" forever (well, for 40 minutes > and counting, so > far); seq# in v$session_wait is static. "tries" is > 0. > > latch free > > address > > C4708BC0 > number > > 66 > tries > 0 > 00 > When examining the latch in question (using the > address from > v$session_wait.p1raw), I'm not seeing any change in > the get, misses, > etc. statistics. It's as though a process has the > latch and won't let > go. Any ideas from the list as to where I look > next? > > TIA, > > Paul Baumgartel > > > __ > Do You Yahoo!? > Find a job, post your resume. > http://careers.yahoo.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > 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). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Gogala, Mladen > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > 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). __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: latch wait
Thanks for the suggestions. I already know what the SQL is, and which latch is waited for (by looking it up in v$latch_children). The latch does show the number of gets increasing over time, but the session's seq# in v$session_wait _does not change_, which shows that it's been waiting on the same latch (the same wait event) for over 40 minutes. This is what's puzzling me. --- Deepak Thapliyal <[EMAIL PROTECTED]> wrote: > Paul , > > try joining the v$session with v$sqlarea to find > offending sql harassing the cache buffers chains.. > > here is a sample sql u could use .. > > select sql_text > from v$sqlarea, v$session > where v$sqlarea.address= v$session.sql_address and > v$sqlarea.hash_value = v$session.sql_hash_value > and sid='Your Sid From V$session wait for latch#66'; > > find the offending sql and tune it .. > > cache buffer chains latch means that your SQL is badly > written and is performing a considerably higher # of > LIO's .. explian plan the sql and see if you can use > indexes .. also analyze your table and indexes .. post > dump from v$session wait for wait=0 (waiting state) .. > so other list members an give you more suggestions > > Deepak > > --- "Gogala, Mladen" <[EMAIL PROTECTED]> wrote: > > Try v$latch_children > > > > -Original Message- > > Sent: Monday, November 12, 2001 2:30 PM > > To: Multiple recipients of list ORACLE-L > > > > > > I have a puzzling problem: a session is apparently > > waiting on a "cache > > buffers chains latch" forever (well, for 40 minutes > > and counting, so > > far); seq# in v$session_wait is static. "tries" is > > 0. > > > > latch free > > > > address > > > > C4708BC0 > > number > > > > 66 > > tries > > 0 > > 00 > > When examining the latch in question (using the > > address from > > v$session_wait.p1raw), I'm not seeing any change in > > the get, misses, > > etc. statistics. It's as though a process has the > > latch and won't let > > go. Any ideas from the list as to where I look > > next? > > > > TIA, > > > > Paul Baumgartel > > > > > > __ > > Do You Yahoo!? > > Find a job, post your resume. > > http://careers.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Paul Baumgartel > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: > > (858) 538-5051 > > San Diego, California-- Public Internet > > access / Mailing Lists > > > > > 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). > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Gogala, Mladen > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: > > (858) 538-5051 > > San Diego, California-- Public Internet > > access / Mailing Lists > > > > > 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). > > > __ > Do You Yahoo!? > Find a job, post your resume. > http://careers.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deepak Thapliyal > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX
RE: latch free - library cache
Title: RE: latch free - library cache Raj, With Oracle 9.2.0.3, I got one of the following three problems when CF=F or S. 1) ORA-03113: end-of-file on communication channel 2) ORA-01802: Julian date is out of range 3) performance problems. My previous statement is still valid. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, May 15, 2003 2:52 PMTo: Multiple recipients of list ORACLE-LSubject: RE: latch free - library cache Send it to me ... I have it CS=F working on all of my 5 production RAC instances 9202 with 5 patches (for various reasons). XML/JAMA/Intermedia/CLOBS ... you name it, we have it. ps: what are the problems you are facing? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Michael Wu [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 15, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: latch free - library cache If somebody can make CURSOR_SHARING = SIMILAR|FORCE to work even with 9iR2 (with the latest patch), I am willing to give him $1000. -Original Message- Sent: Thursday, May 15, 2003 9:42 AM To: Multiple recipients of list ORACLE-L Cursor sharing set to FARCE forces constants to be converted into bind variables and enables SQL statements to be shared even if the're not identical. It also enables quite a few very interesting ora-0600 and ora-7445 errors that have very good applications in a DSS environment. It will work properly in Oracle 19zR20 and not in oracle 9iR2. Plase, de patient. Your money is very important to us. ** This e-mail contains privileged attorney-client communications and/or confidential information, and is only for the use by the intended recipient. Receipt by an unintended recipient does not constitute a waiver of any applicable privilege. Reading, disclosure, discussion, dissemination, distribution or copying of this information by anyone other than the intended recipient or his or her employees or agents is strictly prohibited. If you have received this communication in error, please immediately notify us and delete the original material from your computer. Sempra Energy Trading Corp. (SET) is not the same company as SDG&E or SoCalGas, the utilities owned by SET's parent company. SET is not regulated by the California Public Utilities Commission and you do not have to buy SET's products and services to continue to receive quality regulated service from the utilities. **
RE: latch free - library cache
Title: RE: latch free - library cache Michael, You'll have to wait about 6 months before I'll go to 9203, let's just say, we just got tired of de-bugging patches for Oracle. Raj -Original Message-From: Michael Wu [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 6:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: latch free - library cache Raj, With Oracle 9.2.0.3, I got one of the following three problems when CF=F or S. 1) ORA-03113: end-of-file on communication channel 2) ORA-01802: Julian date is out of range 3) performance problems. My previous statement is still valid. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, May 15, 2003 2:52 PMTo: Multiple recipients of list ORACLE-LSubject: RE: latch free - library cache Send it to me ... I have it CS=F working on all of my 5 production RAC instances 9202 with 5 patches (for various reasons). XML/JAMA/Intermedia/CLOBS ... you name it, we have it. ps: what are the problems you are facing? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Michael Wu [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 15, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: latch free - library cache If somebody can make CURSOR_SHARING = SIMILAR|FORCE to work even with 9iR2 (with the latest patch), I am willing to give him $1000. -Original Message- Sent: Thursday, May 15, 2003 9:42 AM To: Multiple recipients of list ORACLE-L Cursor sharing set to FARCE forces constants to be converted into bind variables and enables SQL statements to be shared even if the're not identical. It also enables quite a few very interesting ora-0600 and ora-7445 errors that have very good applications in a DSS environment. It will work properly in Oracle 19zR20 and not in oracle 9iR2. Plase, de patient. Your money is very important to us. **This e-mail contains privileged attorney-client communications and/or confidential information, and is only for the use by the intended recipient. Receipt by an unintended recipient does not constitute a waiver of any applicable privilege.Reading, disclosure, discussion, dissemination, distribution or copying of this information by anyone other than the intended recipient or his or her employees or agents is strictly prohibited. If you have received this communication in error, please immediately notify us and delete the original material from your computer.Sempra Energy Trading Corp. (SET) is not the same company as SDG&E or SoCalGas, the utilities owned by SET's parent company. SET is not regulated by the California Public Utilities Commission and you do not have to buy SET's products and services to continue to receive quality regulated service from the utilities.** *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.*1
Re: latch free wait event
David, Do you have timed_statistics set to true? If not, you need to do so. Although I am pretty certain that this is the case, but let's assume that you truly waited 0 seconds. If you were able to remove ALL of the latch free events, you would improve the response time in the system by 0 seconds. Not exactly a cost-beneficial activity. Set timed_statistics to true and then look at the wait analysis. Focus on seconds waiting, not total waits. There is where you will find the benefit. Daniel "Can you tell I was just at a Hotsos Clinic?" Fink "Ehresmann, David" wrote: > System-wide Wait Analysis > for current wait events > > Average > Event Total SecondsTotal Wait > NameWaits Waiting Timeouts (in secs) > - - - - > --- > latch free1,4590 1,393 .000 > > After querying v$system_event my biggest concern is the "latch free" wait > event. I understand that latch free is "the process waits for a latch that > is currently busy ( held by another process).How can I drill down and > find the cause of this? I have a feeling it is about rollback or redo > logs. > > thanks, > > David Ehresmann > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ehresmann, David > 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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: latch free wait event
The number of waits is irrelevant. What matters is the time waited which in your case shows 0, but I assume that is because you have not set timed_statistics to true. Without that the data from v$system_event are worthless. Cary will probably step in here and tell you that even with timed_statistics that data is at best of dubious worth. At 02:49 PM 9/4/2003 -0800, you wrote: System-wide Wait Analysis for current wait events Average Event Total SecondsTotal Wait NameWaits Waiting Timeouts (in secs) - - - - --- latch free1,4590 1,393 .000 After querying v$system_event my biggest concern is the "latch free" wait event. I understand that latch free is "the process waits for a latch that is currently busy ( held by another process).How can I drill down and find the cause of this? I have a feeling it is about rollback or redo logs. thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: latch free wait event
When you get wait seconds > 0 use TKPROF and look for statements taking time. HTH GovindanK > > "Ehresmann, David" wrote: > >> System-wide Wait Analysis >> for current wait events >> >> Average >> Event Total SecondsTotal >> Wait >> NameWaits Waiting Timeouts (in >> secs) >> - - - - >> --- >> latch free1,459 1,393 >> .000 >> >> After querying v$system_event my biggest concern is the "latch free" >> wait >> event. I understand that latch free is "the process waits for a latch >> that >> is currently busy ( held by another process).How can I drill down >> and >> find the cause of this? I have a feeling it is about rollback or redo >> logs. >> >> thanks, >> >> David Ehresmann >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: Ehresmann, David >> INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GovindanK 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: latch free wait event
Hi, You can query the v$session_wait (or event 10046 if you are a big fan of 10046) to get the latch#. If I Remeber right the P2 value in the session wait (or p3?) gives the latch number and you can join this with v$latch_name to get the name of the latch. Once you find out the waiting (or spinning) latch you can get an easy fix based on the latch. Regards, K Gopalakrishnan - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, September 05, 2003 5:14 AM > The number of waits is irrelevant. What matters is the time waited which in > your case shows 0, but I assume that is because you have not set > timed_statistics to true. Without that the data from v$system_event are > worthless. Cary will probably step in here and tell you that even with > timed_statistics that data is at best of dubious worth. > > At 02:49 PM 9/4/2003 -0800, you wrote: > > > System-wide Wait Analysis > > for current wait events > > > > Average > >Event Total SecondsTotal Wait > >NameWaits Waiting Timeouts (in secs) > >- - - - > >--- > >latch free1,4590 1,393 .000 > > > >After querying v$system_event my biggest concern is the "latch free" wait > >event. I understand that latch free is "the process waits for a latch that > >is currently busy ( held by another process).How can I drill down and > >find the cause of this? I have a feeling it is about rollback or redo > >logs. > > > >thanks, > > > > > > > >David Ehresmann > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Ehresmann, David > > 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). > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Wolfgang Breitling > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: latch free wait event
Yes. Even with TIMED_STATISTICS=TRUE, relying on *any* statistic with system-wide scope can waste your time. For a 34-page introduction to the rationale behind this proposition, see: http://www.oreilly.com/catalog/optoraclep/chapter/ch01.pdf Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Wolfgang Breitling Sent: Thursday, September 04, 2003 6:44 PM To: Multiple recipients of list ORACLE-L The number of waits is irrelevant. What matters is the time waited which in your case shows 0, but I assume that is because you have not set timed_statistics to true. Without that the data from v$system_event are worthless. Cary will probably step in here and tell you that even with timed_statistics that data is at best of dubious worth. At 02:49 PM 9/4/2003 -0800, you wrote: > System-wide Wait Analysis > for current wait events > > Average >Event Total SecondsTotal Wait >NameWaits Waiting Timeouts (in secs) >- - - - >--- >latch free1,4590 1,393 .000 > >After querying v$system_event my biggest concern is the "latch free" wait >event. I understand that latch free is "the process waits for a latch that >is currently busy ( held by another process).How can I drill down and >find the cause of this? I have a feeling it is about rollback or redo >logs. > >thanks, > > > >David Ehresmann > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Ehresmann, David > 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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: latch free wait event
"Can" instead of "Will" is probably only in the case where there's only one user on the system :-))). Cary Millsap wrote: Yes. Even with TIMED_STATISTICS=TRUE, relying on *any* statistic with system-wide scope can waste your time. For a 34-page introduction to the rationale behind this proposition, see: http://www.oreilly.com/catalog/optoraclep/chapter/ch01.pdf Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Wolfgang Breitling Sent: Thursday, September 04, 2003 6:44 PM To: Multiple recipients of list ORACLE-L The number of waits is irrelevant. What matters is the time waited which in your case shows 0, but I assume that is because you have not set timed_statistics to true. Without that the data from v$system_event are worthless. Cary will probably step in here and tell you that even with timed_statistics that data is at best of dubious worth. At 02:49 PM 9/4/2003 -0800, you wrote: System-wide Wait Analysis for current wait events Average Event Total SecondsTotal Wait NameWaits Waiting Timeouts (in secs) - - - - --- latch free1,4590 1,393 .000 After querying v$system_event my biggest concern is the "latch free" wait event. I understand that latch free is "the process waits for a latch that is currently busy ( held by another process).How can I drill down and find the cause of this? I have a feeling it is about rollback or redo logs. thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: latch free wait event
You sir, are one hell of a great communicator! I'm placing my order now... on 9/5/03 7:19 AM, Cary Millsap at [EMAIL PROTECTED] wrote: > Yes. Even with TIMED_STATISTICS=TRUE, relying on *any* statistic with > system-wide scope can waste your time. For a 34-page introduction to the > rationale behind this proposition, see: > >http://www.oreilly.com/catalog/optoraclep/chapter/ch01.pdf > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic 101 in Sydney > - Hotsos Symposium 2004, March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Wolfgang Breitling > Sent: Thursday, September 04, 2003 6:44 PM > To: Multiple recipients of list ORACLE-L > > The number of waits is irrelevant. What matters is the time waited which > in > your case shows 0, but I assume that is because you have not set > timed_statistics to true. Without that the data from v$system_event are > worthless. Cary will probably step in here and tell you that even with > timed_statistics that data is at best of dubious worth. > > At 02:49 PM 9/4/2003 -0800, you wrote: > >> System-wide Wait Analysis >> for current wait events >> >> > Average >> Event Total SecondsTotal > Wait >> NameWaits Waiting Timeouts (in > secs) >> - - - - >> --- >> latch free1,4590 1,393 > .000 >> >> After querying v$system_event my biggest concern is the "latch free" > wait >> event. I understand that latch free is "the process waits for a latch > that >> is currently busy ( held by another process).How can I drill down > and >> find the cause of this? I have a feeling it is about rollback or redo >> logs. >> >> thanks, >> >> >> >> David Ehresmann >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: Ehresmann, David >> 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). > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: Latch 'parallel query qref latch'
Ben: The qref latch is obtained for each Parallel Query process. It is acquired before the queue buffers can be manipulated so that communication can take place between the various slave processes. The latch is acquired for putting messages into the buffer. You will generally see lots of misses for this latch since it is acquired with a nowait option. Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio >--- Original Message --- >From: "Ben Jones" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Date: 6/14/01 1:56:36 PM > >Does anyone know what causes waits on this latch ? > >Thanks >Ben >_ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ben Jones > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Latch 'parallel query qref latch'
Jon, Thanks for the email. My next question is fairly obvious...I'm getting a "lot" of waits on this latch (up to 5% of all waits) - how do I tune it ? (We've got a pretty typical environment here, a few big tables with parallel degree 4, on a 2 cpu box with 2G of RAM, and only a couple of end users doing big queries)... Any guidelines (from anybody) would be much appreciated... TIA Ben >From: "Jon Walthour" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Latch 'parallel query qref latch' >Date: Thu, 14 Jun 2001 13:01:56 -0800 > > > >Ben: > >The qref latch is obtained for each Parallel Query process. It >is acquired before the queue buffers can be manipulated so that >communication can take place between the various slave processes. >The latch is acquired for putting messages into the buffer. You >will generally see lots of misses for this latch since it is >acquired with a nowait option. > >Jon Walthour, OCDBA >Oracle DBA >Computer Horizons >Cincinnati, Ohio > > >--- Original Message --- > >From: "Ben Jones" <[EMAIL PROTECTED]> > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Date: 6/14/01 1:56:36 PM > > > > >Does anyone know what causes waits on this latch ? > > > >Thanks > >Ben > >_ > >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Ben Jones > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California-- Public Internet access / Mailing >Lists > > > >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). > > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Jon Walthour > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >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). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Jones INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Latch 'parallel query qref latch'
Ben: As I said in my previous post, you may get a lot of waits on this latch and that is not necessarily something to be overly concerned about. However, you can lower this number by increasing the PARALLEL_MAX_SERVERS parameter. This is the maximum number of servers that are allowed to exist concurrently. Set the value to: (max # of concurrent PQO users) * (their max degree of parallelism) * 2 HTH! -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio > From: "Ben Jones" <[EMAIL PROTECTED]> > Organization: Fat City Network Services, San Diego, California > Reply-To: [EMAIL PROTECTED] > Date: Fri, 15 Jun 2001 00:05:48 -0800 > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Latch 'parallel query qref latch' > > > Jon, > > Thanks for the email. My next question is fairly obvious...I'm getting a > "lot" of waits on this latch (up to 5% of all waits) - how do I tune it ? > (We've got a pretty typical environment here, a few big tables with parallel > degree 4, on a 2 cpu box with 2G of RAM, and only a couple of end users > doing big queries)... > > Any guidelines (from anybody) would be much appreciated... > > TIA > Ben > >> From: "Jon Walthour" <[EMAIL PROTECTED]> >> Reply-To: [EMAIL PROTECTED] >> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >> Subject: RE: Latch 'parallel query qref latch' >> Date: Thu, 14 Jun 2001 13:01:56 -0800 >> >> >> >> Ben: >> >> The qref latch is obtained for each Parallel Query process. It >> is acquired before the queue buffers can be manipulated so that >> communication can take place between the various slave processes. >> The latch is acquired for putting messages into the buffer. You >> will generally see lots of misses for this latch since it is >> acquired with a nowait option. >> >> Jon Walthour, OCDBA >> Oracle DBA >> Computer Horizons >> Cincinnati, Ohio >> >>> --- Original Message --- >>> From: "Ben Jones" <[EMAIL PROTECTED]> >>> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >>> Date: 6/14/01 1:56:36 PM >>> >> >>> Does anyone know what causes waits on this latch ? >>> >>> Thanks >>> Ben >>> _ >>> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. >>> >>> -- >>> Please see the official ORACLE-L FAQ: http://www.orafaq.com >>> -- >>> Author: Ben Jones >>> INET: [EMAIL PROTECTED] >>> >>> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >>> San Diego, California-- Public Internet access / Mailing >> Lists >>> >>> 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). >>> >> >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.com >> -- >> Author: Jon Walthour >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >> San Diego, California-- Public Internet access / Mailing Lists >> >> 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). > > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ben Jones > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, sen
RE: Latch 'parallel query qref latch'
inter process communications between groups of parallel query server processes. often found in parallel ==> serial or parallel ==> parallel operations. -Original Message- Sent: Thursday, June 14, 2001 1:57 PM To: Multiple recipients of list ORACLE-L Does anyone know what causes waits on this latch ? Thanks Ben _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Jones INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
