Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Correction Re: UPDATE with unusually high buffer gets

Correction Re: UPDATE with unusually high buffer gets

From: Allan Plesniarski <aplesnia_at_my-deja.com>
Date: Thu, 13 Jan 2000 16:27:26 GMT
Message-ID: <85kucp$5bq$1@nnrp1.deja.com>


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 ...

> There is an index on ACCOUNT_ID, ACCT_BALANCE_ID.
should read
There is an index on ACCOUNT_ID, ACCT_BALANCE_CLASS_TYPE_ID.

In article <85it8c$miv$1_at_nnrp1.deja.com>,   Allan Plesniarski <aplesnia_at_my-deja.com> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 10:27:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US