delete

Show usage of rollback segments

SELECT s.sid , s.username , t.used_ublk FROM v$transaction t , v$session s WHERE 1 = 1 AND t.ses_addr = s.saddr; SELECT s.sid , s.username , t.used_ublk, round((t.used_ublk*8)/1024) size_in_MB_8kb_Block_size, round((t.used_ublk*16)/1024 ) size_in_MB_16kb_Block_size FROM v$transaction t , v$session s WHERE 1 = 1 AND t.ses_addr = s.saddr; SELECT distinct rpad(s.sid,3) "SID",S.USERNAME, E.SEGMENT_NAME, T.START_TIME "Start", rpad(T.STATUS,9) "Status", round((t.used_ublk*8)/1024,4) "Size(MB)" --T.USED_UBLK||' Blocks and '||T.USED_UREC||'...
delete

Statistics of rollback segments

Script for SQL*Plus: column "Rollback Segment"       format a16 column "Size (Kb)"              format 9,999,999 column "Gets"                   format 999,999,990 column "Waits"                  format 9,999,990 column "% Waits"                format 90.00 column "# Shrinks"              format 999,990 column "# Extends"              format 999,990 Prompt Prompt ROLLBACK Segment Statistics... SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",...
delete

Tables with full scans in explain plans

select p.owner, p.name, t.num_rows, ltrim(t.cache) is_table_cached, decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') is_in_Keep_pool, s.blocks blocks, sum(a.executions) number_Full_Table_Scans from dba_tables   t, dba_segments s, v$sqlarea    a, (select distinct address, object_owner owner, object_name name from v$sql_plan where operation = 'TABLE ACCESS' and options = 'FULL') p where a.address = p.address and t.owner = s.owner and t.table_name = s.segment_name and t.table_name = p.name and t.owner = p.owner and t.owner not in ('SYS','SYSTEM') having sum(a.executions) >...
delete

Statistics for queries – buffer / ...

select sql_text, child_number, executions, --      executions - &m_exe_ct          exe_delta, buffer_gets, --      buffer_gets - &m_buf_ct         buf_delta, disk_reads, --      disk_reads - &m_dsk_ct          dsk_delta, rows_processed, --      rows_processed - &m_row_ct      row_delta, --      round((rows_processed - &m_row_ct) / nullif(executions - &m_exe_ct,0) )                   rows_per_exe, round((rows_processed) / nullif(executions,0) )                   rows_per_exe, --      round(       (buffer_gets - &m_buf_ct) /     ...
delete

Check possible duplicities in indexed co...

select C.TABLE_NAME, C.COLUMN_NAME, C.COLUMN_POSITION, COUNT(*) AS POCET from all_ind_columns c where c.table_owner = 'RMS' AND C.TABLE_NAME LIKE '.....your_table_mask....%' HAVING COUNT(*)>1 GROUP BY C.TABLE_NAME, C.COLUMN_NAME, C.COLUMN_POSITION
delete

Buffer intensive queries

select  EXECUTIONS, BUFFER_GETS, round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS) / 400,2) "Gets/Execs", SQL_TEXT from   v$sqlarea where    BUFFER_GETS / decode(EXECUTIONS,0,1, EXECUTIONS) / 400 > 10 order    by EXECUTIONS desc