Database Reference
In-Depth Information
3.
Convert the index to a reverse-key index type if you do not have a license for a partitioning
option or if your database uses Standard Edition software. Because column values are
reversed while they are stored in the index blocks, index entries will be distributed
to numerous leaf blocks, relieving the contention. However, because the values are
distributed between almost all leaf blocks of the index, it is possible to pollute the buffer
cache with these index blocks, causing increased physical reads. Another disadvantage
with this option is that reverse key indexes do not support the index range scan operation.
4.
Implement software-driven localized sequence access, with the sequence generating a
different range of values. For example, you can convert an application to use multiple
sequences generating a disjoint range of values instead of a sequence, such as an
emp_id_seq1 sequence with a starting value of 1 billion, an emp_id_seq2 sequence
with a starting value of 2 billion, and so on. The application will be coded in such a way
that sessions connected to instance 1 will access the emp_id_seq1 sequence, sessions
connected to instance 2 will access the emp_id_seq2 sequence, and so on. Hence,
applications will be inserting values in a different range in each instance, reducing leaf
block contention. Disadvantages with this option are that it requires a code change, and
it does not resolve the problem completely as leaf block contention can still creep up
within an instance.
To learn about methods to identify objects inducing or suffering from gc buffer busy performance issues,
see Chapter 10.
Excessive TRUNCATE or DROP Statements
Applications executing excessive numbers of TRUNCATE and DROP commands will not scale in a single-instance
database. In a RAC database, global waits triggered by these DDL commands will cause severe performance issues.
The TRUNCATE and DROP commands trigger object-level checkpoints for the table to be truncated or the
object to be dropped. In a single-instance database, the object queue in the local buffer cache must be scanned to
identify blocks currently in the buffer cache. In a RAC database, object queues in the buffer caches of all instances
must be scanned to complete the TRUNCATE and DROP commands. Object checkpoints are triggered by posting
remote background processes by means of local background processes. If numerous object checkpoints are triggered
concurrently, then these checkpoint requests might be queued, leading to longer wait times to complete the
commands. Also, as the size of the buffer cache increases, the amount of work to complete these commands also
increases, and so this problem can be magnified in large buffer caches.
■
an object-level checkpoint also is triggered for dependent objects such as indexes. hence, tables with
numerous indexes can suffer the worst consequences.
Note
The TRUNCATE command is a DDL command, so parse locks (also known as library cache locks) of dependent
objects must be broken before the DDL command completes. In a single-instance database, breaking parse locks
requires changes to local shared pool objects. However, in a RAC database, where the parsed representation of
dependent objects can exist in another instance's shared pool, parsed locks must be broken globally. Further, library
cache locks are globalized as Global Enqueue Services (GES) locks, so breaking parse locks requires invalidating GES
enqueues. This activity triggers a set of coordination messages between the instance background processes.
I enabled 10046 event trace in my session and truncated a table. The following are a few lines from the SQL*Trace
file. These wait events are associated with a coordination mechanism between the instances. You can identify the
object details as the object_id is listed in the 'local write wait' event. In this example, the object_id of the truncated
table is 89956.
