DBA常用SQL语句（1）— 概况信息

检查 database 基本信息

select * from v$version;
select name ,open_mode,log_mode from v$database;
select instance_number,instance_name ,status from gv$instance;
show parameter cpu_count
show parameter block_size
select group#,thread#,members,bytes/1024/1024 from gv$log;
show sga
select count(*) from v$controlfile
select count(*) from v$tempfile;
select count(*) from v$datafile;

检查表空间数据文件信息

col tablespace_name for a30
select 
tablespace_name,
sum(bytes)/1024/1024 
from dba_temp_files group by 
tablespace_name;

检查表空间

SELECT
TABLESPACE_NAME,
EXTENT_MANAGEMENT,
ALLOCATION_TYPE,
SEGMENT_SACE_MANAGEMENT
FROM DBA_TABLESPACES;

检查数据文件状态

select count(*),status from v$datafile group by status;

检查表空间使用情况

select
f.tablespace_name,
a.total,
f.free,
(a.total-f.free)/1024 "used SIZE(G)",
round((f.free/a.total)*100) "% Free"
from
(
select 
tablespace_name,
sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(
select 
tablespace_name,
round(sum(bytes/(1024*1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"

查询临时 segment 使用情况

COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT 
username,
segtype,
extents "Extents Allocated",
blocks "Blocks Allocated"
FROM v$tempseg_usage;

查看临时表空间大小

select 
tablespace_name,
file_name,bytes/1024/1024 "file_size(M)",
autoextensible
from dba_temp_files;

select 
status,
enabled,
name,
bytes/1024/1024 file_size
from v$tempfile;

查看临时表空间的使用情况

SELECT 
temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(
SELECT 
tablespace_name,
SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

查找消耗较多临时表空间的 sql

Select 
se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid

查看当前临时表空间使用大小与正在占用临时表空间的 sql 语句

select 
sess.SID,
segtype,
blocks * 8 / 1000 "MB",
sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;

查看数据文件信息,若文件较多可以根据需要字段进行排序 输出 top 10

col datafile for a60
SELECT 
fs.phyrds "Reads",
fs.phywrts "Writes",
fs.avgiotim "Average I/O Time",
df.name "Datafile"
FROM v$datafile df, v$filestat fs
WHERE df.file# = fs.file#;

查看所有数据文件 I/O 情况

phyrds 为物理读的次数极为 Reads， phywrts 为物理写的次数极为 Writes， phyblkrd 为物理块读的次数即为 br， phyblkwrt 为物理写的次数即为 bw。 readtime 为耗费在物理读上的总时间为 RTime， writetim 为耗费在物理写上的总时间为 WTime。 RTime和WTime这两个值只有在参数 timed_statistics 参数为 true 时才有效。

COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
SELECT 
ts.name AS ts,
fs.phyrds "Reads",
fs.phywrts "Writes",
fs.phyblkrd AS br,
fs.phyblkwrt AS bw,
fs.readtim "RTime",
fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT 
ts.name AS ts,
ts.phyrds "Reads",
ts.phywrts "Writes",
ts.phyblkrd AS br,
ts.phyblkwrt AS bw,
ts.readtim "RTime",
ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

获取 top 10 热 segment

set linesize 180
col object_name for a40
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10;

判断物理读最多的 object

select * from (select owner,object_name,value from v$segment_statistics where
statistic_name='physical reads' order by value desc) where rownum<=10;

查看热点数据文件 ( 从单块读取时间判断 )

col FILE_NAME for a60
set linesize 180
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim/s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and s.singleblkrds <>0 and rownum<=10 order by cs desc;

估算表空间大小

select a.tablespace_name,
round(a.s,2) "CURRENT_TOTAL(MB)" ,
round((a.s - f.s),2) "USED(MB)" ,
f.s "FREE(MB)" ,
round(f.s / a.s * 100, 2) "FREE%" ,
g.autoextensible,
round(a.ms,2) "MAX_TOTAL(MB)"
from ( select d.tablespace_name,
sum (bytes / 1024 / 1024) s,
sum (decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms
from dba_data_files d
group by d.tablespace_name) a,
( select f.tablespace_name, sum (f.bytes / 1024 / 1024) s
from dba_free_space f
group by f.tablespace_name) f,
( select distinct tablespace_name, autoextensible
from DBA_DATA_FILES
where autoextensible = 'YES'
union
select distinct tablespace_name, autoextensible
from DBA_DATA_FILES
where autoextensible = 'NO'
and tablespace_name not in
( select distinct tablespace_name
from DBA_DATA_FILES
where autoextensible = 'YES' )) g
where a.tablespace_name = f.tablespace_name
and g.tablespace_name = f.tablespace_name order by "FREE%" ;

精确计算表空间大小，消耗系统资源，慎用

SELECT F.TABLESPACE_NAME,
A.ALL_TOTAL "总空间" ,
A.ALL_USED "总使用空间" ,
A.ALL_TOTAL - A.ALL_USED "总剩余空间" ,
(A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL * 100 "总剩余比例" ,
A.TOTAL "当前大小" ,
U.USED "当前使用空间" ,
F. FREE "当前剩余空间" ,
(U.USED / A.TOTAL) * 100 "当前使用比例" ,
(F. FREE / A.TOTAL) * 100 "当前剩余比例"
FROM ( SELECT TABLESPACE_NAME,
SUM (BYTES / (1024 * 1024 * 1024)) TOTAL,
SUM (DECODE(AUTOEXTENSIBLE, 'YES' , MAXBYTES, BYTES) /
(1024 * 1024 * 1024)) ALL_TOTAL,
SUM (USER_BYTES) / (1024 * 1024 * 1024) ALL_USED
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) USED
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME) U,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
ORDER BY (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL,F. FREE / A.TOTAL ASC ;

检查日志切换频率

select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 1 order by first_time ,minutes;

检查 lgwr i/o 性能 (time_waited/total_waits:表示平均 lgwr 写入完成时间若>1 表示写入过慢 )

select total_waits,time_waited,average_wait,time_waited/total_waits as avg from
v$system_event where event = 'log file parallel write';

查询 redo block size

select max(lebsz) from x$kccle;

查看 user commit 次数

select to_number(value,99999999999) from v$sysstat where name='user commits';

查看系统运行时间

select (sysdate - startup_time)*24*60*60 as seconds from v$instance

计算出每个事务平均处理多少个 redo block

select 
value 
from v$sysstat where name = 'redo blocks written';

select 
a.redoblocks/b.trancount
from (select value redoblocks from v$sysstat where 
name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b

计算每天产生了多少日志

SELECT 
TO_CHAR (TRUNC (COMPLETION_TIME), 'yyyy-mm-dd') "日期",
SUM (blocks * BLOCK_SIZE) / 1024 / 1024 / 1024 "日志量(G)"
FROM V$ARCHIVED_LOG
WHERE dest_id = 1
GROUP BY TRUNC (COMPLETION_TIME)
ORDER BY TRUNC (COMPLETION_TIME) DESC;