[LenDB2]

This is my very first post, so please be kind if my question is lame.

My customer is running 5.5 (CU4) with the database being Oracle 10gR2 (10.2.0.3). They've experienced some interesting performance issues related to two (2) SQL statements against the i_log table.

The first is a SELECT COUNT(*) from the i_log table Where the i_transaction_no is equal to a Bind Variable (:1) and the code is equal to an "E". I suspect this is a check to see if any of the i_log rows written were Error ("E") records.

The second is a DELETE from the i_log table Where the i_transaction_no is equal to a Bind Variable (:1).

We verified that an index exists on the i_log table which contains the i_transction_no as the high order column and that stats were current. The AWR report indicated that each execution of either of the above 2 statements "touched" several thousand blocks to complete. What made it more interesting is the Plan chosen was using the expected index.

After further research, we realized that a large number (almost 100K) of i_log rows contained an i_transaction_no value of negative one (-1). This resulted in Oracle's CBO picking an access path that "touched" a large number of blocks.

Can anyone explain what a value of negatie one (-1) in the i_transaction_no column represents? Any insight would be greatly appreciated. TIA.

Lenny

[svenhaul]

Lenny,

this question is not lame at all - it shows a potential future bottleneck for us that is worth monitoring.

If you open a transmission report in the UI (Business Process Automation, Integration, Transmission Manager, find a transmission, view it, click the Report button), you will see the various log messages of the transmission.
The messages written by the XML_STAGE component all have an ITransactionNo of -1, the messages written by, say, the OrderInterface component have a "real" ITransactionNo.
If there is any way to selectively switch off the log messages from XML_STAGE, I do not know, but maybe that is worth exploring.