Oracle Database

Monday, June 10, 2013

--- TOP 5 QUERY BASED ON LAST 60 MINUTES

select *

from (select s.BUFFER_GETS,

s.DISK_READS,

nvl(s.sql_id, 'null') as id,

Nvl(S.sql_text, 'NULL') AS SQL_ID,

round(COUNT(*) / 60, 2) DB_TIME,

ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) AS PCT_LOAD

FROM V$active_Session_History A, v$SQL S

WHERE A.SQL_ID = S.SQL_ID

AND SAMPLE_TIME > SYSDATE - 60 / 24 / 60 --- TIME e.g for 2 hours 120/24/60

AND SESSION_TYPE <> 'BACKGROUND'

GROUP BY s.sql_id, s.SQL_text, s.BUFFER_GETS, s.DISK_READS

ORDER BY COUNT(*) DESC)

where rownum <= 10; --- HOW MUCH QUERIES

Here is a query to see all the undocumented parameters

SELECT

a.ksppinm "Parameter",

decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",

c.ksppstvl "Instance",

decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",

decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",

decode(p.isdefault,'FALSE','F','TRUE','T') "D",

a.ksppdesc "Description"

FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p

WHERE a.indx = b.indx AND a.indx = c.indx

AND p.name(+) = a.ksppinm

AND UPPER(a.ksppinm) LIKE UPPER('\_%') escape '\'

ORDER BY a.ksppinm;