Temp表空间上进程的查询
|
1
2
3
4
5
|
select
a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text
from
v$sort_usage a,v$session b,v$sqltext c
where
a.session_addr = b.saddr
and
b.sql_address = c.address
order
by
a.tablespace,b.sid,b.serial#,c.address, c.piece;
|
查看表锁
|
1
|
select
*
from
sys.v_$sqlarea
where
disk_reads>100;
|
监控事例的等待
|
1
2
3
4
|
select
event,
sum
(decode(wait_Time,0,0,1))
"Prev"
,
sum
(decode(wait_Time,0,1,0))
"Curr"
,
count
(*)
"Tot"
from
v$session_Wait
group
by
event
order
by
4 ;
|
回滚段的争用情况
|
1
2
3
|
select
name
, waits, gets, waits/gets
"Ratio"
from
v$rollstat a, v$rollname b
where
a.usn = b.usn;
|
查看前台正在发出的SQL语句
|
1
2
3
4
5
|
select
user_name,sql_text
from
v$open_cursor
where
sid
in
(
select
sid
from
(
select
sid,serial#,username,program
from
v$session
where
status=
'ACTIVE'
));
|
数据表占用空间大小情况
|
1
2
3
4
|
select
segment_name,tablespace_name,bytes,blocks
from
user_segments
where
segment_type=
'TABLE'
ORDER
BY
bytes
DESC
,blocks
DESC
;
|
查看表空间碎片大小
|
1
2
3
4
|
select
tablespace_name,round(sqrt(
max
(blocks)/
sum
(blocks))*
(100/sqrt(sqrt(
count
(blocks)))),2) FSFI
from
dba_free_space
group
by
tablespace_name
order
by
1;
|
查看表空间占用磁盘情况
|
1
2
3
4
5
6
7
8
9
10
11
|
select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes 字节数,
(b.bytes-
sum
(nvl(a.bytes,0))) 已使用,
sum
(nvl(a.bytes,0)) 剩余空间,
sum
(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from
dba_free_space a,dba_data_files b
where
a.file_id=b.file_id
group
by
b.tablespace_name,b.file_id,b.bytes
order
by
b.file_id;
|
查看Oracle 表空间使用率
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SELECT
D.TABLESPACE_NAME,
SPACE
||
'M'
"SUM_SPACE(M)"
,
SPACE
- NVL (FREE_SPACE, 0) ||
'M'
"USED_SPACE(M)"
,
ROUND ( (1 - NVL (FREE_SPACE, 0) /
SPACE
) * 100, 2) ||
'%'
"USED_RATE(%)"
,
FREE_SPACE ||
'M'
"FREE_SPACE(M)"
FROM
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES) / (1024 * 1024), 2)
SPACE
,
SUM
(BLOCKS) BLOCKS
FROM
DBA_DATA_FILES
GROUP
BY
TABLESPACE_NAME) D,
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM
DBA_FREE_SPACE
GROUP
BY
TABLESPACE_NAME) F
WHERE
D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION
ALL
--如果有临时表空间
SELECT
D.TABLESPACE_NAME,
SPACE
||
'M'
"SUM_SPACE(M)"
,
USED_SPACE ||
'M'
"USED_SPACE(M)"
,
ROUND (NVL (USED_SPACE, 0) /
SPACE
* 100, 2) ||
'%'
"USED_RATE(%)"
,
NVL (FREE_SPACE, 0) ||
'M'
"FREE_SPACE(M)"
FROM
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES) / (1024 * 1024), 2)
SPACE
,
SUM
(BLOCKS) BLOCKS
FROM
DBA_TEMP_FILES
GROUP
BY
TABLESPACE_NAME) D,
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (
SUM
(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM
V$TEMP_SPACE_HEADER
GROUP
BY
TABLESPACE_NAME) F
WHERE
D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER
BY
1;
|
查看Temp 表空间实际使用磁盘大小
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Select
f.tablespace_name,
d.file_name
"Tempfile name"
,
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)
"total MB"
,
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,
2)
"Free MB"
,
round(nvl(p.bytes_used, 0) / 1024 / 1024, 2)
"Used MB"
,
round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
2)
as
"Used_Rate(%)"
from
SYS.V_$TEMP_SPACE_HEADER f,
DBA_TEMP_FILES d,
SYS.V_$TEMP_EXTENT_POOL p
where
f.tablespace_name(+) = d.tablespace_name
and
f.file_id(+) = d.file_id
and
p.file_id(+) = d.file_id;
|
查看session使用回滚段
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT
r.
name
回滚段名,
s.sid,
s.serial#,
s.username 用户名,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程序
FROM
sys.v_$session s,sys.v_$
transaction
t,sys.v_$rollname r
WHERE
t.addr = s.taddr
and
t.xidusn = r.usn
ORDER
BY
t.cr_get,t.phy_io;
|
查看SGA区剩余可用内存
|
1
2
3
4
5
6
|
select
name
,
sgasize/1024/1024
"Allocated(M)"
,
bytes/1024
"**空间(K)"
,
round(bytes/sgasize*100, 2)
"**空间百分比(%)"
from
(
select
sum
(bytes) sgasize
from
sys.v_$sgastat) s, sys.v_$sgastat f
where
f.
name
=
'free memory'
;
|
–监控表空间I/O比例
select df.tablespace_name name,df.file_name “file”,f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
监控SGA命中率
|
1
2
3
4
5
6
7
|
select
a.value + b.value
"logical_reads"
,
c.value
"phys_reads"
,
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))
"BUFFER HIT RATIO"
from
v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40 ;
|
监控 SGA 中字典缓冲区的命中率
|
1
2
3
4
5
|
select
parameter, gets,Getmisses , getmisses/(gets+getmisses)*100
"miss ratio"
,
(1-(
sum
(getmisses)/ (
sum
(gets)+
sum
(getmisses))))*100
"Hit ratio"
from
v$rowcache
where
gets+getmisses <>0
group
by
parameter, gets, getmisses ;
|
监控 SGA **享缓存区的命中率，应该小于1%
|
1
2
3
|
select
sum
(pins)
"Total Pins"
,
sum
(reloads)
"Total Reloads"
,
sum
(reloads)/
sum
(pins) *100 libcache
from
v$librarycache;
|
监控 SGA 中重做日志缓存区的命中率，应该小于1%
|
1
2
3
4
5
|
SELECT
name
, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM
v$latch
WHERE
name
IN
(
'redo allocation'
,
'redo copy'
);
|
监控内存和硬盘的排序比率，最好使它小于 .10
|
1
2
3
|
SELECT
name
, value
FROM
v$sysstat
WHERE
name
IN
(
'sorts (memory)'
,
'sorts (disk)'
) ;
|
监控字典缓冲区
|
1
2
|
SELECT
SUM
(GETS)
"DICTIONARY GETS"
,
SUM
(GETMISSES)
"DICTIONARY CACHE GET MISSES"
FROM
V$ROWCACHE ;
|
|
1
2
3
4
|
SELECT
owner,table_name
FROM
DBA_TABLES
WHERE
tablespace_name
in
(
'SYSTEM'
,
'USER_DATA'
)
AND
owner
NOT
IN
(
'SYSTEM'
,
'SYS'
,
'OUTLN'
,
'ORDSYS'
,
'MDSYS'
,
'SCOTT'
,
'HOSTEAC'
);
|
性能最差的SQL
|
1
2
3
4
|
SELECT
*
FROM
(
SELECT
PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM
v$sqlarea
ORDER
BY
disk_reads
DESC
)
WHERE
ROWNUM<100;
|
读磁盘数超100次的sql
|
1
|
select
*
from
sys.v_$sqlarea
where
disk_reads>100;
|
最频繁执行的sql
|
1
|
select
*
from
sys.v_$sqlarea
where
executions>100;
|
查询使用CPU多的用户session
|
1
2
3
4
5
6
|
select
a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from
v$session a,v$process b,v$sesstat c
where
c.statistic#=12
and
c.sid=a.sid
and
a.paddr=b.addr
order
by
value
desc
;
|
当前每个会话使用的对象数
|
1
2
3
4
5
|
SELECT
a.sid,s.terminal,s.program,
count
(a.sid)
FROM
V$ACCESS a,V$SESSION s
WHERE
a.owner <>
'SYS'
AND
s.sid = a.sid
GROUP
BY
a.sid,s.terminal,s.program
ORDER
BY
count
(a.sid) ;
|
3458
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
