|
|
P5
|
发表于 2012-5-10 09:18:52
数据文件与空间类
--各种文件数量
select count(*) from v$tempfile;
select count(*) from v$datafile;
--表空间大小
select tablespace_name , sum(bytes)/1024/1024 M from dba_temp_files group by tablespace_name
union all
select tablespace_name , sum(bytes)/1024/1024 M from dba_data_files group by tablespace_name;
--数据文件状态
select t.online_status,count(*)
from dba_data_files t
group by t.online_status ;
--表空间基本信息
SELECT t.status,
t.tablespace_name,
t.extent_management,
t.segment_space_management,
t.contents
FROM DBA_TABLESPACES t
order by t.status
--临时段使用情况
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username,
segtype,
extents "Extents Allocated",
blocks "Blocks Allocated"
FROM v$tempseg_usage;
--查看临时表空间总体使用情况
SELECT TMP_TBS.TABLESPACE_NAME,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'db_block_size') PARA
GROUP BY TMP_USED.TABLESPACE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME;
--查看临时表空间中排序段和数据段的使用情况
SELECT TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE TEMP_SEG_TYPE,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE, TMP_USED.SEGTYPE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'db_block_size') PARA
GROUP BY TMP_USED.TABLESPACE, TMP_USED.SEGTYPE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE;
--表空间
set linesize 200;
col TABLESPACE_NAME for a30;
select a.TABLESPACE_NAME tbs_name,
round(a.BYTES/1024/1024) Total_MB,
round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) Used_MB,
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) Pct_Free,
nvl(round(b.BYTES/1024/1024), 0) Free_MB ,
auto
from (select TABLESPACE_NAME,
sum(BYTES) BYTES,
max(AUTOEXTENSIBLE) AUTO
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
/
---查看数据文件物理IO信息
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情况
/*COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990999;
COL writes FORMAT 999999990;
COL br FORMAT 999999990 HEADING "BlksRead";
COL bw FORMAT 9999999990 HEADING "BlksWrite";
COL rtime FORMAT 9999999990;
COL wtime FORMAT 9999999990;
set linesize 3000;
set pagesize 9999;*/
SELECT ts.name AS ts,
fs.phyrds "Reads",
fs.phywrts "Writes",
fs.phyblkrd AS br,
fs.phyblkwrt AS bw,
fs.readtim/100 "RTime*s",
fs.writetim/100 "WTime*s"
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 /100 "RTime*s",
ts.writetim/100 "WTime*s"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts#
AND tf.file# = ts.file#
ORDER BY 1;
--定位哪些object在buffer cache中存在，占用的buffer cache的量是多少以及占用的是什么类型的buffer cache。
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds
order by decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),bh.blocks;
--针对不同用户的占用buffer的合计
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
own,sum(bh.blocks)*8192/1024/1024 used_M
from x$kcbwds ds,x$kcbwbpd pd,(select set_ds,
o.name object_name,count(*) BLOCKS,u.name own
from obj$ o, x$bh x,user$ u where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0 and o.owner#=u.user#
group by set_ds,o.name,u.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
And pd.bp_size != 0
and ds.addr=bh.set_ds
group by decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),
own
order by own;
--buffer cache 对象所有者，名称， 类型，总大小，cache大小
column c1 heading "Object|Name" format a30 truncate
column c2 heading "Object|Type" format a12 truncate
column c3 heading "Number of|Blocks" format 999,999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999
break on report
compute sum of c3 on report
select owner,
object_name,
object_type,
num_blocks,
sum(blocks),
trunc((num_blocks / decode(sum(blocks), 0, .001, sum(blocks))), 4) * 100 || '%' -- buffer中的数据块比例
from (select o.owner owner_name,
o.object_name object_name,
o.object_type object_type,
count(1) num_blocks
from dba_objects o, v$bh bh
where o.object_id = bh.objd
and o.owner not in ('SYS', 'SYSTEM')
group by o.object_name, o.object_type, o.owner
order by count(1) desc) t1,
dba_segments s
where s.segment_name = t1.object_name
and s.owner = t1.owner_name
and num_blocks > 10
group by object_name, object_type, num_blocks, owner
order by num_blocks desc;
--10个热点对象
col objct_name for a30
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
/
--逻辑读
col objct_name for a30
select *
from (select owner, object_name, value
from v$segment_statistics
where statistic_name = 'logical reads'
order by value desc)
where rownum <= 10
/
--物理读最多十个对象
col objct_name for a30
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 A30
COL TABLESPACE_NAME FOR A20
SELECT T.FILE_NAME,
T.TABLESPACE_NAME,
ROUND(S.SINGLEBLKRDTIM / S.SINGLEBLKRDS, 2) AS CS,
S.READTIM/100 READTIME_S,
S.WRITETIM/100 WIRTETIME_S
FROM V$FILESTAT S, DBA_DATA_FILES T
WHERE S.FILE# = T.FILE_ID
AND ROWNUM <= 10
ORDER BY CS DESC
/
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖
