Basic Concept of Performance Tuning in Oracle

Scope of Performance Tuning
There is four main area for Performance Tuning.
1. SQL Tuning – Responsibility of the Developer
2. Database Tuning – Responsibility of the Database Administrator
3. System Tuning – Responsibility of the System Administrator
4. Network Tuning – Responsibility of the Network / LAN / WAN Administrator.
SQL Tuning
Find the problem to a single SQL
You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.
Analyze the SQL to determine the nature of the problem
Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.
Fix the problem.
Almost every performance problem has a solution; it's just that some are more complex than others. In order of increasing complexity and expense, such fixes include:
• Analyze the underlying table to give Oracle's Cost Based Optimizer the information it needs to resolve the SQL efficiently.
• Add one or more hints to the SQL to encourage or discourage certain execution plans.
• Minor changes to the SQL to encourage or discourage certain execution plans.
• Restructure a poorly designed SQL that cannot be resolved efficiently.
• Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
• Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
• Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.
Database Tuning
For optimum performance an Oracle database should be regularly tuned. Only tune a database after it has been up and running for a little while.
• Tuning the cache hit ratio
• Tuning the library cache
• Tuning the log buffer
• Tuning buffer cache hit ratio
• Tuning sorts
• Tuning rollback segments
• Identifying missing indexes
• Identifying index fragmentation
• Identifying free list contention
• Identify significant reparsing of SQL
• Reducing database fragmentation
• Rebuilding indexes
• Reduce thrashing or poor system performance (or how to un-tune oracle?!)
Operating System Tuning
Tune your operating system according to your operating system documentation. For Windows platforms, the default settings are usually sufficient. However, the Solaris and Linux platforms usually need to be tuned appropriately. The following sections describe issues related to operating system performance:
• Basic OS Tuning Concepts
• Solaris Tuning Parameters
• Linux Tuning Parameters
• HP-UX Tuning Parameters
• Windows Tuning Parameters
• Other Operating System Tuning Information
Network Tuning
Network tuning is the performance optimization and tuning of SQL*Net based on an arbitrary UNP which could be TCP/IP, SPX/IP or DECnet. SQL*Net performance can be maximized by synchronization with tunable parameters of the UNP, for example, buffer size.

SQL*Net transaction performance can be divided into components of connect time and query time, where
Total SQL*Net (Net8) Transaction Time = Connect Time + Query Time

Connect time can be maximized by calibration of tunable parameters of SQL*Net and the UNP when designing and implementing networks.

SQL*Net Performance
For this discussion, SQL*Net performance and tuning analysis is based on two categories:
• SQL*Net performance
• SQL*Net tuning