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

  • From: "eagle fan" <eagle.f@xxxxxxxxx>
  • To: j.velikanovs@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 20 Jun 2006 10:22:44 +0800

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 WT SQL_HASH_VALUE ------------------------------ ---------- ---------- ---- ---- ---- -------------- SQL*Net message from client 1952673792 1 0 0 6 0 SQL*Net message from client 1650815232 1 0 0 0 201505526




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 0 750036270


EVENT P1 P2 P3 W WT SQL_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 0 3445726523


On 6/19/06, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx> 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
//www.freelists.org/archives/ora-apps-dba/05-2006/msg00000.html

On 6/19/06, Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx> wrote:
>
>
> what about locks?
>
>
>  ________________________________
>  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of eagle fan
> Sent: 17. júní 2006 18:35
> To: oracle-l@xxxxxxxxxxxxx
> 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 -- //www.freelists.org/webpage/oracle-l





--
Eagle Fan

Oracle DBA

Other related posts: