Re: Hash Join and sequential reads
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <debaditya.chatterjee@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jul 2008 16:44:21 +0100
There are various components to the update that might
be the source of the single block reads. One conjecture
that I haven't tested is that it's down to the use of the
hash join.
You have an update statement driven through a hash join -
and the table you want to update is the build table. This
means the order in which rows are selected from that
table for update is dictated by the second table (in your
case the view) in the hash join. This means that you are
likely to be updating rows scattered randomly around
the table - which would required single block reads.
It would be interesting to determine whether the optimizer
includes the rowid in the build table to make this efficient -
I suspect it has to, unless there is a uniqueness constraint
on the join column in which case the optimizer could access
the row to be updated by index.
I also notice you've used the undocumented /*+bypass_ujvc */
hint to avoid bypass the requirement for key-preservation. If your
aggregate view manages to produce multiple rows in cases where
it is supposed to produce just one (i.e. updating rows in w_person_d
many times) this could make matters worse.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Debaditya Chatterjee" <debaditya.chatterjee@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, July 17, 2008 1:22 AM
Subject: Hash Join and sequential reads
All,
I have a strange situation and I am unable to co-relate the sql operation
and the associated wait event. We usually associate 'db file sequential
reads' with index scans but in the example below, the current operation of
the sql (from v$session_longops) is a HASH JOIN yet the wait event is 'db
file sequential read'. There are no index scans in the execution plan and I
have checked that the plan for this query hasn't changed in the last 24
hours.
The update statement is now running for more than 24 hours.
The database version is 10gR2.
Can anybody help me understand why there is a db file sequential read wait
event ?
Thanks
Deba.
/* Sql_id of the session */
SQL> Select sql_id from v$session where sid=1956;
SQL_ID
---------------------------------------
9crcdnsy4yq0v
/* sql_plan and statement */
SQL> @/home/dechatt/sql/sql_plan
Enter value for sql_id: 9crcdnsy4yq0v
Enter value for child_number:
old 2: from table( dbms_xplan.display_cursor('&sql_id','&child_number') )
new 2: from table( dbms_xplan.display_cursor('9crcdnsy4yq0v','') )
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9crcdnsy4yq0v, child number 0
-------------------------------------
update /*+ bypass_ujvc */ (select x_recency_date, case when
nvl(pd.CHANGED_ON_DT,to_date('01-JAN-1900','DD-MON-YYYY')) >=
nvl(rd.RESP_DATE,to_date('01-JAN-1900','DD-MON-YYYY')) then pd.CHANGED_ON_DT
when nvl(pd.CHANGED_ON_DT,to_date('01-JAN-1900','DD-MON-YYYY')) <
nvl(rd.RESP_DATE,to_date('01-JAN-1900','DD-MON-YYYY')) then
rd.RESP_DATE end
--
//www.freelists.org/webpage/oracle-l
Other related posts:
