----------------------------------------------------------------------
----- indexes_activity_1db -----
-- Indexes IO-activity (exclude system catalog) in one db
-- Активность (detached) индексов  - все операции вв/выв только для текущей БД 
--   (кроме присоединенных индексов на системных таблицах)
--
--  V.Shulzhenko  DBA_Tools  by Mark Scranton (CDI, 2008-05)
----------------------------------------------------------------------
set isolation dirty read;
select 
-- first 40  
         f.dbsname[ 1 , 18 ]       database_18
        ,t.tabname[ 1 , 18 ]       table_name_18
        ,f.tabname[ 1 , 18 ]       index_name_18
--        ,k.keylen              keylen
        ,substr(ti_nptotal, 1 , 9 )  size_pg
        ,substr (sum(isreads+iswrites+isrewrites+isdeletes
            +bufreads+bufwrites
            +pagreads+pagwrites
            +seqscans
            ), 1 , 10 )            total_io
from sysmaster:sysptprof f, sysmaster:sysptnkey k, sysmaster:systabinfo i
     ,sysindexes x, systables t
where k.partnum = f.partnum
      and k.partnum = f.partnum
      and f.partnum = i.ti_partnum
 and x.idxname = f.tabname and x.tabid = t.tabid      
      and f.tabname not matches 'sys*'
      and f.dbsname=
        (select odb_dbname from sysmaster:sysopendb o
         where odb_sessionid=dbinfo('sessionid') and odb_iscurrent='Y')
group by  1 , 2 , 3 , 4 
-- having sum(isreads+iswrites+isrewrites+isdeletes
--            +bufreads+bufwrites
--            +pagreads+pagwrites
--            +seqscans
--           ) > 0
order by  5  desc,  1 , 2 , 3  
;

----------------------------------------------------------------------
----- indexes_not_activity_1db -----
-- Dead Indexes - without IO-activity (exclude system catalog) in one db
-- "Мертвые" detached индексы , т.е. по ним нет операций вв/выв
--   (кроме присоединенных индексов на системных таблицах)
--    только для указанной БД
--
--  V.Shulzhenko  DBA_Tools  by Mark Scranton (CDI, 2008-05)
----------------------------------------------------------------------
set isolation dirty read;
select 
-- first 40  
         f.dbsname[ 1 , 18 ]       database_18
        ,t.tabname[ 1 , 18 ]       table_name_18
        ,f.tabname[ 1 , 18 ]       index_name_18
        ,k.keylen              key_len
        ,ti_nptotal            size_pg
from sysmaster:sysptprof f, sysmaster:sysptnkey k, sysmaster:systabinfo i
    ,sysindexes x, systables t
where k.partnum = f.partnum
      and k.partnum = f.partnum
      and f.partnum = i.ti_partnum
      and f.tabname not matches 'sys*'    
    and x.idxname = f.tabname and x.tabid = t.tabid      
      and (isreads+iswrites+isrewrites+isdeletes
           +bufreads+bufwrites
           +pagreads+pagwrites+seqscans
          ) =  0 
      and f.dbsname=
        (select odb_dbname from sysmaster:sysopendb o
         where odb_sessionid=dbinfo('sessionid') and odb_iscurrent='Y')
group by  1 , 2 , 3 , 4 , 5 
order by  1 , 2 , 3  
;

----------------------------------------------------------------------
----- index_activity_ -----
-- Index IO-activity (by index name)
-- Активность (detached) индекса  - все операции вв/выв и блокировки
--   поиск индекса по имени (шаблону имени)
--
--  V.Shulzhenko  DBA_Tools  by Mark Scranton (CDI, 2008-05)
----------------------------------------------------------------------
set isolation dirty read;
select 
         t.tabname             table_name
        ,f.tabname             index_name
        ,k.keylen              keylen
        ,sum(ti_nptotal)       size_pg
        ,'---- io ----'        ___________
        ,sum(isreads)          reads
        ,sum(iswrites)         writes
        ,sum(isrewrites)       rewrites
        ,sum(isdeletes)        deletes
        ,sum(bufreads)         bufreads
        ,sum(bufwrites)        bufwrites
        ,sum(pagreads)         pagreads
        ,sum(pagwrites)        pagwrites
        ,sum(seqscans)         seqscans
        ,'---- locks ----'     ___________
        ,sum(lockreqs)         lockreqs
        ,sum(lockwts)          lockwts
        ,sum(deadlks)          deadlks
        ,sum(lktouts)          lktouts
from sysmaster:sysptprof f, sysmaster:sysptnkey k, sysmaster:systabinfo i
     ,sysindexes x, systables t
where k.partnum = f.partnum
      and k.partnum = f.partnum
      and f.partnum = i.ti_partnum
 and x.idxname = f.tabname and x.tabid = t.tabid      
      and f.dbsname=
        (select odb_dbname from sysmaster:sysopendb o
         where odb_sessionid=dbinfo('sessionid') and odb_iscurrent='Y')
 and x.idxname matches 
--'Укажи имя индекса или шаблон'
'*101*'
group by  1 , 2 , 3 
order by  1 , 2 , 4  desc 
;

   select sqx_sessionid, 
          sqx_executions,
          round(sqx_bufreads/sqx_executions),
          round(sqx_pagereads/sqx_executions), 
          sqx_estcost, 
          sqx_estrows, 
          sqx_index, 
          sqx_sqlstatement  
     from  syssqexplain 
     where sqx_executions >  3000 
          and (sqx_bufreads / sqx_executions >  50 
          or sqx_executions >  100 )
          and (sqx_bufreads / sqx_executions >  1000 
          or sqx_executions >  1 )
          and sqx_bufreads / sqx_executions >  50000