Performance Tuning: Session_max_open_files

Performance Tuning: Session_max_open_files

        Specifies the maximum number of BFILEs that can be opened in any session. The maximum value for this parameter depends on the equivalent parameter defined for the underlying operating system. The range of value is 1 to either 50 or the value of MAX_OPEN_FILES defined at the operating system level, whichever is less

To display current value on database:
SQL> show parameter Session_max_open_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_max_open_files               integer     500
SQL>

To display current value on OS per user:
$ ulimit -Hn
65536
$ ulimit -Sn
65536

To set value in database:
SQL> alter system set session_max_open_files =500 scope=spfile;


To set value in OS:
vi /etc/security/limits.conf
orausr     soft    nofile       65536
orausr     hard   nofile       65536

To count number of data file on database:
SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
       180

SQL>

We can increase values of session_max_open_files whenever number of database files get increase.