select
*
from
sys.v_$sqlarea
where
disk_reads>100
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
select
name
,
waits, gets, waits/gets
"Ratio"
from
v$rollstat
a, v$rollname b
where
a.usn
= b.usn
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'
))
select
segment_name,tablespace_name,bytes,blocks
from
user_segments
where
segment_type=
'TABLE'
ORDER
BY
bytes
DESC
,blocks
DESC
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
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
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
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'
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
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
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
select
sum
(pins)
"Total
Pins"
,
sum
(reloads)
"Total
Reloads"
,
sum
(reloads)/
sum
(pins)
*100 libcache
from
v$librarycache
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'
)
SELECT
name
,
value
FROM
v$sysstat
WHERE
name
IN
(
'sorts
(memory)'
,
'sorts
(disk)'
)
SELECT
SUM
(GETS)
"DICTIONARY
GETS"
,
SUM
(GETMISSES)
"DICTIONARY
CACHE GET MISSES"
FROM
V$ROWCACHE
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'
)
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
select
*
from
sys.v_$sqlarea
where
disk_reads>100
select
*
from
sys.v_$sqlarea
where
executions>100
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
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)