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).
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).
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: 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 addresses from Yahoo!
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 from 12:09:35 4 test1 12:09:35 5 where
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 from 12:09:35 4 test1 12:09:35 5 where
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 this mailing list, send an
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
