Re: Server very slow

Hi Maciek,

A listing of the parameter file is not particularly helpful as it's got "it depends" smudged all over it. Neither is a diagnosis of a "server very slow". Just a tad ambiguous.

I personally would install the Statspack and generate a few snapshots of the database statistics. Then generate a report over a particularly busy period and see what's up. It's basically a health check of the database and should reveal much of what's going on. Trick is knowing what it all means, how to interpret it and what to look out for.

I would also investigate the top "X" statements (in terms of resources being used) that are running in your environment and explain the buggers. Note that the statspack report lists these for you, or you could go straight into the appropriate v$ views or you can use something like the OEM Diagnostics Pack or Toad to list them for you. Any particular statements or applications that you're not happy about get the explain treatment as well. The explain plan and associated statistics will tell all about these statements and what tuning might be necessary. By fixing and tuning these trouble makers, many of the unhealthy looking symptoms will disappear.

I would also look at the alert log and see if anything untoward is happening.

Unfortunately, tuning a database is not necessarily a simple problem to solve. Guess that's what makes it fun ...

And watch out, some clever sod is going to come out with SET RUN_FASTER=TRUE, just wait and see ...

Sorry I can't be more specific :(

Good Luck

"Maciek" <lokaj_at_poczta.onet.pl> wrote in message news:e13ef080.0210090341.1b522fc5_at_posting.google.com...
> Hi all. Bit stumped here (hopefully just a simple problem):
>
> os windows 2000 advanced server
> 6gb ram
> 4 x Xeon 700MHz
> Oracle v. 8.1.7
> ESRI sde
> RAID-5 160GB
>
> (initorcl.ora)
> background_dump_dest = e:\oracle\admin\orcl\bdump
> compatible = 8.1.7
> control_files = "e:\oracle\oradata\orcl\control01.ctl"
> control_files = "e:\oracle\oradata\orcl\control02.ctl"
> control_files = "e:\oracle\oradata\orcl\control03.ctl"
> db_block_buffers = 102066
> db_block_lru_latches = 2
> db_block_size = 8192
> db_file_multiblock_read_count = 12
> db_files = 1024
> db_name = orcl
> db_writer_processes = 2
> distributed_transactions = 10
> global_names = TRUE
> hash_area_size = 2097152
> instance_name = orcl
> java_pool_size = 20971520
> job_queue_interval = 60
> job_queue_processes = 4
> large_pool_size = 277872640
> log_archive_dest_1 = "LOCATION=c:\oracle\ora817\RDBMS"
> log_archive_start = TRUE
> log_buffer = 10485760
> log_checkpoint_interval = 10000
> log_checkpoint_timeout = 1800
> max_dump_file_size = 10240
> max_enabled_roles = 60
> mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
> mts_max_servers = 100
> nls_language = Polish
> nls_territory = Poland
> open_cursors = 1000
> open_links = 4
> oracle_trace_collection_name = ""
> os_authent_prefix = ""
> parallel_max_servers = 5
> processes = 400
> remote_login_passwordfile = EXCLUSIVE
> service_names = orcl
> sessions = 321
> shared_pool_size = 278710681
> sort_area_retained_size = 262144
> sort_area_size = 1048576
> user_dump_dest = e:\oracle\admin\orcl\udump
> utl_file_dir = e:\oracle\logs
>
> server very slow !!!
> have any suggestions on
> where I should be looking for possible problem ?
>
> Best Regards
> Maciek
Received on Wed Oct 09 2002 - 09:00:42 CDT