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
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.
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
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
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
. 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
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
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
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
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
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.
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))
: 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
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
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
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
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
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
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
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
:
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
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
Vivek,
Your same question on LazyDBA list hasbeen 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
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
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
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
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
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
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
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
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
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
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
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 foll
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
-- -- - --
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
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
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
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
: 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
-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
-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
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 witha 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
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
Title: Message
well,
have you traced the statement yet? That's where to start, with
autotrace.
My
count(*) which executesa FTS (in a load to mitigate any ORA-1555 error)
takes 15 minutes to count 50 million rows.
Lisa
KoivuTired, Tired, Tired.
Fairfield Resorts, Inc. 5259
Coconut
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
Interestingly enough, I haven't seen an official statement on count(*) being
slower than count(column name).
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
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
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
PROTECTED] wrote:
Interestingly enough, I haven't seen an official statement on
count(*) being
slower than count(column name).
Sunil Nookala
DBA
Dell Corp.
-Original Message-
Sent: Tuesday, February 11, 2003 11:09 AM
To: Multiple recipients of list ORACLE-L
Hi,
1
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
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
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
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
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
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
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
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
!!
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
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
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
SQL%rowcount
cursorname%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
;
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
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
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
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
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
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
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
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
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
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
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
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
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
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
] 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
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
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
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
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
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
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
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
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
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
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
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
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
:
Subject: count(*)
09/19/2002 01:28 PM
, 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
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
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
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
: 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
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
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
Bernard
I think this may be a bug in early versions of 8.1.7. I don't know the
number, but I had a similar problem recently. Its to do with whether the
query is scanning an index or not. Try forcing the query to do a full scan:
-
SQL select /*full(ced_info_mouvement)*/ count(*) from
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
8.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
1 - 100 of 226 matches
Mail list logo
