| how can i know host name using sql id or sid [message #560193] |
Wed, 11 July 2012 03:10  |
 |
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
|
Senior Member |
|
|
hello i am new in rac
here i have three tair application
i want to know it hostname from sid or sqlid
i want to know which query run on which host
coz i have one user from application to database
so i want to know which query consume more time on which host
|
|
|
|
| Re: how can i know host name using sql id or sid [message #560194 is a reply to message #560193] |
Wed, 11 July 2012 03:19  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
If you want which hosts launch a query (not runs as a query runs on the database server) from a sql_id, you can't unless you bought Performances and Tuning pack and can query DBA_HIST_ACTIVE_SESS_HISTORY (and the query you're looking at is not too fast as this view does not record ALL queries but only those that were active at the moment the snapshot is taken).
Regards
Michel
[Edit: clearer]
[Updated on: Thu, 12 July 2012 01:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: how can i know host name using sql id or sid [message #560330 is a reply to message #560327] |
Thu, 12 July 2012 03:23 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The answer to your question is simple: the query was run by instance number 2, so you need to determine which host instance 2 is started. Use SRVCTL STATUS DATABASE to find out.
But I don't understand what use this information is. If your RAC is setup in the usual manner, all connections will be load balanced across all instances, so the query could be run on another instance next time.
And, by the way, before you look at those HIST views you must check whether you have licensed the Performance and Tuning packs.
|
|
|
|
|
|
|
|
|
|
| Re: how can i know host name using sql id or sid [message #560529 is a reply to message #560340] |
Fri, 13 July 2012 20:47 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
By taking snapshots of gv$sqlarea and joining it with gv$instance
you can determine what is currently running on each instance.
In this example I display buffer_gets but you can also make a copy
of the sql and display elapsed_time or cpu_time.
ECSCDAP1P > @buffer_gets_Gv$sqlarea605.sql
INST_ID BUFFER_GETS EXECUTIONS HASH_VALUE SQL_TEXT
------- ----------- ---------- ---------- --------------------------------------
4 1087404 24 82583896 select * from ( select clubarticl0_.id
2 1280628 24 1611313343 select * from ( select content0_.id as
4 1439388 36 969106748 select * from ( select clubarticl0_.id
2 1447572 36 2195328336 select * from ( select clubarticl0_.id
3 1714212 12 2451515768 select * from ( select clubarticl0_.id
4 1726512 48 2955295546 select * from ( select contentlis0_.id
1 2117088 24 969106748 select * from ( select clubarticl0_.id
3 2172228 84 1557134203 select count(contentlis0_.id) as col_0
3 2242560 12 3594462967 select * from ( select image0_.id as i
4 2262456 24 1557134203 select count(contentlis0_.id) as col_0
The sql can be displayed with the following.
ECSCDAP1P > select inst_id,sql_text
2 from gv$sqltext b
3 where b.hash_value=1557134203
4 order by inst_id,hash_value,b.piece;
INST_ID SQL_TEXT
---------- ----------------------------------------------------------------
3 select count(contentlis0_.id) as col_0_0_ from CONTENT_LIST cont
3 entlis0_ inner join CONTENT contentlis0_1_ on contentlis0_.id=co
3 ntentlis0_1_.id where contentlis0_1_.status = 'ACTIVE' and conte
3 ntlis0_1_.CONTENT_DATE <= CURRENT_TIMESTAMP and contentlis0_.LIS
3 T_TYPE='PhotoGallery' and contentlis0_1_.SHOW_IN_DYNA_LISTS='Y'
3 and contentlis0_1_.club_id=:1
4 select count(contentlis0_.id) as col_0_0_ from CONTENT_LIST cont
4 entlis0_ inner join CONTENT contentlis0_1_ on contentlis0_.id=co
4 ntentlis0_1_.id where contentlis0_1_.status = 'ACTIVE' and conte
4 ntlis0_1_.CONTENT_DATE <= CURRENT_TIMESTAMP and contentlis0_.LIS
4 T_TYPE='PhotoGallery' and contentlis0_1_.SHOW_IN_DYNA_LISTS='Y'
4 and contentlis0_1_.club_id=:1
And buffer_gets_Gv$sqlarea605.sql that produces the active sql report is:
set termout off
drop table gv$sqlarea1;
drop table gv$sqlarea2;
create table gv$sqlarea1 as select BUFFER_GETS,EXECUTIONS,HASH_VALUE,SQL_TEXT,INST_ID from Gv$sqlarea;
execute dbms_lock.sleep(5);
create table gv$sqlarea2 as select BUFFER_GETS,EXECUTIONS,HASH_VALUE,SQL_TEXT,INST_ID from Gv$sqlarea;
set wrap off
set termout on
select i.inst_id,(b.buffer_gets-a.buffer_gets)*12 buffer_gets,
(b.executions-a.executions)*12 executions,
a.hash_value,a.sql_text
from gv$sqlarea2 b,gv$sqlarea1 a, gv$instance i
where a.hash_value=b.hash_value and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and b.buffer_gets-a.buffer_gets > 1
order by b.buffer_gets-a.buffer_gets;
set termout off
drop table gv$sqlarea1;
drop table gv$sqlarea2;
set termout on
|
|
|
|
|
|
|
|
|
|
| Re: how can i know host name using sql id or sid [message #560916 is a reply to message #560774] |
Tue, 17 July 2012 14:50 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
My bad. I did not notice that the client was required, not the server.
The following query displays the client "MACHINE" from which the sql was launched.
ECSCDAP1P > @active_sql_display_CLIENT.sql
USERNAME SEC_WAIT MACHINE EVENT SQL_ID SQL_TEXT
----------- -------- ------------ --------------------------- ------------- --------------------
CDA_RO_USER 0 csprdapp04 SQL*Net message from client 13whfbq91yabs select * from ( sele
CDA_WR_USER 0 csprdwrapp01 SQL*Net message from client f1ub4t6w4pasb select user0_.id as
CDA_RO_USER 0 csprdapp01 SQL*Net message from client btw3xcv3kcvrs select * from ( sele
CDA_RO_USER 1 csprdapp02 SQL*Net message from client aw0tp09fczyvv select * from ( sele
CDA_RO_USER 0 csprdapp03 SQL*Net message to client 3pvmncfmkc0nf select distinct tag0
CDA_RO_USER 0 csprdapp02 SQL*Net message from client dx7pczk1dn3ah select * from ( sele
CDA_RO_USER 0 csprdapp02 SQL*Net message from client 0nny8q0s56f1s select tag0_.id as i
CDA_RO_USER 0 csprdapp03 gc cr request aw0tp09fczyvv select * from ( sele
ECSCDAP1P > list
1 select S.USERNAME,S.seconds_in_wait sec_wait,machine,S.event,a.sql_id,a.SQL_TEXT,
2 sid,serial#,to_char(logon_time,'DD-MON-RR HH24:MI') login
3 from v$session S,V$SQLAREA A
4 where S.status='ACTIVE' and S.username is not null
5 AND S.sql_address=A.address
6* and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
ECSCDAP1P > /
USERNAME SEC_WAIT MACHINE EVENT SQL_ID SQL_TEXT
----------- -------- ------------ --------------------------- ------------- --------------------
CDA_RO_USER 0 csprdapp04 gc cr request 1ryb91rqt3xv7 select * from ( sele
CDA_RO_USER 0 csprdapp02 gc cr request aw0tp09fczyvv select * from ( sele
CDA_RO_USER 0 csprdapp03 SQL*Net message from client fsn8p4zyyydyx select count(audiovi
CDA_RO_USER 0 csprdapp04 SQL*Net message from client gsz21b7ahvcaa select * from ( sele
CDA_RO_USER 0 csprdapp02 SQL*Net message from client dcza8qn6hut86 select * from ( sele
CDA_RO_USER 0 csprdapp01 SQL*Net message from client f940s21h0pc5z select * from ( sele
CDA_RO_USER 0 csprdapp03 gc current request cnmnq1mrk3mmu select count(audiovi
CDA_RO_USER 0 csprdapp03 gc current request fnh090pxcvkax select * from ( sele
CDA_RO_USER 0 csprdapp04 SQL*Net message from client 303k9hhvt9jvh select * from ( sele
|
|
|
|
|
|
| Re: how can i know host name using sql id or sid [message #561032 is a reply to message #560916] |
Wed, 18 July 2012 06:51 |
|
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
|
Senior Member |
|
|
thanks for you reply alan.kendall@nfl.com
wheni run this you query its just me info about only waiting sessions only
and michel i want to know about live running all querys into rac database on which node
select S.USERNAME,S.seconds_in_wait sec_wait,machine,S.event,a.sql_id,a.SQL_TEXT,sid,serial#,to_char(logon_time,'DD-MON-RR HH24:MI') login
from v$session S,V$SQLAREA A
where S.status='ACTIVE' and S.username is not null
AND S.sql_address=A.address
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
USERNAME SEC_WAIT MACHINE EVENT SQL_ID SQL_TEXT SID SERIAL# LOGIN
SYSMAN 15 ROZI-PC wait for unread message on broadcast channel 2b064ybzkwf1y BEGIN EMD_NOTIFICATION.QUEU 159 3,526 18-JUL-12:28
hr 469 WORKGROUP\HAMID-PC enq: TX - row lock contention 79p4j022sybm3 update hr. dtest set DEPARTMENT_ID=390 where DEPARTMENT_ID=270
but why i cannot see another session who are query only select statement
|
|
|
|
|
|
|
|
|
|
| Re: how can i know host name using sql id or sid [message #561080 is a reply to message #561043] |
Wed, 18 July 2012 13:43 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
The previous query only displays was is active at this moment in time.
Here is the query revised to show both server node name and client machine.
I commented out the clause about active sessions. Maybe this will find your
query that you just ran.
ECSCDAP1P > @active_sql_display_CLIENT.sql
HOST_NAME USERNAME SEC_WAIT MACHINE EVENT SQL_ID SQL_TEXT
------------- ----------- -------- ------------ --------------------------- ------------- --------
csprdcdadb11 ECSCDAP1P 0 US\KENDALLA- PX Deq: Execution Msg 8n3kbbunw3ag0 select i
csprdcdadb11 ECSCDAP1P 0 US\KENDALLA- PX Deq: Execute Reply 8n3kbbunw3ag0 select i
csprdcdadb11 SYS 2504151 csprdcdadb11 SQL*Net message from client 4qm8a3w6a1rfd DECL
csprdcdadb12 CDA_RO_USER 0 csprdapp03 SQL*Net message from client 12h51s50kp0bd select *
csprdcdadb12 CDA_RO_USER 0 csprdapp05 SQL*Net message from client c7bzgq62rtzu1 select c
csprdcdadb12 ECSCDAP1P 0 US\KENDALLA- PX Deq: Execution Msg 8n3kbbunw3ag0 select i
csprdcdadb12 CDA_RO_USER 0 csprdapp01 SQL*Net message from client gwc3pnjkcmkza select *
csprdcdadb13 CDA_RO_USER 0 csprdapp05 SQL*Net message from client d4z3vkqvpwk3t select *
csprdcdadb13 CDA_RO_USER 0 csprdapp05 SQL*Net message from client cm9nbkpnu5483 select c
csprdcdadb13 ECSCDAP1P 0 US\KENDALLA- PX Deq: Execution Msg 8n3kbbunw3ag0 select i
csprdcdadb14 CDA_RO_USER 0 csprdapp02 SQL*Net message from client f940s21h0pc5z select *
csprdcdadb14 ECSCDAP1P 0 US\KENDALLA- PX Deq: Execution Msg 8n3kbbunw3ag0 select i
csprdcdadb14 CDA_RO_USER 0 csprdapp02 SQL*Net message from client 0nny8q0s56f1s select t
csprdcdadb14 CDA_RO_USER 0 csprdapp05 SQL*Net message from client 7jtbr3ymh5291 select *
ECSCDAP1P > list
1 select i.host_name,S.USERNAME,S.seconds_in_wait sec_wait,machine,S.event,a.sql_id,a.SQL_TEXT,
2 sid,serial#,to_char(logon_time,'DD-MON-RR HH24:MI') login
3 from gv$session S,gV$SQLAREA A,gv$instance i
4 where S.username is not null
5 -- and S.status='ACTIVE'
6 AND S.sql_address=A.address
7 and s.inst_id=a.inst_id and i.inst_id = a.inst_id
8* and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
