Sampling Query
Profiler Sampling Query on Oracle
select
- sysdate ,
- concat(s.sid,concat(', ',s.serial#)) ,
- s.sid ,
- s.sql_hash_value ,
- decode(s.WAIT_TIME, 0,'WAITING','ON CPU') ,
- s.serial# ,
- s.user# ,
- s.username ,
- s.machine ,
- s.sql_address ,
- s.sql_hash_value ,
- s.command ,
- s.type ,
- s.event ,
- s.p1 ,
- s.p2 ,
- s.p3 ,
- s.ROW_WAIT_OBJ# ,
- s.ROW_WAIT_FILE# ,
- s.ROW_WAIT_BLOCK# ,
- s.program ,
- s.module ,
- s.action
from
- v$session s
select
- systimestamp "time",
- concat(s.sid,concat(', ',s.serial#)) "session_id",
- s.sid "sid",
- s.sql_hash_value "sql_id",
- decode(s.WAIT_TIME, 0,'WAITING','ON CPU') "session_state",
- s.serial# "session_serial",
- s.user# "user_id",
- s.username "USER_NAME",
- s.machine "HOSTNAME",
- s.sql_address "sql_address",
- s.command "sql_opcode",
- s.type "session_type",
- decode(s.WAIT_TIME, 0,s.event,'ON CPU') "event",
- s.p1 "p1",
- s.p2 "p2",
- s.p3 "p3",
- s.paddr "paddr",
- s.ROW_WAIT_OBJ# "current_obj",
- s.ROW_WAIT_FILE# "current_file",
- s.ROW_WAIT_BLOCK# "current_block",
- s.program "program",
- s.module "module",
- s.action "action",
- s.sql_child_number "child_number",
- s.logon_time "LOGIN_TIME",
- s.server "CONNECTION_TYPE",
- s.RESOURCE_CONSUMER_GROUP "RESOURCE_GROUP",
- s.osuser "OS_USER",
- s.process "OS_PID",
- s.terminal "TERMINAL",
- s.client_info "CLIENT_INFO",
- s.client_identifier "CLIENT_ID",
- s.last_call_et "LAST_CALL_ELAPSED_TIME",
- s.sql_trace "SQL_TRACE"
from
- v$session s
where
- s.sid != ( select distinct sid from v$mystat where rownum < 2 ) and
- ( ( s.wait_time != 0 and /* on CPU */ s.status='ACTIVE' /* ACTIVE */)
- or
- s.wait_class !='Idle'
- )
SELECT
- file_id file_id,
- tablespace_name file_name
FROM dba_data_files
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')