Re: Can't find the sql text from v$sql via the hash value

Eagle,

>> Why v$session shows 750036270 instead of the sql hash value 3164742762 ?
I believe that this behaviour can be explained quite easily. As we know SQL execution and LOB access phases are separated in Oracle. The first phase (SQL execution) returns just locator (pointer) to LOB data. During second phase Oracle retrieves actual data from LOB.

In you case SQL executed fist of all and SQL_HASH_VALUE in v$session is 3164742762 but straight after that Oracle trying to execute LOB access phase and for that reason Oracle use additional data structure (table_4_%) and reflects it as HV = 750036270.

In normal case all those actions executed quickly and you don't note HV change in v$session.
As in you case access to LOB "hangs" you observe LOB access phase in v$session.

In order to prove that theory you can try to execute the following test:

• Preparation step create table t ( x clob ); insert into t values ( 'hello' ); commit;
• Test declare v_rec t%rowtype; v_var varchar2(1000);

cursor c is select * from t;
begin

dbms_application_info.set_action('open cursor'); open c;
dbms_lock.sleep(10);

dbms_application_info.set_action('fetch cursor'); fetch c into v_rec;
dbms_lock.sleep(10);

dbms_application_info.set_action('access lob'); v_var:=v_rec.x;
dbms_lock.sleep(10);
dbms_application_info.set_action(null);
end;
/

• in separate session execute select * from v$open_cursor a where a.sid=<sid>;

Pay attention at which phase table_4_% cursor will appear in open_cursor view for that session.
You will note that table_4% appears at "access lob" step.

Grant total: Oracle reflects SQL execution in V$SESSION by appropriate HASH_VALUE but straight after that this HV replaced by the LOB access phase HV (table_4%).

Just my 0.02£
Yury

PS Please correct me if I am wrong.
http://www.freelists.org/archives/ora-apps-dba/05-2006/msg00000.html

On 6/18/06, eagle fan <eagle.f_at_gmail.com> wrote:
> The session disconnected due to timeout, didn't get any ORA- errors.
>
> I traced the session using dbms_system.set_ev and I think I got the sql.
>
> But the hash value 750036270 doesn't appear in the session trace file. The
> hash value of the sql text is another hash value 3164742762 .
>
> But select sql_hash_value from v$session where sid = <sid I traced> , it
> always shows 750036270.
>
> Why v$session shows 750036270 instead of the sql hash value 3164742762 ?
>
>
>
> On 6/18/06, Alex Gorbachev <gorbyx_at_gmail.com> wrote:
> > Try to enable 10046 trace. Does application get any ORA- errors or
> > just disconnects due to timeout?
> >
> > 2006/6/17, eagle fan <eagle.f_at_gmail.com>:
> > > The hang just last several seconds, after several seconds, the
> application
> > > client cancel the operation and mark the database as unavaliable.
> >
> >
> > --
> > Best regards,
> > Alex Gorbachev
> >
> > http://blog.oracloid.com
> >
>
>
>
> --
> Eagle Fan
>
> Oracle DBA

-- 
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--
http://www.freelists.org/webpage/oracle-l