Tuesday, September 29, 2009

Tuning Database

Disabling Dispatchers

Use the following to disable dispatchers:

  1. Comment the dispatchers line in the init file:

    #dispatchers="(PROTOCOL=TCP)"

  2. Create spfile from pfile.

Setting Parameters

Set the following parameters:

  1. Set the processes parameter to 750:

    alter system set processes=750 scope=spfile;

  2. Set db_cache_size to 1G:

    alter system set db_cache_size=1G scope=spfile;

  3. Set undo_retention to 1800:

    alter system set undo_retention=1800 scope=spfile;

  4. Set db_writer_processes to 4:

    alter system set db_writer_processes=4 scope=spfile;

  5. Increase the size of redo log files to 300M:

    startup open restrict
    alter database add logfile group 5 '//oradata/data03/redo05.dbf' size 300M;
    alter database add logfile group 6 '//oradata/data03/redo06.dbf' size 300M;
    alter database add logfile group 7 '//oradata/data03/redo07.dbf' size 300M;
    alter database add logfile group 8 '//oradata/data03/redo08.dbf' size 300M;
    select group#, bytes, status from v$log /* to find the status of redo logfile groups */;
    alter system switch logfile
    /* perform this step till group 1, 2, 3 and 4 become inactive */;
    alter database drop logfile group 1;
    alter database drop logfile group 2;
    alter database drop logfile group 3;
    alter database drop logfile group 4;

No comments: