墨墨导读:本文分享Oracle驻场工程师常用的脚本,基本上包含了日常监控、维护、故障定位及处理、SQL性能优化大部分场景,有了这些脚本会让你的工作变得更轻松,文末附下载链接。
1. 2pc_clean.txt
2. ash_sql_line_id.txt
3. awr_db_time.txt
4. awr_metric_name.txt
5. bind_noused.txt
6. cursor_purge.txt
7. ddl_metadata.txt
8. dml_get.txt
9. fra_get.txt
10. param_get.txt
11. segment_size.txt
12. session_sid.txt
13. session_spid.txt
14. shared_pool_free.txt
15. sql_monitor.txt
16. tablespace_used.txt
17. temp_used.txt
18. transaction_get.txt
19. undo_used.txt
20. wait_event.txt
21. wait_event_block.txt
22. wait_event_hash.txt
23. wait_event_sqlid.txt
24. wait_session_hash.txt
25. wait_session_sqlid.txt
26. ash_used.txt
27. sql_profile.txt
28. tabstat.txt
29. sqlinfo_total.txt
30. awr_event_histogram.txt
31. ash_top_sql_event.txt
32. sqlhis_awr.txt
33. session_kill.txt
34. redo_switch.txt
select 'rollback force '||''''||local_tran_id||''''||';' "RollBack"
from dba_2pc_pending
where state='prepared';
select 'exec dbms_transaction.purge_lost_db_entry('||''''||local_tran_id||''''||');' "Purge"
from dba_2pc_pending;
set linesize 260 pagesize 10000
SELECT
SQL_PLAN_HASH_VALUE,
event,
sql_plan_line_id,
COUNT(*)
FROM
dba_hist_active_sess_history
WHERE
sql_id = '&SQL_ID'
AND sample_time between
to_date('&date1', 'yyyymmddhh24miss') and
to_date('&date2', 'yyyymmddhh24miss')
GROUP BY
SQL_PLAN_HASH_VALUE,sql_plan_line_id,event
ORDER BY
4 DESC;
set linesize 220 pagesize 1000
col begin_interval_time for a30
col end_interval_time for a30
col stat_name for a40
WITH sysstat
AS (
SELECT ss.instance_number inst_id,
sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.VALUE e_value,
LAG(ss.VALUE) OVER(partition by ss.instance_number ORDER BY ss.snap_id) b_value
FROM dba_hist_sys_time_model ss, dba_hist_snapshot sn
WHERE sn.begin_interval_time >= SYSDATE - &date
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
and ss.stat_name = 'DB time'
and ss.instance_number in (select instance_number from v$instance)
)
select inst_id,
begin_interval_time,
end_interval_time,
stat_name,
round((e_value - b_value)/1000/1000/60) value_min
from sysstat
order by 2 desc, 3 desc;
set linesize 220 pagesize 1000
select METRIC_NAME from V$SYSMETRIC_SUMMARY where lower(METRIC_NAME) like '%&metric_name%';
set linesize 220 pagesize 1000
col begin_interval_time for a30
col end_interval_time for a30
col METRIC_NAME for a45
select a.SNAP_ID,
b.BEGIN_INTERVAL_TIME,
b.END_INTERVAL_TIME,
a.METRIC_NAME,
round(a.AVERAGE, 2) AVERAGE,
round(a.MAXVAL, 2) MAXVAL
from dba_hist_sysmetric_summary a, dba_hist_snapshot b
where a.SNAP_ID = b.SNAP_ID
and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
and a.INSTANCE_NUMBER in (select instance_number from v$instance)
and a.METRIC_NAME in ('&metric_name')
and b.BEGIN_INTERVAL_TIME>sysdate-&date
order by b.BEGIN_INTERVAL_TIME;
set linesize 220 pagesize 10000
set long 999999999
col MODULE for a40
col sql_id for a30
col PARSING_SCHEMA_NAME for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select a.sql_id,
a.MODULE,
a.PARSING_SCHEMA_NAME,
a.last_active_time,
a.last_load_time,
a.sql_fulltext,
b.pool_mb,
b.cnt
from v$sqlarea a,
(select max(sql_id) sql_id,
FORCE_MATCHING_SIGNATURE,
round(sum(SHARABLE_MEM / 1024 / 1024)) pool_mb,
count(1) cnt
from v$sqlarea
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 3
order by count(1) desc) b
where a.sql_id = b.sql_id
order by cnt desc;
declare
v_address_hash varchar2(128);
begin
select address||', '||hash_value into v_address_hash
from v$sqlarea
where sql_id = '&SQL_ID';
sys.dbms_shared_pool.purge(v_address_hash, 'C');
end;
/
set linesize 260
set long 999999
set pagesize 1000
select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL;
set linesize 220 pagesize 10000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col table_owner for a20
col table_name for a30
col partition_name for a20
col subpartition_name for a20
select a.*,sysdate from dba_tab_modifications a where table_name=upper('&table_name');
set echo off
set lines 300
set pagesize 1000
col reclaimable for a20
COL used for a20
COL QUOTA FOR A20
COL NAME FOR A30
col used1 for 99999 heading 'USED%';
prompt "RECOVERY FILE DEST AND SIZE"
SELECT substr(name, 1, 30) name, round(space_limit/1024/1024)||'M' AS quota,
round(space_used/1024/1024)||'M' AS used,round(100*space_used/space_limit) used1,
round(space_reclaimable/1024/1024)||'M' AS reclaimable,
number_of_files AS files
FROM v$recovery_file_dest
/
Select file_type, percent_space_used,percent_space_reclaimable,number_of_files as "number" from v$flash_recovery_area_usage
/
set linesize 220 pagesize 1000
col ksppinm for a40
col ksppstvl for a40
col ksppdesc for a100
select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id
from sys.x$ksppi a, sys.x$ksppcv b
where upper(a.ksppinm) like upper('%¶m%')
and a.indx = b.indx
order by a.ksppinm;
col owner for a15
col segment_name for a29
col partition_name for a30
col tablespace_name for a29
col size_m for 999,999,999
col blocks for 999,999,999
select owner,segment_name, partition_name,tablespace_name,bytes/1024/1024 size_m,blocks from dba_segments where segment_name=UPPER('&segment_name') order by 1;
set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
a.username,
a.machine,
a.module,
a.event,
a.sql_id,
round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
a.ROW_WAIT_OBJ# object_id,
a.BLOCKING_INSTANCE||'_'||a.blocking_session holder,
a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
to_char(LAST_CALL_ET) seconds,
a.p1 || '_' || a.p2 || '_' || a.p3 param,
b.spid,
trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
substr(c.sql_text,0,6) sql_text
from v$session a, v$process b,v$sql c
where a.paddr = b.addr(+)
and a.status = 'ACTIVE'
and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
a.wait_class = 'Idle')
and a.sql_id=c.sql_id(+)
and a.sql_child_number=c.CHILD_NUMBER(+)
and a.sid='&SID'
order by a.sql_id, a.machine
/
set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
a.username,
a.machine,
a.module,
a.event,
a.sql_id,
round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
a.ROW_WAIT_OBJ# object_id,
a.BLOCKING_INSTANCE||'_'||a.blocking_session holder,
a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
to_char(LAST_CALL_ET) seconds,
a.p1 || '_' || a.p2 || '_' || a.p3 param,
b.spid,
trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
substr(c.sql_text,0,6) sql_text
from v$session a, v$process b,v$sql c
where a.paddr = b.addr(+)
and a.status = 'ACTIVE'
and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
a.wait_class = 'Idle')
and a.sql_id=c.sql_id(+)
and a.sql_child_number=c.CHILD_NUMBER(+)
and b.spid='&SPID'
order by a.sql_id, a.machine
/
set linesize 260 pagesize 1000
select pool, name, bytes / 1024 / 1024 / 1024 GB
from v$sgastat
where name like 'free memory'
;
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => '&SQL_ID',
TYPE => 'TEXT',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;
--表空间使用率
set linesize 220 pagesize 10000
COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
ORDER BY 4 DESC;
--查询temp表空间使用率:
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+);
--查询temp表空间使用率:
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+)
--查询实时使用temp表空间的sql_id和sid:
set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
sid,
serial#,
se.sql_id
machine,
program,
tablespace,
segtype,
(su.BLOCKS*8/1024/1024) GB
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr
order by su.BLOCKS desc;
--需要注意的是这里查询sql_id要用v$session视图的sql_id,而不要用v$sort_usage视图的sql_id,v$sort_usage视图里面的sql_id是不准确的
--查询历史的temp表空间的使用的SQL_ID
select a.SQL_ID,
a.SAMPLE_TIME,
a.program,
sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
from v$active_session_history a
where TEMP_SPACE_ALLOCATED is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
order by 2 asc,4 desc;
set linesize 260 pagesize 10000
column sess format a21 heading "SESSION"
column program format a18
column clnt_pid format a8
column machine format a25
column username format a12
column osuser format a13
column event format a32
column waitsec format 999999
column start_time format a18
column sql_id format a15
column clnt_user format a10
column svr_ospid format a10
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd hh24:mi:ss';
set feedback off
set echo off
set head off
select chr(9) from dual;
select 'Waiting Transactions'||chr(10)||'====================' from dual;
set head on
select /*+ rule */
lpad(nvl(s.username,' '),8)||'('||s.sid||','||s.serial#||')' as sess,
p.spid as svr_ospid,
nvl(osuser,' ') as clnt_user,
s.process as clnt_pid,
substr((case instr(s.PROGRAM, '@')
when 0 then
s.program
else
case instr(s.PROGRAM, '(TNS V1-V3)')
when 0 then
substr(s.program, 1, instr(s.PROGRAM, '@') - 1) || substr(s.program, instr(s.PROGRAM, '(') - 1)
else
substr(s.program, 1, instr(s.PROGRAM, '@') - 1)
end
end),
1, 18) as program,
(case
when length(s.MACHINE) > 8 then substr(s.machine,1,8)||'~'
else s.machine
end
) || '('||nvl(s.client_info, 'Unknown IP')||')' as machine, s.sql_id,
substr(s.event, 1, 32) as event,
s.seconds_in_wait as waitsec
from v$transaction t,v$session s,v$process p
where t.ses_addr=s.saddr and s.paddr=p.addr
order by s.seconds_in_wait, s.program, s.machine;
--实时的undo使用量
set linesize 220
set pagesize 1000
col username for a20
col module for a40
col sql_id for a15
col status for a10
col machine for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select *
from (select start_time,
username,
s.MACHINE,
s.OSUSER,
r.name,
ubafil, --Undo block address (UBA) filenum
ubablk, --UBA block number
t.status,
(used_ublk * 8192 / 1024) kbtye,
used_urec,
s1.SQL_ID,
substr(s1.SQL_TEXT,0,20)
from v$transaction t, v$rollname r, v$session s, v$sqlarea s1
where t.xidusn = r.usn
and s.saddr = t.ses_addr
and s.sql_id = s1.sql_id(+)
order by 9 desc)
where rownum <= 10;
set linesize 220
set pagesize 1000
select inst_id,event,count(*)
from gv$session a
where a.status='ACTIVE'
and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')
group by inst_id,event
order by a.inst_id,count(*) desc
;
set linesize 220
set pagesize 1000
select inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION, count(*)
from gv$session a
where a.status='ACTIVE'
and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle') and upper(event) like upper('%&event%')
group by inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION
order by inst_id ,count(*) desc, sql_id
;
set linesize 220
set pagesize 1000
select a.event, c.plan_hash_value,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets,count(*)
from gv$session a,gv$sql c
where a.status='ACTIVE'
and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')
and a.sql_id=c.sql_id(+)
and a.sql_child_number=c.CHILD_NUMBER(+)
and a.inst_id=c.inst_id
group by a.inst_id,a.event, c.plan_hash_value
order by a.inst_id,count(*) desc, c.plan_hash_value
;
set linesize 220
set pagesize 1000
select a.inst_id,a.event, a.sql_id,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets, count(*)
from gv$session a,gv$sql c
where a.status='ACTIVE'
and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')
and a.sql_id=c.sql_id(+)
and a.sql_child_number=c.CHILD_NUMBER(+)
and a.inst_id=c.inst_id
group by a.inst_id,a.event, a.sql_id
order by a.inst_id,count(*) desc, a.sql_id
;
set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
a.username,
a.machine,
a.module,
a.event,
c.plan_hash_value,
round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
a.ROW_WAIT_OBJ# object_id,
a.BLOCKING_INSTANCE||'_'||a.blocking_session holder,
a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
to_char(LAST_CALL_ET) seconds,
a.p1 || '_' || a.p2 || '_' || a.p3 param,
b.spid,
trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
substr(c.sql_text,0,6) sql_text
from v$session a, v$process b,v$sql c
where a.paddr = b.addr(+)
and a.status = 'ACTIVE'
and a.sql_id=c.sql_id(+)
and a.sql_child_number=c.CHILD_NUMBER(+)
and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
a.wait_class = 'Idle')
order by c.plan_hash_value, a.machine
/
set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
a.username,
a.machine,
a.module,
a.event,
a.sql_id,
round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
a.ROW_WAIT_OBJ# object_id,
a.BLOCKING_INSTANCE||'_'||a.blocking_session holder,
a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
to_char(LAST_CALL_ET) seconds,
a.p1 || '_' || a.p2 || '_' || a.p3 param,
b.spid,
trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
substr(c.sql_text,0,6) sql_text
from v$session a, v$process b,v$sql c
where a.paddr = b.addr(+)
and a.status = 'ACTIVE'
and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
a.wait_class = 'Idle')
and a.sql_id=c.sql_id(+)
and a.sql_child_number=c.CHILD_NUMBER(+)
order by a.sql_id, a.machine
/
因篇幅限制,就不在此一一展示了,大家可通过下面链接下载打包好的脚本。
资源下载:https://www.modb.pro/download/43926(复制到浏览器中打开或者点击“阅读原文”立即下载)
作者
邓秋爽,云和恩墨资深技术专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting。
联系客服