Re: Problem with database 8.1.7. release 3 with patchset 2

In article <9tgalh$ijn$1_at_sunce.iskon.hr>, "Đuro Dretvić" <Djuro.Dretvic_at_infodom.hr> wrote:

> Greetings !!!
>
> Hardware: Pentium 3 800MHz, 1 GB RAM, 60 GB disk. (20 developers, working
> with designer and developer)
> System: Windows 2000 Server (service pack 2). Repository is on that
> database

What type of disk controller do you have SCSI? EIDE? For Oracle, SCSI is definitely better. If it is EIDE, is UDMA3 enabled? What kind of disks do you have? Do you have NG2IO boards? What kind of RAM? How much L2 cache do you have? What kind of network connection? 10baseT? 100baseT? FDDI? CPU speed and the amount of disk space cannot be used without other relevant information about the machine. All the details mentioned above can make a huge difference. The first thing to do is to go into the task manager, monitor performance of the machine and see what is happenening. Is there a CPU bottleneck (usually not), insufficient RAM (very infrequent indeed) or IO bottleneck (the most frequent case). Turn the timed statistics on and see what kind of an average response are you getting from your datafiles. Anything above 30ms is unacceptable.

>
> We have a problem with a database, it is too slow. (Any change in
> Designer 6i, COMMIT(Save option) takes at least 20 sek)
>
> SYSTEM TABLESPACE: 600MB
> TEMP TABLESPACE:300 MB
> USERS: 600 MB
> INDX: 400 MB
> RBS : 500MB
>
> What might be a problem ???? Thanks.
>
> This content of the init.ora file.
>
> db_name = "d817"
>
> db_domain = infosystem
>
> instance_name = d817
>
> service_names = d817.infosystem
>
> db_files = 1024
>
> control_files = ("C:\ora817\oradata\d817\control01.ctl",
> "C:\ora817\oradata\d817\control02.ctl",
> "c:\ora817\oradata\d817\control03.ctl")
>
> open_cursors = 2000
> max_enabled_roles = 147
> db_file_multiblock_read_count = 16
>
>
> #Defaultna vrijednost:db_block_buffers = 5120
> db_block_buffers = 10000

This is fairly small for a database used by CASE Des/2k. HAve you measured the hit rate (physical reads/(db_block_gets+consistent_gets) <0.09) ? What kind of hit rates do you get?

>
> #shared_pool_size = 134217728
> shared_pool_size = 157286400
>
> large_pool_size = 52428800
> java_pool_size = 36700160

Are you using Java? If not, switch that thing off.

>
> log_checkpoint_interval = 10240

Why are you doing this? How big are redo logs? Frequent log switches will cause frequent checkpoints ant checkpoints will keep your system occupied. Very occupied.

> log_checkpoint_timeout = 1800

Same question.

>
> processes = 200
>
> parallel_min_servers = 2
> parallel_max_servers = 5

Do you have multiple CPUs? If not, what do you need parallel query for?

>
> log_buffer = 10485760

This is relatively big. Did you check your database statistics (redo log space waits growing)? It doesn't hurt but I like doing thing for a reason.

>
> db_block_lru_latches = 4
> db_writer_processes = 5
>
> dml_locks = 250

VERY VERY LOW!!!! Monitor your locks and see how many "TX" locks do you have at one time. Increase by more then an order of magnitude! 16384 DML locks would be a good starting point.

>
>
> #audit_trail = true # if you want auditing timed_statistics = true # if
> you want timed statistics max_dump_file_size = 10240 # limit trace file
> size to 5M each
>
> # Uncommenting the line below will cause automatic archiving if archiving
> has
> # been enabled using ALTER DATABASE ARCHIVELOG. # log_archive_start =
> true
> log_archive_dest_1 = "location=D:\ora817\oradata\d817\archive" #
> log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
>
>
> #DBCA uses the default database value (30) for max_rollback_segments #100
> rollback segments (or more) may be required in the future #Uncomment the
> following entry when additional rollback segments are created and made
> online
> #max_rollback_segments = 41
> # If using private rollback segments, place lines of the following # form
> in each of your instance-specific init.ora files: #rollback_segments = (
> RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, RBS7, RBS8, RBS9, RBS10, RBS11,
> RBS12, RBS13, RBS14, RBS15, RBS16, RBS17, RBS18, RBS19, RBS20, RBS21,
> RBS22, RBS23, RBS24, RBS25, RBS26, RBS27, RBS28 )
>
> # Global Naming -- enforce that a dblink has same name as the db it
> connects to
> global_names = false
>
> # Uncomment the following line if you wish to enable the Oracle Trace
> product
> # to trace server activity. This enables scheduling of server
> collections # from the Oracle Enterprise Manager Console. # Also, if the
> oracle_trace_collection_name parameter is non-null, # every session will
> write to the named collection, as well as enabling you # to schedule
> future collections from the console. # oracle_trace_enable = true
>
> oracle_trace_collection_name = ""
> # define directories to store trace and alert files background_dump_dest
> = C:\ora817\admin\d817\bdump #Uncomment this parameter to enable resource
> management for your database. #The SYSTEM_PLAN is provided by default
> with the database. #Change the plan name if you have created your own
> resource plan. user_dump_dest = C:\ora817\admin\d817\udump
>
>
> db_block_size = 8192
>
> remote_login_passwordfile = shared

Do you have a parallel server? If not, set this thing to "none".

>
> os_authent_prefix = ""
>
> # The following parameters are needed for the Advanced Replication Option
> job_queue_processes = 2
> job_queue_interval = 60
> open_links = 4
>
> #hash_area_size = 2097152

Very, very small. Your hash joins will suffer.

>
> distributed_transactions = 10
> mts_dispatchers = "(protocol=TCP)(disp=4)(mul=ON)(con=15)"
> #mts_max_dispatchers = 20
> mts_servers = 10
> mts_max_servers = 25
>
> #local_listener = "listP817"
>
> #mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
> # Uncomment the following line when your listener is configured for SSL #
> (listener.ora and sqlnet.ora)
> #mts_dispatchers =
> "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
>
> compatible = 8.1.7
> sort_area_size = 5242880
> sort_area_retained_size = 65536

Why do you need retained size? Do you have memory problems?

Djuro, ako imas problema mozes mi poslati direktnu postu. Pokusati cu ti pomoci koliko bude islo. Kako ide Infosystemu? Prodajete li jos uvijek Unisys ili ste se prebacili na nesto drugo? Pozdrav!

-- 
Mladen Gogala
I love the smell of napalm in the morning. It smells like victory.
Apocalypse Now