Re: UPDATE with unusually high buffer gets

On Wed, 12 Jan 2000 21:55:32 GMT, 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.

Hi,

please set sql_trace=true, run the statment again, set sql_trace=false and look at the ${UDUMP} directory on the server (Ask your database administrator where user_dump_dest from the init.ora file is poiting to) . There you will find a file named ora_${PID}.trc. This file can be analyzed with tkprof (calling tkprof without parameters shows how to use it with sys=yes). The output will show which statements are executed during this update including all trigger and system code.

Fred Stojentin Received on Thu Jan 13 2000 - 04:39:08 CST