数据库常用 SQL 脚本 - 蓝皮书的自留地

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;

根据等待事件查会话

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;

根据用户查会话

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查会话

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查会话详情

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;

查询阻塞会话

查询会话的对象信息

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;

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

查询某个会话的锁

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;

查询数据库中的锁

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

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 ;

活动会话的等待事件

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 的

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

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

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并中断运行

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

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

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

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

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

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;

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

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;

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;

查看磁盘组

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;

获取创建用户脚本及权限

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;

收集统计信息

1	exec dbms_stats.gather_table_stats('STEVEN','AWEN_OGG_TEST',degree=>10,cascade=> TRUE,no_invalidate=>false);

查询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');

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;

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

查看内存中的执行计划

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;

查看表的索引

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;

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;

 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;

#!/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

未删除的归档信息

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 查询阻塞信息

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

日志按日期切换频率

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;

日志按时间切换频率

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

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格式化

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;

归档每天生成量

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 和执行计划

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的瓶颈也就是被采样到的次数最多的执行步骤

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://liups.cn/posts/be5d06ce/

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

文章目录

根据等待事件查会话

根据用户查会话

根据SQL_ID查会话

根据会话ID查会话详情

查询阻塞会话

查询会话的对象信息

根据SQL_ID杀会话

根据等待事件杀会话

根据用户杀会话

kill所有LOCAL=NO进程

查询某个会话的锁

查询TMTX锁

查询数据库中的锁

活动会话的sql语句

活动会话的等待事件

查询空闲时间超过 600 的

SQL 10046

如果会话已经运行了，可以用oradebug

systemstate dump

hanganalyze

查看运行的JOB并中断运行

temp

undo

判断活跃会话1

判断活跃会话2（PL/SQL只考虑当前SQL)

找到会话对应PL/SQL 对象

找到会话对应的等待对象

dg

tbs

asm

查看磁盘

查看磁盘组

查看ASM Operation

获取创建用户脚本及权限

表相关的统计信息

收集统计信息

查询DB Time

执行计划

explain查看SQL执行计划

查看AWR和CURSOR中的执行计划

查看内存中的执行计划

查看历史执行计划

查看表的索引

未删除的归档信息

MySQL 查询阻塞信息

redo 切换

日志按日期切换频率

日志按时间切换频率

一周内每小时日志生成量

11g

12c格式化

归档每天生成量

通过 ASH 确定瓶颈步骤