Re: distribution of the sleeps on the library cache latches
An excerpt from PIRANHAS IN THE POOL,SQL PERFORMANCE KILLERSInvestigating the effects of literal SQL on Oracle performance John BeresniewiczPrecise Software Solutio -- Effect of CURSOR_SHARINGA primary purpose of the 8.1.6 testing was to assess the impact the new CURSOR_SHARING system parameter and its potential for helping DBAs manage ill-behaved applications with high parse rates of literal SQL. Comparing Test 1 to Test 3 and also Test 0 to Test 4 serves this goal.Comparing these Test results, CURSOR_SHARING exhibits advantages similar to those obtained using bind variables in Test 2:-- Reduced library cache impact.-- Negligible shared pool activity.-- Reduced CPU demands.In fact, Test 3 produced the best elapsed time of all tests in spite of the fact that all the SQL was literal. Thus, it is clear that CURSOR_SHARING can be used to greatly enhance performance of applications that produce high volume literal SQL and thus is a great advantage for the DBA saddled with such applications.CURSOR_SHARING vs. Bind VariablesComparing Test 2 and Test 3 reveals that CURSOR_SHARING = FORCE showed significantly better performance than bind variables in both elapsed time and reduced library cache latching impact. This surprising result deserves further investigation to produce an adequate explanation. Library cache latch impact was significantly reduced as well as shared pool pins and releases. Parsing CPU time increased some but overall CPU was reduced. Perhaps the additional parsing involved in forced cursor sharing also enables increased sharing of shared pool memory heaps.
Re: distribution of the sleeps on the library cache latches
are you using broadvision by any chance .. we have similar issues with one of our main customer databases.. --- Edward Shevtsov <[EMAIL PROTECTED]> wrote: > Hi Steve, > > yes, you're absolutely right. I've inhereted that > system. The > shared_pool_size = 750M. I believe it's HUGE and > oversized. The application > code is mostly based on literal SQL. The miss rate > on the shared pool is > normally about 15%-20% with periodical peaks up to > 50%. But the previous DBA > insist that we shouldn't decrease the size of shared > pool as the miss rate > will be much higher. He also setup periodical > flushing every 3 hours (I > assume he did it in order to prevent ORA-4031). > If I undestand the things right, deacresing of > shared_pool_size will > decrease load on shared pool latch _but_ contention > on the library latches > will be higher because of higher parse rate. Is it > correct and what's your > advice in my case? > > Thanks in advance, > Ed > > > > Hi Ed, > > > > I would agree with the _kgl_latch_count change, > but the _kgl_bucket_count > change seems unwarranted and extreme. Rather I > > suspect that the size of your library cache hash > table rather reflects an > oversized shared pool, probably with some use > > of literal SQL. > > > > @ Regards, > > @ Steve Adams > > @ http://www.ixora.com.au/ - For > DBAs > > @ http://www.secularislam.org/call.htm - For > Muslims > > @ http://www.christianity.net.au/ - For > all > > > > > > -----Original Message----- > > From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] > > Sent: Friday, 19 October 2001 18:02 > > To: [EMAIL PROTECTED] > > Cc: Steve Adams > > Subject: Re: distribution of the sleeps on the > library cache latches > > > > > > Hi Steve, > > > > thanks for your reply. I'm thinking about twice > increasing number of > > library latches ( _kgl_latch_count = 23 ) in order > to mitigate loading on > > them. > > Also I would like to set _kgl_bucket_count = 8 > according to output of your > > script. Do you think it's a good idea in my case. > > > > NAME IMPACT SLEEP_RATE > HOLDING LEVEL# > > - -- -- > -- -- > > library cache 60333579.3 0.32% > 1729452385 > > shared pool19313269.2 1.40% > 8265405 7 > > cache buffers chains1950080.11 0.00% > 629411 1 > > row cache objects 738401.912 0.04% > 3369329 4 > > session allocation 70758.0784 0.01% > 144008 5 > > cache buffer handles56104. 0.01% > 71913 3 > > redo allocation33494.1227 0.02% > 215582 6 > > cache buffers lru chain 12784.3859 0.00% > 198869 3 > > checkpoint queue latch10980.4325 0.00% > 52259 7 > > latch wait list 9976.33016 > 0.04% 24412 9 > > redo writing 4846.5256 0.01% > 75484 5 > > > > Regards, > > Ed > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Edward Shevtsov > 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!? Make a great connection at Yahoo! Personals. http://personals.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: distribution of the sleeps on the library cache latches
Hi Ed It seams that the big problem with force was solved 8172 [BUG:1365873] OERI:17182 / CGA corruption with CURSOR_SHARING=FORCE I havent tried it yet Ivo -Original Message- Sent: Friday, October 19, 2001 01:40 PM To: Multiple recipients of list ORACLE-L Hi Ivo, we are on Linux RH, 8.1.7.0.1. I've tried cursor_sharing=force. Unfortunately, it causes ORA-600. Do 8.1.7.1(2) patches fix this problem? Regards, Ed > Hi Ed > have you tried cursor_sharing=force ? I dont know what version of oracle you > have and there are some known problems but maybe it can help you with > literal sql statements and then decreasing size of shared_pool. > Ivo > > > -Original Message- > Sent: Friday, October 19, 2001 12:11 PM > To: Multiple recipients of list ORACLE-L > > > Hi Steve, > > yes, you're absolutely right. I've inhereted that system. The > shared_pool_size = 750M. I believe it's HUGE and oversized. The application > code is mostly based on literal SQL. The miss rate on the shared pool is > normally about 15%-20% with periodical peaks up to 50%. But the previous DBA > insist that we shouldn't decrease the size of shared pool as the miss rate > will be much higher. He also setup periodical flushing every 3 hours (I > assume he did it in order to prevent ORA-4031). > If I undestand the things right, deacresing of shared_pool_size will > decrease load on shared pool latch _but_ contention on the library latches > will be higher because of higher parse rate. Is it correct and what's your > advice in my case? > > Thanks in advance, > Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: Libal, Ivo 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: distribution of the sleeps on the library cache latches
Hi Ivo, we are on Linux RH, 8.1.7.0.1. I've tried cursor_sharing=force. Unfortunately, it causes ORA-600. Do 8.1.7.1(2) patches fix this problem? Regards, Ed > Hi Ed > have you tried cursor_sharing=force ? I dont know what version of oracle you > have and there are some known problems but maybe it can help you with > literal sql statements and then decreasing size of shared_pool. > Ivo > > > -Original Message- > Sent: Friday, October 19, 2001 12:11 PM > To: Multiple recipients of list ORACLE-L > > > Hi Steve, > > yes, you're absolutely right. I've inhereted that system. The > shared_pool_size = 750M. I believe it's HUGE and oversized. The application > code is mostly based on literal SQL. The miss rate on the shared pool is > normally about 15%-20% with periodical peaks up to 50%. But the previous DBA > insist that we shouldn't decrease the size of shared pool as the miss rate > will be much higher. He also setup periodical flushing every 3 hours (I > assume he did it in order to prevent ORA-4031). > If I undestand the things right, deacresing of shared_pool_size will > decrease load on shared pool latch _but_ contention on the library latches > will be higher because of higher parse rate. Is it correct and what's your > advice in my case? > > Thanks in advance, > Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: distribution of the sleeps on the library cache latches
Hi Steve, I had initiated the process of gradual migration to bind variables. It seems it will take a long time. Thanks for your detailed answer, I appreciate it Ed > Hi Ed, > > Of course, I'd suggest that the application be enhanced to use bind variables appropriately! ;-) > > In the interim, I would introduce a script such as 'keeper.sql' from the Ixora web site to keep all the reusable > material in the library cache so as to reduce the impact of the flushes. Once that is working as desired, I would > increase the flush frequency to an interval of say 1 hour or 30 minutes. The size of the library cache and thus shared > pool utilization will still grow over time, but more slowly. I would then reduce the shared pool size to approximately > the size that it grew to after 1 day of normal application usage. To then mitigate the risk of ORA-4031 errors I would > ensure that 'shared_pool_reserved_size' is allowed to default, but set '_shared_pool_reserved_min_alloc' to its minimum > value (which is 4000 or 5000, version dependent). An instance restart once a week would be good too if you can manage > that. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ - For DBAs > @ http://www.secularislam.org/call.htm - For Muslims > @ http://www.christianity.net.au/ - For all > > > -Original Message- > From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] > Sent: Friday, 19 October 2001 19:09 > To: Steve Adams; [EMAIL PROTECTED] > Subject: Re: distribution of the sleeps on the library cache latches > > > Hi Steve, > > yes, you're absolutely right. I've inhereted that system. The > shared_pool_size = 750M. I believe it's HUGE and oversized. The application > code is mostly based on literal SQL. The miss rate on the shared pool is > normally about 15%-20% with periodical peaks up to 50%. But the previous DBA > insist that we shouldn't decrease the size of shared pool as the miss rate > will be much higher. He also setup periodical flushing every 3 hours (I > assume he did it in order to prevent ORA-4031). > If I undestand the things right, deacresing of shared_pool_size will > decrease load on shared pool latch _but_ contention on the library latches > will be higher because of higher parse rate. Is it correct and what's your > advice in my case? > > Thanks in advance, > Ed > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: distribution of the sleeps on the library cache latches
Hi Ed, Of course, I'd suggest that the application be enhanced to use bind variables appropriately! ;-) In the interim, I would introduce a script such as 'keeper.sql' from the Ixora web site to keep all the reusable material in the library cache so as to reduce the impact of the flushes. Once that is working as desired, I would increase the flush frequency to an interval of say 1 hour or 30 minutes. The size of the library cache and thus shared pool utilization will still grow over time, but more slowly. I would then reduce the shared pool size to approximately the size that it grew to after 1 day of normal application usage. To then mitigate the risk of ORA-4031 errors I would ensure that 'shared_pool_reserved_size' is allowed to default, but set '_shared_pool_reserved_min_alloc' to its minimum value (which is 4000 or 5000, version dependent). An instance restart once a week would be good too if you can manage that. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 19:09 To: Steve Adams; [EMAIL PROTECTED] Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed > Hi Ed, > > I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I > suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use > of literal SQL. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ - For DBAs > @ http://www.secularislam.org/call.htm - For Muslims > @ http://www.christianity.net.au/ - For all > > > -Original Message- > From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] > Sent: Friday, 19 October 2001 18:02 > To: [EMAIL PROTECTED] > Cc: Steve Adams > Subject: Re: distribution of the sleeps on the library cache latches > > > Hi Steve, > > thanks for your reply. I'm thinking about twice increasing number of > library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on > them. > Also I would like to set _kgl_bucket_count = 8 according to output of your > script. Do you think it's a good idea in my case. > > NAME IMPACT SLEEP_RATEHOLDING LEVEL# > - -- -- -- -- > library cache 60333579.3 0.32% 1729452385 > shared pool19313269.2 1.40% 8265405 7 > cache buffers chains1950080.11 0.00% 629411 1 > row cache objects 738401.912 0.04%3369329 4 > session allocation 70758.0784 0.01% 144008 5 > cache buffer handles56104. 0.01% 71913 3 > redo allocation33494.1227 0.02% 215582 6 > cache buffers lru chain 12784.3859 0.00%198869 3 > checkpoint queue latch10980.4325 0.00% 52259 7 > latch wait list 9976.33016 0.04% 24412 9 > redo writing 4846.5256 0.01% 75484 5 > > Regards, > Ed > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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: distribution of the sleeps on the library cache latches
Hi Ed have you tried cursor_sharing=force ? I dont know what version of oracle you have and there are some known problems but maybe it can help you with literal sql statements and then decreasing size of shared_pool. Ivo -Original Message- Sent: Friday, October 19, 2001 12:11 PM To: Multiple recipients of list ORACLE-L Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed > Hi Ed, > > I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I > suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use > of literal SQL. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ - For DBAs > @ http://www.secularislam.org/call.htm - For Muslims > @ http://www.christianity.net.au/ - For all > > > -Original Message- > From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] > Sent: Friday, 19 October 2001 18:02 > To: [EMAIL PROTECTED] > Cc: Steve Adams > Subject: Re: distribution of the sleeps on the library cache latches > > > Hi Steve, > > thanks for your reply. I'm thinking about twice increasing number of > library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on > them. > Also I would like to set _kgl_bucket_count = 8 according to output of your > script. Do you think it's a good idea in my case. > > NAME IMPACT SLEEP_RATEHOLDING LEVEL# > - -- -- -- -- > library cache 60333579.3 0.32% 1729452385 > shared pool19313269.2 1.40% 8265405 7 > cache buffers chains1950080.11 0.00% 629411 1 > row cache objects 738401.912 0.04%3369329 4 > session allocation 70758.0784 0.01% 144008 5 > cache buffer handles56104. 0.01% 71913 3 > redo allocation33494.1227 0.02% 215582 6 > cache buffers lru chain 12784.3859 0.00%198869 3 > checkpoint queue latch10980.4325 0.00% 52259 7 > latch wait list 9976.33016 0.04% 24412 9 > redo writing 4846.5256 0.01% 75484 5 > > Regards, > Ed > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: Libal, Ivo 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: distribution of the sleeps on the library cache latches
Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed > Hi Ed, > > I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I > suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use > of literal SQL. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ - For DBAs > @ http://www.secularislam.org/call.htm - For Muslims > @ http://www.christianity.net.au/ - For all > > > -Original Message- > From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] > Sent: Friday, 19 October 2001 18:02 > To: [EMAIL PROTECTED] > Cc: Steve Adams > Subject: Re: distribution of the sleeps on the library cache latches > > > Hi Steve, > > thanks for your reply. I'm thinking about twice increasing number of > library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on > them. > Also I would like to set _kgl_bucket_count = 8 according to output of your > script. Do you think it's a good idea in my case. > > NAME IMPACT SLEEP_RATEHOLDING LEVEL# > - -- -- -- -- > library cache 60333579.3 0.32% 1729452385 > shared pool19313269.2 1.40% 8265405 7 > cache buffers chains1950080.11 0.00% 629411 1 > row cache objects 738401.912 0.04%3369329 4 > session allocation 70758.0784 0.01% 144008 5 > cache buffer handles56104. 0.01% 71913 3 > redo allocation33494.1227 0.02% 215582 6 > cache buffers lru chain 12784.3859 0.00%198869 3 > checkpoint queue latch10980.4325 0.00% 52259 7 > latch wait list 9976.33016 0.04% 24412 9 > redo writing 4846.5256 0.01% 75484 5 > > Regards, > Ed > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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: distribution of the sleeps on the library cache latches
Hi Ed, I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use of literal SQL. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 18:02 To: [EMAIL PROTECTED] Cc: Steve Adams Hi Steve, thanks for your reply. I'm thinking about twice increasing number of library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on them. Also I would like to set _kgl_bucket_count = 8 according to output of your script. Do you think it's a good idea in my case. NAME IMPACT SLEEP_RATEHOLDING LEVEL# - -- -- -- -- library cache 60333579.3 0.32% 1729452385 shared pool19313269.2 1.40% 8265405 7 cache buffers chains1950080.11 0.00% 629411 1 row cache objects 738401.912 0.04%3369329 4 session allocation 70758.0784 0.01% 144008 5 cache buffer handles56104. 0.01% 71913 3 redo allocation33494.1227 0.02% 215582 6 cache buffers lru chain 12784.3859 0.00%198869 3 checkpoint queue latch10980.4325 0.00% 52259 7 latch wait list 9976.33016 0.04% 24412 9 redo writing 4846.5256 0.01% 75484 5 Regards, Ed > Hi Ed, > > My scripts use the rule of thumb you mention, but it is not a black and white issue. I would characterise your > contention here as having a few hot spots, but a general library cache wide problem as well. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ > @ http://www.christianity.net.au/ > > -Original Message- > Sent: Thursday, 18 October 2001 9:25 > To: Multiple recipients of list ORACLE-L > > > Hi List, > > what is the criteria of uneven distribution of sleeps on the library cache latches? Is there a rule > of thumb to determine uneven distribution? For example, no of sleeps on a latch is twice bigger than > average no of the sleeps on the others latches? Is it correct? > > Do you estimate the following distribution as uneven? > > NAME GETS MISSES SLEEPS SLEEP1 SLEEP2 SLEEP3 > -- -- -- -- -- -- -- > library cache 806881977 103462783105912 3358661020725 217664 > library cache 464142903 39375581318015 154644 422509 94864 > library cache 283177601 19916481127057 120761 368308 80551 > library cache 839438890 79674971478426 195907 479182 95918 > library cache 978851575 131045961614737 213383 527238 104408 > library cache 279613950 1453222 759127 77395 255984 51334 > library cache 834477709 116230003101181 4051021058753 168282 > library cache 260953580 1434471 825151 93505 278275 52608 > library cache 470252271 52629331484982 162567 489911 103336 > library cache 501042073 51344671595443 180043 507939 119648 > library cache 1265644171 250131692374937 371608 754426 152126 > > > TIA, > Ed > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Steve Adams > 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: Steve Adams 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: distribution of the sleeps on the library cache latches
Hi Steve, thanks for your reply. I'm thinking about twice increasing number of library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on them. Also I would like to set _kgl_bucket_count = 8 according to output of your script. Do you think it's a good idea in my case. NAME IMPACT SLEEP_RATEHOLDING LEVEL# - -- -- -- -- library cache 60333579.3 0.32% 1729452385 shared pool19313269.2 1.40% 8265405 7 cache buffers chains1950080.11 0.00% 629411 1 row cache objects 738401.912 0.04%3369329 4 session allocation 70758.0784 0.01% 144008 5 cache buffer handles56104. 0.01% 71913 3 redo allocation33494.1227 0.02% 215582 6 cache buffers lru chain 12784.3859 0.00%198869 3 checkpoint queue latch10980.4325 0.00% 52259 7 latch wait list 9976.33016 0.04% 24412 9 redo writing 4846.5256 0.01% 75484 5 Regards, Ed > Hi Ed, > > My scripts use the rule of thumb you mention, but it is not a black and white issue. I would characterise your > contention here as having a few hot spots, but a general library cache wide problem as well. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ > @ http://www.christianity.net.au/ > > -Original Message- > Sent: Thursday, 18 October 2001 9:25 > To: Multiple recipients of list ORACLE-L > > > Hi List, > > what is the criteria of uneven distribution of sleeps on the library cache latches? Is there a rule > of thumb to determine uneven distribution? For example, no of sleeps on a latch is twice bigger than > average no of the sleeps on the others latches? Is it correct? > > Do you estimate the following distribution as uneven? > > NAME GETS MISSES SLEEPS SLEEP1 SLEEP2 SLEEP3 > -- -- -- -- -- -- -- > library cache 806881977 103462783105912 3358661020725 217664 > library cache 464142903 39375581318015 154644 422509 94864 > library cache 283177601 19916481127057 120761 368308 80551 > library cache 839438890 79674971478426 195907 479182 95918 > library cache 978851575 131045961614737 213383 527238 104408 > library cache 279613950 1453222 759127 77395 255984 51334 > library cache 834477709 116230003101181 4051021058753 168282 > library cache 260953580 1434471 825151 93505 278275 52608 > library cache 470252271 52629331484982 162567 489911 103336 > library cache 501042073 51344671595443 180043 507939 119648 > library cache 1265644171 250131692374937 371608 754426 152126 > > > TIA, > Ed > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Steve Adams > 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: Edward Shevtsov 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: distribution of the sleeps on the library cache latches
Hi Bing, I meant the case when the load (number of requests) is much higher on a particular latch comparing to over latches Regards, Ed > When you say uneven, does it mean fragmented? I am learning this too. > > > Bing > > > -Original Message- > Sent: Wednesday, October 17, 2001 4:25 PM > To: Multiple recipients of list ORACLE-L > > > Hi List, > > what is the criteria of uneven distribution of sleeps on the library cache > latches? Is there a rule > of thumb to determine uneven distribution? For example, no of sleeps on a > latch is twice bigger than > average no of the sleeps on the others latches? Is it correct? > > Do you estimate the following distribution as uneven? > > NAME GETS MISSES SLEEPS SLEEP1 SLEEP2 > SLEEP3 > -- -- -- -- -- -- > -- > library cache 806881977 103462783105912 3358661020725 > 217664 > library cache 464142903 39375581318015 154644 422509 > 94864 > library cache 283177601 19916481127057 120761 368308 > 80551 > library cache 839438890 79674971478426 195907 479182 > 95918 > library cache 978851575 131045961614737 213383 527238 > 104408 > library cache 279613950 1453222 759127 77395 255984 > 51334 > library cache 834477709 116230003101181 4051021058753 > 168282 > library cache 260953580 1434471 825151 93505 278275 > 52608 > library cache 470252271 52629331484982 162567 489911 > 103336 > library cache 501042073 51344671595443 180043 507939 > 119648 > library cache 1265644171 250131692374937 371608 754426 > 152126 > > > TIA, > Ed > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Edward Shevtsov > 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: Wong, Bing > 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: Edward Shevtsov 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).
