ase.sql 常用的查看活动会重要信息的SQL

weizhao.zhang （anbob）

534

2页

8次

2019-11-02

5墨值下载

-- file: ase.sql

-- author: weejar(anbob.com)

-- Desc. To Display all sessions of ForeGround not "inactive"

-- date: 2016/7/25

SET LINES 400 PAGES 1000

COL username FOR a10

COL machine FOR a10

COL osuser FOR a10 TRUNC

COL module FOR a20 TRUNC

COL event FOR a20 TRUNC

COL sqltext FOR a30

COL sql_id FOR a15

COL wai_secinwait FOR a10

COL bs FOR a10

COL ch# FOR 999

COL cpu_value FOR 999,999,999 HEADING 'CPU'

SELECT /*+ordered rule*/

ses.username,

ses.sid,

CASE

WHEN ses.state != 'WAITING' THEN 'On CPU / runqueue'

ELSE event

END

AS event,

ses.machine,

regexp_substr(ses.module,'[^@]+') module,

ses.status,

ses.last_call_et --, seq#

, ses.sql_id,

wait_time

|| ':'

|| seconds_in_wait wai_secinwait,

row_wait_obj#,

substr(sql.sql_text,1,30) sqltext,

blocking_instance

|| ':'

|| blocking_session bs,

sql_child_number ch#,

osuser,

TO_CHAR(sql_exec_id,'xxxxxxxx') hex -- ,sstat.value cpu_value

--ltrim(p1raw,'0') p1raw

--taddr

FROM

v$session ses

LEFT JOIN v$sql sql ON ses.sql_hash_value = sql.hash_value

AND ses.sql_child_number = sql.child_number

WHERE

ses.type = 'USER'

AND ses.status <> 'INACTIVE'

AND ses.sid NOT IN (

SELECT

sys_context('userenv','sid')

FROM

dual

)

ORDER BY

last_call_et,

seconds_in_wait;

SELECT

SYSDATE current_time

FROM

dual;

of 2

5墨值下载

oracle 优化日常运维

关注

评论