Oracle Optimization
Lecture 1

Introduction

You should be very careful in optimization and the main criteria is "not do any harm" rather then "achieve spectacular improvements".

Remember:

The best performance first of all means avoiding blunders in installation and configuration. In a way it comes from the unnecessary work you don't do.

Much depends on the level of qualification of a particular DBA or system administrator.

The higher the level qualification is the more probably that actions taken will have positive, not negative effect.  

Excessive zeal is another danger. Usually it backfires. The key here is measurement of performance. Good ideas without measurement of performance in optimization space are often turn to be useless or even bad ideas.

 

There is no free lunch and the more optimized system is, the more specialized for a particular application it became; as a result any changes in application can disproportionally affect performance. We can  categorized the effects of performance tuning (aka optimization)  in two categories: 

There are five major areas of optimization:

  1. Hardware optimization (there is a difference between using 15K RPM disks and dual channel controller with battery backup and 7200RPM disks with primitive controller. The most basic optimization is selection of the system that has higher transaction benchmark (TCP, for example TCP-C). Solid state disk can make dramatic improvement if the database is mainly used for reading. SAN are necessary for large databases (not only from performance standpoint, but also backup and recovery standpoint). 
  2. Operating system based optimization (generally requires careful measurements, for example using D-trace on Solaris)
  3. Database engine level optimization (for example Oracle is highly tunable and has multiple performance measurement tools)
  4. Database schema and SQL-level optimization
  5. Application level optimization . Here the most dramatic improvements are possible; that's why open source applications can generally beat closed-source application; but understanding the application on the level that makes possible its tuning is often limited by the brainpower available; this is especially true  for complex, off-the-shelf applications)

As we go down the list we generally can get higher and higher returns on the efforts. But risks also generally increase. Also not all application are open source so application level optimization if often limited to the contains of the particular implementation.

The level of optimization available to the organization usually are dependent of the qualification of the staff. The higher the qualification of staff the more levels of optimization are available and the more is the potential return.  

Here is a relevant quote from Oracle database Performance Tuning FAQ - Oracle FAQ

Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.

Database sizes

[edit]

What tools/utilities does Oracle provide to assist with performance tuning?

Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:

[edit]

When is cost based optimization triggered?

It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, and optimizer dynamic sampling isn't performed, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.

Generally, the CBO can change the execution plan when you:

[edit]

How can one optimize %XYZ% queries?

It is possible to improve %XYZ% (wildcard search) queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints.

If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.

[edit]

Where can one find I/O statistics per table?

The STATSPACK and UTLESTAT reports show I/O per tablespace. However, they do not show which tables in the tablespace has the most I/O operations.

The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.

For more details, look at the header comments in the catio.sql script.

[edit]

My query was fine last week and now it is slow. Why?

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.

Some factors that can cause a plan to change are:

What do you think the plan should be? Run the query with hints to see if this produces the required performance.

[edit]

Does Oracle use my index or not?

One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: 

SQL> CREATE TABLE t1 (c1 NUMBER);
Table created.

SQL> CREATE INDEX t1_idx ON t1(c1);
Index created.

SQL> ALTER INDEX t1_idx MONITORING USAGE;
Index altered.

SQL>
SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;
TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
T1                             T1_IDX                         YES NO

SQL> SELECT * FROM t1 WHERE c1 = 1;
no rows selected

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;
TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
T1                             T1_IDX                         YES YES

To reset the values in the v$object_usage view, disable index monitoring and re-enable it: 

ALTER INDEX indexname NOMONITORING USAGE;
ALTER INDEX indexname MONITORING   USAGE;
[edit]

Why is Oracle not using the damn index?

This problem normally only arises when the query plan is being generated by the Cost Based Optimizer (CBO). The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:

Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).

There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

[edit]

When should one rebuild an index?

You can run the ANALYZE INDEX <index> VALIDATE STRUCTURE command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.

[edit]

How does one tune Oracle Wait event XYZ?

Here are some of the wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:

[edit]

What is the difference between DBFile Sequential and Scattered Reads?

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads: 

prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from   sys.v_$system_event a, sys.v_$system_event b
where  a.event = 'db file sequential read'
and    b.event = 'db file scattered read';

Database engines requirements for key subsystems of modern servers

The database server's primary function is to store, search, retrieve, and update data from disk. Examples of Database engines include IBM DB2, Microsoft SQL Server, and Oracle. Due to the high number of random I/O requests that database servers are required to do and the computation intensive activities that occur, the potential areas that have the most impact on performance are:

A balanced system is especially important, for example, if adding additional CPUs, consider upgrading other subsystems such as increasing memory and ensuring that disk resources are adequate. The key subsystems that influence database performance in the servers are:

There are also several subsystems that doesn't affect server performance. one is the video subsystem which in a server is relatively insignificant.

=== End of lecture ===