RE: db block gets /consistent gets
Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version). A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 snapshot too old. As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still reading, I hope it helped! -Mark -Original Message- From: Sultan Syed [mailto:[EMAIL PROTECTED] Sent: Thu 12/18/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Subject:db block gets /consistent gets Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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: db block gets /consistent gets
Mark I know you from the metalink. Thank you for your detailed explanation. Syed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 11:24 AM Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version). A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 snapshot too old. As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still reading, I hope it helped! -Mark -Original Message- From: Sultan Syed [mailto:[EMAIL PROTECTED] Sent: Thu 12/18/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Subject: db block gets /consistent gets Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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: Sultan Syed 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: db block gets /consistent gets
Mark, That is perhaps the most concise and easy to understand explanation of current mode vs. consistent mode that I have yet seen. Thanks for posting it. Jared Bobak, Mark [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2003 11:24 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: db block gets /consistent gets Syed, Oracle accesses blocks in one of two modes, current or consistent. A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version). A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 snapshot too old. As to latching, and how it relates, well, consider that the block buffers are in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that point to the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuning SQL to use more efficient access paths, you can get the same answer to the same query but do less consistent gets. This not only consumes less CPU, it also can significantly reduce latching which reduces serialization and makes your system more scalable. Well, that turned out longer than I planned. If you're still reading, I hope it helped! -Mark -Original Message- Sent: Thu 12/18/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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: db block gets /consistent gets
Thanks. I was having a bout of insomnia last night, so I'm just glad it came out sounding coherent! ;-) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Thursday, December 18, 2003 12:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: db block gets /consistent getsMark, That is perhaps the most concise and easy to understand explanation of current mode vs. consistent mode that I have yet seen. Thanks for posting it. Jared "Bobak, Mark" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/17/2003 11:24 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: db block gets /consistent getsSyed,Oracle accesses blocks in one of two modes, current or consistent.A 'db block get' is a current mode get. That is, it's the most up-to-datecopy of the data in that block, as it is right now, or currently. Therecan only be one current copy of a block in the buffer cache at any time.Db block gets generally are used when DML changes data in the database.In that case, row-level locks are implicitly taken on the updated rows.There is also at least one well-known case where a select statement doesa db block get, and does not take a lock. That is, when it does a fulltable scan or fast full index scan, Oracle will read the segment headerin current mode (multiple times, the number varies based on Oracle version).A 'consistent get' is when Oracle gets the data in a block which is consistentwith a given point in time, or SCN. The consistent get is at the heart ofOracle's read consistency mechanism. When blocks are fetched in order tosatisfy a query result set, they are fetched in consistent mode. If noblock in the buffer cache is consistent to the correct point in time, Oraclewill (attempt to) reconstruct that block using the information in the rollbacksegments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".As to latching, and how it relates, well, consider that the block buffersare in the SGA, which is shared memory. To avoid corruption, latches are used to serialize access to many linked lists and data structures that pointto the buffers as well as the buffers themselves. It is safe to say that each consistent get introduces serialization to the system, and by tuningSQL to use more efficient access paths, you can get the same answer to thesame query but do less consistent gets. This not only consumes less CPU,it also can significantly reduce latching which reduces serialization andmakes your system more scalable.Well, that turned out longer than I planned. If you're still reading,I hope it helped!-Mark-Original Message-Sent: Thu 12/18/2003 1:39 AMTo: Multiple recipients of list ORACLE-LCc: Hi list,What is db block gets and consistent gets.?How can I reduce consistent gets ?Ask Tom says each consistent gets is latch, how it could be?Thanks in advanceSyed-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bobak, MarkINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California-- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
db block gets /consistent gets
Hi list, What is db block gets and consistent gets.? How can I reduce consistent gets ? Ask Tom says each consistent gets is latch, how it could be? Thanks in advance Syed
Re: consistent gets
Hi consistent gets are when the SQL needs the data in consistent mode i.e not the current data. SELECT statements contribute to consistent gets (read from the RBS). DML normally contribute to db block gets, but say an UPDATE based on a search criterion -- will contribute to consistent gets. Thanks and Regards Pradhan - On Wed, 08 Oct 2003 Sultan Syed wrote : Hi, What does the meaning for this consistent gets. Some time my statement return more consistent gets in statistics. How I can reduce this Consistent gets. Thanks in advance.
RE: consistent gets
If you want to reduce your consistent gets, you can reduce your sga size -Original Message-From: Sultan Syed [mailto:[EMAIL PROTECTED]Sent: 08 October 2003 14:14To: Multiple recipients of list ORACLE-LSubject: consistent gets Hi, What does the meaning for this consistent gets. Some time my statement returnmore consistent gets in statistics. How I can reduce this Consistent gets. Thanks in advance.
Re: consistent gets
Hi, Consistent gets means the blocks oracle have to visit , it means the real cost of the SQL.(consisteng gets + db block gets) Reduce SGA size has nothing to do with Consistent gets. TO reduce consistent gets, only Tune the SQL or create proper index etc can help. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 08, 2003 3:34 PM If you want to reduce your consistent gets, you can reduce your sga size -Original Message- Sent: 08 October 2003 14:14 To: Multiple recipients of list ORACLE-L Hi, What does the meaning for this consistent gets. Some time my statement return more consistent gets in statistics. How I can reduce this Consistent gets. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: consistent gets
yeah rite, i just test u -Original Message- Sent: 08 October 2003 16:29 To: Multiple recipients of list ORACLE-L Hi, Consistent gets means the blocks oracle have to visit , it means the real cost of the SQL.(consisteng gets + db block gets) Reduce SGA size has nothing to do with Consistent gets. TO reduce consistent gets, only Tune the SQL or create proper index etc can help. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 08, 2003 3:34 PM If you want to reduce your consistent gets, you can reduce your sga size -Original Message- Sent: 08 October 2003 14:14 To: Multiple recipients of list ORACLE-L Hi, What does the meaning for this consistent gets. Some time my statement return more consistent gets in statistics. How I can reduce this Consistent gets. Thanks in advance. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao 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: Sinardy Xing 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).
consistent gets
Hi, What does the meaning for this consistent gets. Some time my statement returnmore consistent gets in statistics. How I can reduce this Consistent gets. Thanks in advance.
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).
Re: Re: more consistent gets, but more quickly?
When Oracle 'expects' to visit a buffer more than once in a single call, it will hold the cache buffers chains latch long enough to create a pin (in this case a memory structure that associates the session with the buffer) and link it into the linked list of current users (x$bh.usprev, usnxt) of the buffer. On subsequent accesses to the block, Oracle need not grab the latch and search the bucket, instead it can jump to the block by way of the pin which will definitely be there as a pinned block may not be flushed from the buffer. Visits which take this short-cut are recorded under the 'buffer is pinned count' statistic. So it is a logical I/O, but using a shorter, often cheaper, access path. Typically it will be index leaf blocks that show most pinning, as they tend to be revisited during range scans. In your case, I assumed that the rebuilt table would result in there being more usable row entries per leaf block than there had been, so more jumps back and forth from index to table each time a leaf was pinned - hence more pins, fewer gets. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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] Date: 29 January 2003 06:42 Jonathan Lewis, Can you interpret more about this statistics?How does this affect the sql running time? And how did you think about this statistics that is seldom used? The following is the test result: --sql1: 00:00:01.58 00:00:01.59 NAME VALUE VALUE --- -- - CPU used by this session 160 161 CPU used when call started 160 161 buffer is not pinned count 41612 41604 buffer is pinned count 1685183 1685183 consistent gets 43911 43907 no work - consistent read gets 43893 43889 session logical reads43914 43910 Elapsed: --sql2: 00:00:01.69 00:00:01.71 NAME VALUE VALUE -- -- CPU used by this session170 171 CPU used when call started 170 171 buffer is not pinned count19889 19889 buffer is pinned count 1706898 1706898 consistent gets 22192 22192 no work - consistent read gets22174 22174 session logical reads 22195 22195 -- 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: Re: more consistent gets, but more quickly?
Jonathan Lewis,
Can you interpret more about this statistics?How does this affect the
sql running time? And how did you think about this statistics that is seldom used?
The following is the test result:
--sql1:
00:00:01.58
00:00:01.59
NAME VALUE VALUE
--- -- -
CPU used by this session 160 161
CPU used when call started 160 161
buffer is not pinned count 41612 41604
buffer is pinned count 1685183 1685183
consistent gets 43911 43907
no work - consistent read gets 43893 43889
session logical reads43914 43910
Elapsed:
--sql2: 00:00:01.69
00:00:01.71
NAME VALUE VALUE
-- --
CPU used by this session170 171
CPU used when call started 170 171
buffer is not pinned count19889 19889
buffer is pinned count 1706898 1706898
consistent gets 22192 22192
no work - consistent read gets22174 22174
session logical reads 22195 22195
difference value between sql1 and sql2:
buffer is not pinned count sql2-sql1:-21723
buffer is pinned count: sql2-sql1:21715
sql1: to query from the non-ordered ordered table UCM_USERCOMMENT_MAINTAIN_old; less
consistent get one ,slower one.
sql2: to query from the ordered table UCM_USERCOMMENT_MAINTAIN, more consistent gets
one ,faster one.
[oracle@app6 oracle]$ cat mystat.sql
col name format a80
set timing on
set line 200
select a.name,b.value from v$statname a,v$mystat b
where a.statistic#=b.statistic#
and a.name in ('CPU used by this session','CPU used when call started','buffer is not
pinned count','buffer is pinned count','consistent gets','no work - consistent read
gets','session logical reads')
order by a.name
/
--I only gathered these statistics because i found only these statistics is different
from different tests.
--buffer is not pinned count 72 Number of times a buffer was free when visited. Useful
--only for internal debugging purposes.
--buffer is pinned count 72 Number of times a buffer was pinned when visited. Useful
--only for internal debugging purposes.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)
=== 2003-01-27 08:49:00 ,you wrote£º===
Repeat the tests, but take a snapshot of v$sesstat
for the session on each test. I would guess that you
will find that there is a more than balancing difference
in the statistic buffer is pinned count which also
records accesses to buffered block, but uses a
different buffer access mechanism.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March
USA_(FL)_May
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
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]
Date: 27 January 2003 15:56
As you see, the first sql generated 43629 consistent gets and the
second sql 22083 consistent gets, this is ok, how ever, the first
take 1.63 second and the second take 1.70 second.This seems strange,
right? Since in most case, higher consistent gets means longer time.
There is no one else running on this server, And I also tested with
event 10046 with no wait event.I tested for several times, with the
same result.
Can someone help me understand it?
Thanks very much.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org(China Oracle User Group)
--
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
more consistent gets, but more quickly?
hi, friends: I hit some strange performance problem on my 9.2.0.2 on redhat linux. I want to show developer/manager why delete data for archiving history data is not a good idear, and I did a test: There is some big table in our app, and currently we use cron to delete rows everyday(delete rows before 15 days). I exported it from the production and imported it to test env(with same hardware), the imported table named UCM_USERCOMMENT_MAINTAIN_old, later I created a new table UCM_USERCOMMENT_MAINTAIN as select * from UCM_USERCOMMENT_MAINTAIN_old order by ucm_create_dtm; And I want to tell developers the factor of CLUSTERING_FACTOR: SQL select table_name,index_name,CLUSTERING_FACTOR from user_indexes where table_name like 'UCM%'; TABLE_NAME INDEX_NAME CLUSTERING_FACTOR -- -- - UCM_USERCOMMENT_MAINTAIN IDX_UCM4 22165 UCM_USERCOMMENT_MAINTAIN_OLD IDX_UCM5 49681 And I am sure the following SQL: select count(*) from UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN ) WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; To query from the UCM_USERCOMMENT_MAINTAIN should be faster than to query from UCM_USERCOMMENT_MAINTAIN_old, but the result is surprising: SQL select count(*) from UCM_USERCOMMENT_MAINTAIN_old 2 WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; COUNT(*) -- 350399 Elapsed: 00:00:01.63 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11) 10 SORT (AGGREGATE) 21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN_OLD' (Cost=472 Card=10727 Bytes=117997) 32 INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24 Card=7724) Statistics -- 0 recursive calls 0 db block gets 43629 consistent gets 0 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select count(*) from UCM_USERCOMMENT_MAINTAIN 2 WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; COUNT(*) -- 350399 Elapsed: 00:00:01.70 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11) 10 SORT (AGGREGATE) 21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN' (Cost=224 Card=10916 Bytes=120076) 32 INDEX (RANGE SCAN) OF 'IDX_UCM4' (NON-UNIQUE) (Cost=24 Card=7860) Statistics -- 0 recursive calls 0 db block gets 22083 consistent gets 1 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed As you see, the first sql generated 43629 consistent gets and the second sql 22083 consistent gets, this is ok, how ever, the first take 1.63 second and the second take 1.70 second.This seems strange, right? Since in most case, higher consistent gets means longer time. There is no one else running on this server, And I also tested with event 10046 with no wait event.I tested for several times, with the same result. Can someone help me understand it? Thanks very much. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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: more consistent gets, but more quickly?
Repeat the tests, but take a snapshot of v$sesstat for the session on each test. I would guess that you will find that there is a more than balancing difference in the statistic buffer is pinned count which also records accesses to buffered block, but uses a different buffer access mechanism. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March USA_(FL)_May Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) 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] Date: 27 January 2003 15:56 As you see, the first sql generated 43629 consistent gets and the second sql 22083 consistent gets, this is ok, how ever, the first take 1.63 second and the second take 1.70 second.This seems strange, right? Since in most case, higher consistent gets means longer time. There is no one else running on this server, And I also tested with event 10046 with no wait event.I tested for several times, with the same result. Can someone help me understand it? Thanks very much. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) -- 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: more consistent gets, but more quickly?
There is no fixed cost for a logical I/O. The cost of a LIO will change depending on many factors. Anjo. On Monday 27 January 2003 06:59, chao_ping wrote: hi, friends: I hit some strange performance problem on my 9.2.0.2 on redhat linux. I want to show developer/manager why delete data for archiving history data is not a good idear, and I did a test: There is some big table in our app, and currently we use cron to delete rows everyday(delete rows before 15 days). I exported it from the production and imported it to test env(with same hardware), the imported table named UCM_USERCOMMENT_MAINTAIN_old, later I created a new table UCM_USERCOMMENT_MAINTAIN as select * from UCM_USERCOMMENT_MAINTAIN_old order by ucm_create_dtm; And I want to tell developers the factor of CLUSTERING_FACTOR: SQL select table_name,index_name,CLUSTERING_FACTOR from user_indexes where table_name like 'UCM%'; TABLE_NAME INDEX_NAME CLUSTERING_FACTOR -- -- - UCM_USERCOMMENT_MAINTAIN IDX_UCM4 22165 UCM_USERCOMMENT_MAINTAIN_OLD IDX_UCM5 49681 And I am sure the following SQL: select count(*) from UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN ) WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; To query from the UCM_USERCOMMENT_MAINTAIN should be faster than to query from UCM_USERCOMMENT_MAINTAIN_old, but the result is surprising: SQL select count(*) from UCM_USERCOMMENT_MAINTAIN_old 2 WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; COUNT(*) -- 350399 Elapsed: 00:00:01.63 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11) 10 SORT (AGGREGATE) 21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN_OLD' (Cost=472 Card=10727 Bytes=117997) 32 INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24 Card=7724) Statistics -- 0 recursive calls 0 db block gets 43629 consistent gets 0 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select count(*) from UCM_USERCOMMENT_MAINTAIN 2 WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; COUNT(*) -- 350399 Elapsed: 00:00:01.70 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11) 10 SORT (AGGREGATE) 21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN' (Cost=224 Card=10916 Bytes=120076) 32 INDEX (RANGE SCAN) OF 'IDX_UCM4' (NON-UNIQUE) (Cost=24 Card=7860) Statistics -- 0 recursive calls 0 db block gets 22083 consistent gets 1 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed As you see, the first sql generated 43629 consistent gets and the second sql 22083 consistent gets, this is ok, how ever, the first take 1.63 second and the second take 1.70 second.This seems strange, right? Since in most case, higher consistent gets means longer time. There is no one else running on this server, And I also tested with event 10046 with no wait event.I tested for several times, with the same result. Can someone help me understand it? Thanks very much. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) -- Anjo Kolk http://www.oraperf.com -- 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: Re: more consistent gets, but more quickly?
Jonathan Lewis,
You are really oracle expert!Can you interpret more about this
statistics? And how did you think about this statistics that is seldom used?
The following is the test result:
--sql1:
00:00:01.58
00:00:01.59
NAME VALUE VALUE
--- -- -
CPU used by this session 160 161
CPU used when call started 160 161
buffer is not pinned count 41612 41604
buffer is pinned count 1685183 1685183
consistent gets 43911 43907
no work - consistent read gets 43893 43889
session logical reads43914 43910
Elapsed:
--sql2: 00:00:01.69
00:00:01.71
NAME VALUE VALUE
-- --
CPU used by this session170 171
CPU used when call started 170 171
buffer is not pinned count19889 19889
buffer is pinned count 1706898 1706898
consistent gets 22192 22192
no work - consistent read gets22174 22174
session logical reads 22195 22195
difference value between sql1 and sql2:
buffer is not pinned count sql2-sql1:-21723
buffer is pinned count: sql2-sql1:21715
sql1: to query from the non-ordered ordered table UCM_USERCOMMENT_MAINTAIN_old; less
consistent get one ,slower one.
sql2: to query from the ordered table UCM_USERCOMMENT_MAINTAIN, more consistent gets
one ,faster one.
[oracle@app6 oracle]$ cat mystat.sql
col name format a80
set timing on
set line 200
select a.name,b.value from v$statname a,v$mystat b
where a.statistic#=b.statistic#
and a.name in ('CPU used by this session','CPU used when call started','buffer is not
pinned count','buffer is pinned count','consistent gets','no work - consistent read
gets','session logical reads')
order by a.name
/
--I only gathered these statistics because i found only these statistics is different
from different tests.
--buffer is not pinned count 72 Number of times a buffer was free when visited. Useful
--only for internal debugging purposes.
--buffer is pinned count 72 Number of times a buffer was pinned when visited. Useful
--only for internal debugging purposes.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)
=== 2003-01-27 08:49:00 ,you wrote£º===
Repeat the tests, but take a snapshot of v$sesstat
for the session on each test. I would guess that you
will find that there is a more than balancing difference
in the statistic buffer is pinned count which also
records accesses to buffered block, but uses a
different buffer access mechanism.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March
USA_(FL)_May
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
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]
Date: 27 January 2003 15:56
As you see, the first sql generated 43629 consistent gets and the
second sql 22083 consistent gets, this is ok, how ever, the first
take 1.63 second and the second take 1.70 second.This seems strange,
right? Since in most case, higher consistent gets means longer time.
There is no one else running on this server, And I also tested with
event 10046 with no wait event.I tested for several times, with the
same result.
Can someone help me understand it?
Thanks very much.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org(China Oracle User Group)
--
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
RE: more consistent gets, but more quickly?
The stats output for your sql shows no physical reads. This means either the whole table is cached or simply since you ran the test many times you got all the blocks you're interested in cached. Since all the needed blocks are cached, I do not think fetching the rows using the rowid would be any different if the rows are in one cached block or many cached blocks. Waleed -Original Message- Sent: Monday, January 27, 2003 9:59 AM To: Multiple recipients of list ORACLE-L hi, friends: I hit some strange performance problem on my 9.2.0.2 on redhat linux. I want to show developer/manager why delete data for archiving history data is not a good idear, and I did a test: There is some big table in our app, and currently we use cron to delete rows everyday(delete rows before 15 days). I exported it from the production and imported it to test env(with same hardware), the imported table named UCM_USERCOMMENT_MAINTAIN_old, later I created a new table UCM_USERCOMMENT_MAINTAIN as select * from UCM_USERCOMMENT_MAINTAIN_old order by ucm_create_dtm; And I want to tell developers the factor of CLUSTERING_FACTOR: SQL select table_name,index_name,CLUSTERING_FACTOR from user_indexes where table_name like 'UCM%'; TABLE_NAME INDEX_NAME CLUSTERING_FACTOR -- -- - UCM_USERCOMMENT_MAINTAIN IDX_UCM4 22165 UCM_USERCOMMENT_MAINTAIN_OLD IDX_UCM5 49681 And I am sure the following SQL: select count(*) from UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN ) WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; To query from the UCM_USERCOMMENT_MAINTAIN should be faster than to query from UCM_USERCOMMENT_MAINTAIN_old, but the result is surprising: SQL select count(*) from UCM_USERCOMMENT_MAINTAIN_old 2 WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; COUNT(*) -- 350399 Elapsed: 00:00:01.63 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11) 10 SORT (AGGREGATE) 21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN_OLD' (Cost=472 Card=10727 Bytes=117997) 32 INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24 Card=7724) Statistics -- 0 recursive calls 0 db block gets 43629 consistent gets 0 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select count(*) from UCM_USERCOMMENT_MAINTAIN 2 WHERE ucm_create_dtm(sysdate-(2/24)) AND ucm_notify_email=1 AND ucm_notify_sms=0 ; COUNT(*) -- 350399 Elapsed: 00:00:01.70 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11) 10 SORT (AGGREGATE) 21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN' (Cost=224 Card=10916 Bytes=120076) 32 INDEX (RANGE SCAN) OF 'IDX_UCM4' (NON-UNIQUE) (Cost=24 Card=7860) Statistics -- 0 recursive calls 0 db block gets 22083 consistent gets 1 physical reads 0 redo size 381 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed As you see, the first sql generated 43629 consistent gets and the second sql 22083 consistent gets, this is ok, how ever, the first take 1.63 second and the second take 1.70 second.This seems strange, right? Since in most case, higher consistent gets means longer time. There is no one else running on this server, And I also tested with event 10046 with no wait event.I tested for several times, with the same result. Can someone help me understand it? Thanks very much. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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
Re: Negative value for Consistent gets etc. in V$Sesstat
1) values may wrap (and there are some obscure bugs that cause negative values) 2) You want to get the block in mode CR and there is actually no work needed (like cleanout or rollback) to get to that mode. Anjo. On Tuesday 19 November 2002 21:34, you wrote: Hi All, There is one report which takes 12 hours to run during the off hours. And that too on a database 6Gb in size! The report was designed by the consultants and all the queries in the report were doing a nested loops joins on many tables, optimizer mode was RULE. After changing the optimizer mode to CHOOSE, still no effect. Then I rewrote the report to use joins instead of 'SELECT a row, run query for that row, then select another row, run query for that row..'(written in Oracle Reports) the run time came crashing down to 10 Secs. (Hard to believe!!). Now for the question. After running the report for a few hours I terminate the report and see the values in V$sesstat. It is showing me negative values for Consistent gets etc When the report was running i ran the same query on v$sesstat, at that moment it was showing Consistent gets - 47 million approx. 1. Why is it showing negative values? 2. What does stat 'no work - consistent read gets' mean? OUTPUT ONE HOUR AFTER THE REPORT STARTED: - SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE --- -- session connect time 478385736 process last non-idle time 478385736 consistent gets 47024111 session logical reads 47024106 no work - consistent read gets36724753 buffer is not pinned count36650911 table fetch by rowid 36643847 buffer is pinned count36569847 session pga memory 1651312 session pga memory max 1651312 session uga memory 1589476 session uga memory max 1589476 bytes sent via SQL*Net to client156588 CPU used when call started 81035 CPU used by this session 81035 bytes received via SQL*Net from client 48012 sorts (rows) 16390 table fetch continued row 6650 SQL*Net roundtrips to/from client 2043 user calls2033 execute count 788 calls to get snapshot scn: kcmgss 786 parse count (total) 17 opened cursors cumulative 12 table scan blocks gotten11 recursive calls 9 parse count (hard) 9 db block gets9 opened cursors current 6 enqueue requests 5 enqueue releases 5 cursor authentications 5 parse time elapsed 4 table scans (short tables) 3 parse time cpu 2 logons cumulative1 sorts (memory) 1 logons current 1 38 rows selected. STATS 10 HOURS AFTER THE REPORT STARTED RUNNING --- SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE - -- session connect time 478385736 process last non-idle time 478385736 bytes sent via SQL*Net to client
RE: Negative value for Consistent gets etc. in V$Sesstat
Thanx Anjo, Can you elaborate on 'values may wrap'? Regards Naveen -Original Message- Sent: Wednesday, November 20, 2002 2:34 PM To: Multiple recipients of list ORACLE-L 1) values may wrap (and there are some obscure bugs that cause negative values) 2) You want to get the block in mode CR and there is actually no work needed (like cleanout or rollback) to get to that mode. Anjo. On Tuesday 19 November 2002 21:34, you wrote: Hi All, There is one report which takes 12 hours to run during the off hours. And that too on a database 6Gb in size! The report was designed by the consultants and all the queries in the report were doing a nested loops joins on many tables, optimizer mode was RULE. After changing the optimizer mode to CHOOSE, still no effect. Then I rewrote the report to use joins instead of 'SELECT a row, run query for that row, then select another row, run query for that row..'(written in Oracle Reports) the run time came crashing down to 10 Secs. (Hard to believe!!). Now for the question. After running the report for a few hours I terminate the report and see the values in V$sesstat. It is showing me negative values for Consistent gets etc When the report was running i ran the same query on v$sesstat, at that moment it was showing Consistent gets - 47 million approx. 1. Why is it showing negative values? 2. What does stat 'no work - consistent read gets' mean? OUTPUT ONE HOUR AFTER THE REPORT STARTED: - SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE --- -- session connect time 478385736 process last non-idle time 478385736 consistent gets 47024111 session logical reads 47024106 no work - consistent read gets36724753 buffer is not pinned count36650911 table fetch by rowid 36643847 buffer is pinned count36569847 session pga memory 1651312 session pga memory max 1651312 session uga memory 1589476 session uga memory max 1589476 bytes sent via SQL*Net to client156588 CPU used when call started 81035 CPU used by this session 81035 bytes received via SQL*Net from client 48012 sorts (rows) 16390 table fetch continued row 6650 SQL*Net roundtrips to/from client 2043 user calls2033 execute count 788 calls to get snapshot scn: kcmgss 786 parse count (total) 17 opened cursors cumulative 12 table scan blocks gotten11 recursive calls 9 parse count (hard) 9 db block gets9 opened cursors current 6 enqueue requests 5 enqueue releases 5 cursor authentications 5 parse time elapsed 4 table scans (short tables) 3 parse time cpu 2 logons cumulative1 sorts (memory) 1 logons current 1 38 rows selected. STATS 10 HOURS AFTER THE REPORT STARTED RUNNING --- SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE - -- session
RE: Negative value for Consistent gets etc. in V$Sesstat
When the max value of the 4 bytes or 8 bytes have been reached the values may become negative, if oracle keeps on adding to them. Anjo. -Original Message- Nahata Sent: Wednesday, November 20, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Thanx Anjo, Can you elaborate on 'values may wrap'? Regards Naveen -Original Message- Sent: Wednesday, November 20, 2002 2:34 PM To: Multiple recipients of list ORACLE-L 1) values may wrap (and there are some obscure bugs that cause negative values) 2) You want to get the block in mode CR and there is actually no work needed (like cleanout or rollback) to get to that mode. Anjo. On Tuesday 19 November 2002 21:34, you wrote: Hi All, There is one report which takes 12 hours to run during the off hours. And that too on a database 6Gb in size! The report was designed by the consultants and all the queries in the report were doing a nested loops joins on many tables, optimizer mode was RULE. After changing the optimizer mode to CHOOSE, still no effect. Then I rewrote the report to use joins instead of 'SELECT a row, run query for that row, then select another row, run query for that row..'(written in Oracle Reports) the run time came crashing down to 10 Secs. (Hard to believe!!). Now for the question. After running the report for a few hours I terminate the report and see the values in V$sesstat. It is showing me negative values for Consistent gets etc When the report was running i ran the same query on v$sesstat, at that moment it was showing Consistent gets - 47 million approx. 1. Why is it showing negative values? 2. What does stat 'no work - consistent read gets' mean? OUTPUT ONE HOUR AFTER THE REPORT STARTED: - SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE --- -- session connect time 478385736 process last non-idle time 478385736 consistent gets 47024111 session logical reads 47024106 no work - consistent read gets36724753 buffer is not pinned count36650911 table fetch by rowid 36643847 buffer is pinned count36569847 session pga memory 1651312 session pga memory max 1651312 session uga memory 1589476 session uga memory max 1589476 bytes sent via SQL*Net to client156588 CPU used when call started 81035 CPU used by this session 81035 bytes received via SQL*Net from client 48012 sorts (rows) 16390 table fetch continued row 6650 SQL*Net roundtrips to/from client 2043 user calls2033 execute count 788 calls to get snapshot scn: kcmgss 786 parse count (total) 17 opened cursors cumulative 12 table scan blocks gotten11 recursive calls 9 parse count (hard) 9 db block gets9 opened cursors current 6 enqueue requests 5 enqueue releases 5 cursor authentications 5 parse time elapsed 4 table scans (short tables) 3 parse time cpu 2 logons cumulative1 sorts (memory) 1 logons current 1 38 rows selected. STATS 10 HOURS AFTER THE REPORT STARTED RUNNING --- SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME
Negative value for Consistent gets etc. in V$Sesstat
Hi All, There is one report which takes 12 hours to run during the off hours. And that too on a database 6Gb in size! The report was designed by the consultants and all the queries in the report were doing a nested loops joins on many tables, optimizer mode was RULE. After changing the optimizer mode to CHOOSE, still no effect. Then I rewrote the report to use joins instead of 'SELECT a row, run query for that row, then select another row, run query for that row..'(written in Oracle Reports) the run time came crashing down to 10 Secs. (Hard to believe!!). Now for the question. After running the report for a few hours I terminate the report and see the values in V$sesstat. It is showing me negative values for Consistent gets etc When the report was running i ran the same query on v$sesstat, at that moment it was showing Consistent gets - 47 million approx. 1. Why is it showing negative values? 2. What does stat 'no work - consistent read gets' mean? OUTPUT ONE HOUR AFTER THE REPORT STARTED: - SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE --- -- session connect time 478385736 process last non-idle time 478385736 consistent gets 47024111 session logical reads 47024106 no work - consistent read gets36724753 buffer is not pinned count36650911 table fetch by rowid 36643847 buffer is pinned count36569847 session pga memory 1651312 session pga memory max 1651312 session uga memory 1589476 session uga memory max 1589476 bytes sent via SQL*Net to client156588 CPU used when call started 81035 CPU used by this session 81035 bytes received via SQL*Net from client 48012 sorts (rows) 16390 table fetch continued row 6650 SQL*Net roundtrips to/from client 2043 user calls2033 execute count 788 calls to get snapshot scn: kcmgss 786 parse count (total) 17 opened cursors cumulative 12 table scan blocks gotten11 recursive calls 9 parse count (hard) 9 db block gets9 opened cursors current 6 enqueue requests 5 enqueue releases 5 cursor authentications 5 parse time elapsed 4 table scans (short tables) 3 parse time cpu 2 logons cumulative1 sorts (memory) 1 logons current 1 38 rows selected. STATS 10 HOURS AFTER THE REPORT STARTED RUNNING --- SQL SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND VALUE != 0 2 AND A.STATISTIC# = B.STATISTIC# 3 ORDER BY VALUE DESC 4 / NAME VALUE - -- session connect time 478385736 process last non-idle time 478385736 bytes sent via SQL*Net to client 8649748 CPU used when call started 4599084 CPU used by this session 4599084 bytes received via SQL*Net from client 3243913 session pga memory 1659824 session pga memory max
Consistent gets
Has anybody any idea why a query against tables on which very few if any update are applied would display a high number of consistent gets ? Details : Big query involving 4/5 tables, most of them partitioned. This is a test database, db block buffers about 100M, 5,000,000 of logical reads with 8K blocks which means that the SGA is flushed a number of times. The execution plan starts with a partition scan, then a series of nested loops (hash join disappointing). The number of db block gets corresponds to the number of blocks read during the partition scan; everything else appears as consistent gets. You can rule out delayed cleanout, since the same behaviour is displayed when the same query is run over and over and not update at all takes place. Another curious symptom is that the number of rows returned per second decreases by a factor 3 or 4 between the beginning and the end of the query. Believe me, no hideous hidden scan of table of partition. TIA, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Consistent gets
No problem as long as the stat no work - consistent read gets (v$sysstat) is also close to that number. Anjo. Stephane Faroult wrote: Has anybody any idea why a query against tables on which very few if any update are applied would display a high number of consistent gets ? Details : Big query involving 4/5 tables, most of them partitioned. This is a test database, db block buffers about 100M, 5,000,000 of logical reads with 8K blocks which means that the SGA is flushed a number of times. The execution plan starts with a partition scan, then a series of nested loops (hash join disappointing). The number of db block gets corresponds to the number of blocks read during the partition scan; everything else appears as consistent gets. You can rule out delayed cleanout, since the same behaviour is displayed when the same query is run over and over and not update at all takes place. Another curious symptom is that the number of rows returned per second decreases by a factor 3 or 4 between the beginning and the end of the query. Believe me, no hideous hidden scan of table of partition. TIA, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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.com -- 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: Consistent gets
Anjo, Why do they appear in the first place? Jared Anjo Kolk [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/09/2002 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Consistent gets No problem as long as the stat no work - consistent read gets (v$sysstat) is also close to that number. Anjo. Stephane Faroult wrote: Has anybody any idea why a query against tables on which very few if any update are applied would display a high number of consistent gets ? Details : Big query involving 4/5 tables, most of them partitioned. This is a test database, db block buffers about 100M, 5,000,000 of logical reads with 8K blocks which means that the SGA is flushed a number of times. The execution plan starts with a partition scan, then a series of nested loops (hash join disappointing). The number of db block gets corresponds to the number of blocks read during the partition scan; everything else appears as consistent gets. You can rule out delayed cleanout, since the same behaviour is displayed when the same query is run over and over and not update at all takes place. Another curious symptom is that the number of rows returned per second decreases by a factor 3 or 4 between the beginning and the end of the query. Believe me, no hideous hidden scan of table of partition. TIA, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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.com -- 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.com -- Author: 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).
db block get vs consistent gets
Has anybody figures about the CPU cost of a consistent get vs a db block get? I have always expected it to be higher but not extremely higher. I have had a bad surprise today on a process-of-death which I am trying to salvage (although it looks a bit more like the last sacrament rather than a cure). Basically, the most costly part of a one-hour process is a join between 3/4 tables, the biggest of which is 15 million rows. Nested loops, nothing in the process likely to be helped by any parallelization (?) on the 8 processors, a bit of hope with the partitioning of one of the tables but this is not the question. Basically, I had got rid of a UNION and a previous test had shown around 40% fewer logical reads, and I was putting some hope in it. In fact, it was rather worse. In the case with the UNION, I had about 22 million logical reads (don't laugh) and about 863 seconds of CPU. Without the UNION, I was down to about 17,5 million LR (more than expected), with about 15 million db block gets and the rest of consistent reads. CPU consumption about trebled. Concerning the whole process, the highest time spent waited no longer was PIO waits (not surprising with a process accessing 190 G of data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting some change, but not in such proportions. Any figures would be welcome to confirm or infirm what I have seen. TIA Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: db block get vs consistent gets
Stephane, The cost of an LIO is determined by many things. It used to be that one row access was one LIO. Now a days you will see that many rows are gotten in a single LIO. That means that the single LIO is more expensive. You will see that depending on the access plan oracle will access one or multiple rows and that will change the cost of the LIO. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 23, 2002 10:13 PM Has anybody figures about the CPU cost of a consistent get vs a db block get? I have always expected it to be higher but not extremely higher. I have had a bad surprise today on a process-of-death which I am trying to salvage (although it looks a bit more like the last sacrament rather than a cure). Basically, the most costly part of a one-hour process is a join between 3/4 tables, the biggest of which is 15 million rows. Nested loops, nothing in the process likely to be helped by any parallelization (?) on the 8 processors, a bit of hope with the partitioning of one of the tables but this is not the question. Basically, I had got rid of a UNION and a previous test had shown around 40% fewer logical reads, and I was putting some hope in it. In fact, it was rather worse. In the case with the UNION, I had about 22 million logical reads (don't laugh) and about 863 seconds of CPU. Without the UNION, I was down to about 17,5 million LR (more than expected), with about 15 million db block gets and the rest of consistent reads. CPU consumption about trebled. Concerning the whole process, the highest time spent waited no longer was PIO waits (not surprising with a process accessing 190 G of data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting some change, but not in such proportions. Any figures would be welcome to confirm or infirm what I have seen. TIA Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- 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: db block get vs consistent gets
Stephane - I believe that Cary Millsap (www.hotsos.com) has studied this issue in quite some detail. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Monday, September 23, 2002 3:14 PM To: Multiple recipients of list ORACLE-L Has anybody figures about the CPU cost of a consistent get vs a db block get? I have always expected it to be higher but not extremely higher. I have had a bad surprise today on a process-of-death which I am trying to salvage (although it looks a bit more like the last sacrament rather than a cure). Basically, the most costly part of a one-hour process is a join between 3/4 tables, the biggest of which is 15 million rows. Nested loops, nothing in the process likely to be helped by any parallelization (?) on the 8 processors, a bit of hope with the partitioning of one of the tables but this is not the question. Basically, I had got rid of a UNION and a previous test had shown around 40% fewer logical reads, and I was putting some hope in it. In fact, it was rather worse. In the case with the UNION, I had about 22 million logical reads (don't laugh) and about 863 seconds of CPU. Without the UNION, I was down to about 17,5 million LR (more than expected), with about 15 million db block gets and the rest of consistent reads. CPU consumption about trebled. Concerning the whole process, the highest time spent waited no longer was PIO waits (not surprising with a process accessing 190 G of data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting some change, but not in such proportions. Any figures would be welcome to confirm or infirm what I have seen. TIA Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Consistent Gets
Hi. What exactly does Consistent Gets mean? Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard 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: Consistent Gets
Any logical read (which includes SCN verification) Is called as consistent get. Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Sent: Tuesday, October 30, 2001 1:46 PM To: Multiple recipients of list ORACLE-L Hi. What exactly does Consistent Gets mean? Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard 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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Consistent Gets
CONSISTENT GETS is the number of blocks accessed in buffer cache for normal queries (SELECTs without for update clause). Rick -Original Message- Sent: Tuesday, October 30, 2001 4:46 PM To: Multiple recipients of list ORACLE-L Hi. What exactly does Consistent Gets mean? Thanks Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard 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: Cale, Rick T (Richard) 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: Higher Consistent Gets...
You don't quote a version of Oracle - which makes
a difference to the ROWS column on the execution
path.
However, I think you are seeing the extra I/O because
Oracle is driving off the main table, then executing the
subquery for each row returned - thus executing your
5 logical I/O many times. Ideally you would probably
like Oracle to use the subquery to drive the main query.
You may be able to do this by converting the subquery
into an inline view, and joining it (perhaps with an ORDERED
hint) to the main query:
select /*+ ordered */
...
from
(
select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142'
)v
fr_search_query pd
where
pd.location_code = v.location_code
and
etc.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 14 September 2001 18:10
|!! Please do not post Off Topic to this List !!
|
|Hi
|
|I am having problem with a query. This query fetches rows from a
table which
|has 15 million rows.
|
|The problem is, when I execute this query with subquery, the
consistent gets
|are 4700. Where us without the subquery the consistent gets are just
400. If
|I execute the subquery alone, the consistent gets are just 5.
|
|
|Here is the main query with subquery results in cons.gets of 4700:
|
|
|select pd.holiday_id holidayID, pd.package_number l_package_number,
| min(pd.tfr_price) l_tpr_price
| from fr_search_query pd
| where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
| and TO_DATE('13/10/2001','dd/mm/')
| and pd.location_code in (select location_code
| from gn_location
|connect by prior
location_code=parent_code
| start with location_code='3142')
| and ROWNUM 301
| group by pd.holiday_id , pd.package_number
| order by pd.location_name, pd.location_code, pd.accom_unit_name,
| pd.accom_unit_code, pd.departure_date,
min(pd.adult_price)
|
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
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).
Higher Consistent Gets...
!! Please do not post Off Topic to this List !!
Hi
I am having problem with a query. This query fetches rows from a table which
has 15 million rows.
The problem is, when I execute this query with subquery, the consistent gets
are 4700. Where us without the subquery the consistent gets are just 400. If
I execute the subquery alone, the consistent gets are just 5.
Here is the main query with subquery results in cons.gets of 4700:
select pd.holiday_id holidayID, pd.package_number l_package_number,
min(pd.tfr_price) l_tpr_price
from fr_search_query pd
where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
and TO_DATE('13/10/2001','dd/mm/')
and pd.location_code in (select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142')
and ROWNUM 301
group by pd.holiday_id , pd.package_number
order by pd.location_name, pd.location_code, pd.accom_unit_name,
pd.accom_unit_code, pd.departure_date, min(pd.adult_price)
Trace results
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
96 SORT (ORDER BY)
96SORT (GROUP BY)
96 COUNT (STOPKEY)
96 NESTED LOOPS
5137 INLIST ITERATOR
5138TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'FR_SEARCH_QUERY'
8566 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
96 VIEW
5136SORT (UNIQUE)
1 CONNECT BY
2 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_GN_LOCATION' (UNIQUE)
1 TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1 TABLE ACCESS (FULL) OF 'GN_LOCATION'
Running just the subquery results in cons.gets of just 5.
select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142'
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
1 CONNECT BY
2INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
(UNIQUE)
1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)
Both the tables, indexes are analyzed. The optimizer mode is choose.
How do I tune this or Am I missing something obivious??
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
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: Higher Consistent Gets...
!! Please do not post Off Topic to this List !!
Raj,
Which query is fastest? You don't have any timing information.
Make sure that timed_statistics is on in the database:
alter system set timed_statistics = true;
Run your query with trace on:
alter session set sql_trace = true;
Then run the resulting trace files through tkprof.
You'll have much more information to work with.
Jared
Raj Gopalan
raj.gopalan@netdecisi To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
ons.co.uk cc:
Sent by: Subject: Higher Consistent
Gets...
[EMAIL PROTECTED]
09/14/01 09:55 AM
Please respond to
ORACLE-L
!! Please do not post Off Topic to this List !!
Hi
I am having problem with a query. This query fetches rows from a table
which
has 15 million rows.
The problem is, when I execute this query with subquery, the consistent
gets
are 4700. Where us without the subquery the consistent gets are just 400.
If
I execute the subquery alone, the consistent gets are just 5.
Here is the main query with subquery results in cons.gets of 4700:
select pd.holiday_id holidayID, pd.package_number l_package_number,
min(pd.tfr_price) l_tpr_price
from fr_search_query pd
where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
and TO_DATE('13/10/2001','dd/mm/')
and pd.location_code in (select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142')
and ROWNUM 301
group by pd.holiday_id , pd.package_number
order by pd.location_name, pd.location_code, pd.accom_unit_name,
pd.accom_unit_code, pd.departure_date, min(pd.adult_price)
Trace results
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
96 SORT (ORDER BY)
96SORT (GROUP BY)
96 COUNT (STOPKEY)
96 NESTED LOOPS
5137 INLIST ITERATOR
5138TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'FR_SEARCH_QUERY'
8566 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
96 VIEW
5136SORT (UNIQUE)
1 CONNECT BY
2 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_GN_LOCATION' (UNIQUE)
1 TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1 TABLE ACCESS (FULL) OF 'GN_LOCATION'
Running just the subquery results in cons.gets of just 5.
select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142'
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
1 CONNECT BY
2INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
(UNIQUE)
1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)
Both the tables, indexes are analyzed. The optimizer mode is choose.
How do I tune this or Am I missing something obivious??
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
RE: Higher Consistent Gets...
!! Please do not post Off Topic to this List !!
Try this and let's know how it works:
select --+ ordered use_hash(vw1)
pd.holiday_id holidayID, pd.package_number l_package_number,
min(pd.tfr_price) l_tpr_price
from fr_search_query pd ,
(select --+ no_merge
location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142') vw1
where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
and TO_DATE('13/10/2001','dd/mm/')
and pd.location_code = vw1.location_code
and ROWNUM 301
group by pd.holiday_id , pd.package_number
order by pd.location_name, pd.location_code, pd.accom_unit_name,
pd.accom_unit_code, pd.departure_date, min(pd.adult_price)
Regards,
Waleed
-Original Message-
Sent: Friday, September 14, 2001 8:10 PM
To: Multiple recipients of list ORACLE-L
!! Please do not post Off Topic to this List !!
Raj,
Which query is fastest? You don't have any timing information.
Make sure that timed_statistics is on in the database:
alter system set timed_statistics = true;
Run your query with trace on:
alter session set sql_trace = true;
Then run the resulting trace files through tkprof.
You'll have much more information to work with.
Jared
Raj Gopalan
raj.gopalan@netdecisi To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
ons.co.uk cc:
Sent by: Subject: Higher
Consistent Gets...
[EMAIL PROTECTED]
09/14/01 09:55 AM
Please respond to
ORACLE-L
!! Please do not post Off Topic to this List !!
Hi
I am having problem with a query. This query fetches rows from a table
which
has 15 million rows.
The problem is, when I execute this query with subquery, the consistent
gets
are 4700. Where us without the subquery the consistent gets are just 400.
If
I execute the subquery alone, the consistent gets are just 5.
Here is the main query with subquery results in cons.gets of 4700:
select pd.holiday_id holidayID, pd.package_number l_package_number,
min(pd.tfr_price) l_tpr_price
from fr_search_query pd
where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
and TO_DATE('13/10/2001','dd/mm/')
and pd.location_code in (select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142')
and ROWNUM 301
group by pd.holiday_id , pd.package_number
order by pd.location_name, pd.location_code, pd.accom_unit_name,
pd.accom_unit_code, pd.departure_date, min(pd.adult_price)
Trace results
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
96 SORT (ORDER BY)
96SORT (GROUP BY)
96 COUNT (STOPKEY)
96 NESTED LOOPS
5137 INLIST ITERATOR
5138TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'FR_SEARCH_QUERY'
8566 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
96 VIEW
5136SORT (UNIQUE)
1 CONNECT BY
2 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_GN_LOCATION' (UNIQUE)
1 TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1 TABLE ACCESS (FULL) OF 'GN_LOCATION'
Running just the subquery results in cons.gets of just 5.
select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142'
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
1 CONNECT BY
2INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
(UNIQUE)
1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)
Both the tables, indexes are analyzed. The optimizer mode is choose.
How do I tune this or Am I missing something obivious??
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
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
RE: Consistent Gets?
Hi Paul, Thanks for sending the V$MYSTAT data offline. Try this explanation ... The first query is fetching all the rows using an array size of 15. The V$MYSTAT data showed a difference of about 25000 'user calls', and the 'SQL*Net roundtrips to/from client' data below matches that. The number of additional 'consistent gets' is not much less than that. This suggests that there is an extra consistent get for each fetch, unless the previous fetch finished at a database block boundary. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 13 February 2001 8:41 To: Multiple recipients of list ORACLE-L Hi Steve, thanks for your response. I had analyzed the tables - chain_cnt = 0. Just to verify, I also checked "table fetch continued row" which was 0 on all my tests. Any other ideas? Paul --- Steve Adams [EMAIL PROTECTED] wrote: Hi Paul, Analyze the table and see if you have any chained rows. If there are chained rows and if the STATE_CODE field is not always in the last row piece, then a extra consistent gets will be needed to get the column values from the trailing row pieces of chained rows that are not excluded by the where clause predicates. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 13 February 2001 3:31 To: Multiple recipients of list ORACLE-L Hi all, Could someone attempt to explain the difference in the no. of "consistent gets" reported for these 2 queries? I have a table (TEST1) made up of 11,333 blocks. No indexes on this table. I run two queries, both reported to do full table scans (as expected), one returning all the rows from the table and one with a bogus condition resulting in no rows returned. I expected, that since both queries did full table scans, that the amount of IO would be the same. Yet the query which returned data did 3 times as much IO as the one which did not. Output follows : 12:08:16 T10-SERVCBO-CH @p2 12:08:22 T10-SERVCBO-CH set autotrace traceonly exp stat 12:08:22 T10-SERVCBO-CH select 12:08:22 2 * 12:08:22 3 from 12:08:22 4 test1 12:08:22 5 where 12:08:22 6 pay_dealer_date = '01/01/2000' 12:08:22 7 -- and state_code = 'AB' BOGUS CONDITION 12:08:22 8 ; 375043 rows selected. Elapsed: 00:00:55.46 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (FULL) OF 'TEST1' Statistics -- 0 recursive calls 15 db block gets 35581 consistent gets 10575 physical reads 0 redo size 66817080 bytes sent via SQL*Net to client 2775646 bytes received via SQL*Net from client 25004 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 375043 rows processed 12:09:18 T10-SERVCBO-CH ed p2 12:09:33 T10-SERVCBO-CH @p2 12:09:35 T10-SERVCBO-CH set autotrace traceonly exp stat 12:09:35 T10-SERVCBO-CH select 12:09:35 2 * 12:09:35 3 from 12:09:35 4 test1 12:09:35 5 where 12:09:35 6 pay_dealer_date = '01/01/2000' 12:09:35 7 and state_code = 'AB' BOGUS CONDITION 12:09:35 8 ; no rows selected Elapsed: 00:00:03.43 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (FULL) OF 'TEST1' Statistics -- 0 recursive calls 15 db block gets 11337 consistent gets 10573 physical reads 0 redo size 1860 bytes sent via SQL*Net to client 313 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed 12:09:38 T10-SERVCBO-CH spool off What am I missing here? Any help appreciated. Thanx Paul -- 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). __ Do You Yahoo!? Get personalized email
Re: Consistent Gets?
Thanks for your response Riyaj.
I initially had the same thought so I had re-run
the queries a no. of times, all giving the same
(inconsistent) results. I also confirmed from
v$mystat that "consistent gets" as reported by
autotrace were being reported proportionately to
"no work - consistent read gets" - an indication
to me anyway that it was not having to re-create
the blocks for the consistent view.
Any other ideas?
Paul
--- [EMAIL PROTECTED] wrote:
Hi
This may be due to commit cleanout
mechanism. After populating the
table, your commit simply marks the transaction
as completed in the
rollback segment header and does not clean the
rows in the block. So the
flags in the row header portion of the block
indicates that the transaction
is open and active. When you do a select on
those rows Oracle sees that the
transaction is open and goes to the rollback
segment header to check the
status of the transaction, and then marks the
row headers to committed
state.
When you do the select second time, since
the row headers indicates the
commit status, the session doesn't need to do
that much work to get the
consistent data.
To verify this behavior, do the first select
again and you could see
comparable consistent gets.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my
employer. Use at your own risk"
Paul Parker
paul_g_parker@To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
yahoo.com cc:
Sent by:
Subject: Consistent Gets?
[EMAIL PROTECTED]
m
02/12/01 11:30
AM
Please respond
to ORACLE-L
Hi all,
Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?
I have a table (TEST1) made up of 11,333
blocks.
No indexes on this table. I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting
in
no rows returned. I expected, that since both
queries did full table scans, that the amount
of
IO would be the same. Yet the query which
returned data did 3 times as much IO as the one
which did not. Output follows :
12:08:16 T10-SERVCBO-CH @p2
12:08:22 T10-SERVCBO-CH set autotrace
traceonly
exp stat
12:08:22 T10-SERVCBO-CH select
12:08:22 2 *
12:08:22 3 from
12:08:22 4 test1
12:08:22 5 where
12:08:22 6 pay_dealer_date = '01/01/2000'
12:08:22 7 -- and state_code = 'AB'
BOGUS CONDITION
12:08:22 8 ;
375043 rows selected.
Elapsed: 00:00:55.46
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
--
0 recursive calls
15 db block gets
35581 consistent gets
10575 physical reads
0 redo size
66817080 bytes sent via SQL*Net to client
2775646 bytes received via SQL*Net from
client
25004 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
375043 rows processed
12:09:18 T10-SERVCBO-CH ed p2
12:09:33 T10-SERVCBO-CH @p2
12:09:35 T10-SERVCBO-CH set autotrace
traceonly
exp stat
12:09:35 T10-SERVCBO-CH select
12:09:35 2 *
12:09:35 3 fro
Re: Consistent Gets?
Thanks for your response Riyaj.
I initially had the same thought so I had re-run
the queries a no. of times, all giving the same
(inconsistent) results. I also confirmed from
v$mystat that "consistent gets" as reported by
autotrace were being reported proportionately to
"no work - consistent read gets" - an indication
to me anyway that it was not having to re-create
the blocks for the consistent view.
Any other ideas?
Paul
--- [EMAIL PROTECTED] wrote:
Hi
This may be due to commit cleanout
mechanism. After populating the
table, your commit simply marks the transaction
as completed in the
rollback segment header and does not clean the
rows in the block. So the
flags in the row header portion of the block
indicates that the transaction
is open and active. When you do a select on
those rows Oracle sees that the
transaction is open and goes to the rollback
segment header to check the
status of the transaction, and then marks the
row headers to committed
state.
When you do the select second time, since
the row headers indicates the
commit status, the session doesn't need to do
that much work to get the
consistent data.
To verify this behavior, do the first select
again and you could see
comparable consistent gets.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my
employer. Use at your own risk"
Paul Parker
paul_g_parker@To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
yahoo.com cc:
Sent by:
Subject: Consistent Gets?
[EMAIL PROTECTED]
m
02/12/01 11:30
AM
Please respond
to ORACLE-L
Hi all,
Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?
I have a table (TEST1) made up of 11,333
blocks.
No indexes on this table. I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting
in
no rows returned. I expected, that since both
queries did full table scans, that the amount
of
IO would be the same. Yet the query which
returned data did 3 times as much IO as the one
which did not. Output follows :
12:08:16 T10-SERVCBO-CH @p2
12:08:22 T10-SERVCBO-CH set autotrace
traceonly
exp stat
12:08:22 T10-SERVCBO-CH select
12:08:22 2 *
12:08:22 3 from
12:08:22 4 test1
12:08:22 5 where
12:08:22 6 pay_dealer_date = '01/01/2000'
12:08:22 7 -- and state_code = 'AB'
BOGUS CONDITION
12:08:22 8 ;
375043 rows selected.
Elapsed: 00:00:55.46
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
--
0 recursive calls
15 db block gets
35581 consistent gets
10575 physical reads
0 redo size
66817080 bytes sent via SQL*Net to client
2775646 bytes received via SQL*Net from
client
25004 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
375043 rows processed
12:09:18 T10-SERVCBO-CH ed p2
12:09:33 T10-SERVCBO-CH @p2
12:09:35 T10-SERVCBO-CH set autotrace
traceonly
exp stat
12:09:35 T10-SERVCBO-CH select
12:09:35 2 *
12:09:35 3 fro
Re: Consistent Gets?
I believe it's because the query that returned rows had to assemble a result set,
hence the extra consistent gets. The second query had no result set, so much less
work was done (less consistent gets).
[EMAIL PROTECTED] 02/12/01 12:30PM
Hi all,
Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?
I have a table (TEST1) made up of 11,333 blocks.
No indexes on this table. I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned. I expected, that since both
queries did full table scans, that the amount of
IO would be the same. Yet the query which
returned data did 3 times as much IO as the one
which did not. Output follows :
12:08:16 T10-SERVCBO-CH @p2
12:08:22 T10-SERVCBO-CH set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH select
12:08:22 2 *
12:08:22 3 from
12:08:22 4 test1
12:08:22 5 where
12:08:22 6 pay_dealer_date = '01/01/2000'
12:08:22 7 -- and state_code = 'AB'
BOGUS CONDITION
12:08:22 8 ;
375043 rows selected.
Elapsed: 00:00:55.46
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
--
0 recursive calls
15 db block gets
35581 consistent gets
10575 physical reads
0 redo size
66817080 bytes sent via SQL*Net to client
2775646 bytes received via SQL*Net from
client
25004 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
375043 rows processed
12:09:18 T10-SERVCBO-CH ed p2
12:09:33 T10-SERVCBO-CH @p2
12:09:35 T10-SERVCBO-CH set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH select
12:09:35 2 *
12:09:35 3 from
12:09:35 4 test1
12:09:35 5 where
12:09:35 6 pay_dealer_date = '01/01/2000'
12:09:35 7 and state_code = 'AB' BOGUS
CONDITION
12:09:35 8 ;
no rows selected
Elapsed: 00:00:03.43
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
--
0 recursive calls
15 db block gets
11337 consistent gets
10573 physical reads
0 redo size
1860 bytes sent via SQL*Net to client
313 bytes received via SQL*Net from
client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
12:09:38 T10-SERVCBO-CH spool off
What am I missing here? Any help appreciated.
Thanx
Paul
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Parker
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: Tim Sawmiller
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: Consistent Gets?
Hi Paul,
Analyze the table and see if you have any chained rows. If there are chained
rows and if the STATE_CODE field is not always in the last row piece, then a
extra consistent gets will be needed to get the column values from the trailing
row pieces of chained rows that are not excluded by the where clause predicates.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Tuesday, 13 February 2001 3:31
To: Multiple recipients of list ORACLE-L
Hi all,
Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?
I have a table (TEST1) made up of 11,333 blocks.
No indexes on this table. I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned. I expected, that since both
queries did full table scans, that the amount of
IO would be the same. Yet the query which
returned data did 3 times as much IO as the one
which did not. Output follows :
12:08:16 T10-SERVCBO-CH @p2
12:08:22 T10-SERVCBO-CH set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH select
12:08:22 2 *
12:08:22 3 from
12:08:22 4 test1
12:08:22 5 where
12:08:22 6 pay_dealer_date = '01/01/2000'
12:08:22 7 -- and state_code = 'AB'
BOGUS CONDITION
12:08:22 8 ;
375043 rows selected.
Elapsed: 00:00:55.46
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
--
0 recursive calls
15 db block gets
35581 consistent gets
10575 physical reads
0 redo size
66817080 bytes sent via SQL*Net to client
2775646 bytes received via SQL*Net from
client
25004 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
375043 rows processed
12:09:18 T10-SERVCBO-CH ed p2
12:09:33 T10-SERVCBO-CH @p2
12:09:35 T10-SERVCBO-CH set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH select
12:09:35 2 *
12:09:35 3 from
12:09:35 4 test1
12:09:35 5 where
12:09:35 6 pay_dealer_date = '01/01/2000'
12:09:35 7 and state_code = 'AB' BOGUS
CONDITION
12:09:35 8 ;
no rows selected
Elapsed: 00:00:03.43
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
--
0 recursive calls
15 db block gets
11337 consistent gets
10573 physical reads
0 redo size
1860 bytes sent via SQL*Net to client
313 bytes received via SQL*Net from
client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
12:09:38 T10-SERVCBO-CH spool off
What am I missing here? Any help appreciated.
Thanx
Paul
--
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: Consistent Gets?
Hi Paul, Is there a difference in the 'no buffer to keep pinned count' statistic in V$MYSTAT? @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 13 February 2001 8:41 To: Multiple recipients of list ORACLE-L Hi Steve, thanks for your response. I had analyzed the tables - chain_cnt = 0. Just to verify, I also checked "table fetch continued row" which was 0 on all my tests. Any other ideas? Paul --- Steve Adams [EMAIL PROTECTED] wrote: Hi Paul, Analyze the table and see if you have any chained rows. If there are chained rows and if the STATE_CODE field is not always in the last row piece, then a extra consistent gets will be needed to get the column values from the trailing row pieces of chained rows that are not excluded by the where clause predicates. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 13 February 2001 3:31 To: Multiple recipients of list ORACLE-L Hi all, Could someone attempt to explain the difference in the no. of "consistent gets" reported for these 2 queries? I have a table (TEST1) made up of 11,333 blocks. No indexes on this table. I run two queries, both reported to do full table scans (as expected), one returning all the rows from the table and one with a bogus condition resulting in no rows returned. I expected, that since both queries did full table scans, that the amount of IO would be the same. Yet the query which returned data did 3 times as much IO as the one which did not. Output follows : 12:08:16 T10-SERVCBO-CH @p2 12:08:22 T10-SERVCBO-CH set autotrace traceonly exp stat 12:08:22 T10-SERVCBO-CH select 12:08:22 2 * 12:08:22 3 from 12:08:22 4 test1 12:08:22 5 where 12:08:22 6 pay_dealer_date = '01/01/2000' 12:08:22 7 -- and state_code = 'AB' BOGUS CONDITION 12:08:22 8 ; 375043 rows selected. Elapsed: 00:00:55.46 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (FULL) OF 'TEST1' Statistics -- 0 recursive calls 15 db block gets 35581 consistent gets 10575 physical reads 0 redo size 66817080 bytes sent via SQL*Net to client 2775646 bytes received via SQL*Net from client 25004 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 375043 rows processed 12:09:18 T10-SERVCBO-CH ed p2 12:09:33 T10-SERVCBO-CH @p2 12:09:35 T10-SERVCBO-CH set autotrace traceonly exp stat 12:09:35 T10-SERVCBO-CH select 12:09:35 2 * 12:09:35 3 from 12:09:35 4 test1 12:09:35 5 where 12:09:35 6 pay_dealer_date = '01/01/2000' 12:09:35 7 and state_code = 'AB' BOGUS CONDITION 12:09:35 8 ; no rows selected Elapsed: 00:00:03.43 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (FULL) OF 'TEST1' Statistics -- 0 recursive calls 15 db block gets 11337 consistent gets 10573 physical reads 0 redo size 1860 bytes sent via SQL*Net to client 313 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed 12:09:38 T10-SERVCBO-CH spool off What am I missing here? Any help appreciated. Thanx Paul -- 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). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Parker 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 thi
RE: Consistent Gets?
Hi Steve, The 'no buffer to keep pinned count' statistic before and after were both 0. I have included all stats from v$mystat for reference. NAMEBefore After Differerence - background checkpoints completed0 0 0 background checkpoints started 0 0 0 background timeouts 0 0 0 branch node splits 0 0 0 buffer is not pinned count 117564 153170 35606 buffer is pinned count 162016200 bytes received via SQL*Net from client 2802962 5582722 2779760 bytes received via SQL*Net from dblink 0 0 0 bytes sent via SQL*Net to client66850048 133685423 66835375 bytes sent via SQL*Net to dblink0 0 0 Cached Commit SCN referenced0 0 0 calls to get snapshot scn: kcmgss 902 919 17 calls to kcmgas 0 0 0 calls to kcmgcs 0 0 0 calls to kcmgrs 0 0 0 change write time 0 0 0 cleanouts and rollbacks - consistent read gets 0 0 0 cleanouts only - consistent read gets 1 1 0 cluster key scan block gets 414 414 0 cluster key scans 365 365 0 cold recycle reads 0 0 0 commit cleanout failures: block lost0 0 0 commit cleanout failures: buffer being written 0 0 0 commit cleanout failures: callback failure 0 0 0 commit cleanout failures: cannot pin0 0 0 commit cleanout failures: hot backup in progress 0 0 0 commit cleanout failures: write disabled0 0 0 commit cleanouts0 0 0 commit cleanouts successfully completed 0 0 0 Commit SCN cached 0 0 0 consistent changes 0 0 0 consistent gets 118519 154128 35609 CPU used by this session364749461299 CPU used when call started 364749461299 CR blocks created 0 0 0 current blocks converted for CR 0 0 0 cursor authentications 38 38 0 data blocks consistent reads - undo records applied 0 0 0 db block changes44 52 8 db block gets 390 441 51 DBWR buffers scanned0 0 0 DBWR checkpoint buffers written 0 0 0 DBWR checkpoints0 0 0 DBWR cross instance writes 0 0 0 DBWR free buffers found 0 0 0 DBWR lru scans 0 0 0 DBWR make free requests 0 0 0 DBWR revisited being-written buffer 0 0 0 DBWR summed scan depth 0 0 0 DBWR transaction table writes 0 0 0 DBWR undo block writes 0 0 0 DDL statements parallelized 0 0 0 deferred (CURRENT) block cleanout applications 0 0 0 DFO trees parallelized 0 0 0 dirty buffers inspected 0 0 0 DML statements parallelized 0 0 0 enqueue conversions 0 0 0 enqueue deadlocks 0 0 0 enqueue releases64 64 0 enqueue requests66 66 0 enqueue timeouts0 0 0 enqueue waits 0 0 0 exchange deadlocks 0 0 0 execute count 890 903 13 free buffer inspected 0 0 0 free buffer requested 91463 102037 10574 global cache blocks corrupt 0 0 0 global cache convert time 0 0 0 global cache convert timeouts 0 0 0 global cache converts 0 0 0 global cache cr block log flush time0 0 0 global cache cr block log flushes 0 0 0 global cache cr block receive time 0 0 0 global cache cr block send time 0 0 0 global cache cr block serve time0 0 0 global cache cr blocks received 0 0 0 global cache cr blocks served 0 0 0 global cache cr requests blocked0 0 0 global cache cr timeouts0 0 0 global cache defers 0 0 0 global cache freelist waits 0 0 0 global cache get time 0 0 0 global cache gets 0 0 0 global cache prepare failures 0 0 0 global lock async converts 0 0 0 global lock async gets 0 0 0 global lock convert time0 0 0 global lock get time0 0 0 global lock releases0 0 0 global lock sync converts 0 0 0 global lock sync gets 0 0 0 hot buffers moved to head of LRU0 0 0 immediate (CR) block cleanout applications 1 1 0 immediate (CURRENT) block cleanout applications 0 0 0 index fast full scans (direct read) 0 0 0 index fast full scans (full)0 0 0 index fast full scans (rowid ranges)0 0 0 instance
