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
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
SELECT 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
10
set line 199 
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15

SELECT 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
SELECT 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
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
select count(*),blocking_session 
from v$session
where blocking_session is not null group by blocking_session;
col OBJECT_NAME for a30
select owner,object_name,subobject_name,object_type
from dba_objects where object_id=&oid;
1
2
3
4
5
6
7
set line 199 
col event format a35

## 杀某个SID会话
SELECT 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
SELECT  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
SELECT  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
SELECT sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess 
FROM v$session s, v$process p
WHERE s.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 ESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE
from gv$locked_object
where session_id=&sid;

查询TMTX锁

1
select * from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;

查询数据库中的锁

1
2
3
4
5
6
select 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 lines 200 pages 1000
column EVENT format a30
select distinct ses.SID, ses.USERNAME,ses.sql_hash_value, pro.SPID "OS PID", substr(stx.sql_text,1,20), last_call_et
from gV$SESSION ses
,gV$SQL stx
,gV$PROCESS pro
where ses.paddr = pro.addr
-- adn a.username='SYSYEM'
and ses.status = 'ACTIVE'
and stx.hash_value = ses.sql_hash_value ;

活动会话的等待事件

1
2
3
4
5
6
7
8
9
prompt Active session with wait
column USERNAME format a14
set linesize 200
column EVENT format a30
select 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';

查询空闲时间超过 600 的

1
2
3
4
5
6
7
8
9
10
11
12
13
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;

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
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

1
2
3
4
5
6
7
8
9
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

1
2
3
4
5
6
7
8
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并中断运行

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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';

SELECT s.username,
s.sid,
pr.PID,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
rs.segment_id,
r.usn,
rs.segment_name,
r.rssize/1024/1024,
sq.sql_text
FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs ,v$sql sq,v$process pr
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
AND s.sql_address=sq.address
AND s.sql_hash_value = sq.hash_value
AND s.PADDR=pr.ADDR
ORDER BY t.used_ublk DESC;

判断活跃会话1

1
2
3
4
5
6
7
8
9
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)

1
2
3
4
5
6
7
8
9
10
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 对象

1
2
3
4
5
6
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 (+);

找到会话对应的等待对象

1
2
3
4
5
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

1
2
3
4
5
6
7
8
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;
1
select THREAD#,sequence#, first_time, next_time, applied from v$archived_log order by 3;
1
select name,database_role,switchover_status from v$database;
1
select sequence#, first_time, next_time, applied from v$archived_log order by sequence#; 
1
2
3
4
5
6
7
8
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;
1
2
3
4
5
6
7
8
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;
1
2
3
4
5
6
7
8
9
10
11
12
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;

1
2
3
## 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;

tbs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
1
2
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;
1
2
3
4
5
6
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

查看磁盘

1
2
3
4
5
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;

查看磁盘组

1
2
3
set linesize 160
col name for a20
select NAME,STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

查看ASM Operation

1
2
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
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

表相关的统计信息

– 包含分区、索引、索引字段

1
2
3
4
5
6
7
8
9
10
11
12
13
## 先替换掉下面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;

收集统计信息

1
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
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');
1
2
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;

执行计划

explain查看SQL执行计划

1
2
EXPLAIN  PLAN FOR select count(*) from steven.AWEN_OGG_TEST;
select * from table(dbms_xplan.display());

查看AWR和CURSOR中的执行计划

1
2
3
select * from table(dbms_xplan.display_awr('&sqlid'));
select * from table(dbms_xplan.display_cursor('&sqlid'));
select * from table(dbms_xplan.display_cursor('&sql_id',&child))

查看内存中的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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';

查看历史执行计划

1
2
3
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
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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;
 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
1
2
alter system kill session '1349, 42777'immediate;
alter system disconnect session '1349, 42777'immediate;
1
2
3
4
5
6
7
8
9
10
11
 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;
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

未删除的归档信息

1
2
3
4
SELECT NAME, THREAD#, SEQUENCE#, TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') AS FIRST_TIME
FROM V$ARCHIVED_LOG
WHERE DELETED = 'NO' and name like '+%'
ORDER BY FIRST_TIME desc;

dmidecode -s system-product-name

iostat -x -m -t 2 100

===========MySQL

MySQL 查询阻塞信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
USER,
HOST,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
AND trx_started =
(SELECT MIN(trx_started)
FROM INFORMATION_SCHEMA.INNODB_TRX
GROUP BY trx_started HAVING count(trx_started)>=2)
AND TRX_QUERY NOT LIKE 'alter%table%'
OR TRX_QUERY IS NULL;
1
select sql_kill_blocking_connection from sys.schema_table_lock_waits where blocking_lock_type='SHARED_READ'
1
select t from information schema.innode trx;

redo 切换

日志按日期切换频率

1
2
3
4
5
col riqi format a10
select thread#,to_char(first_time,'yyyymmdd') riqi,count(*) from v$log_history
where to_char(first_time,'yyyymm')>='201510'
group by thread#,to_char(first_time,'yyyymmdd')
order by thread#,riqi;

日志按时间切换频率

1
2
3
4
5
col shijian format a10
select thread#,to_char(first_time,'hh24') shijian,count(*) from v$log_history
where to_char(first_time,'yyyymm')>='201510'
group by thread#,to_char(first_time,'hh24')
order by thread#,shijian;

一周内每小时日志生成量

11g

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
set line 200
column h0 format 999
column h1 format 999
column h2 format 999
column h3 format 999
column h4 format 999
column h5 format 999
column h6 format 999
column h7 format 999
column h8 format 999
column h9 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column avg format 999.99
column day format a3
SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
COUNT (1) "Total",
to_char(ROUND (COUNT (1) / 24, 2),'fm99999999990.00') "Avg"
FROM gv$log_history
WHERE first_time >= trunc(SYSDATE) - 30
and thread# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1 ;

12c格式化

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
column h00 format a3
column h01 format a3
column h02 format a3
column h03 format a3
column h04 format a3
column h05 format a3
column h06 format a3
column h07 format a3
column h08 format a3
column h09 format a3
column h10 format a3
column h11 format a3
column h12 format a3
column h13 format a3
column h14 format a3
column h15 format a3
column h16 format a3
column h17 format a3
column h18 format a3
column h19 format a3
column h20 format a3
column h21 format a3
column h22 format a3
column h23 format a3
column avg format a5
column day format a3
col total for a5
set pagesize 50
select THREAD#,
trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
substr(count(1), 1, 5) as "Total",
substr(SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)), 1, 3) as "h00",
substr(SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)), 1, 3) as "h01",
substr(SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)), 1, 3) as "h02",
substr(SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)), 1, 3) as "h03",
substr(SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)), 1, 3) as "h04",
substr(SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)), 1, 3) as "h05",
substr(SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)), 1, 3) as "h06",
substr(SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)), 1, 3) as "h07",
substr(SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)), 1, 3) as "h08",
substr(SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)), 1, 3) as "h09",
substr(SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)), 1, 3) as "h10",
substr(SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)), 1, 3) as "h11",
substr(SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)), 1, 3) as "h12",
substr(SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)), 1, 3) as "h13",
substr(SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)), 1, 3) as "h14",
substr(SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)), 1, 3) as "h15",
substr(SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)), 1, 3) as "h16",
substr(SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)), 1, 3) as "h17",
substr(SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)), 1, 3) as "h18",
substr(SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)), 1, 3) as "h19",
substr(SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)), 1, 3) as "h20",
substr(SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)), 1, 3) as "h21",
substr(SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)), 1, 3) as "h22",
substr(SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)), 1, 3) as "h23"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy'), THREAD#
order by 2 , 1;

归档每天生成量

1
2
3
4
5
SELECT to_char(FIRST_TIME, 'YYYY-MM-DD') YYYYMD,
ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024 / 1024) LOGsize_G
FROM v$archived_log a WHERE a.STANDBY_DEST = 'NO' AND a.FIRST_TIME >= SYSDATE - 30
group by to_char(FIRST_TIME, 'YYYY-MM-DD')
order by to_char(FIRST_TIME, 'YYYY-MM-DD');

通过 ASH 确定瓶颈步骤

  • 查找昂起会话中正在执行的长SQL 的sql_id 和执行计划
1
2
3
4
5
6
7
8
set lines 200 pages 1000 long 50000
select a.sql_id,sql_child_number child_,last_call_et,substr(sql_text,1,200)
from v$session a,v$sqlarea b
where a.sql_id=b.sql_id
and a.status='ACTIVE'
-- and a.username='SYSTEM'
order by last_call_et desc;

  • 查看执行计划
1
select * from table(dbms_xplan.display_cursor('&sql_id',&child,'ADVANCED'))
  • ASH 中找到SQL的瓶颈也就是 被采样到的次数最多的执行步骤
1
2
3
4
5
6
7
8
9
10
11
12
13
select sql_plan_hash_value,
sql_plan_line_id,
sql_plan_operation||' '||sql_plan_options plan,
count(*)
from v$active_session_history
where sql_id='&sql_id'
and sample_time> sysdate-10/1440
-- and to_cahr(sample_time,'yyyymmdd hh24:mi:ss') between '20230111 00:00:00' and '20230111 01:00:00'
group by sql_plan_hash_value,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options
order by count(*) desc;

原文作者: liups.com

原文链接: http://www.liups.com/posts/be5d06ce/

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