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 -> Re: UPDATE with unusually high buffer gets

Re: UPDATE with unusually high buffer gets

From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: Fri, 14 Jan 2000 00:17:45 +0100
Message-ID: <##AmnxhX$GA.150@net003s>


Have you looked in V$SESSION_WAIT for the session that is performing the query to see where the database is accessed? If you can trap a read or write event in this view then P1 will give you the file and P2 will give you the blocknumber that is being processed. Equating P1 with FILE_ID and P2 between BLOCK_ID and BLOCK_ID + BLOCKS - 1 from DBA_EXTENTS will give you the segment that is being accessed. If you write a query linking V$SESSION_WAIT and DBA_EXTENTS in this way that you perform repeatedly while your update is running you should get a good idea where the IO is spending its time.

Possible underwater activity I can think of:

Jaap.

Allan Plesniarski heeft geschreven in bericht <85it8c$miv$1_at_nnrp1.deja.com>...
>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 Thu Jan 13 2000 - 17:17:45 CST

Original text of this message

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