RE: High consistent gets , 10046
Thank you, Jonathan, No need for apology, all you input is very valuable. Note about update/select for update just great, didn't realize this. About chache chains. Taking real-time snapshots revealed breathtaking FILE# DBABLK COUNT(*) -- -- -- 9 38644 2144 9 77084 10 9 68036 7 I remember, 9i's 6 block target length has been discussed recently. This is an index on weblogic's JMS store table. Really hot spot. Have a good day Vadim -Original Message- Sent: Thursday, March 13, 2003 3:49 PM To: Multiple recipients of list ORACLE-L Vadim, Apologies, I answered the question you didn't ask - viz why does it take so long, rather than the 'what are the CR gets'. Your second suggestion is the correct one. It seems unreasonable, but when you do the select for update, Oracle seems to go through a load of read- consistency work for the block to roll back the changes made by other transactions. The excess CR gets are accesses to the UNDO blocks need to build the CR image. Strangely, if you just slam in the 'update', rather than 'select for update' this phenomenon does not occur. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 13 March 2003 18:54 Thank you, Jonathan, I'll continue looking for my options to cool down the hot spots. Not sure if I can go for partitioning since Oracle charges $$$. Is it correct that oracle counts looking through the chain for the correct copy as many CR? Or the reason for these extra CR is access to undo segments in attemt to reconstruct CR block aged out from cache? Thanks Vadim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Gorbounov,Vadim 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).
High consistent gets , 10046
Dear listers, I'm hunting for top LIO consumers to give a relief to our DB cpu and found something that looks interesting. Many plain good queries show up way to high cr when executed in concurrent environment (50 threads) while perform as predicted when executed from SQL*PLUS. The example below is select by primary key, PK index height is 1. Trace taken in concurrent env shows cr=152 = PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65 tim=1022957016971691 hv=941708176 ad='61f780e8' SELECT samp_ver FROM sub_svc WHERE sub_svc_id = :b1 FOR UPDATE END OF STMT PARSE #136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679 EXEC #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087 FETCH #136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208 When tested from SQL*plus prompt (server is idle), is falls to resonable cr=3 = PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410 hv=3412082965 ad='6344f6cc' SELECT samp_ver FROM sub_svc WHERE sub_svc_id = :b1 FOR UPDATE END OF STMT PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397 EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517 FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612 = As you may see, the different is quite essential. Does anybody have an idea why is so? This is 9.2.0.2 on Solaris TIA Vadim G -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gorbounov,Vadim 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: High consistent gets , 10046
I can think of three reasons: 1) You are binding with the wrong datatype and you are getting a full table access for the SELECT and then the rowid is remembered for the FOR UPDATE (results in 1 current get). 2) You are implicitly using array fetch in sqlplus, so the number of cr gets will be lower but given the fact that current gets is 1 in both cases, you can ignore this. 3) Different bind variable values. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:24 PM Dear listers, I'm hunting for top LIO consumers to give a relief to our DB cpu and found something that looks interesting. Many plain good queries show up way to high cr when executed in concurrent environment (50 threads) while perform as predicted when executed from SQL*PLUS. The example below is select by primary key, PK index height is 1. Trace taken in concurrent env shows cr=152 = PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65 tim=1022957016971691 hv=941708176 ad='61f780e8' SELECT samp_ver FROM sub_svc WHERE sub_svc_id = :b1 FOR UPDATE END OF STMT PARSE #136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679 EXEC #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087 FETCH #136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208 When tested from SQL*plus prompt (server is idle), is falls to resonable cr=3 = PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410 hv=3412082965 ad='6344f6cc' SELECT samp_ver FROM sub_svc WHERE sub_svc_id = :b1 FOR UPDATE END OF STMT PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397 EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517 FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612 = As you may see, the different is quite essential. Does anybody have an idea why is so? This is 9.2.0.2 on Solaris TIA Vadim G -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gorbounov,Vadim 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: Anjo Kolk 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: High consistent gets , 10046
If you can check it in real time, you will probably find that you have a very large number of CR copies of the few blocks that are the focus of the concurrent activity. The excess time is likely to be down to a mixture of CPU as Oracle trawls through the chain looking for the correct copy, and latch contention because of the time the latch has to be held whilst the correct copy is being found. 'select for update ...' seems to be particularly prone to this problem - especially if you have an over large db_cache_size, that allows for lots of blocks in state 'FREE'. (even a reasonably size buffer can produce this effect if there is a process elsewhere which is dropping or truncating objects on a regular basis). You may be able to reduce the impact of the problem by spreading out the rows that need to be updated - e.g. by increasing the number of freelists, or hash partitioning the table. If this is a relatively small, static sized, table moving it to a single table hash cluster may help. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 13 March 2003 15:24 Dear listers, I'm hunting for top LIO consumers to give a relief to our DB cpu and found something that looks interesting. Many plain good queries show up way to high cr when executed in concurrent environment (50 threads) while perform as predicted when executed from SQL*PLUS. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: High consistent gets , 10046
Thank you, Jonathan, I'll continue looking for my options to cool down the hot spots. Not sure if I can go for partitioning since Oracle charges $$$. Is it correct that oracle counts looking through the chain for the correct copy as many CR? Or the reason for these extra CR is access to undo segments in attemt to reconstruct CR block aged out from cache? Thanks Vadim -Original Message- Sent: Thursday, March 13, 2003 12:19 PM To: Multiple recipients of list ORACLE-L If you can check it in real time, you will probably find that you have a very large number of CR copies of the few blocks that are the focus of the concurrent activity. The excess time is likely to be down to a mixture of CPU as Oracle trawls through the chain looking for the correct copy, and latch contention because of the time the latch has to be held whilst the correct copy is being found. 'select for update ...' seems to be particularly prone to this problem - especially if you have an over large db_cache_size, that allows for lots of blocks in state 'FREE'. (even a reasonably size buffer can produce this effect if there is a process elsewhere which is dropping or truncating objects on a regular basis). You may be able to reduce the impact of the problem by spreading out the rows that need to be updated - e.g. by increasing the number of freelists, or hash partitioning the table. If this is a relatively small, static sized, table moving it to a single table hash cluster may help. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 13 March 2003 15:24 Dear listers, I'm hunting for top LIO consumers to give a relief to our DB cpu and found something that looks interesting. Many plain good queries show up way to high cr when executed in concurrent environment (50 threads) while perform as predicted when executed from SQL*PLUS. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Gorbounov,Vadim 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: High consistent gets , 10046
Thank you, Anjo. I want to provide more information about this case: - In both cases binding is inside PL/SQL block, bind variable type is correct. this must eliminate reasons 1 and 2 - different bind variables - yes, almost for sure they are different, but this is PK, must make no difference. - contention for cache chain latches is high I'm looking at Cary's article Why you should focus on LIO... Looks like this count may only be caused by undo lookups. It still looks too high, since hot blocks must not age out too fast unless Oracle is too aggressive is keeping number of copies low. Not sure how Oracle counts access to different block versions im memory at 10046, assuming as one access. I'm heading to collect more information on cache chain latches and block counts in x$bh and send it out . Thanks again, Vadim -Original Message- Sent: Thursday, March 13, 2003 12:04 PM To: Multiple recipients of list ORACLE-L I can think of three reasons: 1) You are binding with the wrong datatype and you are getting a full table access for the SELECT and then the rowid is remembered for the FOR UPDATE (results in 1 current get). 2) You are implicitly using array fetch in sqlplus, so the number of cr gets will be lower but given the fact that current gets is 1 in both cases, you can ignore this. 3) Different bind variable values. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:24 PM Dear listers, I'm hunting for top LIO consumers to give a relief to our DB cpu and found something that looks interesting. Many plain good queries show up way to high cr when executed in concurrent environment (50 threads) while perform as predicted when executed from SQL*PLUS. The example below is select by primary key, PK index height is 1. Trace taken in concurrent env shows cr=152 = PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65 tim=1022957016971691 hv=941708176 ad='61f780e8' SELECT samp_ver FROM sub_svc WHERE sub_svc_id = :b1 FOR UPDATE END OF STMT PARSE #136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679 EXEC #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087 FETCH #136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208 When tested from SQL*plus prompt (server is idle), is falls to resonable cr=3 = PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410 hv=3412082965 ad='6344f6cc' SELECT samp_ver FROM sub_svc WHERE sub_svc_id = :b1 FOR UPDATE END OF STMT PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397 EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517 FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612 = As you may see, the different is quite essential. Does anybody have an idea why is so? This is 9.2.0.2 on Solaris TIA Vadim G -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gorbounov,Vadim 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: Anjo Kolk 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: Gorbounov,Vadim 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
Re: High consistent gets , 10046
Vadim, Apologies, I answered the question you didn't ask - viz why does it take so long, rather than the 'what are the CR gets'. Your second suggestion is the correct one. It seems unreasonable, but when you do the select for update, Oracle seems to go through a load of read- consistency work for the block to roll back the changes made by other transactions. The excess CR gets are accesses to the UNDO blocks need to build the CR image. Strangely, if you just slam in the 'update', rather than 'select for update' this phenomenon does not occur. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 13 March 2003 18:54 Thank you, Jonathan, I'll continue looking for my options to cool down the hot spots. Not sure if I can go for partitioning since Oracle charges $$$. Is it correct that oracle counts looking through the chain for the correct copy as many CR? Or the reason for these extra CR is access to undo segments in attemt to reconstruct CR block aged out from cache? Thanks Vadim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
