1

I am getting "buffer busy waits" for Oracle package when accessed concurrently, this error is due to some of the update statements.

These update statements are executing in loop and when I try to change/improve these Update statement using ForAll and BULK COLLECT INTO, there is not much difference in execution time and still this error comes.

I have changed PCTFREE from 10 to 30 for the given table but still I can see "buffer busy waits" and due to this ,process continues to run for longer time and fail after that.

Table configuration :

   CREATE TABLE TEST 
   (--columns)
    SEGMENT CREATION IMMEDIATE 
    PCTFREE 30 PCTUSED 40 INITRANS 1 MAXTRANS 255 
    NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

Is there any other way , I can avoid this error?

Edit:

Update statement:

       FOR rec IN
        (SELECT a.mid,
            SUM(hpct * NVL(mcap,0))/v_mc p_lead
        FROM wip a
        WHERE a.rid      = n_rid
        AND a.rtype      =n_rtype
        AND a.sid             = c_sid
        AND a.pid     = n_pid
        AND ( ( n_spct = 0
        AND vorder     != 1 )
         OR (n_spct    = 1) )
           GROUP BY a.mid
        )
        LOOP
            UPDATE final
            SET PlEAD    =ROUND(rec.p_lead,2)
            WHERE rid  = n_rid
            AND rtype  = n_rtype
            AND pid = n_pid
            AND sid         = c_sid
            AND mid   =rec.mid;
        END LOOP;
Khushi
  • 325
  • 1
  • 11
  • 32
  • Can you post that loop with update statements? – Rene Jul 30 '18 at 06:50
  • @Rene, added Update statement – Khushi Jul 30 '18 at 07:46
  • 1
    Have a look at this question and see if you can do the update without a loop. https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table – Rene Jul 30 '18 at 07:55
  • `buffer busy waits` is not an error, but an Oracle *wait event*. Apparetnly the `UPDATE` statement is performing a `FULL TABLE SCAN`. As proposed try to re-write it as an single UPDATE statement. – Marmite Bomber Aug 23 '18 at 07:13

0 Answers0