Re: UPDATE with unusually high buffer gets

Hi

Is the primark key combination of (ACCOUNT_ID, ACCT_BALANCE_ID) ?. If so, this is a concatenated index and it will be used only for Leading columns or combination of both the columns. In your case,Since in your index ACCOUNT_ID is the leading column, the UPDATE stament goes for full table scan .

Solution:

Just drop the primary key and recreate the primary with (ACC_BALANCE_ID,ACCOUNT_ID) so that ACC_BALANCE_ID becomes the leading column.

Mohamed.

Allan Plesniarski wrote:

> 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 - 16:41:38 CST