Plsql_ Performance Optimization Series 08_oracle Insert/direct Insert performance optimization

2014-09-25 Baoxinjian

First, Insert performance impact

The most common cause of program extensibility is the enqueue between sessions due to the unreasonable design of the application. In addition, contention for some shared resources can also cause performance degradation.

This article describes two wait events (wait event) caused by concurrent insert operations, and how to improve by optimizing the physical design.

The normal insert operation itself produces a row lock, so the process is not locked (enqueue) from one another, but there is a resource conflict when many processes insert the same table.

Here are two examples:

1. Buffer Busy Wait

The bulk process inserts the Test_table table when the speed of the process is significantly reduced (more than 100). Seen in the AWR report (waiting events accounted for 0.95% of 93%,CPU. That means it's all waiting, no one's working ... ）。

Further check to wait for the object is pk_test_table, is the primary key of the test_table table.

The test_table table has a hash partition (16), and Pk_test_table does not have a partition, resulting in the modification of the same index block, which results in tx-index contention and buffer busy waits.

After rebuilding the pk_test_table to a hash partition index, buffer busy waits and enq:tx-index contention are not in top events.

2. Many processes insert, report ora-00060 Dead lock Error

Insert/update/delete operation, although the line is blocked, but the process will occupy a slot on the modified block, when the number of processes to modify the same block more than Initrans, can result in a 60 error. See below

Its possible to get an ora-60 deadlock when more than one transaction does DML on a block if Initrans is set too low. If There is more simultaneous transactions than slots then the transaction layer needs to grow from Initrans towards Maxt RANS, but it can ' t if the rest of the block is packed with data. In this case the transaction that could not get a slot would either hang or get an ora-60 depending on the particular Circu Mstances. This is a issue with tables, indexes, and clusters.

The above two performance problems can be solved by table/index physical design optimization. There are several ways:

(1). When the table is built, the Initrans value of the table and index is set large;

(2). The table and index are built into hash partition to reduce the conflict on a single block;

(3). The index is built as a reverse index. Because many primary keys use sequence values, inserts are sorted by sequence value, and insert creates a conflict on the same block of the index. Using a Reverse key index can eliminate this problem. Interested can check the relevant concepts on Google.

Second, Insert direct performance Impact

The Enq:tm-contention wait event occurs when the database runs the transfers program.

The main reason is that when multiple processes are in insert bill_invoice_*, the Insert/*+ append*/is used to increase the speed by append or direct insert, resulting in a table-level lock.

In this way, the billing process waits for each other, actually becomes the serial operation, but affects the speed.

What's more, if it's a production system, it can also cause all the modifications on the bill to fail, affecting other important business, which is a serious consequence.

The use of Direct insert has its advantages of fast speed, but it also has its drawbacks and must be noted for application. Let's think about it, why doesn't Oracle use append by default?

Summarize here, direct-load Insert (and append,direct mode SQLLDR, etc.)

Suitable for:

(1). The use of a large number of recorded inserts increases speed, as bypassing DataBuffer directly accesses data files without scanning the original block for any remaining space and allocating new space directly;

(2). A single process, usually data maintenance, or temporary inverted data, etc.;

In both cases, the use of direct-load insert is not only fast, but also reduces the use of database data buffer and has less impact on other applications on the database;

Not suitable for:

(1). Few records insert. Because each insert/*+append * * * After the index maintenance, a small number of records insert use Append instead of slow;

(2). Multi-user, OLTP environment. Because append will produce table-level locks. OLTP systems in addition to some batch processing operations, most applications should not use append;

Abalone New ********************

Plsql_ Performance Optimization Series 08_oracle Insert/direct Insert performance optimization