[toc]

等待事件

1
2
3
4
5
6
7
8
9
10
11
set lines 200
col event for a55
SELECT inst_id,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT
FROM GV$SESSION_WAIT
WHERE event NOT
IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message')
AND event NOT LIKE '%idle%'
AND event NOT LIKE '%Idle%'
AND event NOT LIKE '%Streams AQ%'
GROUP BY inst_id,EVENT
ORDER BY 1,5 desc;

查询会话相关

根据等待事件查会话

1
2
3
4
5
6
7
8
9
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10

SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6;

根据用户查会话

1
2
3
4
5
6
7
8
9
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10

SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.username='&user_name' AND s.paddr = p.addr order by 6;

根据SQL_ID查会话

1
2
3
4
5
6
7
8
9
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10

SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.sql_id='&sql_id' AND s.paddr = p.addr order by 6;

根据会话ID查会话详情

1
2
3
4
5
6
7
8
9
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10

SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session b_sess,logon_time FROM v$session s, v$process p WHERE sid = '&sid' AND s.paddr = p.addr;

查询阻塞会话

1
2
3
4
5
6
7
8
9
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10

select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session;

查询会话的对象信息

1
2
3
4
5
6
7
8
9
10
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10
col OBJECT_NAME for a30

select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid;

kill 会话

1
2
3
4
5
set line 199 
col event format a35

--杀某个SID会话
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1;

根据SQL_ID杀会话

1
2
3
4
set line 199 
col event format a35

SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1;

根据等待事件杀会话

1
2
3
set line 199 
col event format a35
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1;

根据用户杀会话

1
2
3
set line 199 
col event format a35
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1;

kill所有LOCAL=NO进程

1
ps -ef|grep LOCAL=NO|grep $ORACLE_SID|grep -v grep|awk '{print $2}' |xargs kill -9

锁相关

1
2
3
4
5
6
7
8
set linesize 180
col username for a15
col owner for a15
col OBJECT_NAME for a30
col SPID for a10

--查询某个会话的锁
select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;

查询TMTX锁

1
2
3
4
5
6
set linesize 180
col username for a15
col owner for a15
col OBJECT_NAME for a30
col SPID for a10
select /*+rule*/* from v$lock where ctime >1 and type in ('TX','TM') order by 3,9;

查询数据库中的锁

1
2
3
4
5
6
7
set linesize 180
col username for a15
col owner for a15
col OBJECT_NAME for a30
col SPID for a10

select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;

活动会话

活动会话的 sql 语句

1
2
3
4
5
6
7
8
9
10
11
12
prompt Active session with sql text
column USERNAME format a14
set linesize 200
column EVENT format a30

select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,500)
from gV$SESSION ses
,gV$SQL stx
,gV$PROCESS pro
where ses.paddr = pro.addr
and ses.status = 'ACTIVE'
and stx.hash_value = ses.sql_hash_value ;

活动会话的等待事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
prompt Active session with wait
select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_HASH_VALUE
from v$session s, v$session_wait sw
where s.sid=sw.sid
and s.USERNAME is not null
and s.status = 'ACTIVE';

select SESS.INST_ID,sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode
    from GV$locked_object lo,
    dba_objects ao,
    GV$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid and lo.ORACLE_USERNAME='INFORES' AND SESS.LAST_CALL_ET>600;

跟踪相关ssd 10046 oradebug hanganalyze

SQL 10046

1
2
3
4
alter session set tracefile_identifier='enmo10046';
alter session set events '10046 trace name context forever, level 12';
run your sql;
alter session set events '10046 trace name context off';

oradebug

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--如果会话已经运行了,可以用 oradebug

conn / as sysdba
oradebug setospid 16835
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off

--systemstate dump
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
oradebug tracefile_name;

--hanganalyze
oradebug setmypid
oradebug unlimit;
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
oradebug tracefile_name;

job 相关

查看运行的JOB并中断运行

1
2
3
4
5

select sid,job from dba_jobs_running;
select sid,serial# from v$session where sid='&sid';
alter system kill session '&sid,&serial';
exec dbms_job.broken(&job,true);

temp & undo

temp 表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

COLUMN tablespace FORMAT A20
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A40
SET LINESIZE 200
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program
FROM v$session a,
v$sort_usage b,
v$parameter p WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

undo 表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A30
COLUMN undoseg FORMAT A25
COLUMN undo FORMAT A20
SET LINESIZE 120
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial,
NVL(s.username, '(oracle)') AS username,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' AS undo
FROM v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

判断活跃会话

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
--判断活跃会话1
select count(*) ACTIVE_SESSION_COUNT,sum(last_call_et) TOTAL_ACTIVE_TIME ,max(last_call_et) MAX_ACTIVE_TIME,
nvl(event,'==grouping==')event, nvl(sql_id,'==grouping==') sql_id
from v$session
where status = 'ACTIVE' and
not ( type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
group by cube(event,sql_id)
having count(*)>1 or (grouping(event)+grouping(sql_id)=0)
order by 1
/

--判断活跃会话2(PL/SQL只考虑当前SQL)
select count(*) ACTIVE_SESSION_COUNT ,sum(sysdate-sql_exec_start)*86400 TOTAL_ACTIVE_TIME ,
max(sysdate-sql_exec_start)*86400 MAX_ACTIVE_TIME,
nvl(event,'==grouping==')event, nvl(sql_id,'==grouping==') sql_id
from v$session
where status = 'ACTIVE' and
not ( type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
group by cube(event,sql_id)
having count(*)>1 or (grouping(event)+grouping(sql_id)=0)
order by 1
/

--找到会话对应PL/SQL 对象
select p.object_name||'.'||p.procedure_name plsql_name--,...
from v$session s , dba_procedures p
where status = 'ACTIVE' and
not ( type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
and s.plsql_object_id = p.object_id (+)
and s.plsql_subprogram_id= p.subprogram_id (+);

--找到会话对应的等待对象
select o.owner||'.'||o.object_name waiting_object_name
from v$session s , dba_objects o
where s.status = 'ACTIVE' and
not ( s.type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
and s.row_wait_obj# = o.object_id (+);

dg 相关

dginfo.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/usr/bin/env sh
sqlplus / as sysdba <<EOF
set lines 123
set pages 200
col CTIME format a20
col NAME format a20
col VALUE format a20
col DATUM_TIME format a20
select open_mode, DATABASE_ROLE from v\$database;
SELECT TO_NUMBER( SUBSTR ( (SUBSTR (VALUE, 5)), 0, 2) * 3600 + SUBSTR ( (SUBSTR (VALUE, 5)), 4, 2) * 60 + SUBSTR ( (SUBSTR (VALUE, 5)), 7, 2)) dgbehind, TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss'
) CTIME, NAME, VALUE,DATUM_TIME FROM V\$DATAGUARD_STATS WHERE NAME ='apply lag';
select process,block#,blocks ,status ,sequence# from v\$managed_standby;
exit
EOF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
set lines 132
col message for a80
col timestamp for a20
SELECT ERROR_CODE, SEVERITY, MESSAGE,
TO_cHAR(TIMESTAMP, 'DD-MON-RR HH24:MI:SS') TIMESTAMP
FROM V$DATAGUARD_STATUS
WHERE CALLOUT='YES'
AND TIMESTAMP > SYSDATE-1;

select THREAD#,sequence#, first_time, next_time, applied from v$archived_log order by 3;

select name,database_role,switchover_status from v$database;
select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

col type for a15
set lines 122
set pages 33
col item for a20
col units for a15
select to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time, type,
item, units, sofar, total, timestamp
from v$recovery_progress;

set lines 132
col message for a80
col timestamp for a20
SELECT ERROR_CODE, SEVERITY, MESSAGE,
TO_cHAR(TIMESTAMP, 'DD-MON-RR HH24:MI:SS') TIMESTAMP
FROM V$DATAGUARD_STATUS
WHERE CALLOUT='YES'
AND TIMESTAMP > SYSDATE-1;

select a.thread#, b.max_available, a.max_applied
from
(
select thread#, max(sequence#) max_applied
from gv$archived_log
where applied='YES'
group by thread# ) a,
(
select thread#, max(sequence#) max_available
from gv$archived_log
group by thread# ) b
where a.thread#=b.thread#;

select name,value,datum_time from v$dataguard_stats;

--ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
--alter database recover managed standby database using current logfile disconnect from session;

表空间、asm

表空间使用率

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
set linesize 200
set pages 2000
col TABLESPACENAME for a30
SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
round(SUM(a.bytes/1024/1024/1024),2) AS "Totle_size(G)",
round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)",
round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Used_space(G)",
ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%",
round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_size(G)",
ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%"
FROM dba_data_files a,
(SELECT SUM(NVL(bytes,0)) free_space1,
file_id
FROM dba_free_space
GROUP BY file_id
) b
WHERE a.file_id = b.file_id(+)
GROUP BY a.TABLESPACE_NAME
ORDER BY "Used_percent%" desc;


set pagesize 10000
select TABLESPACE_NAME,round(sum(bytes)/1024/1024/1024,2) free_g from dba_free_space group by TABLESPACE_NAME order by 2 desc;

set lines 1000 pages 1000
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME;
set lines 160
col file_name for a100
select TABLESPACE_NAME,file_name from dba_temp_files;



asm
--查看磁盘
set linesize 160
col name for a20
col path for a50
col FAILGROUP for a20
select NAME,PATH,FAILGROUP,TOTAL_MB,FREE_MB,STATE from v$asm_disk_stat order by 1;

--查看磁盘组
set linesize 160
col name for a20
select NAME,STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

--查看ASM Operation
set linesize 160
select * from gv$asm_operation;

创建用户及表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
--获取创建用户脚本及权限
set line 199
set long 100000
set pages 1000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_users WHERE username = '&&Username') > 0)
THEN dbms_metadata.get_ddl ('USER', '&&Username')
ELSE to_clob (' -- Note: User not found!')
END ) extracted_ddl
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_ts_quotas WHERE username = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
ELSE to_clob (' -- Note: No TS Quotas found!')
END )
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_role_privs WHERE grantee = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
ELSE to_clob (' -- Note: No granted Roles found!')
END )
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_sys_privs WHERE grantee = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
ELSE to_clob (' -- Note: No System Privileges found!')
END )
FROM dual
UNION ALL
SELECT (
CASE
WHEN ((SELECT COUNT(*) FROM dba_tab_privs WHERE grantee = '&&Username') > 0)
THEN dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
ELSE to_clob (' -- Note: No Object Privileges found!')
END )
FROM dual

--表相关的统计信息
--包含分区、索引、索引字段
--先替换掉下面define值
define owner=BZHIS
define table_name=ZYGL_DXSF
--先替换掉上面define值
set linesize 160
col DATA_TYPE for a15
set pagesize 10000
col COLUMN_NAME for a30
col col for a30
select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tables t where owner = upper('&owner') and table_name = upper('&table_name');
select COLUMN_NAME, DATA_TYPE, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_columns t where owner = upper('&owner') and table_name = upper('&table_name');
select INDEX_NAME, BLEVEL BLev, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_indexes t where table_name = upper('&table_name') and table_owner = upper('&owner');
select /*+ first_rows use_nl(i,t)*/ i.INDEX_NAME, i.COLUMN_NAME, i.COLUMN_POSITION, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col from dba_ind_columns i, dba_tab_columns t where i.index_owner=t.owner and i.table_name = upper('&table_name') and i.index_owner = upper('&owner') and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position;



--查看表的索引
select col.table_owner "table_owner",
idx.table_name "table_name",
col.index_owner "index_owner",
idx.index_name "index_name",
uniqueness "uniqueness",
status,
column_name "column_name",
column_position
from dba_ind_columns col, dba_indexes idx
where col.index_name = idx.index_name
and col.table_name = idx.table_name and col.table_owner = idx.table_owner
and col.table_owner='&owner'
and col.table_name='&table_name')
order by idx.table_type,
idx.table_name,
idx.index_name,
col.table_owner,
column_position;


--收集统计信息
exec dbms_stats.gather_table_stats('STEVEN','AWEN_OGG_TEST',degree=>10,cascade=> TRUE,no_invalidate=>false);

查询DB Time

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
--查询DB Time
SELECT TO_CHAR(a.end_interval_time,'yyyymmdd hh24'),
SUM (a.db_time) inst1_m,
SUM (b.db_time) inst2_m
FROM
(SELECT pre_snap_id,
snap_id,
end_interval_time,
ROUND((value - pre_value) / 1000000 / 60) db_time
FROM
(SELECT a.snap_id,
end_interval_time,
lag(a.snap_id) over(order by a.snap_id) pre_snap_id,
value,
lag(value) over(order by a.snap_id) pre_value
FROM dba_hist_sys_time_model a,
dba_hist_snapshot b
WHERE stat_name = 'DB time'
AND a.dbid = b.dbid
AND a.snap_id = b.snap_id
AND a.instance_number=b.instance_number
AND a.dbid =
(SELECT dbid FROM v$database
)
AND a.instance_number = 1
)
WHERE pre_snap_id IS NOT NULL
AND end_interval_time>sysdate-30
ORDER BY snap_id DESC
) a,
(SELECT pre_snap_id,
snap_id,
end_interval_time,
ROUND((value - pre_value) / 1000000 / 60) db_time
FROM
(SELECT a.snap_id,
end_interval_time,
lag(a.snap_id) over(order by a.snap_id) pre_snap_id,
value,
lag(value) over(order by a.snap_id) pre_value
FROM dba_hist_sys_time_model a,
dba_hist_snapshot b
WHERE stat_name = 'DB time'
AND a.dbid = b.dbid
AND a.snap_id = b.snap_id
AND a.instance_number=b.instance_number
AND a.dbid =
(SELECT dbid FROM v$database
)
AND a.instance_number = 2
)
WHERE pre_snap_id IS NOT NULL
AND end_interval_time>sysdate-30
ORDER BY snap_id DESC
) b
WHERE a.snap_id=b.snap_id(+)
GROUP BY TO_CHAR(a.end_interval_time,'yyyymmdd hh24')
ORDER BY TO_CHAR(a.end_interval_time,'yyyymmdd hh24');



col LOG_HOUR format a12
select to_char(first_time,'YYYY-mm-dd') LOG_DATE, to_char(first_time,'HH24') LOG_HOUR, count(*) SWITCHES from v$loghist group by to_char(first_time,'YYYY-mm-dd') , to_char(first_time,'HH24') order by 1,2;



set linesize 180
col opname format a20
col target format a45
col units format a10
col time_remaining format 99990 heading Remaining[s]
col bps format 9990.99 heading [Units/s]
col fertig format 90.99 heading "complete[%]"
select sid,
opname,
target,
sofar,
totalwork,
units,
(totalwork-sofar)/time_remaining bps,
time_remaining,
sofar/totalwork*100 fertig
from v$session_longops
where time_remaining > 0;

执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
--explain查看SQL执行计划
EXPLAIN PLAN FOR select count(*) from steven.AWEN_OGG_TEST;
select * from table(dbms_xplan.display());

--查看AWR和CURSOR中的执行计划
select * from table(dbms_xplan.display_awr('&sqlid'));
select * from table(dbms_xplan.display_cursor('&sqlid'));

--查看内存中的执行计划
select '| Operation |Object Name | Rows | Bytes| Cost |'
as "Explain Plan in library cache:" from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
rpad(decode(id, 0, '----------------------------',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan sp
where sp.hash_value=&hash_value or sp.sql_id='&sqlid';

--查看历史执行计划
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='&sqlid' order by TIMESTAMP;


阻塞链条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
set lines 200 pages 500
col tree for a30
col event for a40

select *
from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
where isleaf = 1
order by tree_level asc;
1349 42777

INST_ID SID SERIAL# SQL_ID EVENT STATUS ISLEAF TREE TREE_LEVEL
---------- ---------- ---------- ------------- ---------------------------------------- -------- ---------- ------------------------------ ----------
1 150 8742 SQL*Net message from client INACTIVE 1 <- 25@2 <- 150@1 2
1 150 8742 SQL*Net message from client INACTIVE 1 <- 145@2 <- 25@2 <- 150@1 3
1 150 8742 SQL*Net message from client INACTIVE 1 <- 146@1 <- 25@2 <- 150@1 3

alter system kill session '1349, 42777'immediate;
alter system disconnect session '1349, 42777'immediate;

SELECT 'alter system disconnect session '''
|| s.sid
|| ', '
|| s.serial#
|| '''immediate;'
FROM gv$session s
WHERE S.USERNAME IS NOT NULL
AND MODULE LIKE '%.exe'
AND S.LAST_CALL_ET >= 600
AND S.STATUS = 'INACTIVE'
ORDER BY machine DESC;

批量kill

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/sh
. /home/oracle/.profile
sqlplus -s / as sysdba <<EOF
set pagesize 0
set long 90000
set feedback off
set echo off
spool killed_pid.txt
select spid from v\$process where program!= 'PSEUDO' and addr not in (select paddr from v\$session) and addr not in (select paddr from v\$bgprocess) and addr not in (select paddr from v\$shared_server);
spool off
exit;
EOF
echo ''>1.log
echo ''>kill_pid.sh
cat killed_pid.txt| while read line
do
if `ps -ef|grep $line|grep LOCAL=NO`
then echo `ps -ef|grep $line|grep LOCAL=NO`>>1.log
echo "kill -9 $line">>kill_pid.sh
fi
done

ash 3 段式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
  begin
dbms_workload_repository.create_snapshot();
end;
/

--ASH 3段式第一段,确定活跃会话最多的时间段及SNAP_ID
select snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi'),count(*) from
dba_hist_active_sess_history
where sample_time >=to_date('2020050501','yyyymmddhh24')
and sample_time <=to_date('2020050502','yyyymmddhh24')
group by snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi')
order by to_char(sample_time,'yyyymmdd-hh24mi');
201811300740
201811300758

--ASH 3段式第二段,获取特定SNAP及时间段内最多的等待事件
select snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi'),count(*),event from
dba_hist_active_sess_history
where sample_time >=to_date('2020050501','yyyymmddhh24')
and sample_time <=to_date('2020050503','yyyymmddhh24')
group by snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi'),event
having count(*) > 2
order by to_char(sample_time,'yyyymmdd-hh24mi');


--ASH 3段式第二段,获取特定SNAP及时间段内的等待链
with ash as (select instance_number,SESSION_ID,event,BLOCKING_SESSION,program ,
to_char(SAMPLE_TIME,'YYYYMMDD HH24MISS') SAMPLE_TIME,sample_id,blocking_inst_id
from dba_hist_active_sess_history where
sample_time >=to_date('2020030216','yyyymmddhh24')
and sample_time <=to_date('2020030217','yyyymmddhh24')
and snap_id = 13391
)
select SAMPLE_TIME,FINAL_BLK,FINAL_PROGRAM,nvl(FINAL_EVT,'ON CPU') as FINAL_EVT,(LVL2_EVENT) as LVL2_EVENT,count(*) from(
select SESSION_ID,SAMPLE_TIME,sys_connect_by_path(SESSION_ID,',') CHAIN,
connect_by_root(PROGRAM)as FINAL_PROGRAM,connect_by_root(SESSION_ID)FINAL_BLK,
connect_by_root(event) FINAL_EVT,event ,
case when level=2 then event end as LVL2_EVENT
from ash start with BLOCKING_SESSION is null
connect by prior SESSION_ID = BLOCKING_SESSION and prior INSTANCE_NUMBER= BLOCKING_INST_ID and sample_id = prior sample_id
) group by FINAL_BLK,FINAL_EVT,SAMPLE_TIME,FINAL_PROGRAM ,LVL2_EVENT
having count(*) > 2
order by SAMPLE_TIME
/

select /*+ parallel 8 */
level lv,
connect_by_isleaf isleaf,
connect_by_iscycle iscycle,
t.dbid,
t.sample_id,
t.sample_time,
t.instance_number,
t.session_id,
t.sql_id,
t.session_type,
t.event,
t.session_state,
t.blocking_inst_id,
t.blocking_session,
t.blocking_session_status
from m_ash t
/*where sample_time >
to_timestamp('2020-05-05 04:25:00',
'yyyy-mm-dd hh24:mi:ss')
and sample_time <
to_timestamp('2020-05-05 08:00:00',
'yyyy-mm-dd hh24:mi:ss')*/
start with blocking_session is not null
connect by nocycle
prior dbid = dbid
and prior sample_time = sample_time
/*and ((prior sample_time) - sample_time between interval '-1'
second and interval '1' second)*/
and prior blocking_inst_id = instance_number
and prior blocking_session = session_id
and prior blocking_session_serial# = session_serial#
order siblings by dbid, sample_time;

原文作者: liups.com

原文链接: http://liups.cn/posts/8ba8fb54/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议