OracleSource

 Tune business function
 Tune data design
 Tune process design
 Tune SQL statements
 Tune physical structure
 Tune memory allocation
 Tune I/O
 Tune memory contention
 Tune operating system

The list in the slide shows the phases of the development cycle in which tuning can be applied. Following the steps in this order is recommended for the following reasons:
• The earlier the step, the higher the potential for improving performance.
• The earlier the step, the higher the cost for performing or reworking it later. For example, changes to the data structures and application code after the initial design tend to be expensive and require the additional expense of retesting the affected components.
• Decisions made at one step may influence subsequent steps. For example, the SQL statement that you write in Step 4 will have significant bearing on parsing and caching issues that are addressed in Step 6.
The more extensively that you use object-oriented design techniques and multitiered architecture, the better the chances that you will safely achieve any application changes at a reasonable cost.

Note : About object-oriented design techniques: on the other hand, encapsulation tends to make you lose control over events occurring at a lower level.

Tuning SQL Statements

 Tune the schema
• Add indexes
• Create Index Organized tables
• Create Clusters
 Choose the language : SQL or PL/SQL
 Design for the reuse of SQL optimization
 Design and tune SQL statements
 Maximize performance with the optimizer

During the steps within SQL statement tuning, analysis techniques should be used frequently to determine goals and progress.
• Tuning the Schema : The designer is responsible for determining which tables should be used, their contents, and similar issues. The application developer might then need to decide when to use indexing, when to use index organized tables, and when to use clustering. Denormalization may be needed for good performance, especially in data warehouse environments. The decisions that are made at this step greatly affect the performance of any SQL statements that would use these structures.
• Choosing a Language (SQL or PL/SQL) : In some cases, you may achieve better performance by using the PL/SQL language to perform a task.
• Designing for Reuse of SQL Parsing, Optimization, and Tuning Efforts : The Oracle9i Server is often able to reuse some of its efforts at parsing and optimization when it sees an identical statement repeated. Therefore, creating SQL statements that appear identical can improve performance. It also allows the application developer to focus and tune individual SQL statements that are used repeatedly.
• Designing and Tuning SQL Statements : There is a wide variety of methods for designing high-performance SQL statements. Knowing the overall functions of the optimizer helps to show where tuning efforts can be effective. Consider rewriting SQL statements into semantically equivalent statements. Also, use the correct language (SQL or PL/SQL) in each situation.
• Achieving Maximum Performance with the Optimizer : To make the best use of the Oracle cost-based optimizer, you must understand how it chooses access paths to data. You must assist the cost-based optimizer by using the ANALYZE command and by sometimes providing it hints about the best access path. The rule-based optimizer is also available.


 Be proactive: work from the top down.
 If you must be reactive, work from the bottom up, using these hints:
• Establish quantifiable objectives.
• Create a minimum repeatable test.
• Ask questions of affected users, and avoid preconceptions.
• Test hypotheses and keep notes.
• Stop when you meet the target.


• Quantifiable Objectives : Establish quantifiable objectives, and avoid vague ones. An example of an acceptable objective would be the following: “We must be able to support up to 20 operators, each entering 20 orders per hour, and the picking lists must be produced within 30 minutes of the end of the shift.”
• Minimum Repeatable Test : If you are trying to cut a four-hour run down to two hours, repeated timings take too long. Perform your initial trials against a test environment similar to the real one (or provide input for a restrictive condition, such as processing one department instead of 500 of them). The ideal test case should run for more than one minute so that improvements are demonstrated intuitively and can be measured using timing features.
• Ask questions of affected users and avoid preconceptions.
• Test hypotheses and keep notes.
• Stop when you meet the target.

• Tuning a Specific Problem for an Application Already in Production : Often, performance specialists are summoned late in the life cycle of a project, when the system is in production and performing unacceptably. In this situation, start at the bottom of the method list and work up. The items at the end are the cheapest and fastest to perform. If they do not solve the problem, you will have to work back “uphill,” and this starts to increase costs and time delays.