SGA Durumu
select round(used.bytes /1024/1024 ,2) used_sga_mb ,
round(free.bytes /1024/1024 ,2) free_sga_mb ,
round(total.bytes /1024/1024 ,2) total_sga_mb
from (select sum(bytes) bytes</p>
from v$sgastat where name != ‘free memory’) used , (select sum(bytes) bytes
from v$sgastat where name = ‘free memory’) free , (select sum(bytes) bytes
from v$sgastat) total
round(free.bytes /1024/1024 ,2) free_sga_mb ,
round(total.bytes /1024/1024 ,2) total_sga_mb
from (select sum(bytes) bytes</p>
from v$sgastat where name != ‘free memory’) used , (select sum(bytes) bytes
from v$sgastat where name = ‘free memory’) free , (select sum(bytes) bytes
from v$sgastat) total
Undo
SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.VALUE,1,25) “UNDO RETENTION [Sec]”,
ROUND((d.undo_size / (TO_NUMBER(f.VALUE) *
g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
DBA_TABLESPACES c
WHERE c.CONTENTS = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.NAME = ‘undo_retention’
AND f.NAME = ‘db_block_size’
SUBSTR(e.VALUE,1,25) “UNDO RETENTION [Sec]”,
ROUND((d.undo_size / (TO_NUMBER(f.VALUE) *
g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
DBA_TABLESPACES c
WHERE c.CONTENTS = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.NAME = ‘undo_retention’
AND f.NAME = ‘db_block_size’
Kullanıcılara göre PGA kullanımı
select st.sid “SID”,
sn.name “TYPE”,
ceil(st.value / 1024 / 1024) “MB”
from v$sesstat st,
v$statname sn
where st.statistic# = sn.statistic#
and sid in (select sid
from v$session
where username like ‘&user’)
and upper(sn.name) like ‘%PGA%’
order by st.sid,
st.value desc
sn.name “TYPE”,
ceil(st.value / 1024 / 1024) “MB”
from v$sesstat st,
v$statname sn
where st.statistic# = sn.statistic#
and sid in (select sid
from v$session
where username like ‘&user’)
and upper(sn.name) like ‘%PGA%’
order by st.sid,
st.value desc
Belirli bir zaman aralığındaki SQL’leri getiren sorgu
SELECT * FROM V_$SQLAREA WHERE
FIRST_LOAD_TIME
BETWEEN
‘2009-01-10/13:00:08’
AND
‘2009-01-10/14:00:22’
FIRST_LOAD_TIME
BETWEEN
‘2009-01-10/13:00:08’
AND
‘2009-01-10/14:00:22’
VT ‘nin son açılış zamanı
set linesize 1000
column name format a25
column num_cpus format a10
column db_block_size format a15
column requests_for_data format 999999999999999999
column name format a25
column num_cpus format a10
column db_block_size format a15
column requests_for_data format 999999999999999999
SELECT to_char(open_time,
‘DD-MON-YYYY HH24:MI:SS’) last_startup,
SYSDATE – open_time days_passed_from_last_startup,
vp1.VALUE num_cpus,
vp2.VALUE db_block_size
FROM v$thread, v$system_parameter vp1, v$system_parameter vp2
WHERE instance = ‘SID_ismi’
AND vp1.NAME = ‘cpu_count’
AND vp2.NAME = ‘db_block_size’;
TPS
SELECT MAX(d.NAME) db_name,
SUM(s.VALUE / (24 * 60 * 60 * (SYSDATE – t.open_time)) + 0.001) tps
FROM v$database d, v$sysstat s, v$thread t
WHERE s.NAME IN (‘user commits’, ‘transaction rollbacks’);
SUM(s.VALUE / (24 * 60 * 60 * (SYSDATE – t.open_time)) + 0.001) tps
FROM v$database d, v$sysstat s, v$thread t
WHERE s.NAME IN (‘user commits’, ‘transaction rollbacks’);
Request Data
SELECT SUM(s.VALUE) requests_for_data
FROM v$sysstat s
WHERE s.NAME IN (‘db block gets’, ‘consistent gets’);
FROM v$sysstat s
WHERE s.NAME IN (‘db block gets’, ‘consistent gets’);
Logons
SELECT NAME, VALUE FROM v$sysstat WHERE NAME LIKE ‘%logon%’;