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

1.

> parallel_min_servers = 2
> parallel_max_servers = 5

-----> SET TO 0 - useless on this db - it's a small repository not a warehouse - it'll take you more time to create a plan than to execute it

2. try setting optimizer_mode = RULE - see what happens .. if it's way faster then your statistics are outdated - if you WANT to use CBO you should ANALYZE your data frequently

3.db_block_buffers = 10000 (8K blocks) : you have 1GB of RAM - let's say WIN2000 takes 150MB - add another 250MB for the Oracle processes and threads than you could easely beef this up to 40000 db blocks

4. check the size of your redolog files ! 60GB disks - 2.5GB DB - cranck up your redologs - it's not like you're running out of space :-) heck - go wild and make'm a whopping 40Mb :-)

"Đuro Dretvić" <Djuro.Dretvic_at_Infodom.hr> wrote in message news:9tgalh$ijn$1_at_sunce.iskon.hr...
> 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
>
> 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
>
> #shared_pool_size = 134217728
> shared_pool_size = 157286400
>
> large_pool_size = 52428800
> java_pool_size = 36700160
>
> log_checkpoint_interval = 10240
> log_checkpoint_timeout = 1800
>
> processes = 200
>
> parallel_min_servers = 2
> parallel_max_servers = 5
>
> log_buffer = 10485760
>
> db_block_lru_latches = 4
> db_writer_processes = 5
>
> dml_locks = 250
>
>
> #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
>
> 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
>
> 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
>
>
>
>
Received on Wed Nov 21 2001 - 13:29:18 CST