Oracle9i Database: Performance Tuning (8)

Monitoring and Detecting Lock Contention

• V$SYSSTAT will have its statistic ENQUEUE DEADLOCKS incremented each time a deadlock is detected and resolved

Tuning Oracle Shared Server

Application Tuning

• The hash value of the current SQL is compared with the Hash value of the SQLs already cached so that the current SQL can use its parsed version

Using Oracle Blocks Efficiently

• A partitioned table can have partitioned and/or non-partitioned indexes. Priviages for partitions are granted on the parent table or index and cannot be granted on individual partitions.Table that is a part of a cluster cannot be partitioned. CBO is used by default when an SQL statement accesses a partitioned table or index.

SQL Statement Tuning

• Histograms : used on the column that have non-uniformly distributed data. on the column that is used in the where clause of most of the queries. on the column that data does not change for a very long time. Should’t use on the column that use bind variables for comparsion.