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

On Wed, 21 Nov 2001 14:42:15 +0100, "Đ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
>
>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
>
>
>

A 10M log_buffer is way too big and is just asking for trouble. You should also check whether there are any 'checkpoint not complete' or 'cannot allocate log' messages in your alert<sid>.log.
Chances are your online redolog files are way too small (the default is 200k which is just ridiculous, an adequate minimum is 4 groups consisting of 2 files per group, each file with a size of 5M)

Also locating the complete database on one single disk will definitely slow down everything, and it will also increase the chance you loose your complete database (I notice you are running in noarchivelog which you shouldn't do)

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Nov 21 2001 - 12:47:48 CST