Re: Correction Re: UPDATE with unusually high buffer gets

Hi.

 Unless you have an index with acct_balance_id field as the leading  index field - your statements performs FULL table scan each time  it's executed.

 Define an index ( unique index if possible ).

 HTH. Michael.

In article <85kucp$5bq$1_at_nnrp1.deja.com>,   Allan Plesniarski <aplesnia_at_my-deja.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 15:01:20 CST