RE: Hash Join and sequential reads
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <debaditya.chatterjee@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jul 2008 14:08:52 -0400
JL already made a reasonable conjectures about non-index driven db file
sequential read waits from your query.
I have a question about your query and data:
Do you currently have any actual w_person_d.changed_on_dt values earlier
than 01-JAN-1900?
I believe your when case logic only makes use of a null
w_response_d.resp_date by setting a date older than the aforementioned
01-JAN-1900 to null.
It's possible I read that wrong as formatted, but if I'm right I guess I'd
update any very old dates to null (if that is your intention and any exist)
and then dispense with the outer bit of the outer join. Without caring
whether I annoy the anti-guessing alliance, I'd guess you're trying to pick
up the highest date changed for a given integration_id. It hardly seems like
a good idea to update every single row in w_person_d (especially for the
rows for which a null was generated for the outer part of the id match and
the value is updated to either itself [or NULL in the case of an old
current date]).
Of course I may have misread that case clause.
regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Debaditya Chatterjee
Sent: Wednesday, July 16, 2008 8:22 PM
To: oracle-l@xxxxxxxxxxxxx
<snip>Can anybody help me understand why there is a db file sequential read
wait event ?
Thanks
Deba.
<snip>
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
derived_recency_date from w_person_d pd,(select max(d.resp_date) resp_date,
nvl(X_PR_CON_ID, X_PRSP_CON_ID) con_prsp_id from w_response_d d
group by nvl(X_PR_CON_ID, X_PRSP_CON_ID)) rd where pd.integration_id
=
rd.con_prsp_id(+)) v1 set v1.x_recency_date = v1.derived_recency_date
<snip>SQL> SELECT opname, sofar, totalwork, start_time,
time_remaining, elapsed_seconds, MESSAGE
FROM v$session_longops
WHERE SID = 1956
ORDER BY start_time 2 3 4 5
6 /
OPNAME SOFAR TOTALWORK START_TIME
TIME_REMAINING ELAPSED_SECONDS
--------------- ---------- ---------- --------------------------
-------------- ---------------
MESSAGE
-----------------------------------------------------------------
<snip>
Hash Join 22635 66525 15-JUL-2008 14:11:09
185278 95552
Hash Join: : 22635 out of 66525 Blocks done
<snip>
Other related posts:
