Tanel waitprof for session analyze

 


e – sample event names (this is always done automatically)
1 – sample P1 values
2 – sample P2 values
3 – sample P3 values
s – sample SEQ# values


And lets run waitprof now, on SID 142, gather only events waited on and take 100000 samples:


@waitprof noprint 142 e 100000
@waitprof noprint 142 e1 100000
@waitprof print 142 e3 100000
@waitprof print 142 e123 100000




-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

--------------------------------------------------------------------------------
--
-- File name:   waitprof.sql ( Session Wait Profiler )
-- Purpose:     Sample V$SESSION_WAIT at high frequency and show resulting 
--              session wait event and parameter profile by session
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--
-- Usage:       @waitprof <print|noprint> <sid> <e[123s]> <#samples>
--
--                  <print|noprint>
--                              - whether to print P2,P3,SEQ# values or not
--
--                  <sid>       - session ID of session to sample
--
--                  <[e123s]>   - sample grouping
--                      e - group by event name
--                      1 - group by P1 of v$session_wait event
--                      2 - group by P2
--                      3 - group by P3
--                      s - group by SEQ#
--
--                  <#samples>  - how many samples to take (a modern CPU can take
--                                tens of thousands to low hundreds of k samples
--                                per second)
--
--  Examples:
--              @waitprof noprint 350 e 1000000   -- take million samples, group by event only
--              @waitprof print 350 e123 500000 -- take 500k samples, group by event,p1,p2,p3
--              @waitprof print 350 e3 1000000  -- take million samples, group by event,p3
--              @waitprof print 350 es 1000000  -- take million samples, group by event,seq#
--
--  Other:
--              The sampling relies on NESTED LOOP join method and having
--              V$SESSION_WAIT as the inner (probed) table. Note that on 9i
--              you may need to run this script as SYS as it looks like otherwise
--              the global USE_NL hint is not propagated down to X$ base tables
--
--              If sampling always reports a single distinct event even though 
--              many different events (or parameter values) are expected then 
--              the execution plan used is not right.
--
--------------------------------------------------------------------------------

DEF _swp_print=&1
DEF _swp_sid=&2
DEF _swp_p123=&3
DEF _swp_samples=&4

col sw_event    head EVENT for a35 truncate
col sw_p1transl head P1TRANSL for a42
col sw_sid      head SID for 999999
col swp_p1 head P1 for a26 word_wrap
col swp_p2 head P2 for a16 word_wrap &_swp_print
col swp_p3 head P3 for a16 word_wrap &_swp_print
col swp_seq# head SEQ# &_swp_print
col pct_total_samples head "% Total|Time" format 999.99
col waitprof_total_ms head "Total Event|Time ms" format 9999999.999
col dist_events head Distinct|Events
col average_samples head Average|Samples
col waitprof_avg_ms head "Avg time|ms/Event" format 99999.999

prompt
prompt -- WaitProf 1.04 by Tanel Poder ( http://www.tanelpoder.com )

WITH 
    t1 AS (SELECT hsecs FROM v$timer),
    samples AS (
    SELECT /*+ ORDERED NO_MERGE USE_NL(sw.gv$session_wait.s) */
        s.sid sw_sid,
        CASE WHEN sw.state = 'WAITING' THEN 'WAITING' ELSE 'WORKING' END AS state,
        CASE WHEN sw.state = 'WAITING' THEN event ELSE 'On CPU / runqueue' END AS sw_event,
        CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%1%' THEN sw.p1text || '= ' || CASE WHEN (LOWER(sw.p1text) LIKE '%addr%' OR sw.p1 >= 536870912) THEN RAWTOHEX(sw.p1raw) ELSE TO_CHAR(sw.p1) END ELSE NULL END swp_p1,
        CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%2%' THEN sw.p2text || '= ' || CASE WHEN (LOWER(sw.p2text) LIKE '%addr%' OR sw.p2 >= 536870912) THEN RAWTOHEX(sw.p2raw) ELSE TO_CHAR(sw.p2) END ELSE NULL END swp_p2,
        CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%3%' THEN sw.p3text || '= ' || CASE WHEN (LOWER(sw.p3text) LIKE '%addr%' OR sw.p3 >= 536870912) THEN RAWTOHEX(sw.p3raw) ELSE TO_CHAR(sw.p3) END ELSE NULL END swp_p3,
        CASE WHEN LOWER('&_swp_p123') LIKE '%s%' THEN sw.seq# ELSE NULL END seq#,
        COUNT(*) total_samples,
        COUNT(DISTINCT seq#) dist_events,
        TRUNC(COUNT(*)/COUNT(DISTINCT seq#)) average_samples
    FROM
        ( SELECT /*+ NO_MERGE */ TO_NUMBER(&_swp_sid) sid FROM 
        (SELECT rownum r FROM dual CONNECT BY ROWNUM <= 1000) a,
        (SELECT rownum r FROM dual CONNECT BY ROWNUM <= 1000) b,
        (SELECT rownum r FROM dual CONNECT BY ROWNUM <= 1000) c
        WHERE ROWNUM <= &_swp_samples
        ) s,
        v$session_wait sw
    WHERE
        s.sid = sw.sid
    GROUP BY
        s.sid,
        CASE WHEN sw.state = 'WAITING' THEN 'WAITING' ELSE 'WORKING' END,
        CASE WHEN sw.state = 'WAITING' THEN event ELSE 'On CPU / runqueue' END,
        CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%1%' THEN sw.p1text || '= ' || CASE WHEN (LOWER(sw.p1text) LIKE '%addr%' OR sw.p1 >= 536870912) THEN RAWTOHEX(sw.p1raw) ELSE TO_CHAR(sw.p1) END ELSE NULL END,
        CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%2%' THEN sw.p2text || '= ' || CASE WHEN (LOWER(sw.p2text) LIKE '%addr%' OR sw.p2 >= 536870912) THEN RAWTOHEX(sw.p2raw) ELSE TO_CHAR(sw.p2) END ELSE NULL END,
        CASE WHEN sw.state = 'WAITING' AND '&_swp_p123' LIKE '%3%' THEN sw.p3text || '= ' || CASE WHEN (LOWER(sw.p3text) LIKE '%addr%' OR sw.p3 >= 536870912) THEN RAWTOHEX(sw.p3raw) ELSE TO_CHAR(sw.p3) END ELSE NULL END,
        CASE WHEN LOWER('&_swp_p123') LIKE '%s%' THEN sw.seq# ELSE NULL END
    ORDER BY
        CASE WHEN LOWER('&_swp_p123') LIKE '%s%' THEN -seq# ELSE total_samples END DESC
),
    t2 AS (SELECT hsecs FROM v$timer)
SELECT /*+ ORDERED */
    s.sw_sid,
    s.state,
    s.sw_event,
    s.swp_p1,
    s.swp_p2,
    s.swp_p3,
    s.seq# swp_seq#,
    s.total_samples / &_swp_samples * 100 pct_total_samples,
    (t2.hsecs - t1.hsecs) * 10 * s.total_samples / &_swp_samples waitprof_total_ms,
    s.dist_events,
--  s.average_samples,
    (t2.hsecs - t1.hsecs) * 10 * s.total_samples / dist_events / &_swp_samples waitprof_avg_ms
FROM
    t1,
    samples s,
    t2
/

--UNDEF _swp_sid=&1
--UNDEF _swp_p123=&2
--UNDEF _swp_samples=&3

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