Kyle hailey Top sql and Top session and Top Procedure

 


Kyle hailey Top sql and   Top session and Top Procedure 
https://github.com/khailey-zz/ashmasters/blob/master/ash_top_procedure.sql

 
-- Top session 


*/


col name for a12
col program for a25
col CPU for 9999
col IO for 9999
col TOTAL for 99999
col WAIT for 9999
col user_id for 99999
col sid for 9999

set linesize 120

select
        decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
                                                        "STATUS",
        topsession.sid             "SID",
        u.username  "NAME",
        topsession.program                  "PROGRAM",
        max(topsession.CPU)              "CPU",
        max(topsession.WAIT)       "WAITING",
        max(topsession.IO)                  "IO",
        max(topsession.TOTAL)           "TOTAL"
        from (
select * from (
select
     ash.session_id sid,
     ash.session_serial# serial#,
     ash.user_id user_id,
     ash.program,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING',
        decode(wait_class,'User I/O',1, 0 ), 0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING',
        decode(wait_class,'User I/O',1, 0 ), 0))    "IO" ,
     sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
   )    topsession,
        v$session s,
        all_users u
   where
        u.user_id =topsession.user_id and
        /* outer join to v$session because the session might be disconnected */
        topsession.sid         = s.sid         (+) and
        topsession.serial# = s.serial#   (+)
   group by  topsession.sid, topsession.serial#,
             topsession.user_id, topsession.program, s.username,
             s.sid,s.paddr,u.username
   order by max(topsession.TOTAL) desc
/




-- Top Procedure 





/*

   only 10.2.0.3 and above

COUNT(*) SQL_ID        calling_code
--------- ------------- --------------------------------------------------------------------
        2 1xxksrhwtz3zf ORDERENTRY.NEWORDER  => DBMS_RANDOM.VALUE
        2 07p193phmhx3z ORDERENTRY.BROWSEPRODUCTS  => DBMS_APPLICATION_INFO.SET_ACTION
        2 1xxksrhwtz3zf ORDERENTRY.NEWORDER  => DBMS_LOCK.SLEEP
        3 1xxksrhwtz3zf ORDERENTRY.NEWORDER  => DBMS_APPLICATION_INFO.SET_ACTION
       13 1xxksrhwtz3zf ORDERENTRY.NEWORDER
       16 0bzhqhhj9mpaa ORDERENTRY.NEWCUSTOMER
       45 41zu158rqf4kf ORDERENTRY.BROWSEANDUPDATEORDERS
       70 0yas01u2p9ch4 ORDERENTRY.NEWORDER
       76 dw2zgaapax1sg ORDERENTRY.NEWORDER
       82 05s4vdwsf5802 ORDERENTRY.BROWSEANDUPDATEORDERS
      111 75621g9y3xmvd ORDERENTRY.NEWORDER
      120 75621g9y3xmvd ORDERENTRY.BROWSEPRODUCTS
      131 75621g9y3xmvd ORDERENTRY.BROWSEANDUPDATEORDERS
      163 0uuqgjq7k12nf ORDERENTRY.NEWORDER

*/


set linesize 120
col entry_package for a25
col entry_procedure for a25
col cur_package for a25
col cur_procedure for a25
col calling_code for a70
select 
    count(*), 
    sql_id,
    procs1.object_name || decode(procs1.procedure_name,'','','.')||
    procs1.procedure_name ||' '||
    decode(procs2.object_name,procs1.object_name,'',
decode(procs2.object_name,'','',' => '||procs2.object_name)) 
    ||
    decode(procs2.procedure_name,procs1.procedure_name,'',
        decode(procs2.procedure_name,'','',null,'','.')||procs2.procedure_name)
    "calling_code"
from v$active_session_history  ash,
     all_procedures procs1,
     all_procedures procs2
 where
       ash.PLSQL_ENTRY_OBJECT_ID  = procs1.object_id (+)
   and ash.PLSQL_ENTRY_SUBPROGRAM_ID = procs1.SUBPROGRAM_ID (+)
   and ash.PLSQL_OBJECT_ID   = procs2.object_id (+)
   and ash.PLSQL_SUBPROGRAM_ID  = procs2.SUBPROGRAM_ID (+)
   and ash.sample_time > sysdate - &minutes/(60*24)
group by procs1.object_name, procs1.procedure_name, 
         procs2.object_name, procs2.procedure_name,sql_id
order by count(*)
/ 









Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more