Fine tuning Oracle parameters

Last Updated : Jun 03, 2026 |

About this task

Perform the following steps to ensure optimum performance on Oracle database:

Procedure

  1. Set the FILESYSTEMIO_OPTIONS parameter to SETALL by typing: 
    SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  926941184 bytes
Fixed Size                  1222672 bytes
Variable Size             239077360 bytes
Database Buffers          683671552 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL
SQL>
  2. Check the pctfree values for pim_contact and pim_contact_attempts table by typing: 
    SQL> select * from user_tables where table_name = ‘<tablename>';
  3. Set the pctfree values in the pim_contact as 20 and pim_contact_attempts as 30 by typing: 
    SQL> alter table pim_contact pctfree  20 ;
SQL> alter table pim_contact_attempts pctfree 30;