SQL Scripts‎ > ‎S-Z‎ > ‎

wait_analyze

set echo off

---------------------------------------------------
-- @name: wait_analyze
-- @author: dion cho
-- @description: analyze wait info. it's a temporary version!
---------------------------------------------------

define __TRACE_FILE = "&1"
define __ITEM = "&2"
define __WAIT_NAME = "&3"
define __COLUMN_LIST = "&4"

col nam format a30
col f format a10
col block format a10
col ela format a10
col class format a10
col obj format a10
col files format a10
col blocks  format a10
col p1  format a10
col p2  format a10
col p3  format a10

with w as (
    select
        substr(regexp_substr(column_value, 'nam=''([[:print:]])+'''), 5) as nam,
      substr(regexp_substr(column_value, 'ela= [[:digit:]]+'), 6) as ela,
      substr(regexp_substr(column_value, 'file#=[[:digit:]]+'), 7) as f,
      substr(regexp_substr(column_value, 'block#=[[:digit:]]+'), 8) as block,
      substr(regexp_substr(column_value, 'class#=[[:digit:]]+'), 8) as class,
      substr(regexp_substr(column_value, 'obj#=[[:digit:]]+'), 6) as obj,
      substr(regexp_substr(column_value, 'files=[[:digit:]]+'), 7) as files,
      substr(regexp_substr(column_value, 'blocks=[[:digit:]]+'), 8) as blocks,
      substr(regexp_substr(column_value, 'p1=[[:digit:]]+'), 4) as p1,
      substr(regexp_substr(column_value, 'p2=[[:digit:]]+'), 4) as p2,
      substr(regexp_substr(column_value, 'p3=[[:digit:]]+'), 4) as p3
    from table(get_trace_file2('&__TRACE_FILE'))
    where
        column_value like '%&__ITEM%'
)
select
    &__COLUMN_LIST, count(*)
from w
where nam like '%&__WAIT_NAME%'
group by &__COLUMN_LIST
;

set echo on

Comments