INFO-TECH

One point that needs to be made clear is that performance tuning is not an exact science - you can't always predict that a certain tactic or technique will improve performance. Every change has to be tested, because it may improve performance in one area, it may also degrade performance in another area. Adding an index is a classic example of this - it may improve query performance but the performance of inserts and deletes become slower because the index entries also have to be updated.

You also need to be aware that improving response time is not the only aim that you might have - you might want to reduce memory usage instead - which might mean having to redesign or rewrite some stored procedures. All these issues need to be considered before you start looking at performance.

Validation framework comes with set of useful routines to handle form validation automatically and it can handle both server side as well as client side form validation. If certain validation is not present, you can create your own validation logic by implementing java interface.

Causes of Poor Performance

One cause of poor performance is high Oracle communication overhead. Oracle must process SQL statements one at a time. Another cause of poor performance is inefficient SQL statements. Because SQL is so flexible, you can get the same result using two different statements. Using one statement might be less efficient.

For example, the following two SELECT statements return the same rows (the name and number of every department having at least one employee):

EXEC SQL SELECT DNAME, DEPTNO
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP)
END-EXEC.

Contrasted with:

EXEC SQL SELECT DNAME, DEPTNO
FROM DEPT
WHERE EXISTS
(SELECT DEPTNO FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO)
END-EXEC.

The first statement is slower because it does a time-consuming full scan of the EMP table for every department number in the DEPT table. Even if the DEPTNO column in EMP is indexed, the index is not used because the subquery lacks a WHERE clause naming DEPTNO.

Another cause of poor performance is unnecessary parsing and binding. Recall that before executing a SQL statement, Oracle must parse and bind it. Parsing means examining the SQL statement to make sure it follows syntax rules and refers to valid database objects. Binding means associating host variables in the SQL statement with their addresses so that Oracle can read or write their values.

Automatic Performance Tuning Features

The Oracle automatic performance tuning features include:

Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.

Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database.

SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements.

SQLAccess Advisor provides advice on materialized views, indexes, and materialized view logs.

End to End Application tracing identifies excessive workloads on the system by specific user, service, or application component.

Server-generated alerts automatically provide notifications when impending problems are detected.

Additional advisors that can be launched from Oracle Enterprise Manager, such as memory advisors to optimize memory for an instance. The memory advisors are commonly used when automatic memory management is not set up for the database. Other advisors are used to optimize mean time to recovery (MTTR), shrinking of segments, and undo tablespace settings. Tuning an Application / Reducing Load

If your whole application is performing suboptimally, or if you are attempting to reduce the overall CPU or I/O load on the database server, then identifying resource-intensive SQL involves the following steps:

Determine which period in the day you would like to examine; typically this is the application's peak processing time.

Gather operating system and Oracle statistics at the beginning and end of that period. The minimum of Oracle statistics gathered should be file I/O (V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_ STATISTICS).

Using the data collected in step two, identify the SQL statements using the most resources. A good way to identify candidate SQL statements is to query V$SQLSTATS. V$SQLSTATS contains resource usage information for all SQL statements in the shared pool. The data in V$SQLSTATS should be ordered by resource usage. The most common resources are:

Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)

Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)

Sorts (V$SQLSTATS.SORTS, for many sorts)

One method to identify which SQL statements are creating the highest load is to compare the resources used by a SQL statement to the total amount of that resource used in the period. For BUFFER_GETS, divide each SQL statement's BUFFER_GETS by the total number of buffer gets during the period. The total number of buffer gets in the system is available in the V$SYSSTAT table, for the statistic session logical reads. Similarly, it is possible to apportion the percentage of disk reads a statement performs out of the total disk reads performed by the system by dividing V$SQL_STATS.DISK_ READS by the value for the V$SYSSTAT statistic physical reads. The SQL sections of the Automatic Workload Repository report include this data, so you do not need to perform the percentage calculations manually.

After you have identified the candidate SQL statements, the next stage is to gather information that is necessary to examine the statements and tune them.

If you are most concerned with CPU, then examine the top SQL statements that performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL statement that performed the most DISK_READS.

Information to Gather During Tuning

The tuning process begins by determining the structure of the underlying tables and indexes. The information gathered includes the following:

Complete SQL text from V$SQLTEXT

Structure of the tables referenced in the SQL statement, usually by describing the table in SQL*Plus

Definitions of any indexes (columns, column orderings), and whether the indexes are unique or non-unique

Optimizer statistics for the segments (including the number of rows each table, selectivity of the index columns), including the date when the segments were last analyzed

Definitions of any views referred to in the SQL statement

Repeat steps two, three, and four for any tables referenced in the view definitions found in step five

Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN, or the TKPROF output)

Any previous optimizer plans for that SQL statement

Note: It is important to generate and review execution plans for all of the key SQL statements in your application. Doing so lets you compare the optimizer execution plans of a SQL statement when the statement performed well to the plan when that the statement is not performing well. Having the comparison, along with information such as changes in data volumes, can assist in identifying the cause of performance degradation.