压测中数据库异常时的oracle慢sql跟踪

oracle慢sql跟踪

查询执行最慢的sql

select *
from (select
sa.FIRST_LOAD_TIME,
sa.LAST_ACTIVE_TIME,
sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS “执行次数”,
round(sa.ELAPSED_TIME / 1000000, 2) “总执行时间”,
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) “平均执行时间”,
sa.COMMAND_TYPE,
sa.PARSING_USER_ID “用户ID”,
u.username “用户名”,
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
and u.username=‘ZHSCJG’ --用户名按实际填写
order by sa.LAST_ACTIVE_TIME desc,(sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;

查询当天执行慢的SQL：

SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) “执行时间’S’”,
S.EXECUTIONS “执行次数”,
S.OPTIMIZER_COST “COST”,
S.SORTS,
S.MODULE, --连接模式（JDBC THIN CLIENT：程序）
– S.LOCKED_TOTAL,
S.PHYSICAL_READ_BYTES “物理读”,
– S.PHYSICAL_READ_REQUESTS “物理读请求”,
S.PHYSICAL_WRITE_REQUESTS “物理写”,
– S.PHYSICAL_WRITE_BYTES “物理写请求”,
S.ROWS_PROCESSED “返回行数”,
S.DISK_READS “磁盘读”,
S.DIRECT_WRITES “直接路径写”,
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, ‘YYYY-MM-DD’) =
TO_CHAR( SYSDATE, ‘YYYY-MM-DD’ )
AND S.COMMAND_TYPE IN (2 , 3, 5, 6 , 189)
ORDER BY “执行时间’S’” DESC;

---

/* SQL中 COMMAND_TYPE意义:
2：INSERT
3：SELECT
6：UPDATE
7：DELETE
189：MERGE

查询次数最多的SQL

select *
from (select s.SQL_TEXT,
s.EXECUTIONS “执行次数”,
s.PARSING_USER_ID “用户名”,
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;

完整跟踪

select
a.FIRST_LOAD_TIME
, a.LAST_ACTIVE_TIME
,a.*
from v

$

sqlarea a
where 1=1
and a.LAST_ACTIVE_TIME >= to_date(‘2014-12-24 14:12:00’,‘yyyy-MM-dd HH24:mi:ss’)
– and a.LAST_ACTIVE_TIME < to_date(‘2014-11-25 09:41:59’,‘yyyy-MM-dd HH24:mi:ss’)
AND (a.MODULE is null or lower(nvl(a.MODULE,’’)) <> ‘pl/sql developer’ )
–and sql_text like ‘%insert%’
and not (lower(sql_fulltext) like ‘%qdi_modules%’
or lower(sql_fulltext) like ‘%qdi_host %’
or lower(sql_fulltext) like ‘%sm_operatelog%’
or lower(sql_fulltext) like ‘%sys.scheduler${}_{j}oborlower(sq{l}_{f}ulltext)lik{e}^{\prime }$_job_run_details%’
or lower(sql_fulltext) like ‘%sys.scheduler$_event_log%’
)
order by a.last_active_time asc

仅对增删改跟踪

select
a.FIRST_LOAD_TIME
, a.LAST_ACTIVE_TIME
,a.*
from v$
sqlarea a
where 1=1
and a.LAST_ACTIVE_TIME >= to_date(‘2014-12-24 14:12:00’,‘yyyy-MM-dd HH24:mi:ss’)
– and a.LAST_ACTIVE_TIME < to_date(‘2014-11-25 09:41:59’,‘yyyy-MM-dd HH24:mi:ss’)
AND (a.MODULE is null or lower(nvl(a.MODULE,’’)) <> ‘pl/sql developer’ )
and ( lower(sql_fulltext) like ‘%insert%’ or lower(sql_fulltext) like ‘%update%’ or lower(sql_fulltext) like ‘%delete %’ )
and not (lower(sql_fulltext) like ‘%qdi_modules%’
or lower(sql_fulltext) like ‘%qdi_host %’
or lower(sql_fulltext) like ‘%sm_operatelog%’
or lower(sql_fulltext) like ‘%sys.scheduler${}_{j}oborlower(sq{l}_{f}ulltext)lik{e}^{\prime }$_job_run_details%’
or lower(sql_fulltext) like ‘%sys.scheduler$_event_log%’
)
order by a.last_active_time asc

查找最近一分钟内，最消耗CPU的SQL语句：

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV S Q L A R E A V S W H E R E V S . S Q L I D = A S H . S Q L I D A N D A S H . I N S T I D = V S . I N S T I D ) S Q L T E X T , A S H . S Q L C H I L D