QueryList=archive,audit,dbblockgets,dbconsistentgets,dbhitratio,dbphysicalread,hitratio_body,hitratio_sqlarea,hitratio_table_proc,
lio_current_read,locks,maxprocs,maxsession,miss_latch,pga_aggregate_target, pga,phio_datafile_reads,phio_datafile_writes,phio_redo_writes,pinhitratio_body,pinhitratio_sqlarea,pinhitratio_table-proc,pinhitratio_trigger,
pool_dict_cache,pool_free_mem,pool_lib_cache,pool_misc,pool_sql_area,procnum,session_active,session_inactive,session,session_system,sga_buffer_cache,
sga_fixed,sga_java_pool,sga_large_pool,sga_log_buffer,sga_shared_pool,tbl_space,userconn,waits_controfileio,waits_directpath_read,
waits_file_io,waits_latch,waits_logwrite,waits_multiblock_read,waits_singleblock_read,hitratio_trigger,lio_block_changes,lio_consistent_read,waits_other,waits_sqlnet,users_locked,uptime,
dbfilesize,dbsize,elapsedtime,
asm_space,dba_jobs,execute_count,user_commits,user_rollbacks,tps,
checkBitAttack,fra_use,instance_status,delay_apply,delay_transport,db_role,adg_apply_block_id,adg_apply_status
DataGuardPrimaryQueryList=dg_error,dg_sequence_number
DataGuardStandbyQueryList=dg_sequence_number_stby
RmanQueryList=rman_check_status
rman_check_status.Query=select ’ DB NAME->‘||DB_NAME||’- ROW TYPE->‘||ROW_TYPE||’- START TIME->‘||to_char(start_time, ‘Dy DD-Mon-YYYY HH24:MI:SS’) ||’- END TIME->‘||to_char(end_time, ‘Dy DD-Mon-YYYY HH24:MI:SS’)||’- MBYTES PROCESSED->‘||MBYTES_PROCESSED||’- OBJECT TYPE->‘||OBJECT_TYPE||’- STATUS->‘||STATUS||’- OUTPUT DEVICE->‘||OUTPUT_DEVICE_TYPE||’- INPUT MB->‘||INPUT_BYTES/1048576||’- OUT MB’||OUTPUT_BYTES/1048576
FROM rc_rman_status
WHERE start_time > SYSDATE - 1
AND ( STATUS like ‘%FAILED%’
OR STATUS like ‘%ERROR%’)
ORDER BY END_TIME
rman_check_status.NoDataFound=none
checkBitAttack.Query=Select Count(1) From (Select 1 From Dba_Triggers Where Trigger_Name Like 'DBMS_%INTERNAL%’ Union All Select 1 From Dba_Procedures Where Object_Name Like 'DBMS%_INTERNAL% ’ Union All Select 1 From Dba_Objects Where Object_Name Like ‘DBMS_CORE_INTERNA%’ Or Object_Name Like ‘DBMS_SYSTEM_INTERNA%’ Or Object_Name Like ‘DBMS_SUPPORT_INTERNA%’ Or Object_Name Like ‘DBMS_STANDARD_FUN9%’ Or Object_Name Like ‘ORACHK%’ Union All Select 1 From Dba_Jobs Where What Like ‘DBMS_STANDARD_FUN9%’)
checkBitAttack.Period=60
db_role.Query=select database_role from v$database
db_role.NoDataFound=none
adg_apply_status.Query=select status from gv
d
a
t
a
b
a
s
e
a
,
g
v
database a,gv
databasea,gvmanaged_standby b where b.process=‘MRP0’ and b.inst_id=a.inst_id
adg_apply_status.NoDataFound=none
adg_apply_block_id.Query=select block# from gv
d
a
t
a
b
a
s
e
a
,
g
v
database a,gv
databasea,gvmanaged_standby b where b.process=‘MRP0’ and b.inst_id=a.inst_id
adg_apply_block_id.NoDataFound=0
delay_apply.Query=select to_number(substr(v.value, 2, 2)) * 24 * 60 * 60 +
to_number(substr(v.value, 5, 2)) * 60 * 60 +
to_number(substr(v.VALUE, 8, 2)) * 60 +
to_number(substr(v.VALUE, 11, 2)) as delay_seconds
from v$dataguard_stats v
where v.name = ‘apply lag’
delay_apply.NoDataFound=0
delay_transport.Query=select to_number(substr(v.value, 2, 2)) * 24 * 60 * 60 +
to_number(substr(v.value, 5, 2)) * 60 * 60 +
to_number(substr(v.VALUE, 8, 2)) * 60 +
to_number(substr(v.VALUE, 11, 2)) as delay_seconds
from v$dataguard_stats v
where v.name = ‘transport lag’
delay_transport.NoDataFound=0
instance_status.Query=select 'Only ‘||v2.INSTANCE_NAME||’ is running on host '||v2.HOST_NAME as result from gv
i
n
s
t
a
n
c
e
v
2
w
h
e
r
e
(
S
E
L
E
C
T
v
a
l
u
e
F
R
O
M
v
instance v2 where \ (SELECT value FROM v
instancev2where (SELECTvalueFROMvparameter where name = ‘cluster_database_instances’) >(
select count(*) as instance_num from gv$instance v where v.STATUS=‘OPEN’)
instance_status.NoDataFound=none
fra_use.Query=select round(((space_used-SPACE_RECLAIMABLE)/space_limit)*100,2) as fra_used from v$recovery_file_dest
fra_use.NoDataFound=none
asm_space.Query=select name || ‘,free_mb:’ || free_mb || ‘,free_rate:’ || round((free_mb / total_mb) * 100, 2) || ‘%;’ asmdisk_free from v
A
S
M
D
I
S
K
G
R
O
U
P
w
h
e
r
e
r
o
u
n
d
(
(
f
r
e
e
m
b
/
t
o
t
a
l
m
b
)
∗
100
,
2
)
<
9
a
n
d
f
r
e
e
m
b
<
204800
a
s
m
s
p
a
c
e
.
N
o
D
a
t
a
F
o
u
n
d
=
n
o
n
e
d
b
a
j
o
b
s
.
Q
u
e
r
y
=
s
e
l
e
c
t
c
o
u
n
t
(
1
)
f
r
o
m
d
b
a
j
o
b
s
w
h
e
r
e
B
R
O
K
E
N
=
′
Y
′
d
b
a
j
o
b
s
.
N
o
D
a
t
a
F
o
u
n
d
=
n
o
n
e
e
x
e
c
u
t
e
c
o
u
n
t
.
Q
u
e
r
y
=
s
e
l
e
c
t
v
a
l
u
e
f
r
o
m
v
ASM_DISKGROUP where round((free_mb / total_mb) * 100, 2) < 9 and free_mb < 204800 asm_space.NoDataFound=none dba_jobs.Query=select count(1) from dba_jobs where BROKEN = 'Y' dba_jobs.NoDataFound=none execute_count.Query=select value from v
ASMDISKGROUPwhereround((freemb/totalmb)∗100,2)<9andfreemb<204800asmspace.NoDataFound=nonedbajobs.Query=selectcount(1)fromdbajobswhereBROKEN=′Y′dbajobs.NoDataFound=noneexecutecount.Query=selectvaluefromvsysstat where name = ‘execute count’
execute_count.NoDataFound=none
user_commits.Query=select value from v
s
y
s
s
t
a
t
w
h
e
r
e
n
a
m
e
=
′
u
s
e
r
c
o
m
m
i
t
s
′
u
s
e
r
c
o
m
m
i
t
s
.
N
o
D
a
t
a
F
o
u
n
d
=
n
o
n
e
u
s
e
r
r
o
l
l
b
a
c
k
s
.
Q
u
e
r
y
=
s
e
l
e
c
t
v
a
l
u
e
f
r
o
m
v
sysstat where name = 'user commits' user_commits.NoDataFound=none user_rollbacks.Query=select value from v
sysstatwherename=′usercommits′usercommits.NoDataFound=noneuserrollbacks.Query=selectvaluefromvsysstat where name = ‘user rollbacks’
user_rollbacks.NoDataFound=none
tps.Query=select sum(value) from v$sysstat where name in( ‘user rollbacks’,‘user commits’)
tps.NoDataFound=none
elapsedtime.Query=select Elapsedtime from (select snap_id,round((to_date(to_char(end_interval_time, ‘yyyymmddHH24MISS’),‘yyyymmddHH24MISS’) - to_date(to_char(begin_interval_time, ‘yyyymmddHH24MISS’),‘yyyymmddHH24MISS’)) * 24 * 60, 0) Elapsedtime from dba_hist_snapshot where begin_interval_time >= sysdate - 140 / (24 * 60) and instance_number = (select instance_number from v$instance) order by snap_id desc) where rownum=1
elapsedtime.NoDataFound=0
dbfilesize.Query=select to_char(sum(bytes/1024/1024), ‘FM99999999999999990’) retvalue from dba_data_files
dbsize.Query=SELECT to_char(sum( NVL(a.bytes/1024/1024 - NVL(f.bytes/1024/1024, 0), 0)), ‘FM99999999999999990’) retvalue
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’)
dbsize.Period=60
uptime.Query=select to_char((sysdate-startup_time)*86400, ‘FM99999999999999990’) retvalue from v$instance
users_locked.Query=SELECT username||’ ‘|| lock_date ||’ '|| account_status FROM dba_users where ACCOUNT_STATUS like ‘EXPIRED(GRACE)’ or ACCOUNT_STATUS like ‘LOCKED(TIMED)’
users_locked.NoDataFound=none
archive.Query=select round(A.LOGSB.AVG/1024/1024/10) from ( SELECT COUNT () LOGS FROM V L O G H I S T O R Y W H E R E F I R S T T I M E > = ( s y s d a t e − 10 / 60 / 24 ) ) A , ( S E L E C T A v g ( B Y T E S ) A V G , C o u n t ( 1 ) , M a x ( B Y T E S ) M a x B y t e s , M i n ( B Y T E S ) M i n B y t e s F R O M v LOG_HISTORY WHERE FIRST_TIME >= (sysdate -10/60/24)) A, ( SELECT Avg(BYTES) AVG, Count(1), Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM v LOGHISTORYWHEREFIRSTTIME>=(sysdate−10/60/24))A,(SELECTAvg(BYTES)AVG,Count(1),Max(BYTES)MaxBytes,Min(BYTES)MinBytesFROMvlog) B
archive.Query=select nvl(round(sum(blocks * block_size) / 1024 / 1024), 0) from vKaTeX parse error: Expected 'EOF', got '#' at position 27: …g where thread#̲=(select thread…instance) and FIRST_TIME >= (sysdate - 30 / 24 / 60)
archive.RaceConditionQuery=select value from v$parameter where name=‘log_archive_start’
archive.RaceConditionValue=FALSE
audit.Query=select username “username”,
to_char(timestamp,‘DD-MON-YYYY HH24:MI:SS’) “time_stamp”,
action_name “statement”,
os_username “os_username”,
userhost “userhost”,
returncode||decode(returncode,‘1004’,‘-Wrong Connection’,‘1005’,‘-NULL Password’,‘1017’,‘-Wrong Password’,‘1045’,‘-Insufficient Priviledge’,‘0’,‘-Login Accepted’,‘28000’,‘-The account is locked’,‘–’) “returncode”
from sys.dba_audit_session
where timestamp>(sysdate-3/24/60) and returncode <> 0
order by timestamp
audit.NoDataFound=none
dbblockgets.Query=select to_char(sum(decode(name,‘db block gets’, value,0))) “block_gets”
FROM v$sysstat
dbconsistentgets.Query=select to_char(sum(decode(name,‘consistent gets’, value,0))) “consistent_gets”
FROM v$sysstat
dbhitratio.Query=select round((sum(decode(name, ‘consistent gets’, value, 0)) + sum(decode(name, ‘db block gets’, value, 0)) - sum(decode(name, ‘physical reads’, value, 0))) / (sum(decode(name, ‘consistent gets’, value, 0)) + sum(decode(name, ‘db block gets’, value, 0))) * 100, 4) “hit_ratio” FROM v s y s s t a t d b p h y s i c a l r e a d . Q u e r y = s e l e c t s u m ( d e c o d e ( n a m e , ′ p h y s i c a l r e a d s ′ , v a l u e , 0 ) ) " p h y s r e a d s " F R O M v sysstat dbphysicalread.Query=select sum(decode(name,'physical reads', value,0)) "phys_reads" FROM v sysstatdbphysicalread.Query=selectsum(decode(name,′physicalreads′,value,0))"physreads"FROMvsysstat
sqlnotindexed.Query=SELECT SUM(DECODE(NAME, ‘table scans (long tables)’, VALUE, 0))/ (SUM(DECODE(NAME, ‘table scans (long tables)’, VALUE, 0))+SUM(DECODE(NAME, ‘table scans (short tables)’, VALUE, 0)))*100 SQL_NOT_INDEXED FROM V$SYSSTAT WHERE 1=1 AND ( NAME IN (‘table scans (long tables)’,‘table scans (short tables)’) )
hitratio_body.Query=select round(gethitratio100,4) “get_pct” FROM v
l
i
b
r
a
r
y
c
a
c
h
e
w
h
e
r
e
n
a
m
e
s
p
a
c
e
=
′
B
O
D
Y
′
h
i
t
r
a
t
i
o
s
q
l
a
r
e
a
.
Q
u
e
r
y
=
s
e
l
e
c
t
r
o
u
n
d
(
g
e
t
h
i
t
r
a
t
i
o
∗
100
,
4
)
"
g
e
t
p
c
t
"
F
R
O
M
v
librarycache where namespace ='BODY' hitratio_sqlarea.Query=select round(gethitratio*100,4) "get_pct" FROM v
librarycachewherenamespace=′BODY′hitratiosqlarea.Query=selectround(gethitratio∗100,4)"getpct"FROMvlibrarycache where namespace =‘SQL AREA’
hitratio_trigger.Query=select round(gethitratio100,4) “get_pct” FROM v
l
i
b
r
a
r
y
c
a
c
h
e
w
h
e
r
e
n
a
m
e
s
p
a
c
e
=
′
T
R
I
G
G
E
R
′
h
i
t
r
a
t
i
o
t
a
b
l
e
p
r
o
c
.
Q
u
e
r
y
=
s
e
l
e
c
t
r
o
u
n
d
(
g
e
t
h
i
t
r
a
t
i
o
∗
100
,
4
)
"
g
e
t
p
c
t
"
F
R
O
M
v
librarycache where namespace ='TRIGGER' hitratio_table_proc.Query=select round(gethitratio*100,4) "get_pct" FROM v
librarycachewherenamespace=′TRIGGER′hitratiotableproc.Query=selectround(gethitratio∗100,4)"getpct"FROMvlibrarycache where namespace = ‘TABLE/PROCEDURE’
lio_block_changes.Query=SELECT to_char(SUM(DECODE(NAME,‘db block changes’,VALUE,0)))
FROM V$SYSSTAT
WHERE NAME =‘db block changes’
lio_consistent_read.Query=SELECT to_char(sum(decode(name,‘consistent gets’,value,0))) FROM V S Y S S T A T W H E R E N A M E = ′ c o n s i s t e n t g e t s ′ l i o c u r r e n t r e a d . Q u e r y = S E L E C T t o c h a r ( s u m ( d e c o d e ( n a m e , ′ d b b l o c k g e t s ′ , v a l u e , 0 ) ) ) F R O M V SYSSTAT WHERE NAME ='consistent gets' lio_current_read.Query=SELECT to_char(sum(decode(name,'db block gets',value,0))) FROM V SYSSTATWHERENAME=′consistentgets′liocurrentread.Query=SELECTtochar(sum(decode(name,′dbblockgets′,value,0)))FROMVSYSSTAT WHERE NAME =‘db block gets’
locks.Query=select ‘machine:’ || v.machine || ’ osuser:’ || v.osuser || ’ sid:’ ||
v.sid || ’ serial:’ || v.serial# || ’ event:’ || v.event ||
’ program:’ || v.program || ’ sql:’ || v2.sql_text as res
from v
s
e
s
s
i
o
n
v
,
v
session v, v
sessionv,vsql v2
where event like ‘enq%’
and v.sql_id = v2.sql_id
and state = ‘WAITING’
and seconds_in_wait >= 10
#locks.Query=SELECT b.session_id||’ ’ AS sid,
NVL(b.oracle_username, ‘(oracle)’)||’ ’ AS username,
a.owner||’ ’ AS object_owner,
a.object_name||’ ‘,
Decode(b.locked_mode, 0, ‘None’,
1, ‘Null (NULL)’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share (S)’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive (X)’,
b.locked_mode) locked_mode,
nvl(b.os_user_name,’-') os_user_name
FROM dba_objects a,
v
l
o
c
k
e
d
o
b
j
e
c
t
b
,
v
locked_object b,v
lockedobjectb,vsession c
WHERE a.object_id = b.object_id
and b.session_id = c.sid
and c.prev_exec_start < (sysdate - 5 / 60 / 24) \
ORDER BY 1, 2, 3, 4
#locks.Query=select sn.USERNAME ||‘@’||sn.machine,
‘|SID->’ || m.SID,
‘|Serial->’|| sn.SERIAL#,
‘|Lock Type->’||m.TYPE,
decode(LMODE,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’) lock_type,
decode(REQUEST,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’) lock_requested,
‘|Time (Sec)->’||m.CTIME “Time(sec)”,
‘|ID1->’||m.ID1,
‘|ID2->’||m.ID2,
‘|SQL Text->’||t.SQL_TEXT
from v
s
e
s
s
i
o
n
s
n
,
v
session sn, \ v
sessionsn, vlock m ,
v
s
q
l
t
e
x
t
t
w
h
e
r
e
t
.
A
D
D
R
E
S
S
=
s
n
.
S
Q
L
A
D
D
R
E
S
S
a
n
d
t
.
H
A
S
H
V
A
L
U
E
=
s
n
.
S
Q
L
H
A
S
H
V
A
L
U
E
a
n
d
(
(
s
n
.
S
I
D
=
m
.
S
I
D
a
n
d
m
.
R
E
Q
U
E
S
T
!
=
0
)
o
r
(
s
n
.
S
I
D
=
m
.
S
I
D
a
n
d
m
.
R
E
Q
U
E
S
T
=
0
a
n
d
L
M
O
D
E
!
=
4
a
n
d
(
I
D
1
,
I
D
2
)
i
n
(
s
e
l
e
c
t
s
.
I
D
1
,
s
.
I
D
2
f
r
o
m
v
sqltext t \ where t.ADDRESS =sn.SQL_ADDRESS \ and t.HASH_VALUE =sn.SQL_HASH_VALUE \ and ((sn.SID =m.SID and m.REQUEST !=0) \ or (sn.SID =m.SID and m.REQUEST =0 and LMODE !=4 and (ID1, ID2) in \ (select s.ID1, s.ID2 \ from v
sqltextt wheret.ADDRESS=sn.SQLADDRESS andt.HASHVALUE=sn.SQLHASHVALUE and((sn.SID=m.SIDandm.REQUEST!=0) or(sn.SID=m.SIDandm.REQUEST=0andLMODE!=4and(ID1,ID2)in (selects.ID1,s.ID2 fromvlock S
where REQUEST !=0
and s.ctime > 5
and s.ID1 =m.ID1
and s.ID2 =m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECE
locks.NoDataFound=none
maxprocs.Query=select value “maxprocs” from v
p
a
r
a
m
e
t
e
r
w
h
e
r
e
n
a
m
e
=
′
p
r
o
c
e
s
s
e
s
′
m
a
x
s
e
s
s
i
o
n
.
Q
u
e
r
y
=
s
e
l
e
c
t
v
a
l
u
e
"
m
a
x
s
e
s
s
"
f
r
o
m
v
parameter where name ='processes' maxsession.Query=select value "maxsess" from v
parameterwherename=′processes′maxsession.Query=selectvalue"maxsess"fromvparameter where name =‘sessions’
miss_latch.Query=SELECT SUM(misses) FROM V
L
A
T
C
H
p
g
a
a
g
g
r
e
g
a
t
e
t
a
r
g
e
t
.
Q
u
e
r
y
=
s
e
l
e
c
t
t
o
c
h
a
r
(
d
e
c
o
d
e
(
u
n
i
t
,
′
b
y
t
e
s
′
,
v
a
l
u
e
/
1024
/
1024
,
v
a
l
u
e
)
,
′
999999999.
9
′
)
v
a
l
u
e
f
r
o
m
V
LATCH pga_aggregate_target.Query=select to_char(decode( unit,'bytes', value/1024/1024, value),'999999999.9') value from V
LATCHpgaaggregatetarget.Query=selecttochar(decode(unit,′bytes′,value/1024/1024,value),′999999999.9′)valuefromVPGASTAT where name in ‘aggregate PGA target parameter’
pga.Query=select to_char(decode( unit,‘bytes’, value/1024/1024, value),‘999999999.9’) value from V
P
G
A
S
T
A
T
w
h
e
r
e
n
a
m
e
i
n
′
t
o
t
a
l
P
G
A
i
n
u
s
e
′
p
h
i
o
d
a
t
a
f
i
l
e
r
e
a
d
s
.
Q
u
e
r
y
=
s
e
l
e
c
t
t
o
c
h
a
r
(
s
u
m
(
d
e
c
o
d
e
(
n
a
m
e
,
′
p
h
y
s
i
c
a
l
r
e
a
d
s
d
i
r
e
c
t
′
,
v
a
l
u
e
,
0
)
)
)
F
R
O
M
V
PGASTAT where name in 'total PGA inuse' phio_datafile_reads.Query=select to_char(sum(decode(name,'physical reads direct',value,0))) FROM V
PGASTATwherenamein′totalPGAinuse′phiodatafilereads.Query=selecttochar(sum(decode(name,′physicalreadsdirect′,value,0)))FROMVSYSSTAT where name =‘physical reads direct’
phio_datafile_writes.Query=select to_char(sum(decode(name,‘physical writes direct’,value,0))) FROM V
S
Y
S
S
T
A
T
w
h
e
r
e
n
a
m
e
=
′
p
h
y
s
i
c
a
l
w
r
i
t
e
s
d
i
r
e
c
t
′
p
h
i
o
r
e
d
o
w
r
i
t
e
s
.
Q
u
e
r
y
=
s
e
l
e
c
t
t
o
c
h
a
r
(
s
u
m
(
d
e
c
o
d
e
(
n
a
m
e
,
′
r
e
d
o
w
r
i
t
e
s
′
,
v
a
l
u
e
,
0
)
)
)
F
R
O
M
V
SYSSTAT where name ='physical writes direct' phio_redo_writes.Query=select to_char(sum(decode(name,'redo writes',value,0))) FROM V
SYSSTATwherename=′physicalwritesdirect′phioredowrites.Query=selecttochar(sum(decode(name,′redowrites′,value,0)))FROMVSYSSTAT where name =‘redo writes’
pinhitratio_body.Query=select round(pins/(pins+reloads)100,4) “pin_hit ratio” FROM v
l
i
b
r
a
r
y
c
a
c
h
e
w
h
e
r
e
n
a
m
e
s
p
a
c
e
=
′
B
O
D
Y
′
p
i
n
h
i
t
r
a
t
i
o
s
q
l
a
r
e
a
.
Q
u
e
r
y
=
s
e
l
e
c
t
r
o
u
n
d
(
p
i
n
s
/
(
p
i
n
s
+
r
e
l
o
a
d
s
)
∗
100
,
4
)
"
p
i
n
h
i
t
r
a
t
i
o
"
F
R
O
M
v
librarycache where namespace ='BODY' pinhitratio_sqlarea.Query=select round(pins/(pins+reloads)*100,4) "pin_hit ratio" FROM v
librarycachewherenamespace=′BODY′pinhitratiosqlarea.Query=selectround(pins/(pins+reloads)∗100,4)"pinhitratio"FROMvlibrarycache where namespace =‘SQL AREA’
pinhitratio_table-proc.Query=select round(pins/(pins+reloads)100,4) “pin_hit ratio” FROM v
l
i
b
r
a
r
y
c
a
c
h
e
w
h
e
r
e
n
a
m
e
s
p
a
c
e
=
′
T
A
B
L
E
/
P
R
O
C
E
D
U
R
E
′
p
i
n
h
i
t
r
a
t
i
o
t
r
i
g
g
e
r
.
Q
u
e
r
y
=
s
e
l
e
c
t
r
o
u
n
d
(
p
i
n
s
/
(
p
i
n
s
+
r
e
l
o
a
d
s
)
∗
100
,
4
)
"
p
i
n
h
i
t
r
a
t
i
o
"
F
R
O
M
v
librarycache where namespace ='TABLE/PROCEDURE' pinhitratio_trigger.Query=select round(pins/(pins+reloads)*100,4) "pin_hit ratio" FROM v
librarycachewherenamespace=′TABLE/PROCEDURE′pinhitratiotrigger.Query=selectround(pins/(pins+reloads)∗100,4)"pinhitratio"FROMvlibrarycache where namespace =‘TRIGGER’
pool_dict_cache.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘dictionary cache’,(bytes)/(10241024),0),0)),2)) pool_dict_cache FROM V
S
G
A
S
T
A
T
p
o
o
l
f
r
e
e
m
e
m
.
Q
u
e
r
y
=
S
E
L
E
C
T
T
O
C
H
A
R
(
R
O
U
N
D
(
S
U
M
(
d
e
c
o
d
e
(
p
o
o
l
,
′
s
h
a
r
e
d
p
o
o
l
′
,
d
e
c
o
d
e
(
n
a
m
e
,
′
f
r
e
e
m
e
m
o
r
y
′
,
(
b
y
t
e
s
)
/
(
1024
∗
1024
)
,
0
)
,
0
)
)
,
2
)
)
p
o
o
l
f
r
e
e
m
e
m
F
R
O
M
V
SGASTAT pool_free_mem.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'free memory',(bytes)/(1024*1024),0),0)),2)) pool_free_mem FROM V
SGASTATpoolfreemem.Query=SELECTTOCHAR(ROUND(SUM(decode(pool,′sharedpool′,decode(name,′freememory′,(bytes)/(1024∗1024),0),0)),2))poolfreememFROMVSGASTAT
pool_lib_cache.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘library cache’,(bytes)/(10241024),0),0)),2)) pool_lib_cache FROM V
S
G
A
S
T
A
T
p
o
o
l
m
i
s
c
.
Q
u
e
r
y
=
S
E
L
E
C
T
T
O
C
H
A
R
(
R
O
U
N
D
(
S
U
M
(
d
e
c
o
d
e
(
p
o
o
l
,
′
s
h
a
r
e
d
p
o
o
l
′
,
d
e
c
o
d
e
(
n
a
m
e
,
′
l
i
b
r
a
r
y
c
a
c
h
e
′
,
0
,
′
d
i
c
t
i
o
n
a
r
y
c
a
c
h
e
′
,
0
,
′
f
r
e
e
m
e
m
o
r
y
′
,
0
,
′
s
q
l
a
r
e
a
′
,
0
,
(
b
y
t
e
s
)
/
(
1024
∗
1024
)
)
,
0
)
)
,
2
)
)
p
o
o
l
m
i
s
c
F
R
O
M
V
SGASTAT pool_misc.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'library cache',0,'dictionary cache',0,'free memory',0,'sql area', 0,(bytes)/(1024*1024)),0)),2)) pool_misc FROM V
SGASTATpoolmisc.Query=SELECTTOCHAR(ROUND(SUM(decode(pool,′sharedpool′,decode(name,′librarycache′,0,′dictionarycache′,0,′freememory′,0,′sqlarea′,0,(bytes)/(1024∗1024)),0)),2))poolmiscFROMVSGASTAT
pool_sql_area.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘sql area’,(bytes)/(10241024),0),0)),2)) pool_sql_area FROM V
S
G
A
S
T
A
T
p
r
o
c
n
u
m
.
Q
u
e
r
y
=
s
e
l
e
c
t
c
o
u
n
t
(
∗
)
"
p
r
o
c
n
u
m
"
f
r
o
m
v
SGASTAT procnum.Query=select count(*) "procnum" from v
SGASTATprocnum.Query=selectcount(∗)"procnum"fromvprocess
session_active.Query=select count() from v
s
e
s
s
i
o
n
w
h
e
r
e
T
Y
P
E
!
=
′
B
A
C
K
G
R
O
U
N
D
′
a
n
d
s
t
a
t
u
s
=
′
A
C
T
I
V
E
′
s
e
s
s
i
o
n
i
n
a
c
t
i
v
e
.
Q
u
e
r
y
=
s
e
l
e
c
t
S
U
M
(
D
e
c
o
d
e
(
T
y
p
e
,
′
B
A
C
K
G
R
O
U
N
D
′
,
0
,
D
e
c
o
d
e
(
S
t
a
t
u
s
,
′
A
C
T
I
V
E
′
,
0
,
1
)
)
)
F
R
O
M
V
session where TYPE!='BACKGROUND' and status='ACTIVE' session_inactive.Query=select SUM(Decode(Type, 'BACKGROUND', 0, Decode(Status, 'ACTIVE', 0, 1))) FROM V
sessionwhereTYPE!=′BACKGROUND′andstatus=′ACTIVE′sessioninactive.Query=selectSUM(Decode(Type,′BACKGROUND′,0,Decode(Status,′ACTIVE′,0,1)))FROMVSESSION
session.Query=select count() from v
s
e
s
s
i
o
n
s
e
s
s
i
o
n
s
y
s
t
e
m
.
Q
u
e
r
y
=
s
e
l
e
c
t
S
U
M
(
D
e
c
o
d
e
(
T
y
p
e
,
′
B
A
C
K
G
R
O
U
N
D
′
,
1
,
0
)
)
s
y
s
t
e
m
s
e
s
s
i
o
n
s
F
R
O
M
V
session session_system.Query=select SUM(Decode(Type, 'BACKGROUND', 1, 0)) system_sessions FROM V
sessionsessionsystem.Query=selectSUM(Decode(Type,′BACKGROUND′,1,0))systemsessionsFROMVSESSION
sga_buffer_cache.Query=SELECT to_char(ROUND(SUM(decode(pool,NULL,decode(name,‘db_block_buffers’,(bytes)/(10241024),‘buffer_cache’,(bytes)/(10241024),0),0)),2)) sga_bufcache FROM V
S
G
A
S
T
A
T
s
g
a
f
i
x
e
d
.
Q
u
e
r
y
=
S
E
L
E
C
T
T
O
C
H
A
R
(
R
O
U
N
D
(
S
U
M
(
d
e
c
o
d
e
(
p
o
o
l
,
N
U
L
L
,
d
e
c
o
d
e
(
n
a
m
e
,
′
f
i
x
e
d
s
g
a
′
,
(
b
y
t
e
s
)
/
(
1024
∗
1024
)
,
0
)
,
0
)
)
,
2
)
)
s
g
a
f
i
x
e
d
F
R
O
M
V
SGASTAT sga_fixed.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,NULL,decode(name,'fixed_sga',(bytes)/(1024*1024),0),0)),2)) sga_fixed FROM V
SGASTATsgafixed.Query=SELECTTOCHAR(ROUND(SUM(decode(pool,NULL,decode(name,′fixedsga′,(bytes)/(1024∗1024),0),0)),2))sgafixedFROMVSGASTAT
sga_java_pool.Query=SELECT to_char(ROUND(SUM(decode(pool,‘java pool’,(bytes)/(10241024),0)),2)) sga_jpool FROM V
S
G
A
S
T
A
T
s
g
a
l
a
r
g
e
p
o
o
l
.
Q
u
e
r
y
=
S
E
L
E
C
T
t
o
c
h
a
r
(
R
O
U
N
D
(
S
U
M
(
d
e
c
o
d
e
(
p
o
o
l
,
′
l
a
r
g
e
p
o
o
l
′
,
(
b
y
t
e
s
)
/
(
1024
∗
1024
)
,
0
)
)
,
2
)
)
s
g
a
l
p
o
o
l
F
R
O
M
V
SGASTAT sga_large_pool.Query=SELECT to_char(ROUND(SUM(decode(pool,'large pool',(bytes)/(1024*1024),0)),2)) sga_lpool FROM V
SGASTATsgalargepool.Query=SELECTtochar(ROUND(SUM(decode(pool,′largepool′,(bytes)/(1024∗1024),0)),2))sgalpoolFROMVSGASTAT
sga_log_buffer.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,NULL,decode(name,‘log_buffer’,(bytes)/(1024*1024),0),0)),2)) sga_lbuffer FROM V
S
G
A
S
T
A
T
s
g
a
s
h
a
r
e
d
p
o
o
l
.
Q
u
e
r
y
=
S
E
L
E
C
T
T
O
C
H
A
R
(
R
O
U
N
D
(
S
U
M
(
d
e
c
o
d
e
(
p
o
o
l
,
′
s
h
a
r
e
d
p
o
o
l
′
,
d
e
c
o
d
e
(
n
a
m
e
,
′
l
i
b
r
a
r
y
c
a
c
h
e
′
,
0
,
′
d
i
c
t
i
o
n
a
r
y
c
a
c
h
e
′
,
0
,
′
f
r
e
e
m
e
m
o
r
y
′
,
0
,
′
s
q
l
a
r
e
a
′
,
0
,
(
b
y
t
e
s
)
/
(
1024
∗
1024
)
)
,
0
)
)
,
2
)
)
p
o
o
l
m
i
s
c
F
R
O
M
V
SGASTAT sga_shared_pool.Query=SELECT TO_CHAR(ROUND(SUM(decode(pool,'shared pool',decode(name,'library cache',0,'dictionary cache',0,'free memory',0,'sql area',0,(bytes)/(1024*1024)),0)),2)) pool_misc FROM V
SGASTATsgasharedpool.Query=SELECTTOCHAR(ROUND(SUM(decode(pool,′sharedpool′,decode(name,′librarycache′,0,′dictionarycache′,0,′freememory′,0,′sqlarea′,0,(bytes)/(1024∗1024)),0)),2))poolmiscFROMVSGASTAT
tbl_space.Query=SELECT * FROM (
select ‘- Tablespace ->’,t.tablespace_name ktablespace,
‘- Type->’,substr(t.contents, 1, 1) tipo,
‘- Used(MB)->’,trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
‘- ActualSize(MB)->’,trunc(d.tbs_size/1024/1024) ktbs_size,
‘- MaxSize(MB)->’,trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
‘- FreeSpace(MB)->’,trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
‘- Space->’,trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024) kspace,
‘- Perc->’,decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc
from
( select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize, tablespace_name tablespace
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files
union all
select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_temp_files
)
group by tablespace_name
) d,
( select SUM(bytes) free_space,
tablespace_name tablespace
from dba_free_space
group by tablespace_name
) s,
dba_tablespaces t
where t.tablespace_name = d.tablespace(+) and
t.tablespace_name = s.tablespace(+)
order by 8)
where kperc > 90 and kfree_space < 20480
and tipo <>‘T’ \
and tipo <>‘U’
tbl_space.NoDataFound=none
tbl_space.Period=60
userconn.Query=select count(username) from v s e s s i o n w h e r e u s e r n a m e i s n o t n u l l w a i t s c o n t r o f i l e i o . Q u e r y = S E L E C T t o c h a r ( s u m ( d e c o d e ( e v e n t , ′ c o n t r o l f i l e s e q u e n t i a l r e a d ′ , t o t a l w a i t s , ′ c o n t r o l f i l e s i n g l e w r i t e ′ , t o t a l w a i t s , ′ c o n t r o l f i l e p a r a l l e l w r i t e ′ , t o t a l w a i t s , 0 ) ) ) C o n t r o l F i l e I O F R O M V session where username is not null waits_controfileio.Query=SELECT to_char(sum(decode(event,'control file sequential read', total_waits, 'control file single write', total_waits, 'control file parallel write',total_waits,0))) ControlFileIO FROM V sessionwhereusernameisnotnullwaitscontrofileio.Query=SELECTtochar(sum(decode(event,′controlfilesequentialread′,totalwaits,′controlfilesinglewrite′,totalwaits,′controlfileparallelwrite′,totalwaits,0)))ControlFileIOFROMVsystem_event WHERE 1=1 AND event not in ( ‘SQLNet message from client’, 'SQLNet more data from client’,‘pmon timer’, ‘rdbms ipc message’, ‘rdbms ipc reply’, ‘smon timer’)
waits_directpath_read.Query=SELECT to_char(sum(decode(event,‘direct path read’,total_waits,0))) DirectPathRead FROM V$system_event WHERE 1=1 AND event not in ( ‘SQLNet message from ', 'SQLNet more data from client’,‘pmon timer’, ‘rdbms ipc message’, ‘rdbms ipc reply’, ‘smon timer’)
waits_file_io.Query=SELECT to_char(sum(decode(event,‘file identify’,total_waits, ‘file open’,total_waits,0))) FileIO FROM V$system_event WHERE 1=1 AND event not in ( ‘SQLNet message from client’, 'SQLNet more data from client’, ‘pmon timer’, ‘rdbms ipc message’, ‘rdbms ipc reply’, ‘smon timer’)
waits_latch.Query=SELECT to_char(sum(decode(event,‘control file sequential read’, total_waits,
‘control file single write’, total_waits, ‘control file parallel write’,total_waits,0))) ControlFileIO
FROM V$system_event WHERE 1=1 AND event not in (
‘SQLNet message from client’,
'SQLNet more data from client’,
‘pmon timer’, ‘rdbms ipc message’,
‘rdbms ipc reply’, ‘smon timer’)
waits_logwrite.Query=SELECT to_char(sum(decode(event,‘log file single write’,total_waits, ‘log file parallel write’,total_waits,0))) LogWrite
FROM V$system_event WHERE 1=1 AND event not in (
‘SQLNet message from client’,
'SQLNet more data from client’,
‘pmon timer’, ‘rdbms ipc message’,
‘rdbms ipc reply’, ‘smon timer’)
waits_multiblock_read.Query=SELECT to_char(sum(decode(event,‘db file scattered read’,total_waits,0))) MultiBlockRead
FROM V$system_event WHERE 1=1 AND event not in (
‘SQLNet message from client’,
'SQLNet more data from client’,
‘pmon timer’, ‘rdbms ipc message’,
‘rdbms ipc reply’, ‘smon timer’)
waits_other.Query=SELECT to_char(sum(decode(event,‘control file sequential read’,0,‘control file single write’,0,‘control file parallel write’,0,‘db file sequential read’,0,‘db file scattered read’,0,‘direct path read’,0,‘file identify’,0,‘file open’,0,‘SQLNet message to client’,0,'SQLNet message to dblink’,0, ‘SQLNet more data to client’,0,'SQLNet more data to dblink’,0, ‘SQLNet break/reset to client’,0,'SQLNet break/reset to dblink’,0, ‘log file single write’,0,‘log file parallel write’,0,total_waits))) Other FROM V$system_event WHERE 1=1 AND event not in ( ‘SQLNet message from client’, 'SQLNet more data from client’, ‘pmon timer’, ‘rdbms ipc message’, ‘rdbms ipc reply’, ‘smon timer’)
waits_singleblock_read.Query=SELECT to_char(sum(decode(event,‘db file sequential read’,total_waits,0))) SingleBlockRead
FROM V$system_event WHERE 1=1 AND event not in (
‘SQLNet message from client’,
'SQLNet more data from client’,
‘pmon timer’, ‘rdbms ipc message’,
‘rdbms ipc reply’, ‘smon timer’)
waits_sqlnet.Query=SELECT to_char(sum(decode(event,‘SQLNet message to client’,total_waits,'SQLNet message to dblink’,total_waits,‘SQLNet more data to client’,total_waits,'SQLNet more data to dblink’,total_waits,‘SQLNet break/reset to client’,total_waits,'SQLNet break/reset to dblink’,total_waits,0))) SQLNET FROM V$system_event WHERE 1=1
AND event not in ( ‘SQLNet message from client’,'SQLNet more data from client’,‘pmon timer’,‘rdbms ipc message’,‘rdbms ipc reply’, ‘smon timer’)
dg_error.Query=SELECT ERROR_CODE, SEVERITY, MESSAGE, TO_CHAR(TIMESTAMP, ‘DD-MON-RR HH24:MI:SS’) TIMESTAMP FROM V$DATAGUARD_STATUS WHERE CALLOUT=‘YES’ AND TIMESTAMP > SYSDATE-1
dg_error.NoDataFound=none
dg_sequence_number.Query=SELECT MAX (sequence#) FROM v$log_history
#dg_sequence_number_stby.Query=SELECT MAX (sequence#) last_log_applied FROM vKaTeX parse error: Expected 'EOF', got '#' at position 63: …ct max(sequence#̲) from varchived_log
扫一扫
1052
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
