Home » RDBMS Server » Performance Tuning » how can i know host name using sql id or sid (oracle 10g rac 10.2.0.4, windows server 2008 64 bit)
how can i know host name using sql id or sid [message #560193] Wed, 11 July 2012 03:10 Go to next message
x-oracle
Messages: 326
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
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 #560316 is a reply to message #560194] Thu, 12 July 2012 01:41 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
thanks michel for giving me your helpful reply yes we have oracle enterprise edition license also so i think in this this performance tuning also included
Re: how can i know host name using sql id or sid [message #560319 is a reply to message #560316] Thu, 12 July 2012 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The performances and tuning pack is not included in the Entreprise Edition, it is an add-on option.

Regards
Michel
Re: how can i know host name using sql id or sid [message #560327 is a reply to message #560319] Thu, 12 July 2012 03:06 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
michecl i run this query

and my out put is like below

SQL> desc dba_hist_active_sess_history


Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 SNAP_ID                                            NUMBER
 DBID                                               NUMBER
 INSTANCE_NUMBER                                    NUMBER
 SAMPLE_ID                                          NUMBER
 SAMPLE_TIME                                        TIMESTAMP(3)
 SESSION_ID                                         NUMBER
 SESSION_SERIAL#                                    NUMBER
 USER_ID                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 SQL_PLAN_HASH_VALUE                                NUMBER
 FORCE_MATCHING_SIGNATURE                           NUMBER
 SQL_OPCODE                                         NUMBER
 PLSQL_ENTRY_OBJECT_ID                              NUMBER
 PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER
 PLSQL_OBJECT_ID                                    NUMBER
 PLSQL_SUBPROGRAM_ID                                NUMBER
 SERVICE_HASH                                       NUMBER
 SESSION_TYPE                                       VARCHAR2(10)
 SESSION_STATE                                      VARCHAR2(7)
 QC_SESSION_ID                                      NUMBER
 QC_INSTANCE_ID                                     NUMBER
 BLOCKING_SESSION                                   NUMBER
 BLOCKING_SESSION_STATUS                            VARCHAR2(11)
 BLOCKING_SESSION_SERIAL#                           NUMBER
 EVENT                                              VARCHAR2(64)
 EVENT_ID                                           NUMBER
 SEQ#                                               NUMBER
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 WAIT_CLASS                                         VARCHAR2(64)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_TIME                                          NUMBER
 TIME_WAITED                                        NUMBER
 XID                                                RAW(8)
 CURRENT_OBJ#                                       NUMBER
 CURRENT_FILE#                                      NUMBER
 CURRENT_BLOCK#                                     NUMBER
 PROGRAM                                            VARCHAR2(64)
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 CLIENT_ID                                          VARCHAR2(64)
 FLAGS                                              NUMBER



SQL> select USER_ID,SQL_ID,INSTANCE_NUMBER from dba_hist_active_sess_history;

 USER_ID SQL_ID        INSTANCE_NUMBER
-------- ------------- ---------------
      39 5p4rfyvk90d54               2
      39 5p4rfyvk90d54               2
      39 5p4rfyvk90d54               2
      39                             2
      39                             2
      39                             2
      39                             2
      39                             2
      39                             2
      39                             2
      39                             2


my actual question is how can i know the hostname using this SQL_ID "5p4rfyvk90d54" in this output or is there any other way so i can find this hostname
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 Go to previous messageGo to next message
John Watson
Messages: 4336
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 #560332 is a reply to message #560327] Thu, 12 July 2012 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My bad, I thought ash records client information (as in v$session) but it does not.
So you can't have client information history unless you record it in some fields at your service (client identifier, module, action).
You can also audit the sessions and then you have it dba_audit_trail/dba_audit_session.userhost/terminal.

Regards
Michel
Re: how can i know host name using sql id or sid [message #560339 is a reply to message #560332] Thu, 12 July 2012 04:39 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
Johnson thanks for you valuable reply but my question is i just want to know on which host this query is running coz here we have so many users.its ok this query is run on instance 2 but how can i know this which user run this query actuly we have only one user from application to database level. client site we have so many users
Re: how can i know host name using sql id or sid [message #560340 is a reply to message #560339] Thu, 12 July 2012 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you see my answer (which was posted when you were writing yours)?

Regards
Michel
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 Go to previous messageGo to next message
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 #560538 is a reply to message #560529] Sat, 14 July 2012 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not answer the question: "how can i know host name using sql id or sid" where host means client host.

Regards
Michel
Re: how can i know host name using sql id or sid [message #560760 is a reply to message #560538] Mon, 16 July 2012 13:56 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Michel,

I have been using the above queries since before sql_id existed and only hash_value was available. You can replace hash_value with sql_id and it does answer the question. Notice the HASH_VALUE of 1557134203 is only displayed on INST_ID of 3 and 4. The INST_ID of 1 and 2 were not displayed because the sql did not run on those nodes.
Re: how can i know host name using sql id or sid [message #560774 is a reply to message #560760] Tue, 17 July 2012 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This still does not give the client host or explain me where, I don't see it.

Regards
Michel
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 Go to previous messageGo to next message
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 #560967 is a reply to message #560916] Wed, 18 July 2012 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you mentioned in the script name, it only gives the sessions that currently execute the query (or have executed nothing since). I think OP wants all sessions that executed it not only the current ones.

Regards
Michel
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 Go to previous messageGo to next message
x-oracle
Messages: 326
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 #561040 is a reply to message #561032] Wed, 18 July 2012 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want information about other nodes then query gv$ views instead of v$ ones and don't forget to join the views with their inst_id column.

Regards
Michel
Re: how can i know host name using sql id or sid [message #561042 is a reply to message #561040] Wed, 18 July 2012 07:26 Go to previous messageGo to next message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply michel but michel i test this query on single node and for checking this i open 3 session with three different user and i run this select query on all this session after that when i run this query i cannot find this user machine name
Re: how can i know host name using sql id or sid [message #561043 is a reply to message #561042] Wed, 18 July 2012 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58354
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Show us what you do and get.
Do not (try to) explain it, you may miss something and no one can see you missed it.

Regards
Michel
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 Go to previous message
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%'
Previous Topic: Oracle process exceed SGA_MAX_TARGET
Next Topic: DB FILE SEQUENTIAL READ IN Full table scan
Goto Forum:
  


Current Time: Sun Jul 13 09:02:06 CDT 2014

Total time taken to generate the page: 0.19759 seconds