目录
-
-
- 2pc_clean.txt
- ash_sql_line_id.txt
- awr_db_time.txt
- awr_metric_name.txt
- bind_noused.txt
- cursor_purge.txt
- ddl_metadata.txt
- dml_get.txt
- fra_get.txt
- param_get.txt
- segment_size.txt
- session_sid.txt
- session_spid.txt
- shared_pool_free.txt
- sql_monitor.txt
- tablespace_used.txt
- temp_used.txt
- transaction_get.txt
- undo_used.txt
- wait_event.txt
- wait_event_block.txt
- wait_event_hash.txt
- wait_event_sqlid.txt
- wait_session_hash.txt
- wait_session_sqlid.txt
- ash_used.txt
- sql_profile.txt
- tabstat.txt
- sqlinfo_total.txt
- awr_event_histogram.txt
- ash_top_sql_event.txt
- sqlhis_awr.txt
- session_kill.txt
- redo_switch.txt
-
2pc_clean.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;
ash_sql_line_id.txt
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;
awr_db_time.txt
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;
awr_metric_name.txt
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;
bind_noused.txt
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;
cursor_purge.txt
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;
/
ddl_metadata.txt
set linesize 260
set long 999999
set pagesize 1000
select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL;
dml_get.txt
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');
fra_get.txt
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
/
param_get.txt
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;
segment_size.txt
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;
session_sid.txt
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
/
session_spid.txt
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
/
shared_pool_free.txt
set linesize 260 pagesize 1000
select pool, name, bytes / 1024 / 1024 / 1024 GB
from v$sgastat
where name like 'free memory'
;
sql_monitor.txt
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;
tablespace_used.txt
--表空间使用率
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_used.txt
--查询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;
transaction_get.txt
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_used.txt
--实时的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;
wait_event.txt
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
;
wait_event_block.txt
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
;
wait_event_hash.txt
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
;
wait_event_sqlid.txt
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
;
wait_session_hash.txt
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
/
wait_session_sqlid.txt
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?wb
ash_used.txt
sql_profile.txt
tabstat.txt
sqlinfo_total.txt
awr_event_histogram.txt
ash_top_sql_event.txt
sqlhis_awr.txt
session_kill.txt
redo_switch.txt
——————————————————
墨天轮，围绕数据人的学习成长提供一站式的全面服务，打造集新闻资讯、在线问答、活动直播、在线课程、文档阅览、资源下载、知识分享及在线运维为一体的统一平台，持续促进数据领域的知识传播和技术创新。
更多精彩可以前往【墨天轮社区】
关注官方公众号：墨天轮 墨天轮平台 数据库国产化 墨天轮成长营 墨天轮资讯