Gauge Ignores

From zzat
Jump to: navigation, search

Gauge ignores enable the capability to completely ignore certain values while a gauge is being evaluated. A value to be ignored can either be matched against the gauge column value, or the informational column value. Similar to gauge columns, the data type specified must match the data type of the column in the gauge query.

Rendering

Exactly in the same way as gauge columns are injected into the gauge base query, so are ignore values. Let's take the same example we've looked at one the gauge columns page and add an example ignore, to ignore the "library cache load lock" latch:

begin
zz$manage.gauge_ignore_add
(
  gauge                 => 'SES_LATCH',
  column_name           => 'EVENT',
  ignore_value_string   => 'library cache load lock',
  comments              => 'Ignore known issue #123 01/09/2017'
);
end;
/

The gauge base query, as in the previous example, starts off with:

select z.begin_time, z.snap#, s.username, w.sid, w.event, round(count(*) over (partition by w.event) / count(*) over (partition by null),2)*100 as pct_sess,
       case when substr(w.event, 1,6) = 'latch:' then 1 else 0 end as is_latch
  from sys.v_$session s, zz$ses_wait w, zz$snap z
 where s.sid = w.sid
   and w.snap# = z.snap#
   and s.username is not null

zztat will then wrap the query first to add the ignored values:

select * from (
select z.begin_time, z.snap#, s.username, w.sid, w.event, round(count(*) over (partition by w.event) / count(*) over (partition by null),2)*100 as pct_sess,
       case when substr(w.event, 1,6) = 'latch:' then 1 else 0 end as is_latch
  from sys.v_$session s, zz$ses_wait w, zz$snap z
 where s.sid = w.sid
   and w.snap# = z.snap#
   and s.username is not null
) where EVENT != :ZZ$BIEVENT1

And finally, it will render the query with the gauge columns:

select * from (
select * from (
select z.begin_time, z.snap#, s.username, w.sid, w.event, round(count(*) over (partition by w.event) / count(*) over (partition by null),2)*100 as pct_sess,
       case when substr(w.event, 1,6) = 'latch:' then 1 else 0 end as is_latch
  from sys.v_$session s, zz$ses_wait w, zz$snap z
 where s.sid = w.sid
   and w.snap# = z.snap#
   and s.username is not null
) where EVENT != :ZZ$BIEVENT1
) where /* gauge col 1 */PCT_SESS > :ZZ$BCPCT_SESS1 and /* gauge col 2 */IS_LATCH = :ZZ$BCIS_LATCH2

This approach allows zztat to use the most powerful tool to filter out the values - Oracle's native SQL engine.