| linking gv$sql and gv$session [message #639794] |
Thu, 16 July 2015 07:44  |
 |
dag1ngapr1nce
Messages: 19
Registered: June 2012
Location: UK
|
Junior Member |
|
|
I've come across two methods of joining gv$sql with gv$session which returns different data. 1 method joins by sql_id the other by hash value / address, as follows:
Select s.*
From gv$sql s
Where s.sql_id In
(Select gv.sql_id
From gv$session gv
Where gv.sid = 243);
Select s.*
From gv$sql s
Where (s.hash_value, s.address, s.inst_id) In
(Select gv.sql_hash_value,
gv.sql_address,
gv.inst_id
From gv$session gv
Where gv.sid = 243);
Which is the correct method of joining?
|
|
|
|
|
|
|
|
| Re: linking gv$sql and gv$session [message #639810 is a reply to message #639794] |
Thu, 16 July 2015 10:08  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
matspring1 wrote on Thu, 16 July 2015 18:14
Which is the correct method of joining?
To achieve what? And why "gv$" views? Why not "v$"? What is the real problem you want to solve?
|
|
|
|
| Re: linking gv$sql and gv$session [message #639813 is a reply to message #639794] |
Thu, 16 July 2015 12:42 |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to be careful with queries like that. I suspect that they may have been written by someone who had not thought things through.
Are you trying to find the statement that the session is executing right now? Generally I would join on sql_id, but that is not a unique identifier (in recent releases, there may be several versions of the same sql_id so you need to inclide child_number in the join condition. Furthermore, you are querying gv$session, and sid is not a unique identifier.
|
|
|
|
| Re: linking gv$sql and gv$session [message #639900 is a reply to message #639813] |
Mon, 20 July 2015 04:46 |
|
dag1ngapr1nce
Messages: 19
Registered: June 2012
Location: UK
|
Junior Member |
|
|
Thanks for replies...
I'm trying to return the current SQL that is being run in my session. This is being called from a front end form to show user the progress of an job that is running. The GV$ tables are the RAC equivalent of the V$ tables and therefore require the inst_id value to restrict by instance.
See revised SQL:
Select s.*
From gv$sql s
Where (s.sql_id, s.CHILD_NUMBER, s.INST_ID ) In
(Select gv.sql_id, gv.SQL_CHILD_NUMBER, gv.INST_ID
From gv$session gv Where gv.sid = 243);
Select s.*
From gv$sql s
Where (s.hash_value, s.address, s.inst_id) In
(Select gv.sql_hash_value,
gv.sql_address,
gv.inst_id
From gv$session gv
Where gv.sid = 243);
Any thoughts on what is the better method is appreciated, and why each SQL returns different data
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: linking gv$sql and gv$session [message #639935 is a reply to message #639928] |
Mon, 20 July 2015 08:46 |
|
dag1ngapr1nce
Messages: 19
Registered: June 2012
Location: UK
|
Junior Member |
|
|
So I'm corrected the child_number SHOULD be used when joining by hash / address, I did find duplicates in some instances where the child_number was not used.
In this thread I initially said that I was getting different results when using the 2 methods, I think this was down to the timing of running sql 1 before sql 2, I now get exactly the same results when I run the following SQL:
Select s.*
From gv$sql s
Where (s.sql_id, s.CHILD_NUMBER, s.INST_ID ) In
(Select gv.sql_id, gv.SQL_CHILD_NUMBER, gv.INST_ID
From gv$session gv Where gv.sid = 243);
Select s.*
From gv$sql s
Where (s.hash_value, s.address, s.inst_id, s.CHILD_NUMBER) In
(Select gv.sql_hash_value, gv.sql_address, gv.inst_id, gv.child_number
From gv$session gv
Where gv.sid = 243);
Please note this is on a 11g instance, I'm curious as to whether I'd get the same results on 9i, 10g, 12c etc......any thoughts?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
