Re: Re: performance issue on select count(*)
"Exactly" my point! ;-) You cannot use it for anymore than that, and neither should you ignore it completely. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 29, 2003 6:39 AM > yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. > > if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. > > mladen is right. there is no 'exact' very high and very low. you have to interpret it. > > that is about it. Anyone who uses it for anymore than that is wrong. > > > > From: Mladen Gogala <[EMAIL PROTECTED]> > > Date: 2003/10/28 Tue PM 12:09:34 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: performance issue on select count(*) > > > > So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" > > and what do you consider "low"? > > What "HR" are you talking about? > > This would be the infamous BCHR: > > > > select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) > > + sum(decode(name,'db block gets', value,0)) > > - sum(decode(name,'physical reads', value,0))) > > / ( sum(decode(name, 'consistent gets',value,0)) > > + sum(decode(name,'db block gets', value,0)) ) * 100 > > from v$sysstat > > > > What exactly should the number returned by this query tell me? > > > > > > On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > > > > > The symptom suggests caching is a big factor here - most likely > > > block-buffers. > > > > > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > > > performance indicator - either being very high, or being too low - both of > > > which gives a good indication of something that needs to be looked at. > > > > > > > > > > I would be interested to know if there is a way to speed up the initial > > > > execution or how to diagnose what the delay was. It does not seems right > > > > that there is such a big difference in elapsed time between the initial > > > and > > > > subsequent execution. > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Binley Lim > > > 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). > > > > > > > Mladen Gogala > > Oracle DBA > > > > > > > > Note: > > This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. > > Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Mladen Gogala > > 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: [
Re: performance issue on select count(*)
An unusually high BCHR could be an indicator that your database is running Connor McDonald's choose_a_hit_ratio procedure. http://www.oracledba.co.uk/tips/choose.htm Jared Mladen Gogala <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/28/2003 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: performance issue on select count(*) So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" and what do you consider "low"? What "HR" are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > The symptom suggests caching is a big factor here - most likely > block-buffers. > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > performance indicator - either being very high, or being too low - both of > which gives a good indication of something that needs to be looked at. > > > > I would be interested to know if there is a way to speed up the initial > > execution or how to diagnose what the delay was. It does not seems right > > that there is such a big difference in elapsed time between the initial > and > > subsequent execution. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Binley Lim > INET: [EMAIL PROTECTED] >
Re: Re: performance issue on select count(*)
yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. mladen is right. there is no 'exact' very high and very low. you have to interpret it. that is about it. Anyone who uses it for anymore than that is wrong. > > From: Mladen Gogala <[EMAIL PROTECTED]> > Date: 2003/10/28 Tue PM 12:09:34 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: performance issue on select count(*) > > So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" > and what do you consider "low"? > What "HR" are you talking about? > This would be the infamous BCHR: > > select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) > + sum(decode(name,'db block gets', value,0)) > - sum(decode(name,'physical reads', value,0))) > / ( sum(decode(name, 'consistent gets',value,0)) > + sum(decode(name,'db block gets', value,0)) ) * 100 > from v$sysstat > > What exactly should the number returned by this query tell me? > > > On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > > > The symptom suggests caching is a big factor here - most likely > > block-buffers. > > > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > > performance indicator - either being very high, or being too low - both of > > which gives a good indication of something that needs to be looked at. > > > > > > > I would be interested to know if there is a way to speed up the initial > > > execution or how to diagnose what the delay was. It does not seems right > > > that there is such a big difference in elapsed time between the initial > > and > > > subsequent execution. > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Binley Lim > > 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). > > > > Mladen Gogala > Oracle DBA > > > > Note: > This message is for the named person's use only. It may contain confidential, > proprietary or legally privileged information. No confidentiality or privilege is > waived or lost by any mistransmission. If you receive this message in error, please > immediately delete it and all copies of it from your system, destroy any hard copies > of it and notify the sender. You must not, directly or indirectly, use, disclose, > distribute, print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to > monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, except where > the message states otherwise and the sender is authorized to state them to be the > views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > 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: <[EMAIL PROTECTED] 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: performance issue on select count(*)
So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" and what do you consider "low"? What "HR" are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > The symptom suggests caching is a big factor here - most likely > block-buffers. > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > performance indicator - either being very high, or being too low - both of > which gives a good indication of something that needs to be looked at. > > > > I would be interested to know if there is a way to speed up the initial > > execution or how to diagnose what the delay was. It does not seems right > > that there is such a big difference in elapsed time between the initial > and > > subsequent execution. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Binley Lim > 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). > Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: performance issue on select count(*)
The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. > I would be interested to know if there is a way to speed up the initial > execution or how to diagnose what the delay was. It does not seems right > that there is such a big difference in elapsed time between the initial and > subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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: performance issue on select count(*)
Tim, Thanks for your reply. The select count(*) is doing an index range scan on the column tid. No table access in the execution plan. The query you provided returned the following result: NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY LAST_ANAL - --- --- - 2326064.1 161201 1 3 28-OCT-03 I have tried local partition index on tid but the execution time was still around 10secs for the initial execution and <1sec subsequently. The table is partitioned on a date field. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. I monitored the wait events during both executions. They were all pretty low. It does not appear to be I/O bound either. tnsping from my PC to the database took about 30msec. Any other suggestions what I could check? Thanks. linda select * from v$session_event where sid=98; Initial run: SID EVENTTOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 98 latch free 115 681 .008695652 1 98 control file sequential read 300 0 0 98 refresh controlfile command 100 0 0 98 buffer busy waits 100 0 0 98 log file sync 101 1 1 98 db file sequential read 1968 0 827 .42022357710 98 file open 502 .4 1 98 SQL*Net message to client 305 00 0 0 98 SQL*Net message from client 3040 31819 104.667763 29911 Subsequent run: - SID EVENT TOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 99 latch free 162 93 3 .018518519 2 99 control file sequential read 3 0 0 0 0 99 refresh controlfile command1 0 0 0 0 99 buffer busy waits 1 00 0 0 99 log file sync 1 00 0 0 99 file open 3 01 .3 1 99 SQL*Net message to client 54 00 0 0 99 SQL*Net message from client53 02893 54.5849057 2698 From: Tim Gorman <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: performance issue on select count(*) Date: Mon, 27 Oct 2003 10:34:59 -0800 Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = ''; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or "histograms" have not been gathered). If the values of these two columns are high, then
Re: performance issue on select count(*)
Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = ''; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or "histograms" have not been gathered). If the values of these two columns are high, then the CBO will be hesitant to use the index, and with good reason. Thus, with the use of the index rejected as an option, you'll of course have a FULL table scan on your hands. There is probably more to it, but this should be a start. Feel free to post the results of the query above to the list, if you wish... Hope this helps... -Tim on 10/27/03 6:24 AM, Linda Wang at [EMAIL PROTECTED] wrote: > Hi, > I have an online application that does a 'select count(*)' on a few tables. > The 'select counts' always runs slow (about 10secs) for the first time and > then fast again (< 1sec) after subsequent accesses. The query runs slow > again when the data is flushed out of the buffer cache. > 10046 trace shows that the query takes a long time whenever there are disk > accesses to fetch the data (about 1000 8K) into db cache. It should not take > that long to fetch 1000 8K blocks into the cache and I/O does not appear to > be the problem. > > Anyone has any idea what the problem may be or how I can speed up my query? > > DB: 8.1.7.4 > query: select count(*) from tickets where tid='value1'; > where tickets has about 2 million records partition on a date field. > and tid is indexed. > > thanks. > > linda > > _ > Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet > Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: performance issue on select count(*)
Stephane, the execution plan for the statement is an index range scan on tid. It did not access the table. index is not partitioned. I will testpartitioning the index and with the parallel fast full scan. Anyone else has any other suggestions? Thanks. linda From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: performance issue on select count(*) Date: Mon, 27 Oct 2003 05:49:24 -0800 Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF >- --- Original Message --- - >From: "Linda Wang" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 27 Oct 2003 05:24:32 > >Hi, >I have an online application that does a 'select >count(*)' on a few tables. >The 'select counts' always runs slow (about 10secs) >for the first time and >then fast again (< 1sec) after subsequent accesses. >The query runs slow >again when the data is flushed out of the buffer >cache. >10046 trace shows that the query takes a long time >whenever there are disk >accesses to fetch the data (about 1000 8K) into db >cache. It should not take >that long to fetch 1000 8K blocks into the cache >and I/O does not appear to >be the problem. > >Anyone has any idea what the problem may be or how >I can speed up my query? > >DB: 8.1.7.4 >query: select count(*) from tickets where >tid='value1'; >where tickets has about 2 million records partition >on a date field. >and tid is indexed. > >thanks. > >linda > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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). _ See when your friends are online with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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: performance issue on select count(*)
Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF >- --- Original Message --- - >From: "Linda Wang" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 27 Oct 2003 05:24:32 > >Hi, >I have an online application that does a 'select >count(*)' on a few tables. >The 'select counts' always runs slow (about 10secs) >for the first time and >then fast again (< 1sec) after subsequent accesses. >The query runs slow >again when the data is flushed out of the buffer >cache. >10046 trace shows that the query takes a long time >whenever there are disk >accesses to fetch the data (about 1000 8K) into db >cache. It should not take >that long to fetch 1000 8K blocks into the cache >and I/O does not appear to >be the problem. > >Anyone has any idea what the problem may be or how >I can speed up my query? > >DB: 8.1.7.4 >query: select count(*) from tickets where >tid='value1'; >where tickets has about 2 million records partition >on a date field. >and tid is indexed. > >thanks. > >linda > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
