oracle 10g current SQL Monitor Query

from Web



SELECT /* SQL Monitor Query for >= 10g */
'(' || s.SID || ',' || s.serial# || ')' "sid/serial",
s.program "pgrm", s.terminal "term", s.username "db user",
DECODE (ROUND (s.last_call_et / 60),'0', '< 1',ROUND (s.last_call_et / 60)) "rtime (mins)",
DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers",
DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves",
si.block_changes "sess bchgs", si.physical_reads "sess preads",
pss_pr."px preads" "child px preads",
si.consistent_gets "sess cgets", pss_cg."px cgets" "child px cgets",
ss_cpu."sess cpu" "sess cpu", pss_cpu."px cpu" "child px cpu",
sa.optimizer_cost "curr sql cost", su."blocks" "temp blocks",
t."used_ublk" "undo blocks", s.event "wait",
s.seconds_in_wait "wait secs", s.state "wait state",
s.sql_id "current sql id", sa.sql_fulltext "sql text",
DBMS_XPLAN.display_cursor (s.sql_id, s.sql_child_number) "sql xplan"
FROM v$session s,
v$sess_io si,
v$sql sa,
(SELECT qcsid, COUNT (DISTINCT server_set) "px oper cnt", COUNT (*) "px count"
FROM v$px_session
WHERE NOT server_set IS NULL
GROUP BY qcsid, DEGREE) ps,
(SELECT qcsid, SUM (VALUE) "px preads"
FROM v$px_sesstat
WHERE statistic# = 54 AND SID != qcsid
GROUP BY qcsid) pss_pr,
(SELECT qcsid, SUM (VALUE) "px cgets"
FROM v$px_sesstat pss
WHERE statistic# = 50 AND SID != qcsid
GROUP BY qcsid) pss_cg,
(SELECT qcsid, SUM (VALUE) "px cpu"
FROM v$px_sesstat pss
WHERE statistic# = 12 AND SID != qcsid
GROUP BY qcsid) pss_cpu,
(SELECT ss.SID, SUM (ss.VALUE) "sess cpu"
FROM v$sesstat ss
WHERE statistic# = 12
GROUP BY ss.SID) ss_cpu,
(SELECT t.ses_addr, SUM (t.used_ublk) "used_ublk"
FROM v$transaction t
GROUP BY t.ses_addr) t,
(SELECT su.session_addr, SUM (su.blocks) "blocks"
FROM v$sort_usage su
GROUP BY su.session_addr) su
WHERE s.sql_address = sa.address
AND s.sql_hash_value = sa.hash_value
AND s.saddr = su.session_addr(+)
AND s.SID = ps.qcsid(+)
AND s.SID = si.SID(+)
AND s.saddr = t.ses_addr(+)
AND s.SID = pss_pr.qcsid(+)
AND s.SID = pss_cg.qcsid(+)
AND s.SID = pss_cpu.qcsid(+)
AND s.SID = ss_cpu.SID(+)
AND s.TYPE != 'BACKGROUND'
AND s.status = 'ACTIVE'
AND program NOT LIKE ('%(C%') --Eliminate Streams Capture
AND program NOT LIKE ('%(A%') --Eliminate Streams Apply
AND program NOT LIKE ('%(P%') --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;

oracle 10g find large full scans SQL and objects

SELECT sql_id,sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
FROM
(SELECT c.sql_id sql_id, sql_text,
count(*) total_large_scans,
executions
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND c.hash_value = a.hash_value
AND b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
group by c.sql_id, sql_text, executions)
order by 4 desc
/





col TABLE_OWNER format a12

select sql_id,
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select c.sql_id sql_id,
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and a.hash_value = c.hash_value
and b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
-- and a.object_owner not in ('SYS','SYSTEM')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS')
group by c.sql_id,a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type
order by 4 desc, 1, 2 );