AIX - Often Missed Parameter

I am part of an excellent DBA team that support many VLDB's (multi-tera byte range) on many different platforms (primarily Unix and Linux).  It was reiterated today that many people miss setting the AIXTHREAD_SCOPE parameter in the OS.

This tends to be important because Oracle recommends the usage of system-wide contention, which in-turn maps Oracle threads to kernel threads statically.  This fact becomes even more critical in an Oracle RAC environment.

Setting the AIXTHREAD_SCOPE parameter in the OS provides the desirable benefit of reducing memory allocation at the Oracle process level in AIX version 5.2 or later.

The AIXTHREAD_SCOPE parameter should be set at the OS level as an environment variable for your Oracle instance owner.

“AIXTHREAD_SCOPE=S”  ...don't for to export it.

PS: Since we are talking about AIX another parameter you will want to consider setting is an Oracle init. one:

filesystemio_options='SETALL'



File System caching is better suited when your work load tends to be heavily sequential with low 
write content. 
To enable caching for Journal-ed File System (JFS) you would set: default file-system mount options and the Oracle init. parm: filesystemio_options=ASYNCH.



On the other hand, to disable JFS caching and in-turn utilize Direct I/O (DIO)  which tends to benefit heavily random access workloads, you would want to set the init. parm. to filesystemio_options=DIRECT.


Then again, if you want the best of both worlds, Cached I/O (CIO) which tends to be better for heavy update workloads, and DIO, then you will want to use the filesystemio_options='SETALL' parameter.


As an after thought you may want to consider increasing you init. param's for db_cache_size and db_file_multiblock_read_count.  This is because, when you are using CIO and DIO then your file-system buffer cache is not being used.