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;
虽然我们素未谋面,
但你一定要平平安安。