Reversing the index key

This is accomplished by executing the following SQL statement:

ALTER INDEX <index_name> REBUILD REVERSE;

A reverse key index will invert the bytes of the index key column, so that values originally stored in the same block may be spread across multiple index leaf blocks. That will reduce buffer busy contention especially when the index key values inserted are monotonically ascending.

Performance with reverse key indexes may be further improved by disabling global cache defers.

Advantages :-

  • Relatively easy to implement, as only the index segment subject to contention needs to be rebuilt.
  • Reduces buffer busy contention by allowing INSERT statements to be spread across multiple index leaf blocks.
  • Reduces buffer busy contention and cache buffers chains latch even in single instance environments.

Drawbacks

  • Since similar values are stored in different blocks, the index can only be used when the WHERE clause contains an equality predicate on the whole index key. For example if “col” is a column with a reverse key index, “where col = value” can use the index, but “where col > value” cannot. In addition, for composite key indexes, all key columns must be referenced in the where clause, like in “where col1 = value1 and col2 = value2”.
  • Does not reduce the frequency of block transfers, since it does not create block to instance affinity. Although contention is reduced by increasing the set of blocks modified, block changes may still happen in any instance. So, if block contention waits are relatively low compared to block transfer waits, performance improvement will be limited.

Partitioning the table (or only the index) by hash

Partitioning by hash reduces buffer busy contention by spreading inserts among several index partitions. It has a similar effect as the reverse key index approach, without the non-equality predicate limitation. In Oracle9i, the only way to partition an index by hash is to partition the corresponding table as well. In release 10g it is possible to partition only the index, so implementation becomes simpler if contention is limited to index blocks.

The first step is to determine what column or set of columns will be the partition key.  The partition key must not necessarily be defined on the same columns as the index subject to contention. Two factors will influence the partition key choice.

Partition elimination:-
Any query containing an equality predicate on the partition key will scan only one index partition. All inequality queries on the partition key, as well as any queries on other local indexes, must scan all index partitions, resulting in additional logical reads. For that reason, the partition key must be a column that is very frequently queried to take advantage of partition elimination.

To determine the number of partitions, the following factors should be considered:

  • The hash algorithm results in a better data distribution when the number of partitions is a power of 2.
  • Most of the tests performed with an INSERT intensive workload have shown significant contention reduction until 64 partitions. After that, throughput kept rising in some tests, but at a lower rate. Those tests were performed in a 4 nodes, 16 CPUs per node machine, suggesting that the total number of CPUs may be a reasonable starting point.
  • Requirements may vary with INSERT frequency and number of processes concurrently inserting data.
  • In Oracle9i, shared pool memory usage may increase when more partitions are used, and that becomes more significant when more cursors reference the partitioned table. Cursor memory allocation has changed in Oracle10g, so that should not longer be a concern.
  • The set of buffers used for INSERT statements increase proportionally to the number of partitions. For that reason, buffer cache memory usage may rise.

Configuring indexes – LOCAL or GLOBAL :-
When partitioning a table with multiple indexes, one should decide how to configure each of the indexes.
The index that motivated the partitioning effort should be created as LOCAL, so that inserts will be spread across multiple partitions. For other indexes, consider creating them as GLOBAL if they are not subject to buffer busy contention. That would prevent queries from scanning multiple index partitions.

Advantages:

  • Reduces buffer busy contention by allowing INSERT statements to be spread across multiple index partitions.
  • Helps to reduce buffer busy contention and cache buffers chains latch even in single instance environments.
  • Does not have the limitation of the reverse key approach, as the index can be used for non-equality as well as equality predicates in the WHERE clause.

Drawbacks:

  • Only queries containing equality predicates on the partition key will result in partition elimination. Other queries will have to scan all partitions. Depending on the volume of queries that scan all partitions, the benefits of reducing contention for INSERT statements may be outperformed by the additional work associated with scanning multiple partitions. The number of index partition scans will increase based on the number of partitions – instead of having a single index scan with a non-partitioned segment, we now have to do N index partition scans.  A high number of partitions may have significant performance impact on non-insert statements, so that this technique should be employed only after reviewing all statements accessing the index and their frequency.
  • Does not reduce the frequency of block transfers, since it does not create block to instance affinity. Although contention is reduced by increasing the set of blocks modified, block changes may still happen in any instance. So, if block contention waits are relatively low compared to block transfer waits, performance improvement will be limited.
  • May increase buffer cache and shared pool memory usage.

Partitioning the table by list on INSTANCE_NUMBER:-

The idea consists of

  • Adding a column INSTANCE_NUMBER to the table, to be populated with the current instance number.
  • Partition the table by list on INSTANCE_NUMBER, with as many partitions as RAC instances.

Example:

create table rac_dept
(deptno number not null,
dname varchar(80),
loc varchar(200),
instance_number number default sys_context(‘USERENV’,’INSTANCE’))
partition by list(instance_number)
(partition d1 values(1),
partition d2 values(2));

This way, INSERT statements executed from different instances will be stored in different partitions. That is completely transparent to the application, so no application code needs to be changed. If the index subject to contention is created as LOCAL, current block transfers will be greatly reduced.

Note, however, that UNIQUE indexes can only be created as LOCAL if they include the partition key. In our example, if the original table had a unique index on DEPTNO, the new LOCAL UNIQUE index would have to be created on (DEPTNO, INSTANCE_NUMBER), changing the original primary key or unique constraint, as this allows duplicate DEPTNO values to be inserted from different instances.

Note, however, that UNIQUE indexes can only be created as LOCAL if they include the partition key. In our example, if the original table had a unique index on DEPTNO, the new LOCAL UNIQUE index would have to be created on (DEPTNO, INSTANCE_NUMBER), changing the original primary key or unique constraint, as this allows duplicate DEPTNO values to be inserted from different instances.

Advantages:-

  • Greatly reduces the number of current block transfers, thereby improving latencies for INSERT statements.
  • Reduces inter-instance buffer busy contention for INSERT statements.

Drawbacks:-

  • Because the partition key is defined on a column unknown by the application, no queries will result in partition elimination. As a consequence, all partitions will always be scanned for all queries. For that kind of implementation, there would be as many index partition scans as the number of instances, so that queries and other non-insert statements may not scale as instances are added.
  • May cause an increase in CR block transfers when recently inserted data is frequently queried.
  • Implements schema changes that need to be maintained across application releases.
  • Schema changes may need to take place if instances are added to the cluster. A possible alternative is to create as many partitions as the maximum number of instances planned for the application.
  • May imply changes to primary key and unique constraints if local indexes are used for them.

Disabling global cache defers :-

Global cache defers is a mechanism designed to allow local processes changing a block to continue their work, reducing the overhead of repeatedly sending and receiving the same block in heavy contention scenarios.

When there is high concurrency on a block, the deferring mechanism may be triggered, causing all incoming send requests for that block to be suspended for “_gc_defer_time” (defaults to 3) centi-seconds. In some circumstances, when a tuning technique such as a hash partitioned or reverse key index is employed, deferring the send may have adverse effects on performance. That is especially true for Oracle9i, as in that release the deferring algorithm was more aggressive than in 10g.

For the above reason, disabling block defers may be employed in conjunction with hash partitioning or reversing index keys to optimize performance. Note that changing “_gc_defer_time” from the default may or may not provide performance improvements, depending on the workload. As it is a dynamic parameter, experimentation does not require instance shutdown/startup:

alter system set “_gc_defer_time”=0;

The deferring algorithm is being reviewed to avoid a performance penalty with hash partitioned and reverse key indexes. For Oracle9i and Oracle10g release 1, performance testing with defers disabled is advisable when those tuning techniques are employed.

Other Sessions for this Training can be found @

Tuning indexes on RAC environments-1

Tuning indexes on RAC environments-2

Tuning indexes on RAC environments-3

Tuning indexes on RAC environments-4

Watch this place and become a tuning expert….. Subscribe by Email to get each day sessions in your inbox. Everything an Oralce Professional Needs to Cram for a Performance Optimization Job

Enter your email address:

Delivered by FeedBurner

Readers who are interested on doing hands on practice, please feel free to contact us

PrintFriendly

Viewers of this post also viewed...