SCRIPTS TOPS PARA AJUSTE DE PERFORMANCE

----------------------------------
-- SCRIPT  top.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
set echo off
rem
rem ARQUIVO
rem    top.sql
rem
rem FINALIDADE
rem    Mostra as "N" maiores sessoes consumidoras de recursos
rem
rem PARAMETROS
rem    1 = quantos elementos apresentar por estatistica (os "top N")
rem    2 = parte do nome de estatistica
rem
rem EXEMPLOS
rem    @top 5 ga_mem ==> Exibe os 5 maiores consumidores de recursos '%ga_mem%'
rem    @top 5 %      ==> Exibe os 5 maiores consumidores de todos os recursos
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off
set linesize 120 pagesize 10000 verify off

define topN=&1
define topStat=&2
define pctMinimo=1

column sid             format 99999
column top_stat_name   format a30              heading 'STATISTIC_NAME' trunc
column sess_status     format a4               heading 'STATUS' truncate
column username_osuser format a22              heading 'USERNAME/OSUSER'
column minutos         format 9999             heading 'MIN'
column statistic_value format 999g999g999g990  heading 'STATISTIC_VAL'
column conn_time                               heading 'TEMPO DE|CONEXAO'

break on top_stat_name skip 1
compute sum of pct on statistic_name

select statistic_name as top_stat_name
     , rank
     , pct
     , sid
     , user_name
        || decode( osuser, null, null, '/')
        || substr( osuser, 1+instr( osuser, '\') )
    as username_osuser
     , lpad( decode( trunc(sysdate - logon_time)
                   , 0, null
                   , trunc(sysdate - logon_time) || 'd, ' )
          || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
                              , 'SSSSS' )
                     , 'hh24"h "mi"m"' )
           , 12 )
    as conn_time
     , round( last_call_et/60, 1) as minutos
     , decode( status, 'ACTIVE', 'ATIVO', 'INACTIVE', 'INATIVO', status)
    as sess_status
     , statistic_value
  from  ( select sn.name as statistic_name
               , row_number() over ( partition by sn.name
                                         order by sn.name, sv.value desc)
              as rank
               , 100 * ratio_to_report(sv.value) over (partition by sn.name) as pct
               , sv.value as statistic_value
               , decode( ss.username
                       , NULL, substr( ss.program
                                     , instr(upper(ss.program), '(') )
                       , ss.username )
              as user_name
               , ss.*
            from v$sesstat sv
               , v$statname sn
               , v$session ss
           where sn.statistic# = sv.statistic#
             and ss.sid = sv.sid
             and ss.username != 'SYS'
             and ss.type != 'BACKGROUND'
             and ss.status != 'KILLED'
             and sv.value > 0
             and sn.statistic#
              in (   9 ---session logical reads
                 ,  10 ---session stored procedure space
                 ,  14 ---cluster wait time
                 ,  15 ---concurrency wait time
                 ,  16 ---application wait time
                 ,  17 ---user I/O wait time
                 ,  20 ---session uga memory
                 ,  21 ---session uga memory max
                 ,  25 ---session pga memory
                 ,  26 ---session pga memory max
                 ,  27 ---enqueue timeouts
---              ,  28 ---enqueue waits
---              ,  29 ---enqueue deadlocks
---              ,  30 ---enqueue requests
                 ,  31 ---enqueue conversions
---              ,  32 ---enqueue releases
                 ,  33 ---global enqueue gets sync
                 ,  34 ---global enqueue gets async
                 ,  35 ---global enqueue get time
                 ,  36 ---global enqueue releases
---              ,  37 ---physical read total IO requests
---              ,  38 ---physical read total multi block requests
---              ,  39 ---physical read total bytes
---              ,  40 ---physical write total IO requests
---              ,  41 ---physical write total multi block requests
---              ,  42 ---physical write total bytes
---              ,  43 ---IPC CPU used by this session
---              ,  44 ---gcs messages sent
                 ,  45 ---ges messages sent
--               ,  46 ---global enqueue CPU used by this session
                 ,  47 ---db block gets
                 ,  48 ---db block gets from cache
                 ,  49 ---db block gets direct
                 ,  50 ---consistent gets
                 ,  51 ---consistent gets from cache
                 ,  52 ---consistent gets - examination
                 ,  53 ---consistent gets direct
---              ,  54 ---physical reads
---              ,  55 ---physical reads cache
---              ,  56 ---physical reads direct
---              ,  57 ---physical read IO requests
                 ,  58 ---physical read bytes
/*               ,  59 ---db block changes
                 ,  60 ---consistent changes
                 ,  61 ---recovery blocks read
                 ,  62 ---physical writes
                 ,  63 ---physical writes direct
                 ,  64 ---physical writes from cache
                 ,  65 ---physical write IO requests
                 ,  66 ---physical write bytes
                 ,  67 ---physical writes non checkpoint
                 ,  68 ---summed dirty queue length
                 ,  69 ---DBWR checkpoint buffers written
                 ,  70 ---DBWR thread checkpoint buffers written
                 ,  71 ---DBWR tablespace checkpoint buffers written
                 ,  72 ---DBWR parallel query checkpoint buffers written
                 ,  73 ---DBWR object drop buffers written
                 ,  74 ---DBWR transaction table writes
                 ,  75 ---DBWR undo block writes
                 ,  76 ---DBWR revisited being-written buffer
                 ,  77 ---DBWR make free requests
                 ,  78 ---DBWR lru scans
                 ,  79 ---DBWR checkpoints
                 ,  80 ---DBWR fusion writes
                 ,  81 ---prefetch clients - keep
                 ,  82 ---prefetch clients - recycle
                 ,  83 ---prefetch clients - default
                 ,  84 ---prefetch clients - 2k
                 ,  85 ---prefetch clients - 4k
                 ,  86 ---prefetch clients - 8k
                 ,  87 ---prefetch clients - 16k
                 ,  88 ---prefetch clients - 32k
                 ,  89 ---change write time
                 ,  90 ---redo synch writes
                 ,  91 ---redo synch time
                 ,  92 ---exchange deadlocks
                 ,  93 ---free buffer requested
                 ,  94 ---dirty buffers inspected
                 ,  95 ---pinned buffers inspected
                 ,  96 ---hot buffers moved to head of LRU
                 ,  97 ---free buffer inspected
                 ,  98 ---commit cleanout failures: write disabled
                 ,  99 ---commit cleanout failures: block lost
                 , 100 ---commit cleanout failures: cannot pin
                 , 101 ---commit cleanout failures: hot backup in progress
                 , 102 ---commit cleanout failures: buffer being written
                 , 103 ---commit cleanout failures: callback failure
                 , 104 ---commit cleanouts
                 , 105 ---commit cleanouts successfully completed
                 , 106 ---recovery array reads
                 , 107 ---recovery array read time
                 , 108 ---CR blocks created
                 , 109 ---current blocks converted for CR
                 , 110 ---switch current to new buffer
                 , 111 ---write clones created in foreground
                 , 112 ---write clones created in background
                 , 113 ---write clones created for recovery
                 , 114 ---physical reads cache prefetch
                 , 115 ---physical reads prefetch warmup
                 , 116 ---prefetched blocks aged out before use
                 , 117 ---prefetch warmup blocks aged out before use
                 , 118 ---prefetch warmup blocks flushed out before use
                 , 119 ---physical reads retry corrupt
                 , 120 ---physical reads direct (lob)
                 , 121 ---physical reads direct temporary tablespace
                 , 122 ---physical writes direct (lob)
                 , 123 ---physical writes direct temporary tablespace
                 , 124 ---cold recycle reads
                 , 125 ---shared hash latch upgrades - no wait
                 , 126 ---shared hash latch upgrades - wait
                 , 127 ---physical reads for flashback new
                 , 128 ---calls to kcmgcs
                 , 129 ---calls to kcmgrs
                 , 130 ---calls to kcmgas
                 , 131 ---calls to get snapshot scn: kcmgss
                 , 132 ---redo blocks read for recovery
                 , 133 ---redo entries
                 , 134 ---redo size
                 , 135 ---redo buffer allocation retries
                 , 136 ---redo wastage
                 , 137 ---redo writer latching time
                 , 138 ---redo writes
                 , 139 ---redo blocks written
                 , 140 ---redo write time
                 , 141 ---redo log space requests
                 , 142 ---redo log space wait time
                 , 143 ---redo log switch interrupts
                 , 144 ---redo ordering marks
                 , 145 ---redo subscn max counts
*/               , 146 ---gc cr blocks served
                 , 147 ---gc cr block build time
                 , 148 ---gc cr block flush time
                 , 149 ---gc cr block send time
                 , 150 ---gc current blocks served
                 , 151 ---gc current block pin time
                 , 152 ---gc current block flush time
                 , 153 ---gc current block send time
                 , 154 ---gc cr blocks received
                 , 155 ---gc cr block receive time
                 , 156 ---gc current blocks received
                 , 157 ---gc current block receive time
                 , 158 ---gc blocks lost
                 , 159 ---gc claim blocks lost
                 , 160 ---gc blocks corrupt
                 , 161 ---gc CPU used by this session
/*               , 162 ---total number of slots
                 , 163 ---instance recovery database freeze count
                 , 164 ---background checkpoints started
                 , 165 ---background checkpoints completed
                 , 166 ---number of map operations
                 , 167 ---number of map misses
                 , 168 ---flashback log writes
                 , 169 ---serializable aborts
                 , 170 ---transaction lock foreground requests
                 , 171 ---transaction lock foreground wait time
                 , 172 ---transaction lock background gets
                 , 173 ---transaction lock background get time
                 , 174 ---undo change vector size
                 , 175 ---transaction tables consistent reads - undo records
                 , 176 ---transaction tables consistent read rollbacks
                 , 177 ---data blocks consistent reads - undo records applie
                 , 178 ---no work - consistent read gets
                 , 179 ---cleanouts only - consistent read gets
                 , 180 ---rollbacks only - consistent read gets
                 , 181 ---cleanouts and rollbacks - consistent read gets
                 , 182 ---RowCR attempts
                 , 183 ---RowCR hits
                 , 184 ---RowCR - row contention
                 , 185 ---RowCR - resume
                 , 186 ---rollback changes - undo records applied
                 , 187 ---transaction rollbacks
                 , 188 ---immediate (CURRENT) block cleanout applications
                 , 189 ---immediate (CR) block cleanout applications
                 , 190 ---deferred (CURRENT) block cleanout applications
                 , 191 ---commit txn count during cleanout
                 , 192 ---active txn count during cleanout
                 , 193 ---cleanout - number of ktugct calls
                 , 194 ---immediate CR cleanouts (index blocks)
                 , 195 ---deferred CUR cleanouts (index blocks)
                 , 196 ---Commit SCN cached
                 , 197 ---Cached Commit SCN referenced
                 , 198 ---auto extends on undo tablespace
                 , 199 ---drop segment calls in space pressure
                 , 200 ---total number of undo segments dropped
                 , 201 ---doubling up with imu segment
                 , 202 ---tune down retentions in space pressure
                 , 203 ---steps of tune down ret. in space pressure
                 , 204 ---space was found by tune down
                 , 205 ---space was not found by tune down
                 , 206 ---commit batch/immediate requested
                 , 207 ---commit batch requested
                 , 208 ---commit immediate requested
                 , 209 ---commit batch/immediate performed
                 , 210 ---commit batch performed
                 , 211 ---commit immediate performed
                 , 212 ---commit wait/nowait requested
                 , 213 ---commit nowait requested
                 , 214 ---commit wait requested
                 , 215 ---commit wait/nowait performed
                 , 216 ---commit nowait performed
                 , 217 ---commit wait performed
                 , 218 ---global undo segment hints helped
                 , 219 ---global undo segment hints were stale
                 , 220 ---local undo segment hints helped
                 , 221 ---local undo segment hints were stale
                 , 222 ---undo segment header was pinned
                 , 223 ---total number of times SMON posted
                 , 224 ---SMON posted for undo segment recovery
                 , 225 ---SMON posted for txn recovery for other instances
                 , 226 ---SMON posted for instance recovery
                 , 227 ---SMON posted for undo segment shrink
                 , 228 ---SMON posted for dropping temp segment
                 , 229 ---SMON posted for supplemental logging cleanup
                 , 230 ---IMU commits
                 , 231 ---IMU Flushes
                 , 232 ---IMU contention
                 , 233 ---IMU recursive-transaction flush
                 , 234 ---IMU undo retention flush
                 , 235 ---IMU ktichg flush
                 , 236 ---IMU bind flushes
                 , 237 ---IMU mbu flush
                 , 238 ---IMU pool not allocated
                 , 239 ---IMU CR rollbacks
                 , 240 ---IMU undo allocation size
                 , 241 ---IMU Redo allocation size
                 , 242 ---IMU- failed to get a private strand
                 , 243 ---Misses for writing mapping
*/               , 244 ---table scans (short tables)
                 , 245 ---table scans (long tables)
                 , 246 ---table scans (rowid ranges)
                 , 247 ---table scans (cache partitions)
                 , 248 ---table scans (direct read)
                 , 249 ---table scan rows gotten
--               , 250 ---table scan blocks gotten
                 , 251 ---table fetch by rowid
                 , 252 ---table fetch continued row
/*               , 253 ---cluster key scans
                 , 254 ---cluster key scan block gets
                 , 255 ---rows fetched via callback
                 , 256 ---queue update without cp update
                 , 257 ---leaf node splits
                 , 258 ---leaf node 90-10 splits
                 , 259 ---branch node splits
                 , 260 ---failed probes on index block reclamation
                 , 261 ---recursive aborts on index block reclamation
                 , 262 ---native hash arithmetic execute
                 , 263 ---native hash arithmetic fail
                 , 264 ---lob reads
                 , 265 ---lob writes
*/               , 266 ---lob writes unaligned
                 , 267 ---index fast full scans (full)
                 , 268 ---index fast full scans (rowid ranges)
                 , 269 ---index fast full scans (direct read)
                 , 270 ---index fetch by key
                 , 271 ---index scans kdiixs1
/*               , 272 ---queue splits
                 , 273 ---queue flush
                 , 274 ---queue position update
                 , 275 ---queue single row
                 , 276 ---queue ocp pages
                 , 277 ---queue qno pages
                 , 278 ---heap block compress
                 , 279 ---sql area purged
                 , 280 ---sql area evicted
                 , 281 ---CCursor + sql area evicted
                 , 282 ---session cursor cache hits
                 , 283 ---session cursor cache count
                 , 284 ---java call heap total size
                 , 285 ---java call heap total size max
                 , 286 ---java call heap used size
                 , 287 ---java call heap used size max
                 , 288 ---java call heap live size
                 , 289 ---java call heap live size max
                 , 290 ---java call heap object count
                 , 291 ---java call heap object count max
                 , 292 ---java call heap live object count
                 , 293 ---java call heap live object count max
                 , 294 ---java call heap gc count
                 , 295 ---java call heap collected count
                 , 296 ---java call heap collected bytes
                 , 297 ---java session heap used size
                 , 298 ---java session heap used size max
                 , 299 ---java session heap live size
                 , 300 ---java session heap live size max
                 , 301 ---java session heap object count
                 , 302 ---java session heap object count max
                 , 303 ---java session heap live object count
                 , 304 ---java session heap live object count max
                 , 305 ---java session heap gc count
                 , 306 ---java session heap collected count
                 , 307 ---java session heap collected bytes
*/               , 308 ---cursor authentications
                 , 309 ---queries parallelized
                 , 310 ---DML statements parallelized
                 , 311 ---DDL statements parallelized
                 , 312 ---DFO trees parallelized
--               , 313 ---Parallel operations not downgraded
                 , 314 ---Parallel operations downgraded to serial
/*               , 315 ---Parallel operations downgraded 75 to 99 pct
                 , 316 ---Parallel operations downgraded 50 to 75 pct
                 , 317 ---Parallel operations downgraded 25 to 50 pct
                 , 318 ---Parallel operations downgraded 1 to 25 pct
                 , 319 ---PX local messages sent
                 , 320 ---PX local messages recv'd
                 , 321 ---PX remote messages sent
                 , 322 ---PX remote messages recv'd
                 , 323 ---buffer is pinned count
                 , 324 ---buffer is not pinned count
                 , 325 ---no buffer to keep pinned count
                 , 326 ---table lookup prefetch client count
                 , 327 ---workarea memory allocated
                 , 328 ---workarea executions - optimal
                 , 329 ---workarea executions - onepass
                 , 330 ---workarea executions - multipass
                 , 331 ---parse time cpu
                 , 332 ---parse time elapsed
                 , 333 ---parse count (total)
                 , 334 ---parse count (hard)
                 , 335 ---parse count (failures)
*/               , 336 ---frame signature mismatch
                 , 337 ---execute count
                 , 338 ---bytes sent via SQL*Net to client
                 , 341 ---bytes sent via SQL*Net to dblink
                 , 342 ---bytes received via SQL*Net from dblink
                 , 344 ---sorts (memory)
                 , 345 ---sorts (disk)
                 , 346 ---sorts (rows)
/*               , 347 ---OTC commit optimization attempts
                 , 348 ---OTC commit optimization hits
                 , 349 ---OTC commit optimization failure - setup
*/               , 350 ---OS User time used
                 , 351 ---OS System time used
                 , 352 ---OS Maximum resident set size
                 , 353 ---OS Integral shared text size
                 , 354 ---OS Integral unshared data size
                 , 355 ---OS Integral unshared stack size
                 , 356 ---OS Page reclaims
                 , 357 ---OS Page faults
                 , 358 ---OS Swaps
                 , 359 ---OS Block input operations
                 , 360 ---OS Block output operations
                 , 361 ---OS Socket messages sent
                 , 362 ---OS Socket messages received
                 , 363 ---OS Signals received
                 , 364 ---OS Voluntary context switches
                 , 365 ---OS Involuntary context switches
                 )
    )
 where trunc(pct) > &pctMinimo   -- percentuais maiores que pctMinimo
   and rank < 1+&topN            -- "N" maiores sessoes que consomem aquele recurso
   and upper(statistic_name) like upper('%&topStat.%')
/
prompt

clear breaks compute
set feedback 6
undefine topN topStat topUser
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  top10sql.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------

--Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,900) sql,
        buffer_gets, executions, buffer_gets/executions "Gets/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE buffer_gets > 10000
 ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;

--Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,900) sql,
        disk_reads, executions, disk_reads/executions "Reads/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE disk_reads > 1000
 ORDER BY disk_reads DESC)
WHERE rownum <= 10
;

--Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,900) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE executions > 100
 ORDER BY executions DESC)
WHERE rownum <= 10
;

--Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,800) sql,
        parse_calls, executions, hash_value,address
   FROM V$SQLAREA
  WHERE parse_calls > 1000
 ORDER BY parse_calls DESC)
WHERE rownum <= 10
;

--Top 10 by Sharable Memory:

set linesize 100
set pagesize 100
SELECT * FROM 
(SELECT substr(sql_text,1,800) sql,
        sharable_mem, executions, hash_value,address
   FROM V$SQLAREA
  WHERE sharable_mem > 1048576
 ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;

--Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM 
(SELECT substr(sql_text,1,800) sql,
        version_count, executions, hash_value,address
   FROM V$SQLAREA
  WHERE version_count > 20
 ORDER BY version_count DESC)
WHERE rownum <= 10
;


 
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  top5.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
@@top 5 % 1#$*----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topativo.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
set echo off
rem
rem ARQUIVO
rem    topAtivo.sql
rem
rem FINALIDADE
rem    Mostra as "N" maiores sessoes consumidoras de recursos
rem    com status de ACTIVE e/ou INACTIVE ha menos de 5 min 
rem
rem PARAMETROS
rem    1 = quantos elementos apresentar por estatistica (os "top N")
rem    2 = parte do nome de estatistica
rem
rem EXEMPLOS
rem    @top 5 ga_mem ==> Exibe os 5 maiores consumidores de recursos '%ga_mem%'
rem    @top 5 %      ==> Exibe os 5 maiores consumidores de todos os recursos
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off
set linesize 120 pagesize 10000 verify off

define topN=&1
define topStat=&2
define pctMinimo=1

column sid             format 99999
column top_stat_name   format a26            heading 'STATISTIC_NAME' trunc
column sess_status     format a4             heading 'STATUS' truncate
column username_osuser format a26            heading 'USERNAME/OSUSER'
column minutos         format 9999           heading 'MIN'
column statistic_value format 9g999g999g990  heading 'STATISTIC_VAL'
column conn_time                             heading 'LOGADO A'

break on top_stat_name skip 1
compute sum of pct on statistic_name

select statistic_name as top_stat_name
     , rank
     , pct
     , sid
     , decode( username, null, '('||bg_name||')', username)
        || decode( osuser, null, null, '/')
        || substr( osuser, 1+instr( osuser, '\') )
    as username_osuser
     , lpad( decode( trunc(sysdate - logon_time)
                   , 0, null
                   , trunc(sysdate - logon_time) || 'd, ' )
          || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
                              , 'SSSSS' )
                     , 'hh24"h "mi"m"' )
           , 10 )
    as conn_time
     , round( last_call_et/60, 1) as minutos
     , decode( status, 'ACTIVE', 'ATIVO', 'INACTIVE', 'INATIVO', status)
    as sess_status
     , statistic_value
  from  ( select sn.name as statistic_name
               , row_number() over ( partition by sn.name
                                         order by sn.name, sv.value desc)
              as rank
               , 100 * ratio_to_report(sv.value) over (partition by sn.name) as pct
               , sv.value as statistic_value
               , bg.name as bg_name
               , ss.*
            from v$sesstat sv
               , v$statname sn
               , v$session ss
               , v$bgprocess bg
           where sn.statistic# = sv.statistic#
             and ss.sid = sv.sid
             and ss.paddr = bg.paddr(+)
             and ss.type != 'BACKGROUND'
             and (  ( status = 'INACTIVE' and last_call_et/60 < 5 )
                   or status = 'ACTIVE'
                 )
             and sv.value > 0
             and sn.name
             in ( 'consistent gets'
                , 'db block gets'
                , 'physical reads'
                , 'physical writes'
--                , 'physical reads direct'
--                , 'physical writes direct'
--                , 'bytes sent via SQL*Net to client'
--                , 'bytes received via SQL*Net from dblink'
--                , 'bytes sent via SQL*Net to dblink'
--                , 'enqueue releases'
--                , 'enqueue requests'
                , 'enqueue waits'
--                , 'recursive cpu usage'
--                , 'session logical reads'
--                , 'session pga memory'
--                , 'session uga memory'
                , 'session pga memory max'
                , 'session uga memory max'
                , 'execute count'
                , 'parse count (hard)'
--                , 'parse time cpu'
--                , 'parse time elapsed'
--                , 'parse count (total)'
--                , 'sorts (disk)'
--                , 'sorts (memory)'
                , 'sorts (rows)'
                , 'table scans (long tables)'
                , 'table fetch continued row'
                )
       )
 where trunc(pct) > &pctMinimo   -- percentuais maiores que pctMinimo
   and rank < 1+&topN            -- "N" maiores sessoes que consomem aquele recurso
   and upper(statistic_name) like upper('%&topStat.%')
/
prompt

clear breaks compute
set feedback 6
undefine topN topStat topUser
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topevent.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
set echo off
rem
rem ARQUIVO
rem    event.sql
rem
rem FINALIDADE
rem    Mostra as "N" maiores sessoes por evento
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off
set linesize 120 pagesize 10000 verify off
set colsep "|"

define topN=&1
define eventName=&2
define pctMinimo=1

break on event skip 1
column event format a22 truncate
column username_osuser format a22

select *
  from ( select e.event
              , row_number() over ( partition by e.event
                                    order by e.event, e.time_waited desc )
             as rank
              , 100 * ratio_to_report(e.time_waited) over (partition by e.event) as pct
              , e.sid
              , decode( s.username, null, '('||b.name||')', s.username)
                 || decode( s.osuser, null, null, '/')
                 || substr( s.osuser, 1+instr( s.osuser, '\') )
             as username_osuser
              , e.total_waits
              , lpad( decode( trunc(sysdate - logon_time)
                            , 0, null
                            , trunc(sysdate - logon_time) || 'd ' )
                   || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
                                       , 'SSSSS' )
                              , 'hh24"h "mi"m"' )
                    , 10 )
             as time_connected
              , to_char( to_date( trunc(e.time_waited/100) , 'sssss') , 'hh24"h "MI"m' )
             as time_waited
              , e.average_wait
              , e.max_wait
           from v$session_event e
              , v$process p
              , v$session s
              , v$bgprocess b
          where s.paddr = p.addr
            and s.sid = e.sid
            and s.paddr = b.paddr(+)
            and s.username != 'SYS'
            and upper(e.event) like upper('%&eventName.%')
            and e.event
         not in ( 'SQL*Net message from client'
                , 'SQL*Net more data to client'
                , 'SQL*Net more data from client'
                )
            and s.type != 'BACKGROUND'
            and s.username is not null
       )
 where trunc(pct) > &pctMinimo   -- percentuais maiores que pctMinimo
   and time_waited > '00h 00m'   -- tempo acumulado maior que 1 minuto
   and rank < 1+&topN            -- "N" maiores sessoes que consomem aquele recurso
/
prompt

column event format a26 truncate
column username_osuser format a26
clear breaks
set feedback 6
set colsep " "
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topindex.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
column owner format a20 trunc
column segment_name format a30 trunc
select distinct b.owner, b.segment_name
         from x$bh a, dba_extents b
         where
         b.file_id=a.dbarfil and
         a.dbablk between b.block_id and b.block_id+blocks-1 and
         segment_type='INDEX' and
         b.owner not in ('SYS','SYSTEM');
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  toppctio.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads', 'physical writes',
'consistent changes', 'consistent gets',
'db block gets', 'db block changes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads', 'physical writes',
'consistent changes',
'consistent gets', 'db block gets',
'db block changes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topseg_extents.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem    topseg_extents.sql
rem
rem FINALIDADE
rem    Exibe segmentos com maior quantidade de extents
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off

select * from
  ( select
        s.owner
     ,  s.segment_type
     ,  s.segment_name
     ,  decode (s.partition_name, null, 'NAO', 'SIM')
	as  ptt
     ,  s.bytes/1024/1024 as mbytes
     ,  s.extents
--     ,  s.buffer_pool
     ,  decode (t.extent_management, 'LOCAL', 'L|', 'D|')
    ||  s.tablespace_name
    as  tablespace_name
    from
       dba_segments s
     , dba_tablespaces t
    where
       t.tablespace_name = s.tablespace_name
       and s.segment_type != 'TEMPORARY'
    order by
       extents desc )
where rownum < 21
/
prompt
set feedback 6
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topseg_size.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem    topseg_size.sql
rem
rem FINALIDADE
rem    Exibe segmentos com maior tamanho em bytes
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off

select * from
  ( select
        s.owner
     ,  s.segment_type
     ,  s.segment_name
     ,  decode (s.partition_name, null, 'NAO', 'SIM')
	as  ptt
     ,  s.bytes/1024/1024 as mbytes
     ,  s.extents
--     ,  s.buffer_pool
     ,  decode (t.extent_management, 'LOCAL', 'L|', 'D|')
    ||  s.tablespace_name
    as  tablespace_name
    from
       dba_segments s
     , dba_tablespaces t
    where
       t.tablespace_name = s.tablespace_name
       and s.segment_type != 'TEMPORARY'
    order by
       mbytes desc )
where rownum < 21
/
prompt

set feedback 6
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topseg_size_ale.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
select * from (
select segment_name, segment_type, sum(bytes)/1024/1024 as Mbytes
from dba_segments
group by segment_name, segment_type
order by Mbytes desc )
where rownum <21
/
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topseg_size_partit.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
select * from
  ( select
        s.owner
     ,  s.segment_type
     ,  s.segment_name
     ,  decode (s.partition_name, null, 'NAO', 'SIM')
	as  ptt
     ,  s.bytes/1024/1024 as mbytes
     ,  s.extents
--     ,  s.buffer_pool
     ,  decode (t.extent_management, 'LOCAL', 'L|', 'D|')
    ||  s.tablespace_name
    as  tablespace_name
    from
       dba_segments s
     , dba_tablespaces t
    where
       t.tablespace_name = s.tablespace_name
       and s.segment_type != 'TEMPORARY'
       and s.segment_type != 'TYPE2 UNDO'
       and s.partition_name is null
    order by
       mbytes desc )
where rownum < 31
/
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topsess.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
select
'top physical i/o process' category,
sid,
username,
total_user_io amt_used,
round(100 * total_user_io/total_io,2) pct_used
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads', 'physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)')
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads', 'physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)'))
where
rownum < 2
union all
select
'top logical i/o process',
sid,
username,
total_user_io amt_used,
round(100 * total_user_io/total_io,2) pct_used
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('consistent gets', 'db block gets')
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('consistent gets', 'db block gets'))
where
rownum < 2
union all
select
'top memory process',
sid,
username,
total_user_mem,
round(100 * total_user_mem/total_mem,2)
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_mem
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('session pga memory', 'session uga memory')
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_mem
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('session pga memory', 'session uga memory'))
where
rownum < 2
union all
select
'top cpu process',
sid,
username,
total_user_cpu,
round(100 * total_user_cpu/greatest(total_cpu,1),2)
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_cpu
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name = 'CPU used by this session'
group by
b.sid, nvl(b.username,p.name)
order by
3 desc),
(select
sum(value) total_cpu
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name = 'CPU used by this session' )
where
rownum < 2
/
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topsessdet.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
select *
from
(select
b.sid sid,
decode (b.username,null,e.name,b.username) user_name,
d.spid os_id,
b.machine machine_name,
to_char(logon_time, 'mmd/dd/yy hh:mi:ss pm') logon_time,
(sum(decode(c.name,'physical reads',value,0)) +
sum(decode(c.name,'physical writes',value,0)) +
sum(decode(c.name,'physical writes direct',value,0)) +
sum(decode(c.name,'physical writes direct (lob)',value,0))
+
sum(decode(c.name,'physical reads direct (lob)',value,0))
+
sum(decode(c.name,'physical reads direct',value,0)))
total_physical_io,
(sum(decode(c.name,'db block gets',value,0)) +
sum(decode(c.name,'db block changes',value,0)) +
sum(decode(c.name,'consistent changes',value,0)) +
sum(decode(c.name,'consistent gets',value,0)) )
total_logical_io,
100 -
100 *
(round ((sum(decode(c.name,'physical reads',value,
0)) -
sum(decode(c.name,'physical reads direct',value,
0))) /
(sum(decode(c.name,'db block gets',value,1)) +
sum(decode(c.name,'consistent gets',value,0))
),3)) hit_ratio,
(sum(decode(c.name,'session pga memory',value,0))+
sum(decode(c.name,'session uga memory',value,0)) )
total_memory_usage,
sum(decode(c.name,'parse count (total)',value,0)) parse,
sum(decode(c.name,'CPU used by this session',value,0))
total_cpu,
sum(decode(c.name,'parse time cpu',value,0)) parse_cpu,
sum(decode(c.name,'recursive cpu usage',value,0))
recursive_cpu,
sum(decode(c.name,'CPU used by this session',value,0)) -
sum(decode(c.name,'parse time cpu',value,0)) -
sum(decode(c.name,'recursive cpu',value,0))
other_cpu
sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,
sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,
sum(decode(c.name,'sorts (rows)',value,0)) rows_sorts,
sum(decode(c.name,'user commits',value,0)) commits,
sum(decode(c.name,'user rollbacks',value,0)) rollbacks,
sum(decode(c.name,'execute count',value,0)) executions,
sum(decode(c.name,'physical reads',value,0))
physical_reads,
sum(decode(c.name,'db block gets',value,0))
db_block_gets,
sum(decode(c.name,'consistent gets',value,0))
consistent_gets,
sum(decode(c.name,'consistent changes',value,0))
consistent_changes
from
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$statname c,
sys.v_$process d,
sys.v_$bgprocess e
where
a.statistic#=c.statistic# and
b.sid=a.sid and
d.addr=b.paddr and
e.paddr (+) = b.paddr and
c.name in
('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)',
'db block gets',
'db block changes',
'consistent changes',
'consistent gets',
'session pga memory',
'session uga memory',
'parse count (total)',
'CPU used by this session',
'parse time cpu',
'recursive cpu usage',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user commits',
'user rollbacks',
'execute count'
)
group by
b.sid,
d.spid,
decode (b.username,null,e.name,b.username),
b.machine,
to_char(logon_time, 'mm/dd/yy hh:mi:ss pm')
order by
6 desc);
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  toptables.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
select
table_owner "table owner",
table_name "table name",
command "command issued",
0 - executions "executions",
disk_reads "disk reads",
gets "buffer gets",
rows_processed "rows processed"
from
(select
distinct executions,
command,
table_owner,
table_name,
gets,
rows_processed,
disk_reads
from
(select
decode (a.command_type,
2, 'insert',
3, 'select',
6, 'update',
7, 'delete',
26, 'table lock ') command,
c.owner table_owner,
c.name table_name,
sum(a.disk_reads) disk_reads,
sum(0 - a.executions) executions,
sum(a.buffer_gets) gets,
sum(a.rows_processed) rows_processed
from
sys.v_$sql a,
sys.v_$object_dependency b,
sys.v_$db_object_cache c
where
a.command_type in (2,3,6,7,26) and
b.from_address = a.address and
b.to_owner = c.owner and
b.to_name = c.name and
c.type = 'table' and
c.owner not in ('SYS', 'SYSTEM')
group by
a.command_type, c.owner, c.name
)
)
where
rownum <= 100
/
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topuser.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
set echo off
rem
rem ARQUIVO
rem    topUser.sql
rem
rem FINALIDADE
rem    Compara um USERNAME com os top consumers de recursos no banco
rem
rem PARAMETROS
rem    1 = parte de um USERNAME
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off
set linesize 120 pagesize 10000 verify off

define topUser=&1

define topN=3
define pctMinimo=1

column sid             format 99999
column top_stat_name   format a30            heading 'STATISTIC_NAME' trunc
column sess_status     format a4             heading 'STATUS' truncate
column username_osuser format a22            heading 'USERNAME/OSUSER'
column minutos         format 9999           heading 'MIN'
column statistic_value format 999g999g999g990  heading 'STATISTIC|VALUE'

break on top_stat_name skip 1
compute sum of pct on statistic_name

select statistic_name as top_stat_name
     , rank
     , pct
     , sid
     , decode( instr( upper(user_name||osuser), upper('&topUser') )
             , 0, '  '
             , '* ')
        || user_name
        || decode( osuser, null, null, '/')
        || substr( osuser, 1+instr( osuser, '\') )
    as username_osuser
     , lpad( decode( trunc(sysdate - logon_time)
                   , 0, null
                   , trunc(sysdate - logon_time) || 'd, ' )
          || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
                              , 'SSSSS' )
                     , 'hh24:mi' )
           , 10 )
    as conn_time
     , round( last_call_et/60, 1) as minutos
     , decode( status, 'ACTIVE', 'ATIVO', 'INACTIVE', 'INATIVO', status)
    as sess_status
     , statistic_value
  from  ( select sn.name as statistic_name
               , row_number() over ( partition by sn.name
                                         order by sn.name, sv.value desc)
              as rank
               , 100 * ratio_to_report(sv.value) over (partition by sn.name) as pct
               , sv.value as statistic_value
               , bg.name as bg_name
               , decode( ss.username
                       , NULL, substr( ss.program
                                     , instr(upper(ss.program), '(') )
                       , ss.username )
              as user_name
               , ss.*
            from v$sesstat sv
               , v$statname sn
               , v$session ss
               , v$bgprocess bg
           where sn.statistic# = sv.statistic#
             and ss.sid = sv.sid
             and ss.paddr = bg.paddr(+)
             and ss.type != 'BACKGROUND'
             and ss.status != 'KILLED'
             and sv.value > 0
             and sn.name
             in ( 'consistent gets'
                , 'db block gets'
                , 'parse time cpu'
                , 'physical reads'
                , 'physical writes'
                , 'recursive cpu usage'
                , 'session pga memory max'
                , 'session uga memory max'
                , 'bytes sent via SQL*Net to client'
                , 'bytes received via SQL*Net from dblink'
                , 'bytes sent via SQL*Net to dblink'
                , 'enqueue releases'
                , 'enqueue requests'
                , 'enqueue waits'
                , 'execute count'
                , 'parse count (hard)'
                , 'parse count (total)'
                , 'parse time elapsed'
                , 'physical reads direct'
                , 'physical writes'
                , 'physical writes direct'
                , 'session logical reads'
                , 'session pga memory'
                , 'session uga memory'
                , 'sorts (disk)'
                , 'sorts (memory)'
                , 'sorts (rows)'
                , 'table scans (long tables)'
                , 'table fetch continued row'
                )
       )
 where trunc(pct) > &pctMinimo   -- percentuais maiores que pctMinimo
   and (    rank < 1+&topN -- "N" maiores sessoes que consomem aquele recurso
         or upper(username||osuser) like upper('%&topUser%')
       )
/
prompt

clear breaks compute
set feedback 6
undefine topN topStat topUser
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topwaiters.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem    topWaiters.sql
rem
rem FINALIDADE
rem    Exibir as "N" maiores sessoes por tempo de espera
rem
rem PARAMETROS
rem    1 = top "N"
rem    2 = parte de um Event Name, para ser usada em LIKE
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off
set colsep "|"

define topN=&1
define topEvent=&2

break on report skip 1

select *
  from ( select row_number() over ( order by e.time_waited desc )
             as rank
              , 100 * ratio_to_report(e.time_waited) over () as pct
              , e.event
              , e.sid
              , decode( s.username
                      , NULL, substr( s.program
                                    , instr(upper(s.program), '(') )
                      , s.username )
             || decode( osuser, null, null, '/')
             || substr( osuser, 1+instr( osuser, '\') )
             as username_osuser
              , to_char(s.logon_time, 'dd-mon/hh24:mi')
             as logon_time
              , lpad( decode( trunc(sysdate - logon_time)
                            , 0, null
                            , trunc(sysdate - logon_time) || 'd ' )
                   || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
                                       , 'SSSSS' )
                              , 'hh24"h "mi"m"' )
                    , 12 )
             as conn_time
              , to_char( to_date( trunc(e.time_waited/100), 'sssss' ), 'hh24"h "MI"m' )
             as time_waited
              , 100 * (e.time_waited/100) / (86400*(sysdate-logon_time))
             as pct_waited
           from v$session_event e
              , v$session s
              , v$process p
          where s.sid = e.sid
            and s.paddr = p.addr
            and s.username is not null
            and s.username != 'SYS'
            and e.time_waited > 5999 -- centesimos de segundo
            and upper(e.event) like upper('%&topEvent%')
            and e.event not in
                ( 'smon timer'
                , 'pmon timer'
                , 'rdbms ipc message'
                , 'Null event'
                , 'parallel query dequeue'
                , 'pipe get'
                , 'client message'
                , 'SQL*Net message to client'
                , 'SQL*Net message from client'
                , 'SQL*Net more data from client'
                , 'dispatcher timer'
                , 'virtual circuit status'
                , 'lock manager wait for remote message'
                , 'PX Idle Wait'
                , 'PX Deq: Execution Msg'
                , 'PX Deq: Table Q Normal'
                , 'wakeup time manager'
                , 'slave wait'
                , 'i/o slave wait'
                , 'jobq slave wait'
                , 'null event'
                , 'gcs remote message'
                , 'gcs for action'
                , 'ges remote message'
                , 'queue messages' )
          order by e.time_waited desc
       )
 where rownum < 1+&topN  -- "N" maiores sessoes por tempo de espera
/
prompt
clear breaks
set colsep " "
set feedback 6
----------------------------------
-- FIM DO SCRIPT  -- 

———————————-
— —

----------------------------------
-- SCRIPT  topwaitersativos.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem    topWaitersAtivos.sql
rem
rem FINALIDADE
rem    Exibir as "N" maiores sessoes ativas por tempo de espera
rem
rem PARAMETROS
rem    1 = top "N"
rem    2 = parte de um Event Name, para ser usada em LIKE
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off
set colsep "|"

define topN=&1
define topEvent=&2

break on report skip 1

select *
  from ( select row_number() over ( order by e.time_waited desc )
             as rank
              , 100 * ratio_to_report(e.time_waited) over () as pct
              , e.event
              , e.sid
              , decode( s.username
                      , NULL, substr( s.program
                                    , instr(upper(s.program), '(') )
                      , s.username )
             || decode( s.osuser, null, null, '/')
             || substr( s.osuser, 1+instr( s.osuser, '\') )
             as username_osuser
              , to_char(s.logon_time, 'dd-mon/hh24:mi')
             as logon_time
              , lpad( decode( trunc(sysdate - logon_time)
                            , 0, null
                            , trunc(sysdate - logon_time) || 'd ' )
                   || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
                                       , 'SSSSS' )
                              , 'hh24"h "mi"m"' )
                    , 12 )
             as conn_time
                , to_char( to_date( trunc(e.time_waited/100), 'sssss') , 'hh24"h "MI"m' )
               as time_waited
              , 100 * (e.time_waited/100) / (86400*(sysdate-logon_time))
             as pct_waited
           from v$session_event e
              , v$session s
              , v$process p
          where s.sid = e.sid
            and s.paddr = p.addr
            and s.username is not null
            and s.username != 'SYS'
            and (  ( status = 'INACTIVE' and last_call_et/60 < 5 )
                  or status = 'ACTIVE'
                )
            and e.time_waited > 5999 -- centesimos de segundo
            and upper(e.event) like upper('%&topEvent%')
            and e.event not in
                ( 'smon timer'
                , 'pmon timer'
                , 'rdbms ipc message'
                , 'Null event'
                , 'parallel query dequeue'
                , 'pipe get'
                , 'client message'
                , 'SQL*Net message to client'
                , 'SQL*Net message from client'
                , 'SQL*Net more data from client'
                , 'dispatcher timer'
                , 'virtual circuit status'
                , 'lock manager wait for remote message'
                , 'PX Idle Wait'
                , 'PX Deq: Execution Msg'
                , 'PX Deq: Table Q Normal'
                , 'wakeup time manager'
                , 'slave wait'
                , 'i/o slave wait'
                , 'jobq slave wait'
                , 'null event'
                , 'gcs remote message'
                , 'gcs for action'
                , 'ges remote message'
                , 'queue messages' )
          order by e.time_waited desc
       )
 where rownum < 1+&topN  -- "N" maiores sessoes por tempo de espera
/
prompt
clear breaks
set colsep " "
set feedback 6
----------------------------------
-- FIM DO SCRIPT  -- 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em ORACLE 11gR2, PERFORMANCE, PLSQL SCRIPTS e marcado , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s