| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can't find the sql text from v$sql via the hash value
hi Yury:
It's waiting on direct path read(lob). The database issue was related to application problem.
Just curious about why the lob access sql appeared in v$session.sql_hash_value.
This is the info I collected at that time.
The P1, P2 for direct path read is file#, block#. It belongs to the table's lob segment.
EVENT P1 P2 P3 W WTSQL_HASH_VALUE
------------------------------ ---------- ---------- ---- ---- ---- -------------- SQL*Net message from client 1952673792 1 0 0 6 0 SQL*Net message from client 1650815232 1 0 0 0201505526
direct path read (lob) 50 221516 4 0 0 750036270 direct path read (lob) 49 124173 3 0 0 750036270 SQL*Net more data to client 1952673792 2001 0 -1 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0750036270
EVENT P1 P2 P3 W WTSQL_HASH_VALUE
------------------------------ ---------- ---------- ---- ---- ---- -------------- SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net more data to client 1952673792 2001 0 0 0 1347728404 SQL*Net message from client 1650815232 1 0 -1 6 1857334316 SQL*Net message to client 1650815232 1 0 -1 0 2894211034 SQL*Net message from client 1952673792 1 0 0 0 3164742762 SQL*Net message from client 1952673792 1 0 1 03445726523
On 6/19/06, Jurijs Velikanovs <j.velikanovs_at_gmail.com> wrote:
>
> Eagle,
>
> You wrote in you very fist post
> >> When database hang, I got the sql hash value from v$session_wait view.
> Just wonder what kind of EVENT you observed in a v$session_wait view
> at the time of hang?
> What were the parameters P1, P2, P3 ?
>
> Yury
> http://www.freelists.org/archives/ora-apps-dba/05-2006/msg00000.html
>
> On 6/19/06, Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is> wrote:
> >
> >
> > what about locks?
> >
> >
> > ________________________________
> > From: oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]
> > On Behalf Of eagle fan
> > Sent: 17. júní 2006 18:35
> > To: oracle-l_at_freelists.org
> > Subject: Re: Can't find the sql text from v$sql via the hash value
> >
> >
> >
> > Thanks for your reply.
> >
> >
> > The hang just last several seconds, after several seconds, the
> application
> > client cancel the operation and mark the database as unavaliable.
> >
> >
> > Then we took the application clients back and connected to database
> again,
> > but after a while ,the same problem happened again.
> >
> > So I can't do snapshot when the database hung. We collected the
> statspack
> > every 15 minutes, but no useful info showed in it, because the hang just
> > last a very short period.
> >
> > I run a monitor script to collect some infomation such as machine name ,
> > hash value , wait event from v$session_wait and v$session view tables. I
> got
> > the hash value from v$session, but I can't get the sql text. So I still
> > don't know what's the root cause.
> >
> > The hash value seems is realated to "table_4_2000_f4e_0_0_0" , but not
> > related to the original sql text.
> >
> > Why v$session view record this hash value? I think it should record the
> hash
> > value related to the original sql text.
> >
> >
> > --
> > Eagle Fan
> >
> > Oracle DBA
> >
> >
> > Fyrirvari/Disclaimer
> > http://www.landsbanki.is/disclaimer
>
>
> --
> Yury
> +44 7738 013090 (GMT)
> ============================================
> http://otn.oracle.com/ocm/jvelikanovs.html
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Eagle Fan Oracle DBA -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 19 2006 - 21:22:44 CDT
 |
 |