SGA Durumu
|
1 2 3 4 5 6 7 |
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 |
Undo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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' |
Kullanıcılara göre PGA kullanımı
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
Belirli bir zaman aralığındaki SQL’leri getiren sorgu
|
1 2 3 4 5 6 |
SELECT * FROM V_$SQLAREA WHERE FIRST_LOAD_TIME BETWEEN '2009-01-10/13:00:08' AND '2009-01-10/14:00:22' |
VT ‘nin son açılış zamanı
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
set linesize 1000 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
|
1 2 3 4 |
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'); |
Request Data
|
1 2 3 |
SELECT SUM(s.VALUE) requests_for_data FROM v$sysstat s WHERE s.NAME IN ('db block gets', 'consistent gets'); |
Logons
|
1 |
SELECT NAME, VALUE FROM v$sysstat WHERE NAME LIKE '%logon%'; |
ColdBackup (user backup)...
12 Mart 2019