一键获取数据库整体信息脚本
将脚本内容放spooldb.sql中，在sqlplus中执行，相关信息会自动生成5个文件，其中addm是最近一小时文件，ash是最近半小时文件，而awr文件是最近一小时和最近7天的两个文件。SET markup html ON spool ON pre off entmap off
set term off
set heading on
set verify off
set feedback off
set linesize 2000
set pagesize 30000
set long 999999999
set longchunksize 999999
column index_name format a30
column table_name format a30
column num_rows format 999999999
column index_type format a24
column num_rows format 999999999
column status format a8
column clustering_factor format 999999999
column degree format a10
column blevel format 9
column distinct_keys format 9999999999
column leaf_blocks format 9999999
column last_analyzed format a10
column column_name format a25
column column_position format 9
column temporary format a2
column partitioned format a5
column partitioning_type format a7
column partition_count format 999
column program format a30
column spid format a6
column pid format 99999
column sid format 99999
column serial# format 99999
column username format a12
column osuser format a12
column logon_time format date
column event format a32
column JOB_NAME format a30
column PROGRAM_NAME format a32
column STATE format a10
column window_name format a30
column repeat_interval format a60
column machine format a30
column program format a30
column osuser format a15
column username format a15
column event format a50
column seconds format a10
column sqltext format a100
SET markup html off
column dbid new_value spool_dbid
column inst_num new_value spool_inst_num
select dbid from v$database where rownum = 1;
select instance_number as inst_num from v$instance where rownum = 1;
column spoolfile_name new_value spoolfile
select 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_number from v$instance where rownum=1)||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi') as spoolfile_name from dual;
spool &&spoolfile..html
SET markup html off
set serveroutput on;
exec dbms_output.enable(9999999999);
exec dbms_output.put_line('<html>');
exec dbms_output.put_line('<style>');
exec dbms_output.put_line('th {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}');
exec dbms_output.put_line('</style>');
exec dbms_output.put_line('<head>');
exec dbms_output.put_line('</head>');
exec dbms_output.put_line('<body>');
SET markup html on
prompt <p>VERSION
select * from v$version;
select * from dba_registry_history;
prompt <p>Last startup time, version, and whether RAC
select * from
(select name db_name from v$database),
(select instance_name from v$instance),
(select archiver from v$instance),
(select snap_interval awr_interval, retention awr_retention FROM DBA_HIST_WR_CONTROL),
(select flashback_on from v$database),
(select parallel from v$instance),
(select startup_time from v$instance),
(select decode(name,null,'NOT ASM','ASM') IS_ASM from (select null name from dual union all (select name IS_ASM from v$datafile where name like '+%' and rownum = 1))),
(select max(end_time) rman_lastcompleted from v$rman_status where status = 'COMPLETED' and object_type like 'DB FULL')
;
prompt <p>CPU or wait for the longest in 30 minutes
select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser
from (select c.USERNAME,
a.event,
to_char(a.cnt) as seconds,
a.sql_id
--,dbms_lob.substr(b.sql_fulltext,50,1) sqltext
from (select rownum rn, t.*
from (select decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu') Event,
s.sql_id,
s.user_id,
count(*) CNT
from v$active_session_history s
where sample_time > sysdate - 30 / 1440
group by s.user_id,
decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu'),
s.sql_id
order by CNT desc) t
where rownum < 20) a,
v$sqlarea b,
dba_users c
where a.sql_id = b.sql_id
and a.user_id = c.user_id
order by CNT desc) t,
v$session s
where t.sql_id = s.sql_id(+)
;
prompt <p>Recent load status (based on AWR snapshot
select s.snap_date,
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
to_char(round(s.seconds/60,2)) "elapse(min)",
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
s.redosize redo,
round(s.redosize / s.seconds, 2) "redo/s",
s.logicalreads logical,
round(s.logicalreads / s.seconds, 2) "logical/s",
physicalreads physical,
round(s.physicalreads / s.seconds, 2) "phy/s",
s.executes execs,
round(s.executes / s.seconds, 2) "execs/s",
s.parse,
round(s.parse / s.seconds, 2) "parse/s",
s.hardparse,
round(s.hardparse / s.seconds, 2) "hardparse/s",
s.transactions trans,
round(s.transactions / s.seconds, 2) "trans/s"
from (select curr_redo - last_redo redosize,
curr_logicalreads - last_logicalreads logicalreads,
curr_physicalreads - last_physicalreads physicalreads,
curr_executes - last_executes executes,
curr_parse - last_parse parse,
curr_hardparse - last_hardparse hardparse,
curr_transactions - last_transactions transactions,
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
to_char(currtime, 'yy/mm/dd') snap_date,
to_char(currtime, 'hh24:mi') currtime,
currsnap_id endsnap_id,
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
from (select a.redo last_redo,
a.logicalreads last_logicalreads,
a.physicalreads last_physicalreads,
a.executes last_executes,
a.parse last_parse,
a.hardparse last_hardparse,
a.transactions last_transactions,
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
b.end_interval_time lasttime,
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
b.startup_time
from (select snap_id,
dbid,
instance_number,
sum(decode(stat_name, 'redo size', value, 0)) redo,
sum(decode(stat_name,
'session logical reads',
value,
0)) logicalreads,
sum(decode(stat_name,
'physical reads',
value,
0)) physicalreads,
sum(decode(stat_name, 'execute count', value, 0)) executes,
sum(decode(stat_name,
'parse count (total)',
value,
0)) parse,
sum(decode(stat_name,
'parse count (hard)',
value,
0)) hardparse,
sum(decode(stat_name,
'user rollbacks',
value,
'user commits',
value,
0)) transactions
from dba_hist_sysstat
where stat_name in
('redo size',
'session logical reads',
'physical reads',
'execute count',
'user rollbacks',
'user commits',
'parse count (hard)',
'parse count (total)')
group by snap_id, dbid, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.dbid = &&spool_dbid
and a.instance_number = &&spool_inst_num
order by end_interval_time)) s,
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time'
and a.dbid = &&spool_dbid
and a.instance_number = &&spool_inst_num) t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc ,time asc
;
prompt <p>逻辑读最多
select *
from (select sql_id,
s.EXECUTIONS,
s.LAST_LOAD_TIME,
s.FIRST_LOAD_TIME,
s.DISK_READS,
s.BUFFER_GETS
from v$sql s
where s.buffer_gets > 300
order by buffer_gets desc)
where rownum <= 10
;
prompt <p>Logical read most
select * from (select sql_id,
s.EXECUTIONS,
s.LAST_LOAD_TIME,
s.FIRST_LOAD_TIME,
s.DISK_READS,
s.BUFFER_GETS,
s.PARSE_CALLS
from v$sql s
where s.disk_reads > 300
order by disk_reads desc)
where rownum<=10
;
prompt <p>Maximum number of executions
select *
from (select sql_id,
s.EXECUTIONS,
s.LAST_LOAD_TIME,
s.FIRST_LOAD_TIME,
s.DISK_READS,
s.BUFFER_GETS,
s.PARSE_CALLS
from v$sql s
order by s.EXECUTIONS desc)
where rownum <= 10
;
prompt <p>SQL parsing most
select *
from (select sql_id,
s.EXECUTIONS,
s.LAST_LOAD_TIME,
s.FIRST_LOAD_TIME,
s.DISK_READS,
s.BUFFER_GETS,
s.PARSE_CALLS
from v$sql s
order by s.PARSE_CALLS desc)
where rownum <= 10
;
prompt <p>The disk is sorted most
select sess.username, sql.address, sort1.blocks
from v$session sess, v$sqlarea sql, v$sort_usage sort1
where sess.serial# = sort1.session_num
and sort1.sqladdr = sql.address
and sort1.sqlhash = sql.hash_value
and sort1.blocks > 200
order by sort1.blocks desc
;
prompt <p>Commits more than 10,000 session
select t1.sid, t1.value, t2.name
from v$sesstat t1, v$statname t2
where t2.name like '%user commits%'
and t1.STATISTIC# = t2.STATISTIC#
and value >= 10000
order by value desc
;
prompt <p>SQL with a length of more than 100
SELECT SQL_ID, COUNT(*) line_count
FROM V$SQLTEXT
GROUP BY SQL_ID
HAVING COUNT(*) >= 100
ORDER BY COUNT(*) DESC
;
prompt <p>Query shared memory share
select count(*),round(sum(sharable_mem)/1024/1024,2)
from v$db_object_cache a
;
prompt <p>Table with parallelism
select t.owner, t.table_name, degree
from dba_tables t
where trim(t.degree) <>'1'
and trim(t.degree)<>'0'
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and owner not like 'FLOWS%'
and owner not like 'WK%'
;
prompt <p>Index with parallelism
select t.owner, t.table_name, index_name, degree, status
from dba_indexes t
where trim(t.degree) <>'1'
and trim(t.degree)<>'0'
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and owner not like 'FLOWS%'
and owner not like 'WK%'
;
prompt <p>Invalid index
select t.index_name,
t.table_name,
blevel,
t.num_rows,
t.leaf_blocks,
t.distinct_keys
from dba_indexes t
where status = 'UNUSABLE'
and table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
and table_owner not like 'FLOWS%'
;
select t2.owner,
t1.blevel,
t1.leaf_blocks,
t1.INDEX_NAME,
t2.table_name,
t1.PARTITION_NAME,
t1.STATUS
from dba_ind_partitions t1, dba_indexes t2
where t1.index_name = t2.index_name
and t1.STATUS = 'UNUSABLE'
;
prompt <p>Invalid object
select t.owner,
t.object_type,
t.object_name
from dba_objects t
where STATUS='INVALID'
order by 1, 2
;
prompt <p>Bitmap index and function index, reverse key index
select t.owner,
t.table_name,
t.index_name,
t.index_type,
t.status,
t.blevel,
t.leaf_blocks
from dba_indexes t
where index_type in ('BITMAP', 'FUNCTION-BASED NORMAL', 'NORMAL/REV')
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and owner not like 'FLOWS%'
and owner not like 'WK%'
;
prompt <p>The combination of index columns is more than four
select table_owner,table_name, index_name, count(*)
from dba_ind_columns
where table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and table_owner not like 'FLOWS%'
and table_owner not like 'WK%'
group by table_owner,table_name, index_name
having count(*) >= 4
order by count(*) desc
;
prompt <p>Indexed more than 5 numbers
select owner,table_name, count(*) cnt
from dba_indexes
where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and owner not like 'FLOWS%'
and owner not like 'WK%'
group by owner,table_name
having count(*) >= 5
order by cnt desc
;
prompt <p>Never indexed a large table
select segment_name,
bytes / 1024 / 1024 / 1024 "GB",
blocks,
tablespace_name
from dba_segments
where segment_type = 'TABLE'
and segment_name not in (select table_name from dba_indexes)
and bytes / 1024 / 1024 / 1024 >= 0.5
order by GB desc
;
select segment_name, sum(bytes) / 1024 / 1024 / 1024 "GB", sum(blocks)
from dba_segments
where segment_type = 'TABLE PARTITION'
and segment_name not in (select table_name from dba_indexes)
group by segment_name
having sum(bytes) / 1024 / 1024 / 1024 >= 0.5
order by GB desc
;
prompt <p>There is a cross between the combined index of the table and the single column index
select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate
from dba_ind_columns
where table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and table_owner not like 'FLOWS%'
and table_owner not like 'WK%'
group by table_name
having count(distinct(column_name)) / count(*) < 1
order by cross_idx_rate desc
;
prompt <p>Object is built on the system tablespace
select * from (
select owner, segment_name, tablespace_name, count(*) num
from dba_segments
where tablespace_name in('SYSTEM','SYSAUX')
group by owner, segment_name, tablespace_name)
where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
and owner not like 'FLOWS%'
and owner not like 'WK%'
;
prompt <p>Check whether the statistics are collected
select t.job_name,t.program_name,t.state,t.enabled
from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB'
;
select client_name,status
from dba_autotask_client
;
select window_next_time,autotask_status
from DBA_AUTOTASK_WINDOW_CLIENTS
;
prompt <p>Check the object that was not collected or collected for a long time
select owner, count(*)
from dba_tab_statistics t
where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
and table_name not like 'BIN$%'
group by owner
order by owner
;
prompt <p>Temporary table for collecting statistical information
select owner, table_name, t.last_analyzed, t.num_rows, t.blocks
from dba_tables t
where t.temporary = 'Y'
and last_analyzed is not null
;
prompt <p>Log switching frequency analysis
select *
from (select thread#, sequence#, to_char(first_time, 'MM/DD/RR HH24:MI:SS')
from v$log_history
order by first_time desc)
where rownum <= 50
;
prompt <p>The amount of log switch every day in the last 10 days
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 ,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
where first_time>=to_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC
;
prompt <p>Log group size
select group#,bytes,status
from v$log
;
prompt <p>show recovery_file_dest usage
select substr(name, 1, 30) name,
space_limit as quota,
space_used as used,
space_reclaimable as reclaimable,
number_of_files as files
from v$recovery_file_dest
;
select * from V$FLASH_RECOVERY_AREA_USAGE
;
prompt <p>Check if the sequence is less than 20
select sequence_owner,
count(*) CNT,
sum(case when t.cache_size <= 20 then 1 else 0 end ) CNT_LESS_20,
sum(case when t.cache_size > 20 then 1 else 0 end ) CNT_MORE_20
from dba_sequences t
group by sequence_owner
;
prompt <p>Table space usage
set markup html off
prompt <p>
declare
type NUMBER_ARRAY is table of number(15) index by varchar2(30);
ts_free_mb NUMBER_ARRAY;
cursor c1 is
select tablespace_name, sum(free_mb) + sum(expired_mb) free_mb
from (SELECT tablespace_name,
round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) free_mb,
0 expired_mb
FROM dba_free_space
GROUP BY tablespace_name
union all
select tablespace_name,
0 free_mb,
round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) expired_mb
from dba_undo_extents d
where tablespace_name =
(select value
from v$parameter
where name = 'undo_tablespace')
and status = 'EXPIRED'
group by tablespace_name)
group by tablespace_name;
cursor c2 is
SELECT /*+ rule */
tablespace_name, round(sum(bytes) / 1024 / 1024, 2) total_mb
FROM dba_data_files
where tablespace_name not in
(select tablespace_name
from dba_data_files
where upper(AUTOEXTENSIBLE) = 'YES')
GROUP BY tablespace_name;
ts_name varchar2(30);
ts_total number(15);
ts_used number(15);
ts_free number(15);
ts_rate varchar2(5);
begin
for rec1 in c1 loop
ts_free_mb(rec1.tablespace_name) := rec1.free_mb;
end loop;
dbms_output.put_line('<table border="1" width="90%" align="center" summary="Script output">');
dbms_output.put_line('<tr><th>ts_name</th><th>ts_total</th><th>ts_used</th><th>ts_free</th><th>ts_rate</th></tr>');
for rec2 in c2 loop
ts_name := null;
ts_total := null;
ts_used := null;
ts_free := null;
ts_rate := null;
ts_name := rec2.tablespace_name;
ts_total := rec2.total_mb;
ts_free := nvl(ts_free_mb(ts_name), 0);
ts_used := nvl(ts_total - ts_free, 0);
ts_rate := to_char(round((ts_total - ts_free) / ts_total * 100, 2),
'fm990.99');
dbms_output.put_line('<tr><td>' || ts_name || '</td><td>' || ts_total ||
'</td><td>' || ts_used || '</td><td>' || ts_free ||
'</td><td>' || ts_rate || '</td></tr>');
end loop;
dbms_output.put_line('</table>');
end;
/
prompt <p>
set markup html on
prompt <p>The size of the entire database
select owner, round(sum(bytes) / 1024 / 1024 / 1024, 2) "GB"
from dba_segments
group by owner
order by 2 desc
;
prompt <p>Object size TOP10
select *
from (select owner,
segment_name,
segment_type,
round(sum(bytes) / 1024 / 1024) object_size
from DBA_segments
group by owner, segment_name, segment_type
order by object_size desc)
where rownum <= 10
;
prompt <p>Recycle Bin situation (size and quantity)
select *
from (select SUM(BYTES) / 1024 / 1024 / 1024 as recyb_size
from DBA_SEGMENTS
WHERE SEGMENT_NAME LIKE 'BIN$%') a,
(select count(*) as recyb_cnt from dba_recyclebin)
;
prompt <p>Check who took up the undo tablespace
SELECT r.name "roll_segment_name", rssize/1024/1024/1024 "RSSize(G)",
s.sid,
s.serial#,
s.username,
s.status,
s.sql_hash_value,
s.SQL_ADDRESS,
s.MACHINE,
s.MODULE,
substr(s.program, 1, 78) program,
r.usn,
hwmsize/1024/1024/1024, shrinks ,xacts
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r, v$rollstat rs
WHERE t.addr = s.taddr and t.xidusn = r.usn and r.usn=rs.USN
Order by rssize desc
;
prompt <p>Check who took up the temp tablespace
select sql.sql_id,
t.Blocks * 16 / 1024 / 1024,
s.USERNAME,
s.SCHEMANAME,
t.tablespace,
t.segtype,
t.extents,
s.PROGRAM,
s.OSUSER,
s.TERMINAL,
s.sid,
s.SERIAL#
from v$sort_usage t, v$session s , v$sql sql
where t.SESSION_ADDR = s.SADDR
and t.SQLADDR=sql.ADDRESS
and t.SQLHASH=sql.HASH_VALUE
;
prompt <p>Observe the rollback segment, the temporary segment and the general segment No is automatically expanded
select t2.contents, t1.*
from (select file_name, tablespace_name, bytes, maxbytes, autoextensible
from dba_temp_files
union all
select file_name, tablespace_name, bytes, maxbytes, autoextensible
from dba_data_files) t1,
dba_tablespaces t2
where t1.tablespace_name = t2.tablespace_name
;
prompt <p>Table size of more than 10GB did not build the partition table
select owner,
segment_name,
segment_type,
round(sum(bytes) / 1024 / 1024 / 1024,2) object_size
from dba_segments
where segment_type = 'TABLE'
and bytes > 10*1024*1024*1024
group by owner, segment_name, segment_type
order by object_size desc
;
prompt <p>The top 10 partitions are the most
select *
from (select table_owner, table_name, count(*) cnt
from dba_tab_partitions
group by table_owner, table_name
order by cnt desc)
where rownum <= 10
;
prompt <p>Partitioned uneven table
select *
from (select table_owner,
table_name,
max(num_rows) max_num_rows,
trunc(avg(num_rows), 0) avg_num_rows,
sum(num_rows) sum_num_rows,
case
when sum(num_rows) = 0 then
0
else
trunc(max(num_rows) / trunc(avg(num_rows), 0), 2)
end rate,
count(*) part_count
from dba_tab_partitions
group by table_owner, table_name)
where rate > 5;
prompt <p>A table with a column larger than 100 or less than 2
select *
from (select owner, table_name, count(*) col_count
from dba_tab_cols
group by owner, table_name)
where col_count > 100
or col_count <= 2
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
and owner not like 'FLOWS%'
;
prompt <p>The table property is NOLOGGING
select owner, table_name, tablespace_name, logging
from dba_tables
where logging = 'NO'
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
and owner not like 'FLOWS%'
;
prompt <p>Table properties, containing COMPRESSION
select owner, table_name, tablespace_name, COMPRESSION
from dba_tables
where COMPRESSION = 'ENABLED'
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
and owner not like 'FLOWS%'
;
prompt <p>The index property contains COMPRESSION
select owner, index_name, table_name, COMPRESSION
from dba_indexes
where COMPRESSION = 'ENABLED'
and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
and owner not like 'FLOWS%'
;
prompt <p>Trigger
select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS
from dba_triggers
where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
and owner not like 'FLOWS%';
prompt <p>The foreign key is not indexed
select *
from (select pk.owner PK_OWNER,
pk.constraint_name PK_NAME,
pk.table_name PK_TABLE_NAME,
fk.owner FK_OWNER,
fk.constraint_name FK_NAME,
fk.table_name FK_TABLE_NAME,
fk.delete_rule FK_DELETE_RULE,
ind_col.INDEX_NAME FK_INDEX_NAME,
ind.index_type FK_INDEX_TYPE,
con_col.COLUMN_NAME FK_INDEX_COLUMN_NAME,
con_col.POSITION FK_INDEX_COLUMN_POSITION,
decode(ind_col.INDEX_NAME,
NULL,
NULL,
NVL(x_ind.status, 'VALID')) IS_IND_VALID,
decode(ind_col.INDEX_NAME,
NULL,
NULL,
NVL(x_ind_part.status, 'VALID')) IS_IND_PART_VALID
from (select * from dba_constraints where constraint_type = 'R') fk,
(select * from dba_constraints where constraint_type = 'P') pk,
dba_cons_columns con_col,
dba_ind_columns ind_col,
dba_indexes ind,
(select index_owner, index_name, status
from dba_ind_partitions
where status <> 'VALID') x_ind_part,
(select owner as index_owner, index_name, status
from dba_indexes
where status <> 'VALID') x_ind
where fk.r_constraint_name = pk.constraint_name
and pk.owner = fk.owner
and fk.owner = con_col.owner
and fk.table_name = con_col.table_name
and fk.constraint_name = con_col.CONSTRAINT_NAME
and con_col.owner = ind_col.TABLE_OWNER(+)
and con_col.TABLE_NAME = ind_col.TABLE_NAME(+)
and con_col.COLUMN_NAME = ind_col.COLUMN_NAME(+)
and ind_col.INDEX_OWNER = ind.owner(+)
and ind_col.INDEX_NAME = ind.index_name(+)
and ind_col.INDEX_OWNER = x_ind.index_owner(+)
and ind_col.INDEX_NAME = x_ind.index_name(+)
and ind_col.INDEX_OWNER = x_ind_part.index_owner(+)
and ind_col.INDEX_NAME = x_ind_part.index_name(+))
where FK_INDEX_NAME is null
order by FK_OWNER ASC
;
/* Bad performance
prompt <p>Hot block (summary)
SELECT *+ rule *
e.owner, e.segment_name, e.segment_type, sum(b.tch) tch
FROM dba_extents e,
(SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM <= 10) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk
group by e.owner, e.segment_name, e.segment_type
order by tch desc
;
prompt <p>Hot block (unfolded, not summary)
SELECT *+ rule *
distinct e.owner, e.segment_name, e.segment_type, dbablk,b.tch
FROM dba_extents e,
(SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM <= 10) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk
order by tch desc
;
*/
prompt <p>Appendix: check the parameter settings of session_cached_cursors and increase the parameter value if the usage rate is 100%
SELECT 'session_cached_cursors' PARAMETER,
LPAD(VALUE, 5) VALUE,
DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
FROM (SELECT MAX(S.VALUE) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME = 'session cursor cache count'
AND S.STATISTIC# = N.STATISTIC#),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
LPAD(VALUE, 5),
TO_CHAR(100 * USED / VALUE, '990') || '%'
FROM (SELECT MAX(SUM(S.VALUE)) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME IN
('opened cursors current', 'session cursor cache count')
AND S.STATISTIC# = N.STATISTIC#
GROUP BY S.SID),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');
prompt <p>Appendix: all parameters of Oracle for reference
show parameter
set markup html off
exec dbms_output.put_line('</body>');
exec dbms_output.put_line('</html>');
spool off
/* Obtain awr、addm、ash */
--HTML tags are not used below
SET markup html off spool ON pre off entmap off
set trim on
set trimspool on
set heading off
--select dbid、instance_number
column dbid new_value awr_dbid
column instance_number new_value awr_inst_num
select dbid from v$database;
select instance_number from v$instance;
--Ash report in half an hour
column ashbegintime new_value ashbegin_str
column ashendtime new_value ashend_str
select to_char(sysdate-3/144,'yyyymmddhh24miss') as ashbegintime, to_char(sysdate,'yyyymmddhh24miss') as ashendtime from dual;
column ashfile_name new_value ashfile
select 'ashrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&ashbegin_str) || '_' || to_char(&&ashend_str) ashfile_name from dual;
spool &&ashfile..html
select * from table(dbms_workload_repository.ash_report_html(to_char(&&awr_dbid),to_char(&&awr_inst_num),to_date(to_char(&&ashbegin_str),'yyyymmddhh24miss'),to_date(to_char(&&ashend_str),'yyyymmddhh24miss')));
spool off;
--Create AWR snapshot
column begin_snap new_value awr_begin_snap
column end_snap new_value awr_end_snap
select max(snap_id) begin_snap
from dba_hist_snapshot
where snap_id < (select max(snap_id) from dba_hist_snapshot);
select max(snap_id) end_snap from dba_hist_snapshot;
declare
snap_maxtime date;
snap_mintime date;
begin
select max(end_interval_time) + 0
into snap_maxtime
from dba_hist_snapshot
where snap_id = to_number(&&awr_end_snap);
select max(end_interval_time) + 0
into snap_mintime
from dba_hist_snapshot
where snap_id = to_number(&&awr_begin_snap);
if sysdate - snap_maxtime > 10/1445 then
dbms_workload_repository.create_snapshot();
end if;
end;
/
--The latest two AWR reports between snap_id
column begin_snap new_value awr_begin_snap
column end_snap new_value awr_end_snap
select max(snap_id) begin_snap
from dba_hist_snapshot
where snap_id < (select max(snap_id) from dba_hist_snapshot);
select max(snap_id) end_snap from dba_hist_snapshot;
column awrfile_name new_value awrfile
select 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) awrfile_name from dual;
spool &&awrfile..html
select output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap));
spool off;
--The longest AWR report available (all analyses since a week)
column begin_snap new_value awr_begin_snap
column end_snap new_value awr_end_snap
select a.begin_snap, a.end_snap
from (select startup_time, min(snap_id) begin_snap, max(snap_id) end_snap
from dba_hist_snapshot
group by startup_time) a,
(select max(startup_time) startup_time from dba_hist_snapshot) b
where a.startup_time = b.startup_time
and rownum = 1;
column awrfile_name new_value awrfile
select 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) ||'_all' awrfile_name from dual;
spool &&awrfile..html
select output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap));
spool off;
--Latest ADDM Report
column addmfile_name new_value addmfile
select 'addmrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) addmfile_name from dual;
set serveroutput on
spool &&addmfile..txt
declare
id number;
name varchar2(200) := '';
descr varchar2(500) := '';
addmrpt clob;
v_ErrorCode number;
BEGIN
name := '&&addmfile';
begin
dbms_advisor.create_task('ADDM', id, name, descr, null);
dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', &&awr_begin_snap);
dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', &&awr_end_snap);
dbms_advisor.set_task_parameter(name, 'INSTANCE', &&awr_inst_num);
dbms_advisor.set_task_parameter(name, 'DB_ID', &&awr_dbid);
dbms_advisor.execute_task(name);
exception
when others then
null;
end;
select dbms_advisor.get_task_report(name, 'TEXT', 'TYPICAL')
into addmrpt
from sys.dual;
dbms_output.enable(20000000000);
for i in 1 .. (DBMS_LOB.GETLENGTH(addmrpt) / 2000 + 1) loop
dbms_output.put_line(substr(addmrpt, 1900 * (i - 1) + 1, 1900));
end loop;
dbms_output.put_line('');
begin
dbms_advisor.delete_task(name);
exception
when others then
null;
end;
end;
/
spool off;
exit;
刘雅君
欲事之无繁，则必劳于始而逸于终
No results found
文章目录
- 常用Linux命令和脚本
- Docker学习笔记_15 docker应用 - 部署Goldengate
- Docker学习笔记_14 docker应用 - 部署ORACLE 11g单实例数据库.md
- Oracle主库添加datafile备库空间不足错误处理
- crfclust.bdb文件过大处理
- 在Centos和Ubuntu中强制yum和Apt-get使用IPv4或IPv6
- Oracle SQL Tuning_03 CBO算法
- Docker学习笔记_13 docker应用 - 部署MYSQL服务
- Oracle SQL Tuning_02 SQL优化方法论
- redis入门到精通 - 04 link链表
- redis入门到精通 - 03 Redis字符串类型的操作
- redis入门到精通 - 02 通用key操作命令
- redis入门到精通 - 01 redis特点及安装
- Docker学习笔记_12 docker应用 - 部署REDIS服务
- Docker学习笔记_11 docker应用 - 部署GITLAB服务
- Python3自动化运维 - 05 函数
- Docker学习笔记_10 docker应用 - 部署TOMCAT服务
- 注册美区Apple ID（无需信用卡）
- Docker学习笔记_09 docker应用 - 部署DNS服务
- Python3自动化运维 - 04 分支循环与深浅拷贝
- Python3自动化运维 - 02 数据类型及引用计数
- Python3自动化运维 - 03 运算表达式
- Python3自动化运维 - 01 环境配置
- ORA-01157 ORA-01110 错误
- Oracle SQL Tuning_01 SQL优化方法论
- Oracle特殊恢复原理与实战_12 Oracle坏块处理
- Oracle特殊恢复原理与实战_11 ORA-8102 Index Corruption修复
- Oracle特殊恢复原理与实战_10 恢复ora-600[4193][4194]的错误
- Oracle特殊恢复原理与实战_09 Undo深入内部解析
- Oracle特殊恢复原理与实战_08 Redo Architecture and Configuration
- Oracle特殊恢复原理与实战_07 归档模式下缺失Redo Log后的恢复
- oracle 11g修改自动收集统计信息计划任务的执行时间
- Oracle特殊恢复原理与实战_06 使用BBED手工修复block数据
- Oracle特殊恢复原理与实战_05 使用BBED跳过归档的恢复
- Oracle特殊恢复原理与实战_04 SYSTEM文件头损坏的恢复
- Oracle特殊恢复原理与实战_03 Control file深入内部解析
- 一台电脑同时使用GitHub和GitLab仓库
- Docker学习笔记_08使用Rancher pipeline搭建基于容器的CICD
- Oracle特殊恢复原理与实战_02 Control file丢失的恢复
- Oracle特殊恢复原理与实战_01 Oracle特殊恢复入门
- Docker学习笔记_07 搭建harbor企业级私有registry
- Docker学习笔记_06 使用rancher搭建gitlab服务并启用https
- Docker学习笔记_05 Rancher的访问控制
- Docker学习笔记_04 Rancher的部署安装(编排选用K8S)
- Docker学习笔记_03 Docker的部署安装(Ubuntu)
- Ubuntu Server 16.04.3安装及常用配置图解
- Docker学习笔记_02 Docker的部署安装(CentOS)
- Docker学习笔记_01 Docker的相关概念
- Docker学习笔记_00 Docker常用命令
- CentOS7下MySQL5.7.20的安装-源码方式
- MySQL性能优化最佳实践 - 12 MySQL性能优化的最佳20+条经验
- Oracle Linux网卡参数默认设置导致ORA-603
- MySQL性能优化最佳实践 - 10 MySQL写出高效SQL
- MySQL性能优化最佳实践 - 11 MySQL锁优化分析
- MySQL性能优化最佳实践 - 09 MySQL优化器算法解析
- flashback常用命令整理
- RMAN (Recovery Manager) 常用命令整理
- MySQL性能优化最佳实践 - 08 SQL EXPLAIN解析
- Oracle RMAN异机恢复归档RMAN-07518（供日志挖掘使用）及修改dbid步骤
- MySQL性能优化最佳实践 - 07 MySQL索引设计
- ORACLE审计
- MySQL性能优化最佳实践 - 06 MySQL设计与开发最佳实践
- MySQL性能优化最佳实践 - 05 MySQL核心参数优化
- MySQL性能优化最佳实践 - 04 MySQL优化之Linux系统层面调优
- MySQL性能优化最佳实践 - 03 MySQL服务器优化
- CentOS7下MySQL5.7的安装-RPM方式
- MySQL性能优化最佳实践 - 02 MySQL数据库性能衡量
- MySQL性能优化最佳实践 - 01 MySQL优化方法论
- 一键获取数据库整体信息脚本
- 按SQLID一键获取执行计划索引分区等信息的脚本
- 收获不止SQL优化读书笔记 - 第三章 循规蹈矩--如何读懂SQL执行计划
- 收获不止SQL优化读书笔记 - 第一章 全局在胸--用工具对SQL整体优化
- 收获不止SQL优化读书笔记 - 第二章 风驰电掣--SQL优化过程
- Oracle 12c特性解读-容器数据库和灾备-11 灾备维护经验总结
- Oracle 12c特性解读-容器数据库和灾备-10 容灾切换和故障演练实践总结
- Oracle 12c特性解读-容器数据库和灾备-09 12c 灾备环境搭建
- SQL优化--dbms_sqltune应用实例
- Oracle 12c特性解读-容器数据库和灾备-08 容灾简介和环境搭建
- Oracle 12c特性解读-容器数据库和灾备-07 升级数据库至12c
- Oracle 12c特性解读-容器数据库和灾备-06 PDB备份与恢复
- Oracle 12c特性解读-容器数据库和灾备-05 PDB迁移和克隆
- Oracle 12c特性解读-容器数据库和灾备-04 CDB PDB对象管理
- 在DELL R730物理机上安装配置Oracle Linux 7.3图解
- 使用Dell iDRAC服务器远程控制安装操作系统简要图解
- Oracle 12c特性解读-容器数据库和灾备-03 创建与管理CDB和PDB
- Oracle 12c特性解读-容器数据库和灾备-02 数据库环境初始化
- Hugepage计算脚本
- Oracle 12c特性解读-容器数据库和灾备-01 Oracle 12c体系结构
- MySQL-DBA从小白到大神实战-15 MySQL源码初探
- MySQL-DBA从小白到大神实战-14 运维MySQL过程中线上故障分析与排查
- MySQL DBA从小白到大神实战-13 深入分析Online DDL原理
- 那些12c中惹祸的特性关闭列表
- ORA-27086 unable to lock file - already in use
- MySQL DBA从小白到大神实战-12 MySQL构架设计与容量规划
- MySQL DBA从小白到大神实战-11 构建高可用MySQL系统
- Hexo文章的密码访问
- GoldenGate 12.1.2 新特性参数部分解释
- 数据库GI PSU，SPU(CPU)，Bundle Patches 和 Patchsets 补丁号码快速参考 (文档 ID 1922396.1)
- Oracle Database 11g工作中常用的命令和脚本整理
- Oracle expdp中INCLUDE参数限制4000个字符的解决办法
- MySQL DBA从小白到大神实战-10 深入理解MySQL主从复制
- VMWare CentOS 虚拟机根分区磁盘扩容(基于LVM)
- MySQL DBA从小白到大神实战-09 MySQL性能优化的关键点
- MySQL DBA从小白到大神实战-08 MySQL监控系统之Zabbix
- CentOS7.3安装图解
- MySQL DBA从小白到大神实战-07 MySQL锁机制与事务机制实现
- MySQL DBA从小白到大神实战-06 深入浅出MySQL备份与恢复
- hexo解决代码块空行自动删除问题
- MySQL DBA从小白到大神实战-05 MySQL DBA日常操作
- MySQL DBA从小白到大神实战-04 揭密MySQL databock and binlog的格式
- 那些在11gR2中可能惹祸的新特性
- hexo博客添加常用插件
- hexo更换主题并个性化定制
- GIT常用命令整理
- hexo+github搭建个人博客
- Sublime Text 常用快捷键
- MySQL DBA从小白到大神实战-03 深入MySQL体系结构
- VIM常用命令
- MySQL DBA从小白到大神实战-02 MySQL标准化、自动化部署
- MySQL DBA从小白到大神实战-01 MySQL 高级DBA职业规划
- Oracle职业直通车-07 重做日志、日志挖掘和回滚段
- Oracle职业直通车-06 Oracle的内存结构与后台进程
- Oracle职业直通车-05 数据字典&数据库的备份和恢复
- Oracle职业直通车-04 复杂一点的SQL/PLSQL
- Oracle职业直通车-03 Oracle数据库的安装和配置
- Oracle职业直通车-02 从最简单的SQL语句开始
- Oracle职业直通车-01 轻松带你走进Oracle数据库的世界