Index block contention is very
common in busy databases and it’s especially common on tables that have
monotonically increasing key values.
In a high-DML system, index management
activities are constantly taking place (b-tree splitting and spawning) and
these events can cause transient waits, but far and away, the greatest cause
of index contention in an Oracle environment is the “high key” issue.
Oracle b-tree indexes are “right-handed” and the right-hand leafs of the
b-tree contain the highest key in the lowest tree level.
Index leaf node contention happens when
rows are inserted based on a user generated key (i.e. a sequence) and
because the sequence key is always the high order key, each insert of a
low-level index tree node must propagate upwards to the high-key indicators
in the b-tree index.
Detecting index leaf block
contention
Index block contention can
be tricky to diagnose and repair, especially since Oracle does not have many
direct tuning knobs for tuning for contention.
The wait “enq: TX – index contention” indicates a wait on an index, and in a RAC environment you might also see “gc” (global cache) waits on indexes:
Follow the link below for more tips and tuning methods to reduce index contention:
http://www.dba-oracle.com/t_index_leaf_block_contention_tuning.htm
*****************************************
Spot hidden Oracle performance trends!
Using the principles of decision support systems technology, BC has developed
an important new Oracle tuning tool, Ion for Oracle. Ion quickly isolates the
reports on the important optimization issues, separating the wheat from the
chaff like no other performance tuning software.
After several years of
hard work, Ion for Oracle is finally available for general release. Ion is
unlike any other Oracle tool on the market, a tool that encapsulates expert
techniques to allow a DBA to quickly visualize important trends and signatures.
Predictive analytics is the key to repairing Oracle problems before they
cripple your database, and I created Ion for Oracle to allow any DBA to quickly
identify changing workloads and unobtrusive patterns of data access. To try Ion
for free, see here:
http://www.ion-dba.com
*****************************************
Need a Health Check?
Oracle is the world's most complex and robust database and there are
hundreds of sub-optimal settings that can cripple your database performance.
Burleson Consulting has a great Oracle health check where we identify all
database bottlenecks to ensure that your mission critical system is running at
optimal speeds.
Just call 800-766-1884 to schedule your health check.
*****************************************
Need Oracle
Training?
The very best Oracle training comes from Burleson
Consulting, where you get an on-site visit by an experienced Oracle expert and
author. Whether it's one-on-one mentoring or getting a customized on-site Oracle
training class, there is no substitute for BC Oracle training. Just call
800-766-1884 for details, and check out our on-site Oracle training catalog at
the following link:
http://www.dba-oracle.com/bc-catalog.pdf
*****************************************
There's Always Room at
the Top
Burleson Consulting continues to grow as the Oracle
community recognizes that high quality Oracle support and training is a cost
effective option in these tough economic times.
If you have distinguished
yourself in your Oracle career and are ready to step up and enjoy the rewards of
joining the ranks of America's top tier support consultants we would like to
hear from you.
Evidence of high achievement as an Oracle professional
might include publication of books and articles for the Oracle community,
achieving an advanced degree from a competitive university and seeking out
hands-on experience with complex databases. See here for details and a full
qualifications list:
http://www.dba-oracle.com/jobs.htm