RE: Hash Join and sequential reads
Date: Thu, 17 Jul 2008 14:08:52 -0400
Message-ID: <027601c8e838$2ca40b20$1100a8c0@rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Debaditya Chatterjee
Sent: Wednesday, July 16, 2008 8:22 PM
To: oracle-l_at_freelists.org
<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>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 17 2008 - 13:08:52 CDT