Re: help with estimate row count from asktom

[Yong Huang]

One minor caveat about setting timed_os_statistics. On Solaris, if you set
timed_os_statistics to non-zero, microstate accounting at the OS level is
enabled for the server process. Common practice is to leave it off for
performance reason. But I've never seen experimental data proving the negative
effect of turning it on.

Yong Huang

Tanel Poder wrote:

Hi! Statistics level ALL means TYPICAL + row source execution stats +
timed_os_statistics. If you want to switch to ALL for performance reasons, you
can switch only row source stats on with parameter setting
_rowsource_execution_statistics to true (on session level). But I doubt it'll
help in current case anyway.

Tanel

__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  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: help with estimate row count from asktom

[Tanel Poder]

Hi!

Statistics level ALL means TYPICAL + row source execution stats +
timed_os_statistics.
If you want to switch to ALL for performance reasons, you can switch only
row source stats on with parameter setting _rowsource_execution_statistics
to true (on session level).

But I doubt it'll help in current case anyway.

Tanel.

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, December 30, 2003 9:19 PM


> anyone have a better way to do this? im going to post what you said
wolfgang on asktom and see what he has to say.
> >
> > From: Wolfgang Breitling <[EMAIL PROTECTED]>
> > Date: 2003/12/30 Tue PM 12:09:33 EST
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: Re: help with estimate row count from asktom
> >
> > v$sql_plan_statistics (and consequently v$sql_plan_statistics_all) only
> > have data to show if statistics_level is set to ALL. You can set that at
> > the session level.
> > Has anyone done measurements on a busy system to evaluate what the
impact
> > is of setting that system-wide. The impression I have is that it is not
> > something I want to set in production all the time.
> >
> > At 08:39 AM 12/30/2003, you wrote:
> > >I have a very strict SLA and I posted a question on asktom about the
best
> > >way to get the 'estimate' of rows and return it to the user. Im getting
> > >'no data found'. anyone have ideas? Im on 9.2, tables are analyzed, and
Im
> > >in a DBA account.
> > >
> > >my question is at the bottom.
> > >
> >
>http://asktom.oracle.com/pls/ask/f?p=4950:8:352052922015846036::NO::F4950_P
8_DISPLAYID,F4950_P8_CRITERIA:1933814740032,
> > >
> > >--
> > >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).
> >
> > Wolfgang Breitling
> > Oracle7, 8, 8i, 9i OCP DBA
> > Centrex Consulting Corporation
> > http://www.centrexcc.com
> >
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Wolfgang Breitling
> >   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).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  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: help with estimate row count from asktom

[ryan_oracle]

i could have swarn i read in multiple places that in a high transaction system hitting 
v$views repeatedly kills performance? causes excessive latching? 

ill have to test it to see if this is better than a count. Gonna be ugly either way. 
> 
> From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]>
> Date: 2003/12/30 Tue PM 01:29:25 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Re: help with estimate row count from asktom
> 
> Don't be afraid to access  v$ views, just beware of the bug that throws a ora-600 
> when selecting 'filter_predicates' and 'access_predicates' under 9202. As a 
> workaround, don't select those two columns. If I were you, I'd make sure that users 
> are *very* clear that the number you are going to get is an 'ESTIMATE' only.
> 
> We run with statistics_level=ALL, haven't seen any noticeable difference, YMMV.
> Raj
> 
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
> 
> 
> -Original Message-
> Sent: Tuesday, December 30, 2003 1:19 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> im concerned about hitting the v$views in production. we have 30,000 users. its 
> either that or do counts. Its a requirement from the users. not sure what to do. 
> doesnt tom kyte do this on asktom?
> 
> **
> This e-mail message is confidential, intended only for the named recipient(s) above 
> and may contain information that is privileged, attorney work product or exempt from 
> disclosure under applicable law. If you have received this message in error, or are 
> not the named recipient(s), please immediately notify corporate MIS at (860) 
> 766-2000 and delete this e-mail message from your computer, Thank you.
> **4
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jamadagni, Rajendra
>   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: Re: help with estimate row count from asktom

[ryan_oracle]

anyone have a better way to do this? im going to post what you said wolfgang on asktom 
and see what he has to say. 
> 
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/12/30 Tue PM 12:09:33 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: help with estimate row count from asktom
> 
> v$sql_plan_statistics (and consequently v$sql_plan_statistics_all) only 
> have data to show if statistics_level is set to ALL. You can set that at 
> the session level.
> Has anyone done measurements on a busy system to evaluate what the impact 
> is of setting that system-wide. The impression I have is that it is not 
> something I want to set in production all the time.
> 
> At 08:39 AM 12/30/2003, you wrote:
> >I have a very strict SLA and I posted a question on asktom about the best 
> >way to get the 'estimate' of rows and return it to the user. Im getting 
> >'no data found'. anyone have ideas? Im on 9.2, tables are analyzed, and Im 
> >in a DBA account.
> >
> >my question is at the bottom.
> >
> >http://asktom.oracle.com/pls/ask/f?p=4950:8:352052922015846036::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1933814740032,
> >
> >--
> >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).
> 
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Wolfgang Breitling
>   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: Re: help with estimate row count from asktom

[Bobak, Mark]

Ryan,

I asked Tom that very question a while ago, here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:8900576360328284797::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3489618933902,

The short answer is that he's using Intermedia for his searching, which has
the 'ctx_query.count_hits' functionality.  It's built-in to Intermedia.

-Mark


-Original Message-
From:   [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent:   Tue 12/30/2003 1:19 PM
To: Multiple recipients of list ORACLE-L
Cc: 
Subject:Re: Re: help with estimate row count from asktom
im concerned about hitting the v$views in production. we have 30,000 users. its either 
that or do counts. Its a requirement from the users. 

not sure what to do. doesnt tom kyte do this on asktom?
> 
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/12/30 Tue PM 12:09:33 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: help with estimate row count from asktom
> 
> v$sql_plan_statistics (and consequently v$sql_plan_statistics_all) only 
> have data to show if statistics_level is set to ALL. You can set that at 
> the session level.
> Has anyone done measurements on a busy system to evaluate what the impact 
> is of setting that system-wide. The impression I have is that it is not 
> something I want to set in production all the time.
> 
> At 08:39 AM 12/30/2003, you wrote:
> >I have a very strict SLA and I posted a question on asktom about the best 
> >way to get the 'estimate' of rows and return it to the user. Im getting 
> >'no data found'. anyone have ideas? Im on 9.2, tables are analyzed, and Im 
> >in a DBA account.
> >
> >my question is at the bottom.
> >
> >http://asktom.oracle.com/pls/ask/f?p=4950:8:352052922015846036::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1933814740032,
> >
> >--
> >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).
> 
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Wolfgang Breitling
>   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).



-- 
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: Re: help with estimate row count from asktom

[Jamadagni, Rajendra]

Don't be afraid to access  v$ views, just beware of the bug that throws a ora-600 when 
selecting 'filter_predicates' and 'access_predicates' under 9202. As a workaround, 
don't select those two columns. If I were you, I'd make sure that users are *very* 
clear that the number you are going to get is an 'ESTIMATE' only.

We run with statistics_level=ALL, haven't seen any noticeable difference, YMMV.
Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Tuesday, December 30, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L


im concerned about hitting the v$views in production. we have 30,000 users. its either 
that or do counts. Its a requirement from the users. not sure what to do. doesnt tom 
kyte do this on asktom?

**
This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**4
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  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: help with estimate row count from asktom

[ryan_oracle]

im concerned about hitting the v$views in production. we have 30,000 users. its either 
that or do counts. Its a requirement from the users. 

not sure what to do. doesnt tom kyte do this on asktom?
> 
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/12/30 Tue PM 12:09:33 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: help with estimate row count from asktom
> 
> v$sql_plan_statistics (and consequently v$sql_plan_statistics_all) only 
> have data to show if statistics_level is set to ALL. You can set that at 
> the session level.
> Has anyone done measurements on a busy system to evaluate what the impact 
> is of setting that system-wide. The impression I have is that it is not 
> something I want to set in production all the time.
> 
> At 08:39 AM 12/30/2003, you wrote:
> >I have a very strict SLA and I posted a question on asktom about the best 
> >way to get the 'estimate' of rows and return it to the user. Im getting 
> >'no data found'. anyone have ideas? Im on 9.2, tables are analyzed, and Im 
> >in a DBA account.
> >
> >my question is at the bottom.
> >
> >http://asktom.oracle.com/pls/ask/f?p=4950:8:352052922015846036::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1933814740032,
> >
> >--
> >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).
> 
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Wolfgang Breitling
>   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: help with estimate row count from asktom

[Wolfgang Breitling]

v$sql_plan_statistics (and consequently v$sql_plan_statistics_all) only 
have data to show if statistics_level is set to ALL. You can set that at 
the session level.
Has anyone done measurements on a busy system to evaluate what the impact 
is of setting that system-wide. The impression I have is that it is not 
something I want to set in production all the time.

At 08:39 AM 12/30/2003, you wrote:
I have a very strict SLA and I posted a question on asktom about the best 
way to get the 'estimate' of rows and return it to the user. Im getting 
'no data found'. anyone have ideas? Im on 9.2, tables are analyzed, and Im 
in a DBA account.

my question is at the bottom.

http://asktom.oracle.com/pls/ask/f?p=4950:8:352052922015846036::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1933814740032,

--
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).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 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).

help with estimate row count from asktom

[ryan_oracle]

I have a very strict SLA and I posted a question on asktom about the best way to get 
the 'estimate' of rows and return it to the user. Im getting 'no data found'. anyone 
have ideas? Im on 9.2, tables are analyzed, and Im in a DBA account. 

my question is at the bottom.

http://asktom.oracle.com/pls/ask/f?p=4950:8:352052922015846036::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1933814740032,

-- 
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: Re: performance issue on select count(*)

[Binley Lim]

"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(*)

[Jared . Still]

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(*)

[ryan_oracle]

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(*)

[Mladen Gogala]

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(*)

[Binley Lim]

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(*)

[Linda Wang]

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(*)

[Tim Gorman]

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(*)

[Linda Wang]

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(*)

[Stephane Faroult]

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).

performance issue on select count(*)

[Linda Wang]

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: 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: count(*) vs count(0)

[Farnsworth, Dave]

Thanks 
for burying that dead horse Kirti!!
 
;o)
 
 
dave

  -Original Message-From: Kirtikumar Deshpande 
  [mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003 
  10:30 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: count(*) vs count(0)
  Vivek, 
  Your same question on LazyDBA list has been answered correctly. 
  
  :) 
   
  - Kirti VIVEK_SHARMA 
  <[EMAIL PROTECTED]> wrote:
  Is 
there a performance equality between COUNT(*) & COUNT(0) ?Is it same 
, indpendent of the Oracle Version ?Forgive the repeat raising , as 
this issue seems to have come before .Thanks-- 
  
  
  
  Do you Yahoo!?Free online 
  calendar with sync to Outlook(TM).

Re: count(*) vs count(0)

[Kirtikumar Deshpande]

Vivek, 
Your same question on LazyDBA list has been answered correctly. 
:) 
 
- Kirti VIVEK_SHARMA <[EMAIL PROTECTED]> wrote:
Is there a performance equality between COUNT(*) & COUNT(0) ?Is it same , indpendent of the Oracle Version ?Forgive the repeat raising , as this issue seems to have come before .Thanks-- 
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).

Re: count(*) vs count(0)

[Wolfgang Breitling]

It may be at your end, I have no difficulty getting to asktom. Here is a 
link to one of the threads about count(*) vs other count() techniques:

http://asktom.oracle.com/pls/ask/f?p=4950:8:26428220175898::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4567980767113,

At 06:45 AM 6/6/2003 -0800, you wrote:
Having tested both forms... it looks like to be the
same...
asktom has a detailed thread about this...

sorry, because I'm not posting the url link, but in
this moment the site is experiencing 'technical
difficulties'
:-)
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 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: count(*) vs count(0)

[Jose Luis Delgado]

Having tested both forms... it looks like to be the
same...

asktom has a detailed thread about this...

sorry, because I'm not posting the url link, but in
this moment the site is experiencing 'technical
difficulties'

:-)

HTH
JL

--- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote:
> 
> Is there a performance equality between COUNT(*) &
> COUNT(0) ?
> Is it same , indpendent of the Oracle Version ?
> 
> Forgive the repeat raising , as this issue seems to
> have come before .
> 
> Thanks
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: VIVEK_SHARMA
>   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).


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jose Luis Delgado
  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).

count(*) vs count(0)

[VIVEK_SHARMA]

Is there a performance equality between COUNT(*) & COUNT(0) ?
Is it same , indpendent of the Oracle Version ?

Forgive the repeat raising , as this issue seems to have come before .

Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  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: select count(case ...) slow in PL/SQL, any better way?

[Connor McDonald]

Seems to be different queries to me:

select count(case ... ) into count1,
   count(case ... ) into count2
from isi.nametag

suggests a full scan on isi.nametag whereas the two
separate queries:

select count(*) from isi.nametag where geneid=geneid1
select count(*) from isi.nametag where geneid=geneid2

have the benefit of a predicate.  You could always try

select count(case ... ) into count1,
   count(case ... ) into count2
from isi.nametag
where geneid in (geneid1,geneid2)

hth
connor

 --- Darrell Landrum <[EMAIL PROTECTED]> wrote: >
I thought case in PL/SQL was not available until 9i.
>  I'll have to look that up.
> 
> 
> >>> [EMAIL PROTECTED] 02/24/03 04:38PM >>>
> I too faced the problem of case not working in
> pl/sql & procedures. So I
> created a view.
> 
> Would creating a view work for you ?
> 
> -Original Message-
> Sent: Monday, February 24, 2003 3:07 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi:
> 
> Oracle 8173 on Sun Solaris 2.8. I am trying to
> "optimize" the follwoing code
> (in an PL/SQL package) into one table call (instead
> of two)
> 
>   select count(1) into count1 from isi.nametag where
> geneid=geneid1;
>   select count(1) into count2 from isi.nametag where
> geneid=geneid2;
> 
> The following code works in sqlplus, but not in
> PL/SQL:
> 
>  select count(case when geneid=geneid1 then 1 else
> null end ) into count1,
>count(case when geneid=geneid2 then 1
> else null end ) into count2
>   from isi.nametag ;
> 
> I have to use dynamic sql to get around this
> problem. But it's perofrmance
> is horrible.
> 
> SQL> set serveroutput on
> SQL> declare
>   2i  number:=0;
>   3str varchar2(200);
>   4   count1 number;
>   5count2 number;
>   6  begin
>   7str := 'select count(case when geneid=:x1
> then 1 else null end ) ,
>   8  count(case when geneid=:x2 then
> 1 else null end )
>   9from isi.nametag';
>  10for x1 in 1 .. 10 Loop
>  11  for x2 in 20 .. 30 Loop
>  12 i := i +1;
>  13 EXECUTE IMMEDIATE str INTO count1,
> count2 USING x1, x2;
>  14  end loop;
>  15end loop;
>  16dbms_output.put_line('i =' || i);
>  17  end;
>  18  /
> i =110
> 
> PL/SQL procedure successfully completed.
> 
> Elapsed: 00:00:10.96
> 
> SQL> declare
>   2   i  number:=0;
>   3   count1 number;
>   4count2 number;
>   5  begin
>   6for x1 in 1 .. 100 Loop
>   7  for x2 in 200 .. 300 Loop
>   8i := i +1;
>   9   select count(1) into count1 from
> isi.nametag where geneid=x1;
>  10   select count(1) into count2 from
> isi.nametag where geneid=x2;
>  11  end loop;
>  12end loop;
>  13dbms_output.put_line('i =' || i);
>  14  end;
>  15  /
> i =10100
> 
> PL/SQL procedure successfully completed.
> 
> Elapsed: 00:00:04.06
> 
> Is there a better way to optimize the orginal code? 
> TIA.
> 
> Guang Mei
> 
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net 
> --
> Author: gmei
>   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).
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Darrell Landrum
> 

Re: select count(case ...) slow in PL/SQL, any better way?

[Darrell Landrum]

Thanks!

>>> [EMAIL PROTECTED] 02/24/03 06:28PM >>>
CASE appeared (with little fanfare) in 8.1.6. However, until Oracle9, it 
was not available in PL/SQL, when they integrated the PL/SQL engine into 
the kernel.

Darrell Landrum wrote:

>I thought case in PL/SQL was not available until 9i.  I'll have to look that up.
>
>
>  
>
>>>>[EMAIL PROTECTED] 02/24/03 04:38PM >>>
>>>>
>>>>
>I too faced the problem of case not working in pl/sql & procedures. So I
>created a view.
>
>Would creating a view work for you ?
>
>-Original Message-
>Sent: Monday, February 24, 2003 3:07 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi:
>
>Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code
>(in an PL/SQL package) into one table call (instead of two)
>
>  select count(1) into count1 from isi.nametag where geneid=geneid1;
>  select count(1) into count2 from isi.nametag where geneid=geneid2;
>
>The following code works in sqlplus, but not in PL/SQL:
>
> select count(case when geneid=geneid1 then 1 else null end ) into count1,
>   count(case when geneid=geneid2 then 1 else null end ) into count2
>  from isi.nametag ;
>
>I have to use dynamic sql to get around this problem. But it's perofrmance
>is horrible.
>
>SQL> set serveroutput on
>SQL> declare
>  2    i  number:=0;
>  3str varchar2(200);
>  4   count1 number;
>  5count2 number;
>  6  begin
>  7str := 'select count(case when geneid=:x1 then 1 else null end ) ,
>  8  count(case when geneid=:x2 then 1 else null end )
>  9from isi.nametag';
> 10for x1 in 1 .. 10 Loop
> 11  for x2 in 20 .. 30 Loop
> 12 i := i +1;
> 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
> 14  end loop;
> 15end loop;
> 16dbms_output.put_line('i =' || i);
> 17  end;
> 18  /
>i =110
>
>PL/SQL procedure successfully completed.
>
>Elapsed: 00:00:10.96
>
>SQL> declare
>  2   i  number:=0;
>  3   count1 number;
>  4count2 number;
>  5  begin
>  6for x1 in 1 .. 100 Loop
>  7  for x2 in 200 .. 300 Loop
>  8i := i +1;
>  9   select count(1) into count1 from isi.nametag where geneid=x1;
> 10   select count(1) into count2 from isi.nametag where geneid=x2;
> 11  end loop;
> 12end loop;
> 13dbms_output.put_line('i =' || i);
> 14  end;
> 15  /
>i =10100
>
>PL/SQL procedure successfully completed.
>
>Elapsed: 00:00:04.06
>
>Is there a better way to optimize the orginal code?  TIA.
>
>Guang Mei
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net 
>--
>Author: gmei
>  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: Darrell Landrum
  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: select count(case ...) slow in PL/SQL, any better way?

[Daniel W. Fink]

CASE appeared (with little fanfare) in 8.1.6. However, until Oracle9, it
was not available in PL/SQL, when they integrated the PL/SQL engine into
the kernel.

Darrell Landrum wrote:

  I thought case in PL/SQL was not available until 9i.  I'll have to look that up.


  
  

  
[EMAIL PROTECTED] 02/24/03 04:38PM >>>

  

  
  I too faced the problem of case not working in pl/sql & procedures. So I
created a view.

Would creating a view work for you ?

-Original Message-
Sent: Monday, February 24, 2003 3:07 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code
(in an PL/SQL package) into one table call (instead of two)

  select count(1) into count1 from isi.nametag where geneid=geneid1;
  select count(1) into count2 from isi.nametag where geneid=geneid2;

The following code works in sqlplus, but not in PL/SQL:

 select count(case when geneid=geneid1 then 1 else null end ) into count1,
   count(case when geneid=geneid2 then 1 else null end ) into count2
  from isi.nametag ;

I have to use dynamic sql to get around this problem. But it's perofrmance
is horrible.

SQL> set serveroutput on
SQL> declare
  2i  number:=0;
  3str varchar2(200);
  4   count1 number;
  5count2 number;
  6  begin
  7str := 'select count(case when geneid=:x1 then 1 else null end ) ,
  8  count(case when geneid=:x2 then 1 else null end )
  9from isi.nametag';
 10for x1 in 1 .. 10 Loop
 11  for x2 in 20 .. 30 Loop
 12 i := i +1;
 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
 14  end loop;
 15end loop;
 16dbms_output.put_line('i =' || i);
 17  end;
 18  /
i =110

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.96

SQL> declare
  2   i  number:=0;
  3   count1 number;
  4count2 number;
  5  begin
  6for x1 in 1 .. 100 Loop
  7  for x2 in 200 .. 300 Loop
  8i := i +1;
  9   select count(1) into count1 from isi.nametag where geneid=x1;
 10   select count(1) into count2 from isi.nametag where geneid=x2;
 11  end loop;
 12end loop;
 13dbms_output.put_line('i =' || i);
 14  end;
 15  /
i =10100

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.06

Is there a better way to optimize the orginal code?  TIA.

Guang Mei


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
--
Author: gmei
  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: select count(case ...) slow in PL/SQL, any better way?

[Darrell Landrum]

I thought case in PL/SQL was not available until 9i.  I'll have to look that up.


>>> [EMAIL PROTECTED] 02/24/03 04:38PM >>>
I too faced the problem of case not working in pl/sql & procedures. So I
created a view.

Would creating a view work for you ?

-Original Message-
Sent: Monday, February 24, 2003 3:07 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code
(in an PL/SQL package) into one table call (instead of two)

  select count(1) into count1 from isi.nametag where geneid=geneid1;
  select count(1) into count2 from isi.nametag where geneid=geneid2;

The following code works in sqlplus, but not in PL/SQL:

 select count(case when geneid=geneid1 then 1 else null end ) into count1,
   count(case when geneid=geneid2 then 1 else null end ) into count2
  from isi.nametag ;

I have to use dynamic sql to get around this problem. But it's perofrmance
is horrible.

SQL> set serveroutput on
SQL> declare
  2i  number:=0;
  3str varchar2(200);
  4   count1 number;
  5count2 number;
  6  begin
  7str := 'select count(case when geneid=:x1 then 1 else null end ) ,
  8  count(case when geneid=:x2 then 1 else null end )
  9from isi.nametag';
 10for x1 in 1 .. 10 Loop
 11  for x2 in 20 .. 30 Loop
 12 i := i +1;
 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
 14  end loop;
 15end loop;
 16dbms_output.put_line('i =' || i);
 17  end;
 18  /
i =110

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.96

SQL> declare
  2   i  number:=0;
  3   count1 number;
  4count2 number;
  5  begin
  6for x1 in 1 .. 100 Loop
  7  for x2 in 200 .. 300 Loop
  8i := i +1;
  9   select count(1) into count1 from isi.nametag where geneid=x1;
 10   select count(1) into count2 from isi.nametag where geneid=x2;
 11  end loop;
 12end loop;
 13dbms_output.put_line('i =' || i);
 14  end;
 15  /
i =10100

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.06

Is there a better way to optimize the orginal code?  TIA.

Guang Mei


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
--
Author: gmei
  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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  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: select count(case ...) slow in PL/SQL, any better way?

[Sarnowski, Chris]

Upon further thought, what are you trying to gain by this "optimization"?
If these are primary key values, you will do a unique index lookup and
they should both be very fast. If you are doing millions of these, you
may want to rethink the whole algorithm (this said without knowing how you
are using the counts - and don't tell me because we are competitors).

-Chris

> -Original Message-
> From: gmei [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 24, 2003 4:07 PM
> To: Multiple recipients of list ORACLE-L
> Subject: select count(case ...) slow in PL/SQL, any better way? 
> 
> 
> Hi:
> 
> Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the 
> follwoing code
> (in an PL/SQL package) into one table call (instead of two)
> 
>   select count(1) into count1 from isi.nametag where geneid=geneid1;
>   select count(1) into count2 from isi.nametag where geneid=geneid2;
> 


LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. 
It is intended for the addressee(s) only. Access to this e-mail by anyone else is 
unauthorized. If you are not an addressee, any disclosure or copying of the contents 
or any action taken (or not taken) in reliance on it is unauthorized and may be 
unlawful. If you are not an addressee, please inform the sender immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  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: select count(case ...) slow in PL/SQL, any better way?

[Sarnowski, Chris]

responses to 2 posts: comments below each post.

> -Original Message-
> From: gmei [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 24, 2003 4:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: select count(case ...) slow in PL/SQL, any better way? 
> 
> 
> Hi:
> 
> Just after I sent my original message, it occured to me that 
> I could use
> this to "optimize" the sql (gneid is the PK column of the table):
> 
> select count(A.geneid), count(B.geneid) into count1, count2
> from isi.nametag A , isi.nametag B where A.geneid=geneid1 and
> B.geneid=geneid2;
> 
> Guang
> 

This won't be good because it will do a cartesian join of the tables.

SQL> select * from test_user.cps1;

ID NAME
--  
 1 blah
 1 blah
 2 blah2
 2 blah2
     2 blah2

SQL> select count(id) from test_user.cps1 where id = 1;

 COUNT(ID) 
--
 2 

SQL> select count(a.id), count(b.id)
 from  test_user.cps1 a, test_user.cps1 b
 where a.id = 1 and b.id = 2;

COUNT(A.ID) COUNT(B.ID)
--- ---
  6   6

If it is the primary key you won't get the wrong numbers
but that is more or less by accident.

And as Stephane Faroult points out, it will fail altogether
if one of the values doesn't exist (but you won't know which one
doesn't exist).

> -Original Message-
> From: Mark Richard [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 24, 2003 5:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: select count(case ...) slow in PL/SQL, any better way?
> 
> 
> Hi,
> 
> How about a statement like
> 
> select count(decode(geneid, geneid1, 1, 0)),
>  count(decode(geneid, geneid2, 1, 0))
> into count1, count2
> from isi.nametag
> 
> This should produce the same result I believe, and is one 
> single simple SQL
> statement so PL/SQL should eat it up just fine.
> 
> Regards,
>  Mark.
>

This is more the solution I was going to recommend but
I'd do this instead:

select sum(decode(geneid, geneid1, 1, 0)),
 sum(decode(geneid, geneid2, 1, 0))
into count1, count2
from isi.nametag
where geneid in (geneid1, geneid2);

If these are primary key values you are really just
looking for existence, in this case 'count' would work
just as well, but you still want to avoid the full index scan.


-Chris





LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. 
It is intended for the addressee(s) only. Access to this e-mail by anyone else is 
unauthorized. If you are not an addressee, any disclosure or copying of the contents 
or any action taken (or not taken) in reliance on it is unauthorized and may be 
unlawful. If you are not an addressee, please inform the sender immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  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: select count(case ...) slow in PL/SQL, any better way?

[netmadcap]

I too faced the problem of case not working in pl/sql & procedures. So I
created a view.

Would creating a view work for you ?

-Original Message-
Sent: Monday, February 24, 2003 3:07 PM
To: Multiple recipients of list ORACLE-L


Hi:

Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code
(in an PL/SQL package) into one table call (instead of two)

  select count(1) into count1 from isi.nametag where geneid=geneid1;
  select count(1) into count2 from isi.nametag where geneid=geneid2;

The following code works in sqlplus, but not in PL/SQL:

 select count(case when geneid=geneid1 then 1 else null end ) into count1,
   count(case when geneid=geneid2 then 1 else null end ) into count2
  from isi.nametag ;

I have to use dynamic sql to get around this problem. But it's perofrmance
is horrible.

SQL> set serveroutput on
SQL> declare
  2i  number:=0;
  3str varchar2(200);
  4   count1 number;
  5count2 number;
  6  begin
  7str := 'select count(case when geneid=:x1 then 1 else null end ) ,
  8  count(case when geneid=:x2 then 1 else null end )
  9from isi.nametag';
 10for x1 in 1 .. 10 Loop
 11  for x2 in 20 .. 30 Loop
 12 i := i +1;
 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
 14  end loop;
 15end loop;
 16dbms_output.put_line('i =' || i);
 17  end;
 18  /
i =110

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.96

SQL> declare
  2   i  number:=0;
  3   count1 number;
  4count2 number;
  5  begin
  6for x1 in 1 .. 100 Loop
  7  for x2 in 200 .. 300 Loop
  8i := i +1;
  9   select count(1) into count1 from isi.nametag where geneid=x1;
 10   select count(1) into count2 from isi.nametag where geneid=x2;
 11  end loop;
 12end loop;
 13dbms_output.put_line('i =' || i);
 14  end;
 15  /
i =10100

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.06

Is there a better way to optimize the orginal code?  TIA.

Guang Mei


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
  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: select count(case ...) slow in PL/SQL, any better way?

[Mark Richard]

Hi,

How about a statement like

select count(decode(geneid, geneid1, 1, 0)),
 count(decode(geneid, geneid2, 1, 0))
into count1, count2
from isi.nametag

This should produce the same result I believe, and is one single simple SQL
statement so PL/SQL should eat it up just fine.

Regards,
 Mark.



   

"gmei" 

<[EMAIL PROTECTED]   To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
om>  cc:   

Sent by:     Subject: select count(case ...) slow in 
PL/SQL, any better way?   
[EMAIL PROTECTED]  
   
om 

   

   

25/02/2003 

08:07  

Please respond 

to ORACLE-L

   

   





Hi:

Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing
code
(in an PL/SQL package) into one table call (instead of two)

  select count(1) into count1 from isi.nametag where geneid=geneid1;
  select count(1) into count2 from isi.nametag where geneid=geneid2;

The following code works in sqlplus, but not in PL/SQL:

 select count(case when geneid=geneid1 then 1 else null end ) into count1,
   count(case when geneid=geneid2 then 1 else null end ) into
count2
  from isi.nametag ;

I have to use dynamic sql to get around this problem. But it's perofrmance
is horrible.

SQL> set serveroutput on
SQL> declare
  2i  number:=0;
  3str varchar2(200);
  4   count1 number;
  5count2 number;
  6  begin
  7str := 'select count(case when geneid=:x1 then 1 else null end ) ,
  8  count(case when geneid=:x2 then 1 else null end )
  9from isi.nametag';
 10for x1 in 1 .. 10 Loop
 11  for x2 in 20 .. 30 Loop
 12 i := i +1;
 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
 14  end loop;
 15end loop;
 16dbms_output.put_line('i =' || i);
 17  end;
 18  /
i =110

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.96

SQL> declare
  2   i  number:=0;
  3   count1 number;
  4count2 number;
  5  begin
  6for x1 in 1 .. 100 Loop
  7  for x2 in 200 .. 300 Loop
  8i := i +1;
  9   select count(1) into count1 from isi.nametag where geneid=x1;
 10   select count(1) into count2 from isi.nametag where geneid=x2;
 11  end loop;
 12end loop;
 13dbms_output.put_line('i =' || i);
 14  end;
 15  /
i =10100

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.06

Is there a better way to optimize the orginal code?  TIA.

Guang Mei


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
  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).




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
 

Re: select count(case ...) slow in PL/SQL, any better way?

[Stephane Faroult]

gmei wrote:
> 
> Hi:
> 
> Just after I sent my original message, it occured to me that I could use
> this to "optimize" the sql (gneid is the PK column of the table):
> 
> select count(A.geneid), count(B.geneid) into count1, count2
> from isi.nametag A , isi.nametag B where A.geneid=geneid1 and
> B.geneid=geneid2;
> 
> Guang
> 

Think harder. What happens when say geneid1 is not found ?

BTW this kind of existence test is quite often dispensable ...

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
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).

RE: select count(case ...) slow in PL/SQL, any better way?

[gmei]

Hi:

Just after I sent my original message, it occured to me that I could use
this to "optimize" the sql (gneid is the PK column of the table):

select count(A.geneid), count(B.geneid) into count1, count2
from isi.nametag A , isi.nametag B where A.geneid=geneid1 and
B.geneid=geneid2;

Guang

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of gmei
> Sent: Monday, February 24, 2003 4:07 PM
> To: Multiple recipients of list ORACLE-L
> Subject: select count(case ...) slow in PL/SQL, any better way?
>
>
> Hi:
>
> Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the
> follwoing code
> (in an PL/SQL package) into one table call (instead of two)
>
>   select count(1) into count1 from isi.nametag where geneid=geneid1;
>   select count(1) into count2 from isi.nametag where geneid=geneid2;
>
> The following code works in sqlplus, but not in PL/SQL:
>
>  select count(case when geneid=geneid1 then 1 else null end )
> into count1,
>count(case when geneid=geneid2 then 1 else null
> end ) into count2
>   from isi.nametag ;
>
> I have to use dynamic sql to get around this problem. But
> it's perofrmance
> is horrible.
>
> SQL> set serveroutput on
> SQL> declare
>   2i  number:=0;
>   3str varchar2(200);
>   4   count1 number;
>   5count2 number;
>   6  begin
>   7str := 'select count(case when geneid=:x1 then 1 else
> null end ) ,
>   8  count(case when geneid=:x2 then 1 else null end )
>   9from isi.nametag';
>  10for x1 in 1 .. 10 Loop
>  11  for x2 in 20 .. 30 Loop
>  12 i := i +1;
>  13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
>  14  end loop;
>  15end loop;
>  16dbms_output.put_line('i =' || i);
>  17  end;
>  18  /
> i =110
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:10.96
>
> SQL> declare
>   2   i  number:=0;
>   3   count1 number;
>   4count2 number;
>   5  begin
>   6for x1 in 1 .. 100 Loop
>   7  for x2 in 200 .. 300 Loop
>   8i := i +1;
>   9   select count(1) into count1 from isi.nametag where
> geneid=x1;
>  10   select count(1) into count2 from isi.nametag where
> geneid=x2;
>  11  end loop;
>  12end loop;
>  13dbms_output.put_line('i =' || i);
>  14  end;
>  15  /
> i =10100
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:04.06
>
> Is there a better way to optimize the orginal code?  TIA.
>
> Guang Mei
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: gmei
>   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: gmei
  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).

select count(case ...) slow in PL/SQL, any better way?

[gmei]

Hi:

Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code
(in an PL/SQL package) into one table call (instead of two)

  select count(1) into count1 from isi.nametag where geneid=geneid1;
  select count(1) into count2 from isi.nametag where geneid=geneid2;

The following code works in sqlplus, but not in PL/SQL:

 select count(case when geneid=geneid1 then 1 else null end ) into count1,
   count(case when geneid=geneid2 then 1 else null end ) into count2
  from isi.nametag ;

I have to use dynamic sql to get around this problem. But it's perofrmance
is horrible.

SQL> set serveroutput on
SQL> declare
  2i  number:=0;
  3str varchar2(200);
  4   count1 number;
  5count2 number;
  6  begin
  7    str := 'select count(case when geneid=:x1 then 1 else null end ) ,
  8  count(case when geneid=:x2 then 1 else null end )
  9from isi.nametag';
 10for x1 in 1 .. 10 Loop
 11  for x2 in 20 .. 30 Loop
 12 i := i +1;
 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
 14  end loop;
 15end loop;
 16dbms_output.put_line('i =' || i);
 17  end;
 18  /
i =110

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.96

SQL> declare
  2   i  number:=0;
  3   count1 number;
  4count2 number;
  5  begin
  6for x1 in 1 .. 100 Loop
  7  for x2 in 200 .. 300 Loop
  8i := i +1;
  9   select count(1) into count1 from isi.nametag where geneid=x1;
 10   select count(1) into count2 from isi.nametag where geneid=x2;
 11  end loop;
 12end loop;
 13dbms_output.put_line('i =' || i);
 14  end;
 15  /
i =10100

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.06

Is there a better way to optimize the orginal code?  TIA.

Guang Mei


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: gmei
  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: Count(*) last 30 seconds

[Ramon E. Estevez]

Title: Message



Tks 
Herman, will make that change.

  
  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: 
  Thursday, February 13, 2003 10:15 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Count(*) last 30 
  secondsLet's see ...Your DB_CACHE_SIZE is 
  16MB ==  2048 blocksThe table is approx 14,677 blocksThe 
  record-size is something like 800bytesEach multiblock read call will read 
  32-blocksYour DB_CACHE will be filled in less than 64 read-calls and 
  will have less than 1/7th of the table.Your server will certainly be 
  very busy doing physical reads for aFull-Table-Scan of this table 
  alone.Now, the only thing I can suggest is to increase your 
  DB_CACHE_SIZE significantly.16MB is too low for any activity in 
  Oracle.HemantAt 09:54 AM 12-02-03 -0800, you wrote:
  Hermant and Chitale, DB_FILE_MULTIBLOCK_READ_COUNT=32DB_CACHE_SIZE   big integer 
16777216DB_BLOCK_BUFFERS = 
0 Tablespace is 
LMT with a uniform size of 128 MB, DB not in archive mode is for a DW 
system. The time 
for the first run and the re-run last the 
same.  To my understanding the table has only one extent.  This query 
runs in about 7 seconds.  In my production DB runs inmediately that is 
in NT also but 8.1.7.  SELECT TABLESPACE_NAME, EXTENT_ID, BYTES/1048576, 
BLOCKSFROM DBA_EXTENTSWHERESEGMENT_NAME = 
'DM_VENTAS' TABLESPACE_NAME  EXTENT_ID BYTES/1048576 
BLOCKS-- -- - 
--DTMVENTAS   
0   
128  16384  TKS 

  -Original Message- 
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K 
  Chitale 
  Sent: Wednesday, February 12, 2003 8:59 AM 
  To: Multiple recipients of list ORACLE-L 
  Subject: RE: Count(*) last 30 seconds
  That's approx 100 records per blocks. 
  What is the value of DB_FILE_MULTIBLOCK_READ_COUNT ? 
  Also, what is the elapsed time for the query if you re-run the query 
  immediately ? 
  [the first run fetched everything in physical reads, the second run 
  should still 
  find some or most blocks in the SGA, unless the DB_CACHE_SIZE or 
  DB_BLOCK_BUFFERS 
  is very small]. 
  Hemant 
  At 05:18 AM 12-02-03 -0800, you wrote:
  
Hermant, Sergey 
 
The table has 13 columns, the PK is formed for the first 11. 
 
There is no deletion nor update, just inserts in the table.  I 
had truncated the tables sometimes testing the procedure that load the 
rows. 
 
This is the result with an auto trace. 
 
  COUNT(*) 
-- 
   1466196 
 
Execution Plan 
-- 
   0  SELECT STATEMENT 
Optimizer=CHOOSE (Cost=896 Card=1) 
   1    0   SORT (AGGREGATE) 
   2    1 TABLE 
ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) 
 
 
 
 
 
Statistics 
-- 
  0  
recursive calls 
  0  db 
block gets 
  14677  consistent gets 
  14644  physical reads 
  0  redo 
size 
    386  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 
 
  
-Original Message- 
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K 
Chitale 
Sent: Tuesday, February 11, 2003 10:24 PM 
To: Multiple recipients of list ORACLE-L 
Subject: Re: Count(*) last 30 seconds
You are doing Full-Table-Scans.
1.  What's the average row length ?  How many columns does 
the table have ? 
2.  How many "consistent gets" does the count(*) cause ? [ie, 
how many blocks does it actually have to read ?] 
3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE 
large enough to hold most of the 
blocks ?  What is the query-run-time if you re-run the query 
immediately again ?
Hemant 
At 08:19 AM 11-02-03 -0800, you wrote: 

      Hi list, 
  I issue a select count(*) from mytable and last 30 seconds. 
  
  The table has 1,466,196 records and were loaded with a batch 
  process, so they are in a countinous space. 
  I consider that time exagerated. 
  The TBS is LMT 

RE: Count(*) last 30 seconds

[Hemant K Chitale]

Let's see ...
Your DB_CACHE_SIZE is 16MB ==  2048 blocks
The table is approx 14,677 blocks
The record-size is something like 800bytes
Each multiblock read call will read 32-blocks
Your DB_CACHE will be filled in less than 64 read-calls and will have

less than 1/7th of the table.
Your server will certainly be very busy doing physical reads for a
Full-Table-Scan of this table alone.
Now, the only thing I can suggest is to increase your DB_CACHE_SIZE
significantly.
16MB is too low for any activity in Oracle.
Hemant
At 09:54 AM 12-02-03 -0800, you wrote:
Hermant
and Chitale,
 
DB_FILE_MULTIBLOCK_READ_COUNT=32
DB_CACHE_SIZE   big
integer 16777216
DB_BLOCK_BUFFERS =
0
 
Tablespace is LMT with a
uniform size of 128 MB, DB not in archive mode is for a DW
system.
 
The time for the first run and
the re-run last the same.
 
 
To my understanding the table
has only one extent.  This query runs in about 7 seconds.  In
my production DB runs inmediately that is in NT also but
8.1.7.
 
 
SELECT TABLESPACE_NAME,
EXTENT_ID, BYTES/1048576, BLOCKS
FROM DBA_EXTENTS
WHERE
SEGMENT_NAME = 'DM_VENTAS'
 
TABLESPACE_NAME 
EXTENT_ID BYTES/1048576 BLOCKS
-- -- - --
DTMVENTAS  
0  
128  16384
 
 
TKS

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
On Behalf Of Hemant K Chitale
Sent: Wednesday, February 12, 2003 8:59 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Count(*) last 30 seconds

That's approx 100 records per blocks.
What is the value of DB_FILE_MULTIBLOCK_READ_COUNT ?
Also, what is the elapsed time for the query if you re-run the query
immediately ?
[the first run fetched everything in physical reads, the second run
should still
find some or most blocks in the SGA, unless the DB_CACHE_SIZE or
DB_BLOCK_BUFFERS
is very small].
Hemant
At 05:18 AM 12-02-03 -0800, you
wrote:
Hermant, Sergey
 
The table has 13 columns, the PK is formed for the first 11.
 
There is no deletion nor update, just inserts in the table.  I
had truncated the tables sometimes testing the procedure that load the
rows.
 
This is the result with an auto trace.
 
  COUNT(*)
--
   1466196
 
Execution Plan
--
   0  SELECT STATEMENT
Optimizer=CHOOSE (Cost=896 Card=1)
   1    0   SORT (AGGREGATE)
   2    1 TABLE
ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)
 
 
 
 
 
Statistics
--
  0 
recursive calls
  0  db
block gets
  14677  consistent gets
  14644  physical reads
  0  redo
size
    386  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
 
  
-Original Message- 
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
On Behalf Of Hemant K Chitale 
Sent: Tuesday, February 11, 2003 10:24 PM 
To: Multiple recipients of list ORACLE-L 
Subject: Re: Count(*) last 30 seconds

You are doing Full-Table-Scans.

1.  What's the average row length ?  How many columns does
the table have ? 
2.  How many "consistent gets" does the count(*) cause
? [ie, how many blocks does it actually have to read ?] 
3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE
large enough to hold most of the 
blocks ?  What is the query-run-time if you re-run the query
immediately again ?

Hemant 
At 08:19 AM 11-02-03 -0800, you wrote:

Hi list, 
I issue a select count(*) from mytable and last 30 seconds.

The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space. 
I consider that time exagerated. 
The TBS is LMT with a Uniform size of 128 MB. 
The block size is 8MB, version 9.2.0.1.0 in Windows 2000. 
Where should I start looking ??? 
TIA 
Ramon E. Estevez 
[EMAIL PROTECTED] 
809-565-3121 
 
Hemant K Chitale 
My web site page is :  http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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). 
Hemant K Chitale
My web site page is :  http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fa

RE: Count(*) last 30 seconds

[Ramon E. Estevez]

Title: Message



Hermant and Chitale,
 
DB_FILE_MULTIBLOCK_READ_COUNT=32
DB_CACHE_SIZE   big integer 16777216
DB_BLOCK_BUFFERS = 0
 
Tablespace is LMT with a uniform size of 128 MB, DB not in archive mode 
is for a DW system.
 
The 
time for the first run and the re-run last the same.
 
 
To my 
understanding the table has only one extent.  This query runs in about 7 
seconds.  In my production DB runs inmediately that is in NT also but 
8.1.7.
 
 
SELECT TABLESPACE_NAME, EXTENT_ID, BYTES/1048576, BLOCKSFROM 
DBA_EXTENTSWHERESEGMENT_NAME = 'DM_VENTAS'
 
TABLESPACE_NAME  EXTENT_ID 
BYTES/1048576 BLOCKS-- -- 
- 
--DTMVENTAS   
0   
128  16384
 
 
TKS

  
  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: 
  Wednesday, February 12, 2003 8:59 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Count(*) last 30 
  secondsThat's approx 100 records per blocks.What 
  is the value of DB_FILE_MULTIBLOCK_READ_COUNT ?Also, what is the elapsed 
  time for the query if you re-run the query immediately ?[the first run 
  fetched everything in physical reads, the second run should stillfind some 
  or most blocks in the SGA, unless the DB_CACHE_SIZE or DB_BLOCK_BUFFERSis 
  very small].HemantAt 05:18 AM 12-02-03 -0800, you wrote:
  Hermant, Sergey The table has 13 columns, the PK is formed for the first 
11. There is no 
deletion nor update, just inserts in the table.  I had truncated the 
tables sometimes testing the procedure that load the 
rows. This is the 
result with an auto trace.   COUNT(*)--   
1466196 Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=896 
Card=1)   1    0   SORT 
(AGGREGATE)   2    1 
TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 
Card=1466196) Statistics--  
0  recursive 
calls  0  db 
block gets  14677  consistent 
gets  14644  physical 
reads  0  redo 
size    386  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   

  -Original Message- 
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K 
  Chitale 
  Sent: Tuesday, February 11, 2003 10:24 PM 
  To: Multiple recipients of list ORACLE-L 
  Subject: Re: Count(*) last 30 seconds
  You are doing Full-Table-Scans.
  1.  What's the average row length ?  How many columns does 
  the table have ? 
  2.  How many "consistent gets" does the count(*) cause ? [ie, how 
  many blocks does it actually have to read ?] 
  3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE 
  large enough to hold most of the 
  blocks ?  What is the query-run-time if you re-run the query 
  immediately again ?
  Hemant 
  At 08:19 AM 11-02-03 -0800, you wrote:
  
Hi list, 
 
I issue a select count(*) from mytable and last 30 seconds. 
 
The table has 1,466,196 records and were loaded with a batch 
process, so they are in a countinous space. 
 
I consider that time exagerated. 
 
The TBS is LMT with a Uniform size of 128 MB. 
 
The block size is 8MB, version 9.2.0.1.0 in Windows 2000. 
 
Where should I start looking ??? 
 
TIA 
 
Ramon E. Estevez 
[EMAIL PROTECTED] 
809-565-3121 
 
  Hemant K Chitale 
  My web site page is :  http://hkchital.tripod.com
  -- Please see the official ORACLE-L FAQ: http://www.orafaq.net 
  -- Author: Hemant K Chitale 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). 
  
  
  Hemant K ChitaleMy web site page is :  http://hkchital.tripod.com-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: 
  [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 
  http://www.fatcity.com San Diego, Cali

RE: Count(*) last 30 seconds

[Turner, Adrian A SITI-ITPSIE]

Title: Message




Ramon,
 
Our Win2k boxes get between 1000-2000 gets a 
second off a SAN. 

 
Are you using 
compressed folders to store your datafiles?
 
Whats 
Multi_block_read_count set to? 
Set MBRC to 32 (32x8K=256K). Make 
your extent sizes are divisable by 
256K to reduce 
gets
 
Regards
Adrian

  -Original Message-From: Broodbakker, Mario 
  [mailto:[EMAIL PROTECTED]]Sent: 12 February 2003 
  14:09To: Multiple recipients of list ORACLE-LSubject: 
  RE: Count(*) last 30 seconds
  That's not so bad: 14644 physical reads in 30 seconds..that's about 500 
  I/O sec. Depending on your disk layout that's pretty optimal, I 
  think.
   
  Mario
  
-Original Message-From: Ramon E. Estevez 
[mailto:[EMAIL PROTECTED]]Sent: woensdag 12 februari 
2003 14:19To: Multiple recipients of list 
ORACLE-LSubject: RE: Count(*) last 30 
seconds
Hermant, Sergey
 
The table has 13 columns, the PK is formed for the first 
11.
 
There is no deletion nor update, just inserts in the table.  I 
had truncated the tables sometimes testing the procedure that load the 
rows.
 
This is the result with an auto trace.
     
  COUNT(*)--   
1466196
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=896 
Card=1)   1    0   SORT 
(AGGREGATE)   2    1 
TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)
 
 
 
 
 
Statistics--  
0  recursive 
calls  0  db 
block gets  14677  consistent 
gets  14644  physical 
reads  0  redo 
size    386  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
 
 

  
  -Original Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K 
  ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: 
  Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 
  30 secondsYou are doing 
  Full-Table-Scans.1.  What's the average row length ?  
  How many columns does the table have ?2.  How many "consistent 
  gets" does the count(*) cause ? [ie, how many blocks does it actually have 
  to read ?]3.  Are all these Physical Reads ?  Is the 
  DB_CACHE_SIZE large enough to hold most of theblocks ?  What is 
  the query-run-time if you re-run the query immediately again 
  ?HemantAt 08:19 AM 11-02-03 -0800, you wrote:
  Hi 
    list, I issue a select 
count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with 
a batch process, so they are in a countinous 
space. I consider that 
time exagerated. The TBS 
is LMT with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 
2000. Where should I start 
looking ??? TIA Ramon E. 
Estevez[EMAIL PROTECTED]809-565-3121 
  Hemant K ChitaleMy web site page is :  http://hkchital.tripod.com-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 
  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: Count(*) last 30 seconds

[Broodbakker, Mario]

Title: Message



That's 
not so bad: 14644 physical reads in 30 seconds..that's about 500 I/O sec. 
Depending on your disk layout that's pretty optimal, I 
think.
 
Mario

  -Original Message-From: Ramon E. Estevez 
  [mailto:[EMAIL PROTECTED]]Sent: woensdag 12 februari 
  2003 14:19To: Multiple recipients of list 
  ORACLE-LSubject: RE: Count(*) last 30 seconds
  Hermant, Sergey
   
  The 
  table has 13 columns, the PK is formed for the first 11.
   
  There is no deletion nor update, just inserts in the table.  I had 
  truncated the tables sometimes testing the procedure that load the 
  rows.
   
  This is the result with an auto trace.
   
    COUNT(*)--   
  1466196
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=896 
  Card=1)   1    0   SORT 
  (AGGREGATE)   2    1 
  TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)
   
   
   
   
   
  Statistics--  
  0  recursive 
  calls  0  db 
  block gets  14677  consistent 
  gets  14644  physical 
  reads  0  redo 
  size    386  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
   
   
  

-Original Message-From: 
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K 
ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: 
Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 30 
secondsYou are doing 
Full-Table-Scans.1.  What's the average row length ?  How 
many columns does the table have ?2.  How many "consistent gets" 
does the count(*) cause ? [ie, how many blocks does it actually have to read 
?]3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE 
large enough to hold most of theblocks ?  What is the 
query-run-time if you re-run the query immediately again 
?HemantAt 08:19 AM 11-02-03 -0800, you wrote:
Hi 
  list, I issue a select 
  count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a 
  batch process, so they are in a countinous 
  space. I consider that time 
  exagerated. The TBS is LMT 
  with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 
  2000. Where should I start 
  looking ??? TIA Ramon E. 
  Estevez[EMAIL PROTECTED]809-565-3121 
Hemant K ChitaleMy web site page is :  http://hkchital.tripod.com-- Please see the 
official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 
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: Count(*) last 30 seconds

[Hemant K Chitale]

That's approx 100 records per blocks.
What is the value of DB_FILE_MULTIBLOCK_READ_COUNT ?
Also, what is the elapsed time for the query if you re-run the query
immediately ?
[the first run fetched everything in physical reads, the second run
should still
find some or most blocks in the SGA, unless the DB_CACHE_SIZE or
DB_BLOCK_BUFFERS
is very small].
Hemant
At 05:18 AM 12-02-03 -0800, you wrote:
Hermant,
Sergey
 
The table has 13 columns, the
PK is formed for the first 11.
 
There is no deletion nor
update, just inserts in the table.  I had truncated the tables
sometimes testing the procedure that load the rows.
 
This is the result with an
auto trace.
 
  COUNT(*)
--
   1466196
 

Execution Plan
--
   0  SELECT STATEMENT
Optimizer=CHOOSE (Cost=896 Card=1)
   1    0   SORT (AGGREGATE)
   2    1 TABLE ACCESS
(FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)
 
 
 
 
 
Statistics
--
  0  recursive
calls
  0  db block
gets
  14677  consistent gets
  14644  physical reads
  0  redo
size
    386  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
 
 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
On Behalf Of Hemant K Chitale
Sent: Tuesday, February 11, 2003 10:24 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Count(*) last 30 seconds

You are doing Full-Table-Scans.

1.  What's the average row length ?  How many columns does
the table have ?
2.  How many "consistent gets" does the count(*) cause
? [ie, how many blocks does it actually have to read ?]
3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE
large enough to hold most of the
blocks ?  What is the query-run-time if you re-run the query
immediately again ?

Hemant
At 08:19 AM 11-02-03 -0800, you
wrote:
Hi list,
 
I issue a select count(*) from mytable and last 30 seconds.
 
The table has 1,466,196 records and were loaded with a batch process,
so they are in a countinous space.
 
I consider that time exagerated.
 
The TBS is LMT with a Uniform size of 128 MB.
 
The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
 
Where should I start looking ???
 
TIA
 
Ramon E. Estevez
[EMAIL PROTECTED]
809-565-3121
 
Hemant K Chitale
My web site page is : 
http://hkchital.tripod.com

-- Please see the official ORACLE-L FAQ:
http://www.orafaq.net
-- Author: Hemant K Chitale 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). 


Hemant K Chitale
My web site page is :  http://hkchital.tripod.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  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: Count(*) last 30 seconds

[Ramon E. Estevez]

Title: Message



Hermant, Sergey
 
The 
table has 13 columns, the PK is formed for the first 11.
 
There 
is no deletion nor update, just inserts in the table.  I had truncated the 
tables sometimes testing the procedure that load the rows.
 
This 
is the result with an auto trace.
 
  COUNT(*)--   1466196
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=896 
Card=1)   1    0   SORT 
(AGGREGATE)   2    1 TABLE 
ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196)
 
 
 
 
 
Statistics--  
0  recursive 
calls  0  db block 
gets  14677  consistent 
gets  14644  physical 
reads  0  redo 
size    386  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
 
 

  
  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: 
  Tuesday, February 11, 2003 10:24 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Re: Count(*) last 30 
  secondsYou are doing 
  Full-Table-Scans.1.  What's the average row length ?  How 
  many columns does the table have ?2.  How many "consistent gets" does 
  the count(*) cause ? [ie, how many blocks does it actually have to read 
  ?]3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE large 
  enough to hold most of theblocks ?  What is the query-run-time if you 
  re-run the query immediately again ?HemantAt 08:19 AM 11-02-03 
  -0800, you wrote:
  Hi 
list, I issue a select 
count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a 
batch process, so they are in a countinous space. I consider that time 
exagerated. The TBS is LMT 
with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 
2000. Where should I start 
looking ??? TIA Ramon E. 
Estevez[EMAIL PROTECTED]809-565-3121 
  Hemant K ChitaleMy web site page is :  http://hkchital.tripod.com-- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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[2]: Count(*) last 30 seconds

[Sergey Ten]

What could possibly happen is that the table experienced many
deletions followed by bulk inserts, and the value for high water mark
is way off the actual size of the data.

Once bulk insert via direct path load occurs, Oracle does not reuse
blocks in free lists, but allocates new ones instead, advancing the
value of high water mark (HWM).

After deletion the value of HWM doesn't get reset, and new batch
process that loads data advances it again. After several times of
inserting/deleting records, the number of blocks Oracle has to read
while doing full scan is much more than necessary.

It's easy to find out if that's the case. Try

  create table b as select * from a;

where "a" is the "slow" table.

After that try

  select count(*) from b;

and see how long does it take. If it happened quickly, the table "a" needs
to be reorganized.

Tuesday, February 11, 2003, 7:23:41 PM, you wrote:

HKC> Hemant
HKC> At 08:19 AM 11-02-03 -0800, you wrote:
HKC> Hi
HKC> list,
HKC>  
HKC> I issue a select count(*) from mytable and
HKC> last 30 seconds.
HKC>  
HKC> The table has 1,466,196 records and were
HKC> loaded with a batch process, so they are in a countinous
HKC> space.
HKC>  
HKC> I consider that time exagerated.
HKC>  
HKC> The TBS is LMT with a Uniform size of 128
HKC> MB.
HKC>  
HKC> The block size is 8MB, version 9.2.0.1.0 in
HKC> Windows 2000.
HKC>  
HKC> Where should I start looking ???
HKC>  
HKC> TIA
HKC>  
HKC> Ramon E. Estevez
HKC> [EMAIL PROTECTED]
HKC> 809-565-3121
HKC>  

HKC> Hemant K Chitale
HKC> My web site page is : http://hkchital.tripod.com

HKC> -- 
HKC> Please see the official ORACLE-L FAQ: http://www.orafaq.net



-- 
Best regards,
 Sergeymailto:[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sergey Ten
  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: Count(*) last 30 seconds

[Hemant K Chitale]

You are doing Full-Table-Scans.
1.  What's the average row length ?  How many columns does the
table have ?
2.  How many "consistent gets" does the count(*) cause ?
[ie, how many blocks does it actually have to read ?]
3.  Are all these Physical Reads ?  Is the DB_CACHE_SIZE large
enough to hold most of the
blocks ?  What is the query-run-time if you re-run the query
immediately again ?
Hemant
At 08:19 AM 11-02-03 -0800, you wrote:
Hi
list,
 
I issue a select count(*) from mytable and
last 30 seconds.
 
The table has 1,466,196 records and were
loaded with a batch process, so they are in a countinous
space.
 
I consider that time exagerated.
 
The TBS is LMT with a Uniform size of 128
MB.
 
The block size is 8MB, version 9.2.0.1.0 in
Windows 2000.
 
Where should I start looking ???
 
TIA
 
Ramon E. Estevez
[EMAIL PROTECTED]
809-565-3121
 

Hemant K Chitale
My web site page is : 
http://hkchital.tripod.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  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: Count(*) last 30 seconds

[Ramon E. Estevez]

Title: Message




Tks 
to all of you for your help.
 
The 
table has a PK that is formed by 11 fields of 13 that the table has.  This 
table is for a DW system.
 
This 
the only one in the table.
 
I 
analyze the table again, had done it before using 
dbms_stats.gather_schema_stats, and the time went down to 7 
seconds.
 
And 
there is no difference between count(*) and count(1), the same results with both 
of them.
 
tks 
to all
 

  
  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]] On Behalf Of Koivu, LisaSent: 
  Tuesday, February 11, 2003 11:40 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Count(*) last 30 seconds
  well, have you traced the statement yet?  That's 
  where to start, with autotrace.
   
  My 
  count(*) which executes a FTS (in a load to mitigate any ORA-1555 error) 
  takes 15 minutes to count 50 million rows.
   
  
  Lisa 
  Koivu Tired, Tired, 
  Tired. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. 
  Lauderdale, FL, USA  33063 Office: 
  954-935-4117  Fax:    
  954-935-3639 Cell:    
  954-683-4459 
  
-Original Message-From: Ramon E. Estevez 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 11, 
2003 11:19 AMTo: Multiple recipients of list 
ORACLE-LSubject: Count(*) last 30 seconds
Hi 
list,
 
I issue a 
select count(*) from mytable and last 30 seconds.
 
The table has 
1,466,196 records and were loaded with a batch process, so they are in 
a countinous space.
 
I consider 
that time exagerated.
 
The TBS is LMT 
with a Uniform size of 128 MB.
 
The block size 
is 8MB, version 9.2.0.1.0 in Windows 2000.
 
Where should I 
start looking ???
 
TIA
 
Ramon E. 
Estevez
[EMAIL PROTECTED]
809-565-3121
 

RE: Count(*) last 30 seconds

[Richard Ji]

It used to be different, but Oracle has long optimized count(*)
so it all does the same thing now.  What version of the doc did
you read this from?

Richard

-Original Message-
Sent: Tuesday, February 11, 2003 1:34 PM
To: Multiple recipients of list ORACLE-L


Because Oracle documentation says that count(1) has better performance than 
count(*).
I do not know why is it so. WIll it make clear some guru on this list?

JP

On Tuesday 11 February 2003 18:59, you wrote:
> Why use count(1) instead of count(*)?  They all does the same thing.
> So does count(primary key).
>
> Richard Ji
>
> -Original Message-
> Sent: Tuesday, February 11, 2003 12:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
>   1. create unique index or primary key   AND update statistics of the
> table
>   2. use count(1) instead of count(*)
>
> JP
>
> On Tuesday 11 February 2003 17:19, you wrote:
> > Hi list,
> >
> > I issue a select count(*) from mytable and last 30 seconds.
> >
> > The table has 1,466,196 records and were loaded with a batch process, so
> > they are in a countinous space.
> >
> > I consider that time exagerated.
> >
> > The TBS is LMT with a Uniform size of 128 MB.
> >
> > The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
> >
> > Where should I start looking ???
> >
> > TIA
> >
> > Ramon E. Estevez
> > [EMAIL PROTECTED]
> > 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: Richard Ji
  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: Count(*) last 30 seconds

[JayMiller]

Does it take the same amount of time if you run the count(*) a second time
(i.e., might it reading the entire table from disk the first time)?

Jay Miller

-Original Message-
Sent: Tuesday, February 11, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L


Hi,
1. create unique index or primary key   AND update statistics of the
table
2. use count(1) instead of count(*)

JP

On Tuesday 11 February 2003 17:19, you wrote:
> Hi list,
>
> I issue a select count(*) from mytable and last 30 seconds.
>
> The table has 1,466,196 records and were loaded with a batch process, so
> they are in a countinous space.
>
> I consider that time exagerated.
>
> The TBS is LMT with a Uniform size of 128 MB.
>
> The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
>
> Where should I start looking ???
>
> TIA
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: 
  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: Count(*) last 30 seconds

[Jan Pruner]

Because Oracle documentation says that count(1) has better performance than 
count(*).
I do not know why is it so. WIll it make clear some guru on this list?

JP

On Tuesday 11 February 2003 18:59, you wrote:
> Why use count(1) instead of count(*)?  They all does the same thing.
> So does count(primary key).
>
> Richard Ji
>
> -Original Message-
> Sent: Tuesday, February 11, 2003 12:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
>   1. create unique index or primary key   AND update statistics of the
> table
>   2. use count(1) instead of count(*)
>
> JP
>
> On Tuesday 11 February 2003 17:19, you wrote:
> > Hi list,
> >
> > I issue a select count(*) from mytable and last 30 seconds.
> >
> > The table has 1,466,196 records and were loaded with a batch process, so
> > they are in a countinous space.
> >
> > I consider that time exagerated.
> >
> > The TBS is LMT with a Uniform size of 128 MB.
> >
> > The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
> >
> > Where should I start looking ???
> >
> > TIA
> >
> > Ramon E. Estevez
> > [EMAIL PROTECTED]
> > 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: Count(*) last 30 seconds

[Toepke, Kevin M]

With Oracle 6 and sometimes in Oracle 7 one count was faster than the other.


With Oracle 8.0+ you'll see that count(1) == count(*) == count(unique
column) == count(not null column) 

Don't believe me? Try it. 

-Original Message-
Sent: Tuesday, February 11, 2003 12:55 PM
To: Multiple recipients of list ORACLE-L


Interestingly enough, I haven't seen an official statement on count(*) being
slower than count(). 

Sunil Nookala
DBA
Dell Corp.



-Original Message-
Sent: Tuesday, February 11, 2003 11:09 AM
To: Multiple recipients of list ORACLE-L


Hi,
1. create unique index or primary key   AND update statistics of the
table
    2. use count(1) instead of count(*)

JP

On Tuesday 11 February 2003 17:19, you wrote:
> Hi list,
>
> I issue a select count(*) from mytable and last 30 seconds.
>
> The table has 1,466,196 records and were loaded with a batch process, so
> they are in a countinous space.
>
> I consider that time exagerated.
>
> The TBS is LMT with a Uniform size of 128 MB.
>
> The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
>
> Where should I start looking ???
>
> TIA
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: 
  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: Toepke, Kevin M
  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: Count(*) last 30 seconds

[Rachel Carmichael]

If I remember correctly, there was a discussion here a while ago (like
a few years) on which was faster and I believe (caffeine levels are
low, so memory could be faulty) that it was determined that it made no
difference which you used.

This was pre 9i so things could have changed.


--- [EMAIL PROTECTED] wrote:
> Interestingly enough, I haven't seen an official statement on
> count(*) being
> slower than count(). 
> 
> Sunil Nookala
> DBA
> Dell Corp.
> 
> 
> 
> -Original Message-
> Sent: Tuesday, February 11, 2003 11:09 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi,
>   1. create unique index or primary key   AND update statistics of the
> table
>   2. use count(1) instead of count(*)
> 
> JP
> 
> On Tuesday 11 February 2003 17:19, you wrote:
> > Hi list,
> >
> > I issue a select count(*) from mytable and last 30 seconds.
> >
> > The table has 1,466,196 records and were loaded with a batch
> process, so
> > they are in a countinous space.
> >
> > I consider that time exagerated.
> >
> > The TBS is LMT with a Uniform size of 128 MB.
> >
> > The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
> >
> > Where should I start looking ???
> >
> > TIA
> >
> > Ramon E. Estevez
> > [EMAIL PROTECTED]
> > 809-565-3121
> 
> -- 
>  Pruner Jan
>[EMAIL PROTECTED]
>  http://jan.pruner.cz/
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jan Pruner
>   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: 
>   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).
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  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: Count(*) last 30 seconds

[Sutton, Reed]

Unfortunately I don't have the reference in front of my right now, but I
believe the optimizer has a built in way to understand a count(*) and it is
just as fast or faster than count(column_name).

-Original Message-
Sent: Tuesday, February 11, 2003 12:55 PM
To: Multiple recipients of list ORACLE-L


Interestingly enough, I haven't seen an official statement on count(*) being
slower than count(). 

Sunil Nookala
DBA
Dell Corp.



-Original Message-
Sent: Tuesday, February 11, 2003 11:09 AM
To: Multiple recipients of list ORACLE-L


Hi,
1. create unique index or primary key   AND update statistics of the
table
    2. use count(1) instead of count(*)

JP

On Tuesday 11 February 2003 17:19, you wrote:
> Hi list,
>
> I issue a select count(*) from mytable and last 30 seconds.
>
> The table has 1,466,196 records and were loaded with a batch process, so
> they are in a countinous space.
>
> I consider that time exagerated.
>
> The TBS is LMT with a Uniform size of 128 MB.
>
> The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
>
> Where should I start looking ???
>
> TIA
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: 
  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: Sutton, Reed
  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: Count(*) last 30 seconds

[Richard Ji]

Why use count(1) instead of count(*)?  They all does the same thing.
So does count(primary key).

Richard Ji

-Original Message-
Sent: Tuesday, February 11, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L


Hi,
1. create unique index or primary key   AND update statistics of the
table
2. use count(1) instead of count(*)

JP

On Tuesday 11 February 2003 17:19, you wrote:
> Hi list,
>
> I issue a select count(*) from mytable and last 30 seconds.
>
> The table has 1,466,196 records and were loaded with a batch process, so
> they are in a countinous space.
>
> I consider that time exagerated.
>
> The TBS is LMT with a Uniform size of 128 MB.
>
> The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
>
> Where should I start looking ???
>
> TIA
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: Richard Ji
  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: Count(*) last 30 seconds

[Sunil_Nookala]

Interestingly enough, I haven't seen an official statement on count(*) being
slower than count(). 

Sunil Nookala
DBA
Dell Corp.



-Original Message-
Sent: Tuesday, February 11, 2003 11:09 AM
To: Multiple recipients of list ORACLE-L


Hi,
1. create unique index or primary key   AND update statistics of the
table
2. use count(1) instead of count(*)

JP

On Tuesday 11 February 2003 17:19, you wrote:
> Hi list,
>
> I issue a select count(*) from mytable and last 30 seconds.
>
> The table has 1,466,196 records and were loaded with a batch process, so
> they are in a countinous space.
>
> I consider that time exagerated.
>
> The TBS is LMT with a Uniform size of 128 MB.
>
> The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
>
> Where should I start looking ???
>
> TIA
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: 
  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: Count(*) last 30 seconds

[Jan Pruner]

Hi,
1. create unique index or primary key   AND update statistics of the table
2. use count(1) instead of count(*)

JP

On Tuesday 11 February 2003 17:19, you wrote:
> Hi list,
>
> I issue a select count(*) from mytable and last 30 seconds.
>
> The table has 1,466,196 records and were loaded with a batch process, so
> they are in a countinous space.
>
> I consider that time exagerated.
>
> The TBS is LMT with a Uniform size of 128 MB.
>
> The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
>
> Where should I start looking ???
>
> TIA
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-565-3121

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: Count(*) last 30 seconds

[Koivu, Lisa]

Title: Message



well, 
have you traced the statement yet?  That's where to start, with 
autotrace.
 
My 
count(*) which executes a FTS (in a load to mitigate any ORA-1555 error) 
takes 15 minutes to count 50 million rows.
 

Lisa 
Koivu Tired, Tired, Tired. 
Fairfield Resorts, Inc. 5259 
Coconut Creek Parkway Ft. Lauderdale, FL, USA  
33063 Office: 954-935-4117  Fax:    954-935-3639 Cell:    954-683-4459 


  -Original Message-From: Ramon E. Estevez 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 11, 
  2003 11:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Count(*) last 30 seconds
  Hi 
  list,
   
  I issue a select 
  count(*) from mytable and last 30 seconds.
   
  The table has 
  1,466,196 records and were loaded with a batch process, so they are in a 
  countinous space.
   
  I consider that 
  time exagerated.
   
  The TBS is LMT 
  with a Uniform size of 128 MB.
   
  The block size 
  is 8MB, version 9.2.0.1.0 in Windows 2000.
   
  Where should I 
  start looking ???
   
  TIA
   
  Ramon E. 
  Estevez
  [EMAIL PROTECTED]
  809-565-3121
   

RE: Count(*) last 30 seconds

[DENNIS WILLIAMS]

Ramon - Start by adding an index? :-)
 
Do you have any indexes on this table? My first guess is that it is doing a
full table scan. This does seem to be a bit long for a full table scan. Do
you expect to do a lot of full table scans on this table? If so, then you
may want to look at how to tune the full table scan. Otherwise add an index.



Dennis Williams 
DBA, 40%OCP 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, February 11, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L


Hi list,
 
I issue a select count(*) from mytable and last 30 seconds.
 
The table has 1,466,196 records and were loaded with a batch process, so
they are in a countinous space.
 
I consider that time exagerated.
 
The TBS is LMT with a Uniform size of 128 MB.
 
The block size is 8MB, version 9.2.0.1.0 in Windows 2000.
 
Where should I start looking ???
 
TIA
 
Ramon E. Estevez
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
809-565-3121
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

Count(*) last 30 seconds

[Ramon E. Estevez]

Title: Message



Hi 
list,
 
I issue a select 
count(*) from mytable and last 30 seconds.
 
The table has 
1,466,196 records and were loaded with a batch process, so they are in a 
countinous space.
 
I consider that 
time exagerated.
 
The TBS is LMT 
with a Uniform size of 128 MB.
 
The block size is 
8MB, version 9.2.0.1.0 in Windows 2000.
 
Where should I 
start looking ???
 
TIA
 
Ramon E. 
Estevez
[EMAIL PROTECTED]
809-565-3121
 

Re: cant set multiblock read count > 8 !!!

[Stephane Faroult]

[EMAIL PROTECTED] wrote:
> 
> Rahul,
> I believe there is an O/S maximum setting of this parameter to be 64K  (this
> covers AIX and HP and and may affect others)
> This fits in with your finding so I am sure it is correct
> 
> John
> -Original Message-
> Sent: 12 November 2002 09:11
> To: Multiple recipients of list ORACLE-L
> 
> list !!
> my ora7.3.2 instance on AIX with a db_block_size of 4k has a
> multi block read count of 16 !!
> 
> i thought i would create a new instance with a block size of 8k, so i can
> set
> the max multiblock read count to more than 16, BUT, after creating the new
> instance
> the db_multiblock_read_count *always* defaults to 8 !!
> 
> here the new instance params
> 
> db_files = 16
> db_file_multiblock_read_count = 32
> db_block_buffers = 3840
> db_file_simultaneous_writes = 8
> db_block_lru_latches = 8
> shared_pool_size = 31457280
> 
> which of these parameter is affecting the value of multiblock read count ???
> 
> TIA
> Rahul
> 
> PS: i hv checked the multiblock reads using ixora script... always 8 !!
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rahul
>   INET: [EMAIL PROTECTED]
> 

Rahul,

   There is an extremely  interesting paper on Connor McDonald's site,

   http://www.oracledba.co.uk/tips/mbrc.htm

-- 
Regards,

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: cant set multiblock read count > 8 !!!

[DENNIS WILLIAMS]

Rahul
   Check out http://www.ixora.com.au/tips/max_multiblock_read.htm
Steve Adams mentions "The first trap to be aware of is that Oracle does not
complain about attempts to set the multiblock read count to values that are
larger than the maximum value allowed under the operating system"

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, November 12, 2002 3:11 AM
To: Multiple recipients of list ORACLE-L


list !!
my ora7.3.2 instance on AIX with a db_block_size of 4k has a 
multi block read count of 16 !! 

i thought i would create a new instance with a block size of 8k, so i can
set 
the max multiblock read count to more than 16, BUT, after creating the new
instance
the db_multiblock_read_count *always* defaults to 8 !! 

here the new instance params

db_files = 16
db_file_multiblock_read_count = 32
db_block_buffers = 3840
db_file_simultaneous_writes = 8
db_block_lru_latches = 8
shared_pool_size = 31457280

which of these parameter is affecting the value of multiblock read count ???


TIA
Rahul

PS: i hv checked the multiblock reads using ixora script... always 8 !!



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  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).

RE: cant set multiblock read count > 8 !!!

[John . Hallas]

Rahul,
I believe there is an O/S maximum setting of this parameter to be 64K  (this
covers AIX and HP and and may affect others)
This fits in with your finding so I am sure it is correct


John
-Original Message-
Sent: 12 November 2002 09:11
To: Multiple recipients of list ORACLE-L


list !!
my ora7.3.2 instance on AIX with a db_block_size of 4k has a 
multi block read count of 16 !! 

i thought i would create a new instance with a block size of 8k, so i can
set 
the max multiblock read count to more than 16, BUT, after creating the new
instance
the db_multiblock_read_count *always* defaults to 8 !! 

here the new instance params

db_files = 16
db_file_multiblock_read_count = 32
db_block_buffers = 3840
db_file_simultaneous_writes = 8
db_block_lru_latches = 8
shared_pool_size = 31457280

which of these parameter is affecting the value of multiblock read count ???


TIA
Rahul

PS: i hv checked the multiblock reads using ixora script... always 8 !!



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  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).

cant set multiblock read count > 8 !!!

[Rahul]

list !!
my ora7.3.2 instance on AIX with a db_block_size of 4k has a 
multi block read count of 16 !! 

i thought i would create a new instance with a block size of 8k, so i can
set 
the max multiblock read count to more than 16, BUT, after creating the new
instance
the db_multiblock_read_count *always* defaults to 8 !! 

here the new instance params

db_files = 16
db_file_multiblock_read_count = 32
db_block_buffers = 3840
db_file_simultaneous_writes = 8
db_block_lru_latches = 8
shared_pool_size = 31457280

which of these parameter is affecting the value of multiblock read count ???


TIA
Rahul

PS: i hv checked the multiblock reads using ixora script... always 8 !!



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  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: Record count within a procedure

[Jared Still]

SQL%rowcount
%rowcount

RTFM the PL/SQL Users Guide and Reference, available
at tahiti.oracle.com

Jared

Read the Application
On Monday 21 October 2002 14:23, Smith, Ron L. wrote:
> Within an update or load procedure, is there any way to return the
> transaction count for the number
> of rows affected?  Kind of like the Feedback / NoFeedback option of
> SQL*PLUS?
>
> Thanks!
> R. Smith
> If you are not the intended recipient of this e-mail message, any use,
> distribution or copying of the message is prohibited.  Please let me know
> immediately by return e-mail if you have received this message by mistake,
> then delete the e-mail message. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  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).

Record count within a procedure

[Smith, Ron L.]

Within an update or load procedure, is there any way to return the
transaction count for the number 
of rows affected?  Kind of like the Feedback / NoFeedback option of
SQL*PLUS?

Thanks!
R. Smith
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited.  Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Smith, Ron L.
  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: How to get the count of all tables using dbms_sql

[Charu Joshi]

If the database is 8i+, you can try 'EXECUTE IMMEDIATE' command. Does make
life somewhat simpler.

Regards,
Charu

-Original Message-
Ling Catherine (CSC)
Sent: Thursday, October 17, 2002 3:25 PM
To: Multiple recipients of list ORACLE-L

Hi,

I've found the answer.

DECLARE
countval NUMBER;
curidBINARY_INTEGER;
retval   NUMBER;
BEGIN

for i in (select OWNER,TABLE_NAME from DBA_TABLES WHERE ROWNUM) loop

  curid:= dbms_sql.open_cursor;
  dbms_sql.parse( curid, 'BEGIN SELECT count(*) INTO :cntval FROM '||
 i.owner||'.'||i.table_name||'; END;',  dbms_sql.v7 );
  dbms_sql.bind_variable( curid, 'cntval', countval );
  retval:= dbms_sql.execute( curid );
  dbms_sql.variable_value( curid, 'cntval', countval );
  dbms_sql.close_cursor( curid );
  dbms_output.put_line( 'Count is:= ' || countval );

end loop;

END;
/

Regds,
New Bee
-Original Message-
From:   CHAN Chor Ling Catherine (CSC)
Sent:   Thursday, October 17, 2002 4:12 PM
To:     '[EMAIL PROTECTED]'
Subject:How to get the count of all tables using
dbms_sql

Hi,

I need to insert the total number of records all the tables
into the table, MIGRATION_TABLE. I want to use the
    How do I obtain the count(*) into a variable in
"dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' ||i.owner||'.'||i.table_name,
dbms_sql.v7); " ?

TIA

Declare
   cid INTEGER;
BEGIN
  for i in (select OWNER,TABLE_NAME from DBA_TABLES) loop

-- Open new cursor and return cursor ID.
cid := dbms_sql.open_cursor;

   /* Parse and immediately execute dynamic SQL statement
built by
  concatenating table name to DROP TABLE command.
(Unlike DML
  statements, DDL statements are executed at parse
time.) */
   dbms_sql.parse(cid, 'SELECT COUNT(*) FROM '
||i.owner||'.'||i.table_name, dbms_sql.v7);

/* Close cursor. */
   dbms_sql.close_cursor(cid);

  end loop;

EXCEPTION
   /* If an exception is raised, close cursor before
exiting. */
   WHEN OTHERS THEN
  dbms_sql.close_cursor(cid);
END;

Regds,
New Bee
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CHAN Chor Ling Catherine (CSC)
  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).

*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charu Joshi
  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: How to get the count of all tables using dbms_sql

[CHAN Chor Ling Catherine (CSC)]

Hi,

I've found the answer.

DECLARE
countval NUMBER;
curidBINARY_INTEGER;
retval   NUMBER;
BEGIN

for i in (select OWNER,TABLE_NAME from DBA_TABLES WHERE ROWNUM) loop

  curid:= dbms_sql.open_cursor;
  dbms_sql.parse( curid, 'BEGIN SELECT count(*) INTO :cntval FROM '||
 i.owner||'.'||i.table_name||'; END;',  dbms_sql.v7 );
  dbms_sql.bind_variable( curid, 'cntval', countval );
  retval:= dbms_sql.execute( curid );
  dbms_sql.variable_value( curid, 'cntval', countval );
  dbms_sql.close_cursor( curid );
  dbms_output.put_line( 'Count is:= ' || countval );

end loop;

END;
/

Regds,
New Bee
-Original Message-
From:   CHAN Chor Ling Catherine (CSC) 
Sent:   Thursday, October 17, 2002 4:12 PM
To: '[EMAIL PROTECTED]'
Subject:How to get the count of all tables using
dbms_sql

Hi,

I need to insert the total number of records all the tables
into the table, MIGRATION_TABLE. I want to use the 
How do I obtain the count(*) into a variable in
"dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' ||i.owner||'.'||i.table_name,
dbms_sql.v7); " ?

TIA

Declare
   cid INTEGER; 
BEGIN 
  for i in (select OWNER,TABLE_NAME from DBA_TABLES) loop

-- Open new cursor and return cursor ID. 
cid := dbms_sql.open_cursor; 

   /* Parse and immediately execute dynamic SQL statement
built by 
  concatenating table name to DROP TABLE command.
(Unlike DML 
  statements, DDL statements are executed at parse
time.) */ 
   dbms_sql.parse(cid, 'SELECT COUNT(*) FROM '
||i.owner||'.'||i.table_name, dbms_sql.v7); 

/* Close cursor. */ 
   dbms_sql.close_cursor(cid); 

  end loop;

EXCEPTION 
   /* If an exception is raised, close cursor before
exiting. */ 
   WHEN OTHERS THEN 
  dbms_sql.close_cursor(cid); 
END;

Regds,
New Bee
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  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: How to get the count of all tables using dbms_sql

[Robson, Peter]

Its just possible that the script I presented at UK-OUG two years ago may
meet your requirements. It uses nested SQL*Plus (not pl/sql) to count as
many tables as you identify in a driver table, and store those results.

Check it out at http://peter-robson.port5.com/count.htm

The site will be subject to some modifications over the next couple of
months, but most of the stuff is there.

peter
edinburgh


> -Original Message-
> From: CHAN Chor Ling Catherine (CSC) [mailto:clchan@;nie.edu.sg]
> Sent: 17 October 2002 10:19
> To: Multiple recipients of list ORACLE-L
> Subject: How to get the count of all tables using dbms_sql
> 
> 
> Hi,
> 
> I need to insert the total number of records all the tables 
> into the table,
> MIGRATION_TABLE. I want to use the 
> How do I obtain the count(*) into a variable in 
> "dbms_sql.parse(cid, 'SELECT
> COUNT(*) FROM ' ||i.owner||'.'||i.table_name, dbms_sql.v7); " ?
> 
> TIA
> 
> Declare
>cid INTEGER; 
> BEGIN 
>   for i in (select OWNER,TABLE_NAME from DBA_TABLES) loop
> 
> -- Open new cursor and return cursor ID. 
> cid := dbms_sql.open_cursor; 
> 
>/* Parse and immediately execute dynamic SQL statement built by 
>   concatenating table name to DROP TABLE command.  (Unlike DML 
>   statements, DDL statements are executed at parse time.) */ 
>dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' 
> ||i.owner||'.'||i.table_name,
> dbms_sql.v7); 
> 
> /* Close cursor. */ 
>dbms_sql.close_cursor(cid); 
> 
>   end loop;
> 
> EXCEPTION 
>/* If an exception is raised, close cursor before exiting. */ 
>WHEN OTHERS THEN 
>   dbms_sql.close_cursor(cid); 
> END;
> 
> Regds,
> New Bee
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: CHAN Chor Ling Catherine (CSC)
>   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).
> 


*
This  e-mail   message,  and  any  files  transmitted   with  it, are
confidential  and intended  solely for the  use of the  addressee. If
this message was not addressed to  you, you have received it in error
and any  copying,  distribution  or  other use  of any part  of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not  necessarily represent  those of the British
Geological  Survey. The  security of e-mail  communication  cannot be
guaranteed and the BGS  accepts no liability  for claims arising as a
result of the use of this medium to  transmit messages from or to the
BGS. The BGS cannot accept any responsibility  for viruses, so please
scan all attachments.http://www.bgs.ac.uk
*

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robson, Peter
  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: How to get the count of all tables using dbms_sql

[Stephane Faroult]

"CHAN Chor Ling Catherine (CSC)" wrote:
> 
> Hi,
> 
> I need to insert the total number of records all the tables into the table,
> MIGRATION_TABLE. I want to use the
> How do I obtain the count(*) into a variable in "dbms_sql.parse(cid, 'SELECT
> COUNT(*) FROM ' ||i.owner||'.'||i.table_name, dbms_sql.v7); " ?
> 
> TIA
> 
> Declare
>cid INTEGER;
> BEGIN
>   for i in (select OWNER,TABLE_NAME from DBA_TABLES) loop
> 
> -- Open new cursor and return cursor ID.
> cid := dbms_sql.open_cursor;
> 
>/* Parse and immediately execute dynamic SQL statement built by
>   concatenating table name to DROP TABLE command.  (Unlike DML
>   statements, DDL statements are executed at parse time.) */
>dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' ||i.owner||'.'||i.table_name,
> dbms_sql.v7);
> 
> /* Close cursor. */
>dbms_sql.close_cursor(cid);
> 
>   end loop;
> 
> EXCEPTION
>/* If an exception is raised, close cursor before exiting. */
>WHEN OTHERS THEN
>   dbms_sql.close_cursor(cid);
> END;
> 
> Regds,
> New Bee
> --


Read DBMSQL.SQL (under $ORACLE_HOME/rdbms/admin), you have examples in
the comments.
-- 
Regards,

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).

How to get the count of all tables using dbms_sql

[CHAN Chor Ling Catherine (CSC)]

Hi,

I need to insert the total number of records all the tables into the table,
MIGRATION_TABLE. I want to use the 
How do I obtain the count(*) into a variable in "dbms_sql.parse(cid, 'SELECT
COUNT(*) FROM ' ||i.owner||'.'||i.table_name, dbms_sql.v7); " ?

TIA

Declare
   cid INTEGER; 
BEGIN 
  for i in (select OWNER,TABLE_NAME from DBA_TABLES) loop

-- Open new cursor and return cursor ID. 
cid := dbms_sql.open_cursor; 

   /* Parse and immediately execute dynamic SQL statement built by 
  concatenating table name to DROP TABLE command.  (Unlike DML 
  statements, DDL statements are executed at parse time.) */ 
   dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' ||i.owner||'.'||i.table_name,
dbms_sql.v7); 

/* Close cursor. */ 
   dbms_sql.close_cursor(cid); 

  end loop;

EXCEPTION 
   /* If an exception is raised, close cursor before exiting. */ 
   WHEN OTHERS THEN 
  dbms_sql.close_cursor(cid); 
END;

Regds,
New Bee
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  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: count(*)

[Naveen Nahata]

Title: RE: count(*)



I 
think this kind of huge volume of insertions can only take place if the 
application is doing a batch processing, so the issue of contention should not 
come.
 
if it 
is something like recording to page-hits or web-hits, then obviously the table 
will slow down the things a lot.
 
i 
don't have experience of huge DBs but 3 million rows an hour means 72 million 
rows in a day, which means 2.1 billion rows a month! this means it is recording 
some kind of transactions mostly web hits on a very popular web-site. what logic 
will force someone like to do a count(*) on such a 
table?
 
my gut 
feeling is that count(*) must be unnecessary, and simply because it is available 
easily in Informix(according to the poster), the application has somehow found a 
reason to use it
 
regards
naveen

  -Original Message-From: Craig Munday 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, September 23, 2002 
  5:29 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: count(*) 
  Hi 
  Depending on how the transactions are defined I do not think 
  that you will be able to achieve the throughput as the single row will be a 
  point of serialisation.
  Cheers, Craig. 
  -Original Message- From: 
  Naveen Nahata [mailto:[EMAIL PROTECTED]] 
  Sent: Saturday, 21 September 2002 12:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: count(*) 
  What about having a separate table with a single column and a 
  single row to store only the count, and increment and 
  decrement it using a row trigger on Insert and 
  deletes? 
  that way select count(*) will be very fast, the only ovehead 
  will of the trigger, which i think should be offset by 
  the performance gained by the select. 
  Regards naveen 
  -Original Message- Sent: 
  Friday, September 20, 2002 6:24 PM To: Multiple 
  recipients of list ORACLE-L 
  Rishi, 
  Do records get deleted from this table?  If not, you 
  could simply add an additional column that gets 
  populated by a sequence, add an index on that column, 
  and select max() from that column.  Even better, simply query 
  'select sequence_name,last_number from user_sequences' to get 
  the last value used.  You may need to check 
  whether sequence caching makes a difference with this 
  query. 
  Otherwise, Dennis gave some good advice. 
  Hope this helps. 
  Tom Mercadante Oracle Certified 
  Professional 
  -Original Message- Sent: 
  Thursday, September 19, 2002 5:04 PM To: Multiple 
  recipients of list ORACLE-L 
  Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' 
  Rishi - I've encountered this as well. I think the problem is 
  the fact that you are pounding millions of rows into 
  the table. When you ask for a count, Oracle won't give 
  you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your 
  query can actually slow performance. No, to my 
  knowledge Oracle doesn't maintain a record of the 
  number of rows in the table, my guess being that could become a performance bottleneck.    My 
  recommendation would be to ask very precisely what is to be achieved 
  with the count. As you noticed, the count will lag reality by 
  quite awhile. Perhaps the application could maintain 
  the count. I have quite a few batch programs that will 
  display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how 
  many segments are used and calculating. Just some 
  thoughts. 
  Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
  
  -Original Message- [mailto:[EMAIL PROTECTED]] 
  Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L 
  Hi Gurus, 
  In one of our insert intensive application we are inserting 
  around 3-4 million rows / hour. Also this app needs to 
  do a count(*) of the tables every 10 minutes for 
  verifying some application based logic. This is really killing us and it takes a lot of time. 
  Can you please guide me to a direction ( built in functions or 
  something similar). 
  Actually this app is being ported from Informix. Informix can 
  somehow keep a trak of the count(*) of a  table 
  in its header somewhere. 
  And yes I have tries count(1) , count(indexed_column) 
  etc. 
  Thanks In Advance. 
  R.h -- 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

RE: count(*)

[Craig Munday]

Title: RE: count(*)  





Hi


Depending on how the transactions are defined I do not think that you will be able to achieve the throughput as the single row will be a point of serialisation.

Cheers,
Craig.



-Original Message-
From: Naveen Nahata [mailto:[EMAIL PROTECTED]]
Sent: Saturday, 21 September 2002 12:04 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: count(*) 



What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?


that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.


Regards
naveen


-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L



Rishi,


Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.


Otherwise, Dennis gave some good advice.


Hope this helps.


Tom Mercadante
Oracle Certified Professional



-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L




Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'



Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.


Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 



-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L



Hi Gurus,


In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 


Can you please guide me to a direction ( built in functions or something
similar). 


Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.


And yes I have tries count(1) , count(indexed_column) etc.



Thanks In Advance.


R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  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

RE: count(*)

[Craig Munday]

Title: RE: count(*)



Hi,
 
I've 
read so many suggestions for making this count(*) quicker but we are all 
assuming that it is needed and has a purpose in the first place - which may or 
may not be the case.  Obviously the quickest way to do the count(*) is to 
NOT do it in the first place.
 
I'd 
still like to know the purpose of the count(*) and what verification it is 
performing?  And also, why are there so many rows being inserted into the 
table.
 
Cheers,Craig.
 

  -Original Message-From: Craig Munday 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, 20 September 2002 
  10:53 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: count(*)
  Hi, 
  I am not sure if I have a solution for you, but I have a 
  number of questions: 
  1) just for my interest, what type of application would need 
  to insert 3-4 million records per hour? 
  2) Why does the application need to do the select 
  count(*)?  What verification is being performed? 
  3) Perhaps the design of the application could change so the 
  number of records is reduced? 
  Cheers, Craig.   
  

RE: count(*)

[Jamadagni, Rajendra]

Title: RE: count(*)





Hmmm ... everyone is throwing their ideas around ... so here is mine ...


Hopefully on this large table you have a PK.


1. First time when you do the count(*) (I hope it will be > 10 minutes), do it as follows ...


select count(*), max(pk_column)
from my ludicrously_large_table
/


Somehow, remember the max(pk_column) value.


Next time onwards do ...
select count(*), max(pk_column)
from my ludicrously_large_table
where pk_column > previously_saved_max_pk_column_value
/


Then add this count(*) plus last count(*) to get the total number.


How is that ??


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!




*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: count(*)

[Viral Desai]

I think maintaining counts in other table (Naveen's approach) is more of an 
application issue. To avoid multiple processes waiting for a lock to update 
records in seperate table, you could have each process its dedicated row in 
a seperate table with the current count in it. A slight modified version of 
Naveen's approach to the problem is described below.

I'm interested in knowing any pitfalls with the following approach, Please 
don't hesitate to take a shot at this.

Thanks,
Viral

Lets say, P1, P2, P3, P4 ... Pn processes would insert large number of rows 
in large_table.

   -- Create a table called rcd_cnter or something like that.
create table rcd_cnter
  (
prcss_name varchar2(30) primary key,
recd_count number
   );
   -- Each Pi would drop and create its own seq. at the
  beginining of the process.

   -- At the beginning the Pi, it would
  use dbms_application_info.set_module to set
  the process name i.e. Pi,  to identify itself.
  This will be later available in v$session.module column to
  the trigger on the large_table.

   -- Create an after insert trigger on the large_table. The trigger
  would query v$seesion.module to identify the module/seq number
  to query, and then use that number and v$session.module to insert
  or update record in rcd_cntr;

   -- At any given time the number of records in the table would be
  arrived by following sql

  select sum(nvl(recd_cnter,0)) from rcd_cntr;



>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: count(*)  Date: Fri, 20 Sep 2002 08:33:37 -0800
>
>Naveen - This approach would probably work fine as long as only a single
>process was running. If multiple processes were inserting rows (likely at
>the scale of millions of rows/hour), this new table would probably be the
>bottleneck. Each process must acquire a lock on this row of this table, so
>the other processes must wait unnecessarily. Been there, done that. Very
>difficult to do what the user asks without degrading performance, which is
>probably why Oracle scales higher that Informix. Okay, cheap shot but worth
>mentioning again.
>
> 
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
>-Original Message-
>Sent: Friday, September 20, 2002 9:04 AM
>To: Multiple recipients of list ORACLE-L
>
>
>What about having a separate table with a single column and a single row to
>store only the count, and increment and decrement it using a row trigger on
>Insert and deletes?
>
>that way select count(*) will be very fast, the only ovehead will of the
>trigger, which i think should be offset by the performance gained by the
>select.
>
>Regards
>naveen
>
>-Original Message-
>Sent: Friday, September 20, 2002 6:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Rishi,
>
>Do records get deleted from this table?  If not, you could simply add an
>additional column that gets populated by a sequence, add an index on that
>column, and select max() from that column.  Even better, simply query
>'select sequence_name,last_number from user_sequences' to get the last 
>value
>used.  You may need to check whether sequence caching makes a difference
>with this query.
>
>Otherwise, Dennis gave some good advice.
>
>Hope this helps.
>
>Tom Mercadante
>Oracle Certified Professional
>
>
>-Original Message-
>Sent: Thursday, September 19, 2002 5:04 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>Sent: Thursday, September 19, 2002 2:48 PM
>To: '[EMAIL PROTECTED]'
>
>
>Rishi - I've encountered this as well. I think the problem is the fact that
>you are pounding millions of rows into the table. When you ask for a count,
>Oracle won't give you an approximate answer, but insists on giving you a
>precise answer as of the moment you hit return. You are right, your query
>can actually slow performance. No, to my knowledge Oracle doesn't maintain 
>a
>record of the number of rows in the table, my guess being that could become
>a performance bottleneck.
>    My recommendation would be to ask very precisely what is to be achieved
>with the count. As you noticed, the count will lag reality by quite awhile.
>Perhaps the application could maintain the count. I have quite a few batch
>programs that will display a running counter. If only an approximate count
>is needed, there may be an alternate method, like looking at how many
>segments are used and calculating. Just some thoughts.
>
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
>-Original Message-

Re: count(*)

[Jan Pruner]

Do you really need it?
In case of huge DELETE statement you can always recreate sequence with START 
WITH .

JP

On Friday 20 September 2002 19:04, you wrote:
> but sequences cannot be decremented by PREVVAL too, what about DELETIONS?!
>
> -Original Message-
> Sent: Friday, September 20, 2002 8:23 PM
> To: Multiple recipients of list ORACLE-L
>
>
> this is what Oracle invented SEQUENCES for!  :)
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -Original Message-
> Sent: Friday, September 20, 2002 10:04 AM
> To: Multiple recipients of list ORACLE-L
>
>
> What about having a separate table with a single column and a single row to
> store only the count, and increment and decrement it using a row trigger on
> Insert and deletes?
>
> that way select count(*) will be very fast, the only ovehead will of the
> trigger, which i think should be offset by the performance gained by the
> select.
>
> Regards
> naveen
>
> -Original Message-
> Sent: Friday, September 20, 2002 6:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Rishi,
>
> Do records get deleted from this table?  If not, you could simply add an
> additional column that gets populated by a sequence, add an index on that
> column, and select max() from that column.  Even better, simply query
> 'select sequence_name,last_number from user_sequences' to get the last
> value used.  You may need to check whether sequence caching makes a
> difference with this query.
>
> Otherwise, Dennis gave some good advice.
>
> Hope this helps.
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -Original Message-
> Sent: Thursday, September 19, 2002 5:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Sent: Thursday, September 19, 2002 2:48 PM
> To: '[EMAIL PROTECTED]'
>
>
> Rishi - I've encountered this as well. I think the problem is the fact that
> you are pounding millions of rows into the table. When you ask for a count,
> Oracle won't give you an approximate answer, but insists on giving you a
> precise answer as of the moment you hit return. You are right, your query
> can actually slow performance. No, to my knowledge Oracle doesn't maintain
> a record of the number of rows in the table, my guess being that could
> become a performance bottleneck.
>My recommendation would be to ask very precisely what is to be achieved
> with the count. As you noticed, the count will lag reality by quite awhile.
> Perhaps the application could maintain the count. I have quite a few batch
> programs that will display a running counter. If only an approximate count
> is needed, there may be an alternate method, like looking at how many
> segments are used and calculating. Just some thoughts.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 19, 2002 1:28 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Gurus,
>
> In one of our insert intensive application we are inserting around 3-4
> million rows / hour. Also this app needs to do a count(*) of the tables
> every 10 minutes for verifying some application based logic. This is really
> killing us and it takes a lot of time.
>
> Can you please guide me to a direction ( built in functions or something
> similar).
>
> Actually this app is being ported from Informix. Informix can somehow keep
> a trak of the count(*) of a  table in its header somewhere.
>
> And yes I have tries count(1) , count(indexed_column) etc.
>
>
> Thanks In Advance.
>
> R.h

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
  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: count(*)

[Mercadante, Thomas F]

Naveen,

Please read all the posts.  That is the first question I asked.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, September 20, 2002 1:04 PM
To: Multiple recipients of list ORACLE-L


but sequences cannot be decremented by PREVVAL too, what about DELETIONS?!

-Original Message-
Sent: Friday, September 20, 2002 8:23 PM
To: Multiple recipients of list ORACLE-L


this is what Oracle invented SEQUENCES for!  :)

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, September 20, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L


What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?

that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.

Regards
naveen

-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


Rishi,

Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.

Otherwise, Dennis gave some good advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L



Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 h

RE: count(*)

[DENNIS WILLIAMS]

Naveen - This approach would probably work fine as long as only a single
process was running. If multiple processes were inserting rows (likely at
the scale of millions of rows/hour), this new table would probably be the
bottleneck. Each process must acquire a lock on this row of this table, so
the other processes must wait unnecessarily. Been there, done that. Very
difficult to do what the user asks without degrading performance, which is
probably why Oracle scales higher that Informix. Okay, cheap shot but worth
mentioning again.

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
Sent: Friday, September 20, 2002 9:04 AM
To: Multiple recipients of list ORACLE-L


What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?

that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.

Regards
naveen

-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


Rishi,

Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.

Otherwise, Dennis gave some good advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L



Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.o

RE: count(*)

[Naveen Nahata]

but sequences cannot be decremented by PREVVAL too, what about DELETIONS?!

-Original Message-
Sent: Friday, September 20, 2002 8:23 PM
To: Multiple recipients of list ORACLE-L


this is what Oracle invented SEQUENCES for!  :)

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, September 20, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L


What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?

that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.

Regards
naveen

-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


Rishi,

Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.

Otherwise, Dennis gave some good advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L



Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  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: count(*)

[Paula_Stankus]

Title: RE: count(*)  





That is the best advice yet.  Duh for the rest of us.


-Original Message-
From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 10:53 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: count(*) 



this is what Oracle invented SEQUENCES for!  :)


Tom Mercadante
Oracle Certified Professional



-Original Message-
Sent: Friday, September 20, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L



What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?


that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.


Regards
naveen


-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L



Rishi,


Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.


Otherwise, Dennis gave some good advice.


Hope this helps.


Tom Mercadante
Oracle Certified Professional



-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L




Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'



Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.


Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 



-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L



Hi Gurus,


In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 


Can you please guide me to a direction ( built in functions or something
similar). 


Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.


And yes I have tries count(1) , count(indexed_column) etc.



Thanks In Advance.


R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California    -- Mailing list and web hosting services
---

RE: count(*)

[Gogala, Mladen]

There is a difference between 10 and 120 minutes, especially on Friday.

> -Original Message-
> From: Anjo Kolk [mailto:[EMAIL PROTECTED]]
> Sent: Friday, September 20, 2002 3:03 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: count(*)
> 
> 
> If you can live with 10 minutes, why not 60 minutes or 120 minutes ?
> Examine why this app feels so insecure that it needs to know 
> the number of 
> rows every 10 minutes.
> 
> 
> On Thursday 19 September 2002 20:28, you wrote:
> > Hi Gurus,
> >
> > In one of our insert intensive application we are inserting 
> around 3-4
> > million rows / hour. Also this app needs to do a count(*) 
> of the tables
> > every 10 minutes for verifying some application based 
> logic. This is really
> > killing us and it takes a lot of time.
> >
> > Can you please guide me to a direction ( built in functions 
> or something
> > similar).
> >
> > Actually this app is being ported from Informix. Informix 
> can somehow keep
> > a trak of the count(*) of a  table in its header somewhere.
> >
> > And yes I have tries count(1) , count(indexed_column) etc.
> >
> >
> > Thanks In Advance.
> >
> > R.h
> 
> -- 
> 
> Anjo Kolk
> http://www.oraperf.com
> 
> 
> -- 
> 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: Gogala, Mladen
  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: count(*)

[Mercadante, Thomas F]

this is what Oracle invented SEQUENCES for!  :)

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, September 20, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L


What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?

that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.

Regards
naveen

-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


Rishi,

Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.

Otherwise, Dennis gave some good advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L



Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  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

Re: count(*)

[Alexandre Gorbatchev]

Tom,

The sequences are transaction independant, so the trick with 'select
sequence_name,last_number from user_sequences' will only work if there are
no rollbacks after insert as well as no use of cache in sequence as you
mentioned.
Moreover, what if records get deleted? I guess this may be solved using
another trigger on delete selecting another sequence. Than count(*) may be
determined as a difference between two sequences. Again there should be no
rollbacks.

Rishi, I would also join the point of redesigning of the logic without use
of count(*).

Regards,
Alexandre

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 20, 2002 2:53 PM


> Rishi,
>
> Do records get deleted from this table?  If not, you could simply add an
> additional column that gets populated by a sequence, add an index on that
> column, and select max() from that column.  Even better, simply query
> 'select sequence_name,last_number from user_sequences' to get the last
value
> used.  You may need to check whether sequence caching makes a difference
> with this query.
>
> Otherwise, Dennis gave some good advice.
>
> Hope this helps.
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -Original Message-
> Sent: Thursday, September 19, 2002 5:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Sent: Thursday, September 19, 2002 2:48 PM
> To: '[EMAIL PROTECTED]'
>
>
> Rishi - I've encountered this as well. I think the problem is the fact
that
> you are pounding millions of rows into the table. When you ask for a
count,
> Oracle won't give you an approximate answer, but insists on giving you a
> precise answer as of the moment you hit return. You are right, your query
> can actually slow performance. No, to my knowledge Oracle doesn't maintain
a
> record of the number of rows in the table, my guess being that could
become
> a performance bottleneck.
>My recommendation would be to ask very precisely what is to be achieved
> with the count. As you noticed, the count will lag reality by quite
awhile.
> Perhaps the application could maintain the count. I have quite a few batch
> programs that will display a running counter. If only an approximate count
> is needed, there may be an alternate method, like looking at how many
> segments are used and calculating. Just some thoughts.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 19, 2002 1:28 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Gurus,
>
> In one of our insert intensive application we are inserting around 3-4
> million rows / hour. Also this app needs to do a count(*) of the tables
> every 10 minutes for verifying some application based logic. This is
really
> killing us and it takes a lot of time.
>
> Can you please guide me to a direction ( built in functions or something
> similar).
>
> Actually this app is being ported from Informix. Informix can somehow keep
a
> trak of the count(*) of a  table in its header somewhere.
>
> And yes I have tries count(1) , count(indexed_column) etc.
>
>
> Thanks In Advance.
>
> R.h
> --
> 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).
> --
> 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).
> --
> Please see the official ORA

RE: count(*)

[Naveen Nahata]

What about having a separate table with a single column and a single row to
store only the count, and increment and decrement it using a row trigger on
Insert and deletes?

that way select count(*) will be very fast, the only ovehead will of the
trigger, which i think should be offset by the performance gained by the
select.

Regards
naveen

-Original Message-
Sent: Friday, September 20, 2002 6:24 PM
To: Multiple recipients of list ORACLE-L


Rishi,

Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.

Otherwise, Dennis gave some good advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L



Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  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: Naveen Nahata
  INET: [EMAIL PROTECTED]

Fat Ci

RE: count(*)

[Mercadante, Thomas F]

Rishi,

Do records get deleted from this table?  If not, you could simply add an
additional column that gets populated by a sequence, add an index on that
column, and select max() from that column.  Even better, simply query
'select sequence_name,last_number from user_sequences' to get the last value
used.  You may need to check whether sequence caching makes a difference
with this query.

Otherwise, Dennis gave some good advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 19, 2002 5:04 PM
To: Multiple recipients of list ORACLE-L



Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  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: count(*)

[Anjo Kolk]

If you can live with 10 minutes, why not 60 minutes or 120 minutes ?
Examine why this app feels so insecure that it needs to know the number of 
rows every 10 minutes.


On Thursday 19 September 2002 20:28, you wrote:
> Hi Gurus,
>
> In one of our insert intensive application we are inserting around 3-4
> million rows / hour. Also this app needs to do a count(*) of the tables
> every 10 minutes for verifying some application based logic. This is really
> killing us and it takes a lot of time.
>
> Can you please guide me to a direction ( built in functions or something
> similar).
>
> Actually this app is being ported from Informix. Informix can somehow keep
> a trak of the count(*) of a  table in its header somewhere.
>
> And yes I have tries count(1) , count(indexed_column) etc.
>
>
> Thanks In Advance.
>
> R.h

-- 

Anjo Kolk
http://www.oraperf.com


--
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: count(*)

[Mladen Gogala]

If the table has a primary key, you can also combine the parallel hint with 
index_ffs on that primary key which is by far the fastest way of doing count(*).

On 2002.09.19 16:18 "Suri, Deepak" wrote:
> I have in the past used parallel hints to speed up a count(*) kind of full
> table scan query.
> 
> thanx
> deepak
> 
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 19, 2002 2:28 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi Gurus,
> 
> In one of our insert intensive application we are inserting around 3-4
> million rows / hour. Also this app needs to do a count(*) of the tables
> every 10 minutes for verifying some application based logic. This is really
> killing us and it takes a lot of time. 
> 
> Can you please guide me to a direction ( built in functions or something
> similar). 
> 
> Actually this app is being ported from Informix. Informix can somehow keep a
> trak of the count(*) of a  table in its header somewhere.
> 
> And yes I have tries count(1) , count(indexed_column) etc.
> 
> 
> Thanks In Advance.
> 
> R.h
> -- 
> 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).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Suri, Deepak
>   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
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: count(*)

[Craig Munday]

Title: RE: count(*)





Hi,


I am not sure if I have a solution for you, but I have a number of questions:


1) just for my interest, what type of application would need to insert 3-4 million records per hour?


2) Why does the application need to do the select count(*)?  What verification is being performed?


3) Perhaps the design of the application could change so the number of records is reduced?


Cheers,
Craig.
  


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Friday, 20 September 2002 9:53 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: count(*)




Paula et al:


It could work if the analyze is done after the load using DBMS_STATS. Then
the num_rows would be accurate.  With the monitoring turned on , analyzing
would be done on the
table if more than a certain percentage had changed.  It is not an elegant
solution.  A more pratical one , if you're using SQL Loader, is to use a
counter variable. If not using SQL Loader, then a synthetic key backed with
a trigger and sequence, could suffice.  It could give you the accurate
count after a load by checking the NEXTVAL.sequence_name or
CURRENTVAL.sequence_name.  The method could be put in a procedure to
improve efficiency.


Just a thought.


RWB

RE: count(*)

[Reginald . W . Bailey]

Paula et al:

It could work if the analyze is done after the load using DBMS_STATS. Then
the num_rows would be accurate.  With the monitoring turned on , analyzing
would be done on the
table if more than a certain percentage had changed.  It is not an elegant
solution.  A more pratical one , if you're using SQL Loader, is to use a
counter variable. If not using SQL Loader, then a synthetic key backed with
a trigger and sequence, could suffice.  It could give you the accurate
count after a load by checking the NEXTVAL.sequence_name or
CURRENTVAL.sequence_name.  The method could be put in a procedure to
improve efficiency.

Just a thought.

RWB




[EMAIL PROTECTED]@fatcity.com on 09/19/2002 05:38:24 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:




That won't work if he is constantly loading data as analyze is going to
take longer then just the count(*).  What about writing in the load process
or a trigger itself a variable with a running total could use dbms_output
to write it out to a file.  Starting with existing rowcount this would be
the fastest as you would really not have any additional I/O - just a
counter.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 5:34 PM
To: Multiple recipients of list ORACLE-L


Try this:

select table_name, num_rows from dba_tables where table_name
= [table_name];

[table_name] = the name of the table or a variable.
This works if you are updating your statistics and you may also want
monitoring turned on for the table.
It is easier to get the information from the data dictionary sometimes,
than actually doing a count of rows. This is Oracle8i or above, right?

RWB




[EMAIL PROTECTED]@fatcity.com on 09/19/2002 01:28:27 PM

Please respond to [EMAIL PROTECTED]


Sent by:  [EMAIL PROTECTED]

To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:

Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time.

Can you please guide me to a direction ( built in functions or something
similar).

Actually this app is being ported from Informix. Informix can somehow keep
a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.

Thanks In Advance.

R.h
--
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).


--
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).




--
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: count(*)

[JApplewhite]

If you can modify the application code, how about incrementing a sequence
for each row inserted?  Not to hamper performance with a "Select
MySequence.NextVal From Dual" you could select from - I vaguely remember
this from a thread a few weeks ago - x$dual (or something like that - I
can't remember how to set this up, maybe you could check the archives).

Then you could simply query User_Sequences.Last_Number every 10 minutes to
see how many rows you've inserted.

If you can't modify the application code, how about an After Insert Row
trigger on the table that would increment the sequence?

It might work for you.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715
[EMAIL PROTECTED]



   
   
Rishi.Jain@verizonwi   
   
reless.com To: Multiple recipients of list 
ORACLE-L   
Sent by:<[EMAIL PROTECTED]> 
   
[EMAIL PROTECTED]   cc: 
   
   Subject: count(*)   
   
   
   
09/19/2002 01:28 PM
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   




Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time.

Can you please guide me to a direction ( built in functions or something
similar).

Actually this app is being ported from Informix. Informix can somehow keep
a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.

Thanks In Advance.

R.h



-- 
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: count(*)

[Paula_Stankus]

Title: RE: count(*)





That won't work if he is constantly loading data as analyze is going to take longer then just the count(*).  What about writing in the load process or a trigger itself a variable with a running total could use dbms_output to write it out to a file.  Starting with existing rowcount this would be the fastest as you would really not have any additional I/O - just a counter.  

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 5:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: count(*)




Try this:


select table_name, num_rows from dba_tables where table_name
= [table_name];


[table_name] = the name of the table or a variable.
This works if you are updating your statistics and you may also want
monitoring turned on for the table.
It is easier to get the information from the data dictionary sometimes,
than actually doing a count of rows. This is Oracle8i or above, right?


RWB






[EMAIL PROTECTED]@fatcity.com on 09/19/2002 01:28:27 PM


Please respond to [EMAIL PROTECTED]




Sent by:  [EMAIL PROTECTED]



To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:



Hi Gurus,


In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time.


Can you please guide me to a direction ( built in functions or something
similar).


Actually this app is being ported from Informix. Informix can somehow keep
a
trak of the count(*) of a  table in its header somewhere.


And yes I have tries count(1) , count(indexed_column) etc.



Thanks In Advance.


R.h
--
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).




-- 
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: count(*)

[Mark J. Bobak]

That's certainly a possibility, but it still won't scale.  Regardless of
whether PQ is used, Oracle will need to do x consistent gets.  As the
tables grow larger, x will increase.  Regardless of whether PQ is used,
that number of buffer gets that are required will not change, and the
total load on the system will not change (except to increase as the
tables grow larger).  PQ may reduce elapsed time, but will do nothing
for the total resources consumed.

-Mark
On Thu, 2002-09-19 at 16:18, Suri, Deepak wrote:
> I have in the past used parallel hints to speed up a count(*) kind of full
> table scan query.
> 
> thanx
> deepak
> 
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 19, 2002 2:28 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi Gurus,
> 
> In one of our insert intensive application we are inserting around 3-4
> million rows / hour. Also this app needs to do a count(*) of the tables
> every 10 minutes for verifying some application based logic. This is really
> killing us and it takes a lot of time. 
> 
> Can you please guide me to a direction ( built in functions or something
> similar). 
> 
> Actually this app is being ported from Informix. Informix can somehow keep a
> trak of the count(*) of a  table in its header somewhere.
> 
> And yes I have tries count(1) , count(indexed_column) etc.
> 
> 
> Thanks In Advance.
> 
> R.h
-- 
--
Mark J. Bobak
Oracle DBA
[EMAIL PROTECTED]
"It is not enough to have a good mind.  The main thing is to use it
well."
-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  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: count(*)

[Reginald . W . Bailey]

Try this:

select table_name, num_rows from dba_tables where table_name
= [table_name];

[table_name] = the name of the table or a variable.
This works if you are updating your statistics and you may also want
monitoring turned on for the table.
It is easier to get the information from the data dictionary sometimes,
than actually doing a count of rows. This is Oracle8i or above, right?

RWB





[EMAIL PROTECTED]@fatcity.com on 09/19/2002 01:28:27 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time.

Can you please guide me to a direction ( built in functions or something
similar).

Actually this app is being ported from Informix. Informix can somehow keep
a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
--
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).



-- 
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: count(*)

[Mark J. Bobak]

Hi Rishi,

Oracle does not keep track of the total number of rows anywhere.  When
you need to do this, you need to issue a 'select count(*)...'.  Note
that unless ou are on a very old version of Oracle count(*), count(1),
and count(pk_column) will do the same thing.  

This is a design issue.  You need to re-design your applicaiton to not
require that count(*) every 10 minutes.  You're right, it's killing
you.  And, as the tables continue to increase in size, this will NOT
scale.  You'll reach a point where the amount of time required to
execute a count(*) exceeds the amount of time between calls to
count(*).  When that happens, it's all over.  And, actually, even well
before that happens, the frequency of the count(*) operation will
severely limit your scalability.  

Sorry I can't offer any better solutions.

Hope that helps,

-Mark
On Thu, 2002-09-19 at 14:28, [EMAIL PROTECTED] wrote:
> Hi Gurus,
> 
> In one of our insert intensive application we are inserting around 3-4
> million rows / hour. Also this app needs to do a count(*) of the tables
> every 10 minutes for verifying some application based logic. This is really
> killing us and it takes a lot of time. 
> 
> Can you please guide me to a direction ( built in functions or something
> similar). 
> 
> Actually this app is being ported from Informix. Informix can somehow keep a
> trak of the count(*) of a  table in its header somewhere.
> 
> And yes I have tries count(1) , count(indexed_column) etc.
> 
> 
> Thanks In Advance.
> 
> R.h
-- 
--
Mark J. Bobak
Oracle DBA
[EMAIL PROTECTED]
"It is not enough to have a good mind.  The main thing is to use it
well."
-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  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: count(*)

[DENNIS WILLIAMS]

Sent: Thursday, September 19, 2002 2:48 PM
To: '[EMAIL PROTECTED]'


Rishi - I've encountered this as well. I think the problem is the fact that
you are pounding millions of rows into the table. When you ask for a count,
Oracle won't give you an approximate answer, but insists on giving you a
precise answer as of the moment you hit return. You are right, your query
can actually slow performance. No, to my knowledge Oracle doesn't maintain a
record of the number of rows in the table, my guess being that could become
a performance bottleneck.
   My recommendation would be to ask very precisely what is to be achieved
with the count. As you noticed, the count will lag reality by quite awhile.
Perhaps the application could maintain the count. I have quite a few batch
programs that will display a running counter. If only an approximate count
is needed, there may be an alternate method, like looking at how many
segments are used and calculating. Just some thoughts.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
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).

RE: count(*)

[Suri, Deepak]

I have in the past used parallel hints to speed up a count(*) kind of full
table scan query.

thanx
deepak

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 2:28 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suri, Deepak
  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).

count(*)

[Rishi . Jain]

Hi Gurus,

In one of our insert intensive application we are inserting around 3-4
million rows / hour. Also this app needs to do a count(*) of the tables
every 10 minutes for verifying some application based logic. This is really
killing us and it takes a lot of time. 

Can you please guide me to a direction ( built in functions or something
similar). 

Actually this app is being ported from Informix. Informix can somehow keep a
trak of the count(*) of a  table in its header somewhere.

And yes I have tries count(1) , count(indexed_column) etc.


Thanks In Advance.

R.h
-- 
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: dbwr high count of threads

[David Miller]

Hi Richard,

Ok.  Since you are using filesystems (and I assume no directio on the ufs
filesystem), and have not specified asynch_io, the default is to have it on.
Because kaio (kernel asynch I/O) is not supported on ufs without directio or
vxfs without QIO, Oracle will be using libaio (library asynch I/O) instead.
The default number of worker threads in libaio is 256, so that's where your
258 threads count comes from.  So things are working perfectly normally.

Besides on any modern system 258 threads is not a "high number".

Hope this helps.

Dave Miller

>From: "Ji, Richard" <[EMAIL PROTECTED]>
>To: "'David Miller'" <[EMAIL PROTECTED]>
>Subject: RE: dbwr high count of threads
>Date: Tue, 10 Sep 2002 16:49:52 -0400
>
>Sorry I didn't provide all the details.
>It's Oracle 8.1.7.4 EE on Solaris 8.
>The development box is on UFS and Production on Veritas without QIO.
>Both are showing 258 LWP.
>I didn't specify ioslaves.  Both db_writer_proesses and db_block_lru_latches
>are using default.  Dev has 1 CPU, Prod has 2.
>"ps -edfL | grep dbw" shows 258 lwp under dbw0 process.  Top also shows
>258 threads.
>No the database is not on NFS.  We only used NFS to copy the datafiles
>during hotbackup.  It was a server panic, due to NFS.  Sun just confirmed it
>that it's a known NFS bug that under heavy usage, NFS could cause kernel
>panic.
>
>I am looking into KAIO as this seems to be related to those LWP threads.
>
>I was just wondering I am seeing it on all of my databases and I didn't
>set anything special.  Does other people see the samething on their plain
>vanilla database?
>
>-Original Message-
>From: David Miller [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, September 10, 2002 3:09 PM
>To: [EMAIL PROTECTED]
>Cc: [EMAIL PROTECTED]
>Subject: Re: dbwr high count of threads
>
>
>Hi Richard,
>
>A couple of questions. 
> 
>What version of Oracle?
>What version of Solaris? 
>Are you using asynch I/O?
>Are you on filesystems?  If so, which one (ufs, vxfs, vxfs with quickio)?
>Have you specified ioslaves?  If so, how many?
>How did you determine how many threads you were using?
>Is the entire database on NFS?
>Is the server crash a Solaris crash or and Oracle crash?
>
>Dave Miller
>
>>X-Unix-From: [EMAIL PROTECTED]  Mon Sep  9 16:26:57 2002
>>Date: Mon, 09 Sep 2002 14:18:24 -0800
>>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>>X-Comment: Oracle RDBMS Community Forum
>>X-Sender: "Ji, Richard" <[EMAIL PROTECTED]>
>>From: "Ji, Richard" <[EMAIL PROTECTED]>
>>Subject: dbwr high count of threads
>>X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
>>Mime-Version: 1.0
>>Content-Transfer-Encoding: 7bit
>>
>>Hi all,
>>
>>I noticed the dbwr process on Solaris has a very high number of threads
>>(258).
>>To me this is not a problem since I am seeing this on my small development
>>box too.
>>But we recently had some server crash and the consultant is saying Oracle
>is
>>consuming
>>a lots of resource, citing the high number of Oracle thread count from the
>>core dump analysis.
>>I don't believe this lead to the crash because the core stack trace points
>>to NFS calls in both
>>times.
>>
>>However, I don't know how to explain the high number thread count mostly
>>from the dbwr process.
>>Is this normal?  I mean, it looks like it's normal since I see this on all
>>of my instances.
>>How do I convince him that this is ok?
>>
>>Thanks for your help.
>>
>>Richard
>>-- 
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>-- 
>>Author: Ji, 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: David Miller
  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: dbwr high count of threads

[David Miller]

Hi Richard,

A couple of questions. 
 
What version of Oracle?
What version of Solaris? 
Are you using asynch I/O?
Are you on filesystems?  If so, which one (ufs, vxfs, vxfs with quickio)?
Have you specified ioslaves?  If so, how many?
How did you determine how many threads you were using?
Is the entire database on NFS?
Is the server crash a Solaris crash or and Oracle crash?

Dave Miller

>X-Unix-From: [EMAIL PROTECTED]  Mon Sep  9 16:26:57 2002
>Date: Mon, 09 Sep 2002 14:18:24 -0800
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "Ji, Richard" <[EMAIL PROTECTED]>
>From: "Ji, Richard" <[EMAIL PROTECTED]>
>Subject: dbwr high count of threads
>X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
>Mime-Version: 1.0
>Content-Transfer-Encoding: 7bit
>
>Hi all,
>
>I noticed the dbwr process on Solaris has a very high number of threads
>(258).
>To me this is not a problem since I am seeing this on my small development
>box too.
>But we recently had some server crash and the consultant is saying Oracle is
>consuming
>a lots of resource, citing the high number of Oracle thread count from the
>core dump analysis.
>I don't believe this lead to the crash because the core stack trace points
>to NFS calls in both
>times.
>
>However, I don't know how to explain the high number thread count mostly
>from the dbwr process.
>Is this normal?  I mean, it looks like it's normal since I see this on all
>of my instances.
>How do I convince him that this is ok?
>
>Thanks for your help.
>
>Richard
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Ji, 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: David Miller
  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).

dbwr high count of threads

[Ji, Richard]

Hi all,

I noticed the dbwr process on Solaris has a very high number of threads
(258).
To me this is not a problem since I am seeing this on my small development
box too.
But we recently had some server crash and the consultant is saying Oracle is
consuming
a lots of resource, citing the high number of Oracle thread count from the
core dump analysis.
I don't believe this lead to the crash because the core stack trace points
to NFS calls in both
times.

However, I don't know how to explain the high number thread count mostly
from the dbwr process.
Is this normal?  I mean, it looks like it's normal since I see this on all
of my instances.
How do I convince him that this is ok?

Thanks for your help.

Richard
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ji, 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: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?

[Casey A. Jordan]

I had the same issue in a 8.1.6 database and found that was bug related to
the cost based optimizer and materialized views.  Never did resolve it but
found that the problem occurred when the table in question had a
materialized view selecting data from in.

Thanks,
caj

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, August 30, 2002 9:23 AM


On
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
PL/SQL Release 8.1.7.2.0 - Production
CORE8.1.7.0.0   Production
TNS for Solaris: Version 8.1.7.2.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

I got a stange result and I wish to find an explanation.
Look
I did
SQL> select count(*) from ced_info_mouvement ;
 cls  COUNT(*)
--
 0

1 ligne sÚlectionnÚe.


Execution Plan
--
  0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)


  1  0
  SORT (AGGREGATE)


  2  1
PARTITION RANGE (ALL)


  3  2
  PARTITION HASH (ALL)


  4  3
INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2
Card=24507)






Statistics
--
  0  recursive calls
288  db block gets
120  consistent gets
  0  physical reads
  0  redo size
203  bytes sent via SQL*Net to client
248  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
1 rows processed

I tryed with and order by on the first column_name, I got the exact number
of rows in this table, why ?
SQL> select count(*) from ced_info_mouvement order by
IDENTIF_PRODUIT_COMPTABLE;
 cls  COUNT(*)
--
  5227

1 ligne sÚlectionnÚe.


Execution Plan
--
  0
SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17)


  1  0
  SORT (AGGREGATE)


  2  1
PARTITION RANGE (ALL)


  3  2
  PARTITION HASH (ALL)


  4  3
TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507
Bytes=416619)






Statistics
--
  0  recursive calls
116  db block gets
246  consistent gets
  0  physical reads
  0  redo size
206  bytes sent via SQL*Net to client
248  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
1 rows processed


I tryed vith HINTS FULL and I got  5227 rows.

Regards.


NAME  VALUE
-
-
active_instance_count
always_anti_join  NESTED_LOOPS
always_semi_join  standard
aq_tm_processes   0
audit_file_dest   ?/rdbms/audit
audit_trail   NONE
background_core_dump  partial
background_dump_dest  /sscedre/data/sqcedi/admin/log
backup_tape_io_slaves FALSE
bitmap_merge_area_size1048576
blank_trimmingFALSE
buffer_pool_keep
buffer_pool_recycle
commit_point_strength 1
compatible8.1.0
control_file_record_keep_time 7
control_files
/sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl,

/sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl,

/sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl

core_dump_dest?/dbs
cpu_count 4
create_bitmap_area_size   8388608
cursor_sharingEXACT
cursor_space_for_time FALSE
db_block_buffers  3200
db_block_checking FALSE
db_block_checksum FALSE
db_block_lru_latches  2
db_block_max_dirty_target 3200
db_block_size 8192
db_domain
db_file_direct_io_count   64
db_file_multiblock_read_count 32
db_file_name_convert
db_files  500
dblink_encrypt_login  FALSE
db_name   sqcedi
dbwr_io_slaves0
db_writer_processes   1
disk_asynch_ioTRUE
distributed_transactions  23
dml_loc

RE: select count(*) = 0, select /*+ full(a) */ count(*) = 5227 ?

[Fink, Dan]

There was a bug (still is in 8?) in 7.3 where a parallel index creation
would cause an index to be built with 0 entries. This would occur when the
index columns could be found in another index. The parallel master process
would get confused as to which index was the source and target. The end
result...an index with 0 entries.

I still fondly recall the panicked call from a developer 'I created an index
and it deleted all the rows!'.

-Original Message-
Sent: Friday, August 30, 2002 8:09 AM
To: Multiple recipients of list ORACLE-L
5227
?



MVT_ID_LOT_IDX is corrupt?  Does rebuilding the index fix the problem?


-Original Message-
Sent: Friday, August 30, 2002 9:23 AM
To: Multiple recipients of list ORACLE-L
?


On 
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
PL/SQL Release 8.1.7.2.0 - Production
CORE8.1.7.0.0   Production
TNS for Solaris: Version 8.1.7.2.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

I got a stange result and I wish to find an explanation.
Look
I did 
SQL> select count(*) from ced_info_mouvement ;
 cls  COUNT(*)
--
 0

1 ligne sÚlectionnÚe.


Execution Plan
--
  0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)


  1  0
  SORT (AGGREGATE)


  2  1
PARTITION RANGE (ALL)


  3  2
  PARTITION HASH (ALL)


  4  3
INDEX (FAST FULL SCAN) OF 'MVT_ID_LOT_IDX' (NON-UNIQUE) (Cost=2
Card=24507)






Statistics
--
  0  recursive calls
288  db block gets
120  consistent gets
  0  physical reads
  0  redo size
203  bytes sent via SQL*Net to client
248  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
1   rows processed

I tryed with and order by on the first column_name, I got the exact number
of rows in this table, why ?
SQL> select count(*) from ced_info_mouvement order by
IDENTIF_PRODUIT_COMPTABLE;
 cls  COUNT(*)
--
  5227

1 ligne sÚlectionnÚe.


Execution Plan
--
  0
SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17)


  1  0
  SORT (AGGREGATE)


  2  1
PARTITION RANGE (ALL)


  3  2
  PARTITION HASH (ALL)


  4  3
TABLE ACCESS (FULL) OF 'CED_INFO_MOUVEMENT' (Cost=19 Card=24507
Bytes=416619)






Statistics
--
  0  recursive calls
116  db block gets
246  consistent gets
  0  physical reads
  0  redo size
206  bytes sent via SQL*Net to client
248  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
1   rows processed


I tryed vith HINTS FULL and I got  5227 rows.

Regards.


NAME  VALUE
-
-
active_instance_count
always_anti_join  NESTED_LOOPS
always_semi_join  standard
aq_tm_processes   0
audit_file_dest   ?/rdbms/audit
audit_trail   NONE
background_core_dump  partial
background_dump_dest  /sscedre/data/sqcedi/admin/log
backup_tape_io_slaves FALSE
bitmap_merge_area_size1048576
blank_trimmingFALSE
buffer_pool_keep
buffer_pool_recycle
commit_point_strength 1
compatible8.1.0
control_file_record_keep_time 7
control_files
/sscedre/data/sqcedi/disk1/ctrl11sqcedi.ctl,
 
/sscedre/data/sqcedi/disk2/ctrl12sqcedi.ctl,
 
/sscedre/data/sqcedi/disk3/ctrl13sqcedi.ctl

core_dump_dest?/dbs
cpu_count 4
create_bitmap_area_size   8388608
cursor_sharingEXACT
cursor_space_for_time FALSE
db_block_buffers  3200
db_block_checking FALSE
db_block_checksum FALSE
db_block_lru_latches  2
db_block_max_dirty_target 3200
db_block_size 8192
db_domain
db_file_direct_io_count   64
db_file_multiblock_read_count 32
db_file_name_convert
db_files