Re: Hash Join and sequential reads

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_at_gmail.com> To: <oracle-l_at_freelists.org> 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

--
http://www.freelists.org/webpage/oracle-l