Re: Count(*) is very slow

My math skills might not be up to par, but I see a total SGA at 32MB. 3200 db blocks at 2k each + 25MB shared pool + log buffer. Anyway, somethings fishy if it takes 32 seconds to do a count(*). Is this done by SQLPLUS by any chance? I know there is a bug with version 8.0.3 and windows NT, where the count(*) returns the wrong value. Anyway, I'm rambling away... When you discover why it takes 32 seconds to do a Count(*), let the rest of us know.

Good Luck,
Satar

In article <70hg3e$dmd$1_at_news.ipf.net>,   "Andreas Kyritz" <AKyritz_at_easy-soft-dresden.de> wrote:
> Hallo Jerry
>
> Jerry Gitomer schrieb in Nachricht ...
> >Hi Andreas,
> >
> > The Oracle default database and database parameters will not give you
> >decent performance. The defaults appear to have been selected on the basis
> >of allowing you to use Oracle on a system without very much RAM. (Given
> the
> >price of RAM these days Oracle should probably revise their default
> >strategy.)
>
> The Oracle-process ist running with 75 MB of RAM.
>
> >
> > The first thing to do is to go into the init<sid>.ora file (If I
> >remember correctly it's in the ORANT\DBS directory) and change all of the
> >small/medium/large choices to the large values. Next if you have the space
> >double the data base buffer space.
>
> The values are mostly large (and more), but it still takes 36 seconds. 8-(
> The analyze command I've used was:
> ANALYZE TABLE a_table COMPUTE STATISTICS
>
> The primary key of the table is varchar(9). Can this be a problem ?
>
> I'll include my init<sid>.orafile, maybe somethings wrong with it:
> db_name = orc0
> db_files = 1024
> control_files = ("D:\orant\database\ctl1orc0.ora",
> "D:\orant\database\ctl2orc0.ora")
> db_file_multiblock_read_count = 32
> db_block_buffers = 3200
> shared_pool_size = 25000000
> log_checkpoint_interval = 10000
> processes = 59
> dml_locks = 100
> log_buffer = 163840
> sequence_cache_entries = 100
> sequence_cache_hash_buckets = 89
> background_dump_dest = D:\orant\rdbms80\trace
> user_dump_dest = D:\orant\rdbms80\trace
> db_block_size =2048
> compatible = 8.0.5.0.0
> sort_area_size = 655360
> log_checkpoint_timeout = 0
> remote_login_passwordfile = shared
> max_dump_file_size = 10240
>
> Thanks
>
> --
> Andreas Kyritz
> AKyritz_at_easy-soft-dresden.de
>
>

--
Oracle DBA/UNIX System Admin
Advanced Enterprise Solutions
(949) 756-0588
Oracle Re-Seller

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Oct 20 1998 - 18:19:24 CDT