Queries | SysDBA

The status of the SGA (System Global Area)

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&lt;/p&gt;
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

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

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'

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'

The Program Global Array usage on a user basis

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 '&amp;user')
and upper(sn.name) like '%PGA%'
order by st.sid,
st.value desc

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

Retrieving queries that have been run at specified times

SELECT * FROM V_$SQLAREA WHERE
FIRST_LOAD_TIME
BETWEEN
'2009-01-10/13:00:08'
AND
'2009-01-10/14:00:22'

SELECT * FROM V_$SQLAREA WHERE

FIRST_LOAD_TIME

BETWEEN

'2009-01-10/13:00:08'

AND

'2009-01-10/14:00:22'

The last time the database was started

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';

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';

Transactions Per Second (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');

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

SELECT SUM(s.VALUE) requests_for_data
FROM v$sysstat s
WHERE s.NAME IN ('db block gets', 'consistent gets');

SELECT SUM(s.VALUE) requests_for_data

FROM v$sysstat s

WHERE s.NAME IN ('db block gets', 'consistent gets');

Logons

SELECT NAME, VALUE FROM v$sysstat WHERE NAME LIKE '%logon%';

1	SELECT NAME, VALUE FROM v$sysstat WHERE NAME LIKE '%logon%';