Oracle常用知识

–查找最近一分钟内，最消耗CPU的sql语句
SELECT sql_id,
count(*),
round(count() / sum(count()) over(), 2) pctload
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1 / (24 * 60)
AND session_type <> ‘BACKGROUND’
AND session_state = ‘ON CPU’
GROUP BY sql_id
ORDER BY count(*) desc;

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || ‘%’ PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT() / SUM(COUNT()) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
AND ASH.SESSION_STATE = ‘ON CPU’
AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

–查找最近一分钟内，最消耗I/O的sql语句
SELECT ash.sql_id,
count(*)
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT
WHERE ash.sample_time > sysdate -1/(24*60)
AND ash.session_state = ‘WAITING’
AND ash.event_id = evt.event_id
AND evt.wait_class = ‘USER I/O’
GROUP BY ash.sql_id
ORDER BY count(*) desc;

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || ‘%’ PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT() / SUM(COUNT()) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
AND ASH.SESSION_STATE = ‘WAITING’
AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
AND ASH.WAIT_CLASS = ‘USER I/O’
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

–查找最近一分钟内，最消耗CPU的session
SELECT session_id,
count(*)
FROM V$ACTIVE_SESSION_HISTORY
WHERE session_state = ‘ON CPU’
AND sample_time > sysdate -1/(24*60)
GROUP BY session_id
ORDER BY count(*) desc;

SELECT SESSION_ID,
COUNT(*)
FROM V$ACTIVE_SESSION_HISTORY V
WHERE V.SESSION_STATE = ‘ON CPU’
AND V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)
GROUP BY SESSION_ID
ORDER BY COUNT(*) DESC;

–查找最近一分钟内，最消耗资源的sql语句
SELECT ash.sql_id,
sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
sum(decode(ash.session_state,’WAITING’,1,0)) -
sum(decode(ash.session_state,’WAITING’,decode(en.wait_class,’USER I/O’,1,0),0)) “WAIT”,
sum(decode(ash.session_state,’WAITING’,decode(en.wait_class,’USER I/O’,1,0),0)) “IO”,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.sql_id
ORDER BY sum(decode(ash.session_state,’ON CPU’,1,1)) desc;

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || ‘%’ PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT() / SUM(COUNT()) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
AND ASH.SESSION_STATE = ‘WAITING’
AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
AND ASH.WAIT_CLASS = ‘USER I/O’
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

–查找最近一分钟内，最消耗资源的session
SELECT ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
sum(decode(ash.session_state,’WAITING’,1,0)) -
sum(decode(ash.session_state,’WAITING’,decode(en.wait_class,’USER I/O’,1,0),0)) “WAITING”,
sum(decode(ash.session_state,’WAITING’,decode(en.wait_class,’USER I/O’,1,0),0)) “IO”,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program
ORDER BY sum(decode(ash.session_state,’ON CPU’,1,1));

SELECT ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.USER_ID,
ASH.PROGRAM,
SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 0)) “CPU”,
SUM(DECODE(ASH.SESSION_STATE, ‘WAITING’, 1, 0)) -
SUM(DECODE(ASH.SESSION_STATE,
‘WAITING’,
DECODE(ASH.WAIT_CLASS, ‘USER I/O’, 1, 0),
0)) “WAITING”,
SUM(DECODE(ASH.SESSION_STATE,
‘WAITING’,
DECODE(ASH.WAIT_CLASS, ‘USER I/O’, 1, 0),
0)) “IO”,
SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 1)) “TOTAL”
FROM V$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
ORDER BY SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 1));

–查看日志目录
select * from v$diag_info;

–查看最近执行SQL
SELECT t.SQL_TEXT,t.SQL_FULLTEXT FROM v$sqlarea

–查看锁信息
SELECT t2.USERNAME,
t2.SID, –session号
t2.SERIAL#, –序列号
t2.OSUSER,
t2.MACHINE,
t2.PROGRAM,
t2.LOGON_TIME,
t2.COMMAND,
t2.LOCKWAIT,
t2.SADDR, –session地址
t2.PADDR, –进程地址，可以与v$process关联查询进程信息，如：SELECT * FROM v$process t where t.ADDR = t2.PADDR;
t2.TADDR, –事务地址
t2.SQL_ADDRESS,
t1.LOCKED_MODE
FROM V$locked_Object t1, V$session t2, dba_objects t3
where t1.SESSION_ID = t2.SID
and t1.OBJECT_ID = t3.OBJECT_ID
order by t2.LOGON_TIME;

说明：
username：oracle用户名
sid：进程号
serial#：序列号
object_name：表名
osuser：操作系统用户名
machine：机器名
program：操作工具
logon_time：登陆时间
lockwait：表示当前这张表是否正在等待其他用户解锁这张表
locked_mode：锁表模式（下面详细说明）
注意：这时候如果通过system用户执行select * from scott.emp for update;语句就无法成功执行。

v$locked_object中的LOCKED_MODE字段表示锁的模式，oracle中锁的模式有如下几种:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive

数字越大锁级别越高, 影响的操作越多。

1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, “resource busy and acquire with NOWAIT specified”
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

–解锁
alter system kill session ‘sid,seial#’;

–查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.
SELECT /+ ORDERED /
sql_text
FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b
WHERE b.sid = ‘67’) /* 此处67 为SID*/
ORDER BY piece ASC;

–级联删除父子分区
ALTER TABLE table_parent TRUNCATE PARTITION part_name CASCADE UPDATE INDEXES;

–oracle 12c以后，多行合并为一列查询方法(行合并为列)
SELECT t.user_id, listagg(t.role_id,’,’) within group (order by t.user_id) as role_id
FROM t_user_role_map t
group by t.user_id