OraFAQ Forum: Performance Tuning » Query Hanging with Sequential Read or Latch free Waits

Home » RDBMS Server » Performance Tuning » Query Hanging with Sequential Read or Latch free Waits (Oracle 9.2.0.6.0 on HP-UX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Hanging with Sequential Read or Latch free Waits [message #502568]

Thu, 07 April 2011 06:27

OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom

Senior Member

Hello

Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event

Important thing is the table which is self joined in subquery here does not have any index at all

While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion'

The table has only 120000 records and it shall update 34000 records


UPDATE invoice_header inv
   SET inv.modified_due_date =
          (SELECT inv1.btn_due_date
             FROM invoice_header inv1
            WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')
 WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1   1267.73    1242.37          0   42312921      16852           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2   1267.73    1242.37          0   42312921      16852           0

During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.

During lath free I found following

SELECT name, 'Child '||child#, gets, misses, sleeps 
  FROM v$latch_children
 WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION
 SELECT name, null, gets, misses, sleeps
  FROM v$latch
 WHERE addr= (select p1raw from v$session_wait where sid=18)

NAME	'CHILD'||CHILD#	GETS	MISSES	SLEEPS
cache buffer handles		3532981370	139895301	17452

However instead of self join when I creaed global temporary table as

create global temporary table t as select * from invoice_header where release='A5'

And used it in the update as

 UPDATE invoice_header inv
   SET inv.modified_due_date =
          (SELECT t.btn_due_date
             FROM t
            WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
 WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)

It updated the records in a second!!

Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?

Please let me know in case DDL of table is required. It has all nullable columns and no index at all.

Since it is 9i I am unable to use MERGE effectively in this case

Regards,
OraKaran

[Updated on: Thu, 07 April 2011 06:31]