1
.监控事例的等待：
select
event,
sum
(decode(wait_time,
0
,
0
,
1
)) prev,
sum
(decode(wait_time,
0
,
1
,
0
)) curr,
count
(
*
)
from
v$session_wait
group
by
event
order
by
4
;
2
.回滚段的争用情况：
select
name,waits,gets,waits
/
gets ratio
from
v$rollstat a,v$rollname b
where
a.usn
=
b.usn;
3
.监控表空间的I
/
O比例：
select
df.tablespace_name name,df.
file_name
"ile",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
4
.监空文件系统的I
/
O比例：
select
substr(a.
file
#,
1
,
2
) "#",substr(a.name,
1
,
30
) "name",a.status,a.bytes,
b.phyrds,b.phywrts
from
v$datafile a,v$filestat b
where
a.
file
#
=
b.
file
#
5
.在某个用户下找所有的索引：
select
user_indexes.table_name, user_indexes.index_name,uniqueness,
column_name
from
user_ind_columns, user_indexes
where
user_ind_columns.index_name
=
user_indexes.index_name
and
user_ind_columns.table_name
=
user_indexes.table_name
order
by
user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6
. 监控 SGA 的命中率
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
;
7
. 监控 SGA 中字典缓冲区的命中率
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;
8
. 监控 SGA 中共享缓存区的命中率，应该小于1
%
select
sum
(pins) "Total Pins",
sum
(reloads) "Total Reloads",
sum
(reloads)
/
sum
(pins)
*
100
libcache
from
v$librarycache;
select
sum
(pinhits
-
reloads)
/
sum
(pins) "hit radio",
sum
(reloads)
/
sum
(pins)
"reload
percent
"
from
v$librarycache;
9
. 显示所有数据库对象的类别和大小
select
count
(name) num_instances ,type ,
sum
(source_size) source_size ,
sum
(parsed_size) parsed_size ,
sum
(code_size) code_size ,
sum
(error_size)
error_size,
sum
(source_size)
+
sum
(parsed_size)
+
sum
(code_size)
+
sum
(error_size)
size_required
from
dba_object_size
group
by
type
order
by
2
;
10
. 监控 SGA 中重做日志缓存区的命中率，应该小于1
%
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
'
);
11
. 监控内存和硬盘的排序比率，最好使它小于 .
10
，增加 sort_area_size
SELECT
name, value
FROM
v$sysstat
WHERE
name
IN
(
'
sorts (memory)
'
,
'
sorts (disk)
'
);
12
. 监控当前数据库谁在运行什么SQL语句
SELECT
osuser, username, sql_text
from
v$session a, v$sqltext b
where
a.sql_address
=
b.address
order
by
address, piece;
13
. 监控字典缓冲区
SELECT
(
SUM
(PINS
-
RELOADS))
/
SUM
(PINS) "LIB CACHE"
FROM
V$LIBRARYCACHE;
SELECT
(
SUM
(GETS
-
GETMISSES
-
USAGE
-
FIXED))
/
SUM
(GETS) "ROW
CACHE"
FROM
V$ROWCACHE;
SELECT
SUM
(PINS) "EXECUTIONS",
SUM
(RELOADS) "CACHE MISSES
WHILE
EXECUTING"
FROM
V$LIBRARYCACHE;
后者除以前者,此比率小于1
%
,接近0
%
为好。
SELECT
SUM
(GETS) "DICTIONARY GETS",
SUM
(GETMISSES)
"DICTIONARY CACHE GET MISSES"
FROM
V$ROWCACHE
14
. 找ORACLE字符集
select
*
from
sys.props$
where
name
=
'
NLS_CHARACTERSET
'
;
15
. 监控 MTS
select
busy
/
(busy
+
idle) "shared servers busy"
from
v$dispatcher;
此值大于0.5时，参数需加大
select
sum
(wait)
/
sum
(totalq) "ispatcher waits"
from
v$queue
where
type
=
'
dispatcher
'
;
select
count
(
*
)
from
v$dispatcher;
select
servers_highwater
from
v$mts;
servers_highwater接近mts_max_servers时，参数需加大
16
. 碎片程度
select
tablespace_name,
count
(tablespace_name)
from
dba_free_space
group
by
tablespace_name
having
count
(tablespace_name)
>
10
;
alter
tablespace name
coalesce
;
alter
table
name
deallocate
unused;
create
or
replace
view
ts_blocks_v
as
select
tablespace_name,block_id,bytes,blocks,
'
free space
'
segment_name
from
dba_free_space
union
all
select
tablespace_name,block_id,bytes,blocks,segment_name
from
dba_extents;
select
*
from
ts_blocks_v;
select
tablespace_name,
sum
(bytes),
max
(bytes),
count
(block_id)
from
dba_free_space
group
by
tablespace_name;
查看碎片程度高的表
SELECT
segment_name table_name ,
COUNT
(
*
) extents
FROM
dba_segments
WHERE
owner
NOT
IN
(
'
SYS
'
,
'
SYSTEM
'
)
GROUP
BY
segment_name
HAVING
COUNT
(
*
)
=
(
SELECT
MAX
(
COUNT
(
*
) )
FROM
dba_segments
GROUP
BY
segment_name);
17
. 表、索引的存储情况检查
select
segment_name,
sum
(bytes),
count
(
*
) ext_quan
from
dba_extents
where
tablespace_name
=
'
&tablespace_name
'
and
segment_type
=
'
TABLE
'
group
by
tablespace_name,segment_name;
select
segment_name,
count
(
*
)
from
dba_extents
where
segment_type
=
'
INDEX
'
and
owner
=
'
&wner
'
group
by
segment_name;
18
、找使用CPU多的用户session
12是cpu used
by
this session
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
;
20
.监控log_buffer的使用情况：(值最好小于1
%
，否则增加log_buffer 的大小)
select
rbar.name,rbar.value,re.name,re.value,
(rbar.value
*
100
)
/
re.value
||
'
%
'
"radio"
from
v$sysstat rbar,v$sysstat re
where
rbar.name
=
'
redo buffer allocation retries
'
and
re.name
=
'
redo entries
'
;
19
、查看运行过的SQL语句：
SELECT
SQL_TEXT
FROM
V$SQL
2849
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
