Active session who wait events - Historique des versions

Lli le 23 mars 2023 à 13:03

2023-03-23T13:03:36Z

← Version précédente		Version du 23 mars 2023 à 15:03
Ligne 84 :		Ligne 84 :

	== wait event ==		== wait event ==
	<select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,		<syntaxhighlight lang="sql">
			select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
	substr(b.program\|\|b.module,1,15) program,substr(b.machine,1,22) machine,		substr(b.program\|\|b.module,1,15) program,substr(b.machine,1,22) machine,
	a.event,a.p1,b.sql_hash_value		a.event,a.p1,b.sql_hash_value
Ligne 94 :		Ligne 95 :
	order by 6		order by 6
	/		/
			</syntaxhighlight>
	[[Catégorie:Oracle]]		[[Catégorie:Oracle]]

Lli le 23 mars 2023 à 13:02

2023-03-23T13:02:33Z

← Version précédente		Version du 23 mars 2023 à 15:02
Ligne 41 :		Ligne 41 :

	== version with spid/linux ==		== version with spid/linux ==
	<~~code~~>SELECT nvl(ses.username,'ORACLE PROC')\|\|' ('\|\|ses.sid\|\|')' USERNAME,		<syntaxhighlight lang="sql">
	~~<nowiki> </nowiki>~~ SES.SID,		SELECT nvl(ses.username,'ORACLE PROC')\|\|' ('\|\|ses.sid\|\|')' USERNAME,
	~~<nowiki> </nowiki>~~ P.spid,		SES.SID,
	~~<nowiki> </nowiki>~~ P.Program,		P.spid,
	~~<nowiki> </nowiki>~~ SES.serial#,		P.Program,
	~~<nowiki> </nowiki>~~ MACHINE,		SES.serial#,
	~~<nowiki> </nowiki>~~ SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,		MACHINE,
	~~<nowiki> </nowiki>~~ --REPLACE(SQL.SQL_TEXT,CHR(10),~~<nowiki>~~''~~</nowiki>~~) STMT,		SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,
	~~<nowiki> </nowiki>~~ SQL.SQL_TEXT STMT,		--REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
	~~<nowiki> </nowiki>~~ ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) \|\| ':'		SQL.SQL_TEXT STMT,
	~~<nowiki> </nowiki> <nowiki>~~\|\|~~</nowiki>~~ ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) ~~<nowiki>~~\|\|~~</nowiki>~~ ':'		ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) \|\| ':'
	~~<nowiki> </nowiki> <nowiki>~~\|\|~~</nowiki>~~ ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT		\|\| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) \|\| ':'
	~~<nowiki> </nowiki>~~ FROM V$SESSION SES,		\|\| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
	~~<nowiki> </nowiki>~~ v$process p,		FROM V$SESSION SES,
	~~<nowiki> </nowiki>~~ V$SQL SQL		v$process p,
	~~<nowiki> </nowiki>~~ --V$SQLtext_with_newlines SQL		V$SQL SQL
	~~<nowiki> </nowiki>~~ --V$SQLtext SQL		--V$SQLtext_with_newlines SQL
	~~<nowiki> </nowiki>~~where SES.STATUS = 'ACTIVE'		--V$SQLtext SQL
	~~<nowiki> </nowiki>~~ and SES.USERNAME is not null		where SES.STATUS = 'ACTIVE'
	~~<nowiki> </nowiki>~~ and SES.SQL_ADDRESS = SQL.ADDRESS		and SES.USERNAME is not null
	~~<nowiki> </nowiki>~~ and SES.SQL_HASH_VALUE = SQL.HASH_VALUE		and SES.SQL_ADDRESS = SQL.ADDRESS
	~~<nowiki> </nowiki>~~ and SES.paddr = p.addr		and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
	~~<nowiki> </nowiki>~~ --and Ses.AUDSID <> userenv('SESSIONID')		and SES.paddr = p.addr
	~~<nowiki> </nowiki>~~order by runt desc, 1--,sql.piece;</~~code~~>		--and Ses.AUDSID <> userenv('SESSIONID')
			order by runt desc, 1--,sql.piece;
	<select x.sid		</syntaxhighlight><syntaxhighlight lang="sql">
	,x.serial#		select x.sid
	,x.username		,x.serial#
	,x.sql_id		,x.username
	,x.sql_child_number		,x.sql_id
	,optimizer_mode		,x.sql_child_number
	,hash_value		,optimizer_mode
	,address		,hash_value
	,sql_text		,address
	from v$sqlarea sqlarea		,sql_text
	,v$session x		from v$sqlarea sqlarea
	where x.sql_hash_value = sqlarea.hash_value		,v$session x
	and x.sql_address = sqlarea.address		where x.sql_hash_value = sqlarea.hash_value
	and x.username is not null;</~~code~~>		and x.sql_address = sqlarea.address
			and x.username is not null;
			</syntaxhighlight>

	== wait event ==		== wait event ==
Ligne 91 :		Ligne 93 :
	and username is not null		and username is not null
	order by 6		order by 6
	/~~</code>~~		/
	[[Catégorie:Oracle]]		[[Catégorie:Oracle]]

Lli le 23 mars 2023 à 12:59

2023-03-23T12:59:55Z

← Version précédente		Version du 23 mars 2023 à 14:59
Ligne 1 :		Ligne 1 :
	<~~code~~>set linesize 1000;		<syntaxhighlight lang="sql">
	set pagesize 1000;		set linesize 1000;
	select		set pagesize 1000;
	substr(a.spid,1,9) pid,		select
	substr(b.sid,1,5) sid,		substr(a.spid,1,9) pid,
	substr(b.serial#,1,5) ser#,		substr(b.sid,1,5) sid,
	substr(b.machine,1,25) box,		substr(b.serial#,1,5) ser#,
	substr(b.username,1,25) username,		substr(b.machine,1,25) box,
	-- b.server,		substr(b.username,1,25) username,
	substr(b.osuser,1,10) os_user,		-- b.server,
	substr(b.program,1,40) program,		substr(b.osuser,1,10) os_user,
	to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS')		substr(b.program,1,40) program,
	from v$session b, v$process a		to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS')
	where		from v$session b, v$process a
	b.paddr = a.addr		where
	and type='USER'		b.paddr = a.addr
	order by spid;</~~code~~>		and type='USER'
	'''strong text'''		order by spid;
	<~~code~~>SELECT nvl(ses.username,'ORACLE PROC')\|\|' ('\|\|ses.sid\|\|')' USERNAME,		</syntaxhighlight>'''strong text'''<syntaxhighlight lang="sql">
	~~<nowiki> </nowiki>~~ SID,		SELECT nvl(ses.username,'ORACLE PROC')\|\|' ('\|\|ses.sid\|\|')' USERNAME,
	~~<nowiki> </nowiki>~~ serial#,		SID,
	~~<nowiki> </nowiki>~~ MACHINE,		serial#,
	~~<nowiki> </nowiki>~~ SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,		MACHINE,
	~~<nowiki> </nowiki>~~ --REPLACE(SQL.SQL_TEXT,CHR(10),~~<nowiki>~~''~~</nowiki>~~) STMT,		SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,
	~~<nowiki> </nowiki>~~ SQL.SQL_TEXT STMT,		--REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
	~~<nowiki> </nowiki>~~ ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) \|\| ':'		SQL.SQL_TEXT STMT,
	~~<nowiki> </nowiki> <nowiki>~~\|\|~~</nowiki>~~ ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) ~~<nowiki>~~\|\|~~</nowiki>~~ ':'		ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) \|\| ':'
	~~<nowiki> </nowiki> <nowiki>~~\|\|~~</nowiki>~~ ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT		\|\| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) \|\| ':'
	~~<nowiki> </nowiki>~~ FROM V$SESSION SES,		\|\| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
	~~<nowiki> </nowiki>~~ V$SQL SQL		FROM V$SESSION SES,
	~~<nowiki> </nowiki>~~ --V$SQLtext_with_newlines SQL		V$SQL SQL
	~~<nowiki> </nowiki>~~ --V$SQLtext SQL		--V$SQLtext_with_newlines SQL
	~~<nowiki> </nowiki>~~where SES.STATUS = 'ACTIVE'		--V$SQLtext SQL
	~~<nowiki> </nowiki>~~ and SES.USERNAME is not null		where SES.STATUS = 'ACTIVE'
	~~<nowiki> </nowiki>~~ and SES.SQL_ADDRESS = SQL.ADDRESS		and SES.USERNAME is not null
	~~<nowiki> </nowiki>~~ and SES.SQL_HASH_VALUE = SQL.HASH_VALUE		and SES.SQL_ADDRESS = SQL.ADDRESS
	~~<nowiki> </nowiki>~~ --and Ses.AUDSID <> userenv('SESSIONID')		and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
	~~<nowiki> </nowiki>~~order by runt desc, 1--,sql.piece;</~~code~~>		--and Ses.AUDSID <> userenv('SESSIONID')
			order by runt desc, 1--,sql.piece;
			</syntaxhighlight>

	== version with spid/linux ==		== version with spid/linux ==
Ligne 64 :		Ligne 66 :
	<nowiki> </nowiki>order by runt desc, 1--,sql.piece;</code>		<nowiki> </nowiki>order by runt desc, 1--,sql.piece;</code>

	<~~code>~~select x.sid		<select x.sid
	,x.serial#		,x.serial#
	,x.username		,x.username
Ligne 80 :		Ligne 82 :

	== wait event ==		== wait event ==
	<~~code>~~select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,		<select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
	substr(b.program\|\|b.module,1,15) program,substr(b.machine,1,22) machine,		substr(b.program\|\|b.module,1,15) program,substr(b.machine,1,22) machine,
	a.event,a.p1,b.sql_hash_value		a.event,a.p1,b.sql_hash_value

Lli : Page créée avec «  `set linesize 1000; set pagesize 1000; select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, substr(b.machine,1,25) box, substr(b.username,1,25) username, -- b.server, substr(b.osuser,1,10) os_user, substr(b.program,1,40) program, to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS') from v$session b, v$process a where b.paddr = a.addr and type='USER' order... »`


2023-03-23T12:57:48Z
Page créée avec «   <code>set linesize 1000;  set pagesize 1000;  select         substr(a.spid,1,9) pid,         substr(b.sid,1,5) sid,         substr(b.serial#,1,5) ser#,         substr(b.machine,1,25) box,         substr(b.username,1,25) username,  --       b.server,         substr(b.osuser,1,10) os_user,         substr(b.program,1,40) program,          to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS')   from v$session b, v$process a  where  b.paddr = a.addr  and type='USER'  order... »
Nouvelle page
  <code>set linesize 1000;

 set pagesize 1000;

 select

        substr(a.spid,1,9) pid,

        substr(b.sid,1,5) sid,

        substr(b.serial#,1,5) ser#,

        substr(b.machine,1,25) box,

        substr(b.username,1,25) username,

 --       b.server,

        substr(b.osuser,1,10) os_user,

        substr(b.program,1,40) program,

         to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS') 

 from v$session b, v$process a

 where

 b.paddr = a.addr

 and type='USER'

 order by spid;</code> 

'''strong text'''

 <code>SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

 <nowiki> </nowiki>      SID,  

 <nowiki> </nowiki>      serial#,

 <nowiki> </nowiki>      MACHINE, 

 <nowiki> </nowiki>      SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,

 <nowiki> </nowiki>      --REPLACE(SQL.SQL_TEXT,CHR(10),<nowiki>''</nowiki>) STMT, 

 <nowiki> </nowiki>     SQL.SQL_TEXT STMT, 

 <nowiki> </nowiki>     ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

 <nowiki> </nowiki>      <nowiki>||</nowiki> ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) <nowiki>||</nowiki> ':'

 <nowiki> </nowiki>      <nowiki>||</nowiki> ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 

 <nowiki> </nowiki> FROM V$SESSION SES,   

 <nowiki> </nowiki>      V$SQL SQL 

 <nowiki> </nowiki>      --V$SQLtext_with_newlines SQL 

 <nowiki> </nowiki>      --V$SQLtext SQL 

 <nowiki> </nowiki>where SES.STATUS = 'ACTIVE'

 <nowiki> </nowiki>  and SES.USERNAME is not null

 <nowiki> </nowiki>  and SES.SQL_ADDRESS    = SQL.ADDRESS 

 <nowiki> </nowiki>  and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 

 <nowiki> </nowiki>  --and Ses.AUDSID <> userenv('SESSIONID') 

 <nowiki> </nowiki>order by runt desc, 1--,sql.piece;</code>



== version with spid/linux ==

 <code>SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

 <nowiki> </nowiki>      SES.SID,

 <nowiki> </nowiki>      P.spid,

 <nowiki> </nowiki>      P.Program,

 <nowiki> </nowiki>      SES.serial#,

 <nowiki> </nowiki>      MACHINE, 

 <nowiki> </nowiki>      SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,

 <nowiki> </nowiki>      --REPLACE(SQL.SQL_TEXT,CHR(10),<nowiki>''</nowiki>) STMT, 

 <nowiki> </nowiki>     SQL.SQL_TEXT STMT, 

 <nowiki> </nowiki>     ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

 <nowiki> </nowiki>      <nowiki>||</nowiki> ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) <nowiki>||</nowiki> ':'

 <nowiki> </nowiki>      <nowiki>||</nowiki> ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 

 <nowiki> </nowiki> FROM V$SESSION SES,

 <nowiki> </nowiki> v$process p,

 <nowiki> </nowiki>      V$SQL SQL 

 <nowiki> </nowiki>      --V$SQLtext_with_newlines SQL 

 <nowiki> </nowiki>      --V$SQLtext SQL 

 <nowiki> </nowiki>where SES.STATUS = 'ACTIVE'

 <nowiki> </nowiki>  and SES.USERNAME is not null

 <nowiki> </nowiki>  and SES.SQL_ADDRESS    = SQL.ADDRESS 

 <nowiki> </nowiki>  and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

 <nowiki> </nowiki>  and SES.paddr = p.addr

 <nowiki> </nowiki>  --and Ses.AUDSID <> userenv('SESSIONID') 

 <nowiki> </nowiki>order by runt desc, 1--,sql.piece;</code>



 <code>select x.sid

       ,x.serial#

       ,x.username

       ,x.sql_id

       ,x.sql_child_number

       ,optimizer_mode

       ,hash_value

       ,address

       ,sql_text

 from   v$sqlarea sqlarea

       ,v$session x

 where  x.sql_hash_value = sqlarea.hash_value

 and    x.sql_address    = sqlarea.address

 and    x.username       is not null;</code>



== wait event ==

     <code>select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,

     substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,

     a.event,a.p1,b.sql_hash_value

     from v$session_wait a,V$session b

     where b.sid=a.sid

     and a.event not in('SQL*Net message from client','SQL*Net message to client',

     'smon timer','pmon timer')

     and username is not null

     order by 6

 /</code>

[[Catégorie:Oracle]]