UPDATE with unusually high buffer gets

Hello

I have a problem at the moment which has pretty much stumped me.

The following query:

         UPDATE acct_balances
            SET closed_date      = p_closed_date,
                remaining_curi_amount = p_remaining_curi_amount,
                modify_user      = g_audit_user,
                modify_date_time = g_audit_date_time
          WHERE acct_balance_id  = p_acct_balance_id

Updates one record in the acct_balances table.

However it takes an age and a half to execute. For some reason it needs to look at 240 MB of data (15,000 blocks) per execution in order to resolve it. Currently on 2, million events this query accounts for 40 minutes of processing time and it only gets executed 30,000 times!!

The following are the statistics gathered for this query:

EXECUTIONS BUFFER_GETS AVG_GETS ROWS_PROCESSED DISK_READS SQL_TEXT ---------- ----------- -------- -------------- ---------- ----------

     30343     5434781      179          30345      32808 SELECT ...
     18858   293638092    15571              0       1078 UPDATE ...

As you may note the query above it also accesses the same table but does not have the same issues. The query text is:

SELECT AB.ACCT_BALANCE_ID,AB.REMAINING_CURI_AMOUNT FROM ACCT_BALANCES AB
WHERE AB.ACCOUNT_ID = :b1
AND AB.ACCT_BALANCE_CLASS_TYPE_ID = :b2 FOR UPDATE

There is a primary key on ACCT_BALANCE_ID. There is an index on ACCOUNT_ID, ACCT_BALANCE_ID.

The stats say that there are no large-scale table scans going on and even then the acct_balances table (940,000 records) is only using about 5500 blocks.

Any ideas would be gratefully appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 12 2000 - 15:55:32 CST