How to Avoid Server Performance bottleneck in Oracle Database Server

Keeping your file systems and directories, including the root directory, slim helps prevent cluttering of the file systems, thus helping administration. This also enhances performance, since the file system does not have to deal with the overhead of managing a large number of unwieldy partitions. Directory lookups are relatively slow, and their speed is in many ways directly proportional to directory size. Hence, keep your directories as small as possible. Directory sizes are usually based on bytes in most Unix and Linux systems. You can check the directory sizes via the "ls –l" command. When you start seeing multiple of bytes like 4096, 8192, etc, it's time to start cleaning up.

Truncate all old log files. If left unattended, Oracle's trace and other logs, such as alert.log, listener.log, and so on, can grow very large, taking up a lot of file system space. For tips on managing Oracle log files, create some unique strategy for purging and schedule the scripts. Similarly, purge all core dumps, temporary files, and error logs generated by your applications. Remove very old backups including export backups, e-mails, and so forth from disk and, if necessary, archive them to tape.

Many times, even after a good cleanup, directory entries do not reduce in size. Dropping and re-creating the directory can rectify such a situation. However, if multiple subdirectories are involved, this can be quite cumbersome. Large directory entries are not problems in themselves. They are only reminders that we need to clean up, remove all unnecessary files, and keep directories compact.

In addition to checking for I/O bottlenecks, proactive monitoring for availability of free space is also mandatory. In live production environment, lack of free space is instant death. Adding more disks to an external disk array is usually a relatively easy task, especially with hot-pluggable configurations, in which new disks can be plugged in while the system is online. However, availability of the required disks might be a problem. You can take advantage of storage area or network storage devices. Given such external environments in which we have limited control, the best we can do is to implement full-fledged proactive monitoring for free space. Sometimes required free space is in danger of being used up, or when maximum hardware capacity is about to be reached. Give advance warning to your higher authorities to ensure timely availability of resources. Means, if we are monitoring constantly free space then we can eliminate such kind of issues. If database environment is complex then we can create some scripts and schedule for monitoring. Remember, database monitoring is essential for 24/7 running large production databases.