Script:To Report Information on Indexes

2007/06/30 by Leave a Comment

Reports index fragmentation statistics:

==========
Script #1:
==========

SET ECHO off
REM NAME:   TFSIFRAG.SQL
REM USAGE:"@path/tfsifrag schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Reports index fragmentation statistics
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                     Index Fragmentation Statistic
REM
REM    index name        S_EMP_USERID_UK
REM    leaf rows deleted            0
REM    leaf rows in use            25
REM    index badness            0.000
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr  = &&1
def name  = &&2

ttitle -
  center 'Index Fragmentation Statistic'   skip 2

set heading off

col name                 newline
col lf_blk_rows          newline
col del_lf_rows          newline
col ibadness newline

validate index &ownr..&name;

select
  'index name        '||name,
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                         Index Fragmentation Statistic


index name                   S_EMP_USERID_UK
leaf rows deleted            0
leaf rows in use             25
index badness                0.000




==========
Script #2:
==========

SET ECHO off
REM NAME:   TFSISTAT.SQL
REM USAGE:"@path/tfsistat schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Report index statistics.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                                Index Statistics
REM
REM    S_EMP_USERID_UK
REM    ----------------------------------------------------------
REM    height                          1
REM    blocks                          5
REM    del_lf_rows                     0
REM    del_lf_rows_len                 0
REM    distinct_keys                  25
REM    most_repeated_key               1
REM    btree_space                 1,876
REM    used_space                    447
REM    pct_used                       24
REM    rows_per_key                    1
REM    blks_gets_per_access            2
REM    lf_rows                        25            br_rows               0
REM    lf_blks                         1            br_blks               0
REM    lf_rows_len                   447            br_rows_len           0
REM    lf_blk_len                  1,876            br_blk_len            0
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr        = &&1
def name        = &&2

ttitle -
  center  'Index Statistics'  skip 2

set heading off

col name   newline
col headsep              newline
col height               newline
col blocks               newline
col lf_rows              newline
col lf_blks        	 newline
col lf_rows_len          newline
col lf_blk_len           newline
col br_rows              newline
col br_blks              newline
col br_rows_len          newline
col br_blk_len           newline
col del_lf_rows          newline
col del_lf_rows_len      newline
col distinct_keys        newline
col most_repeated_key    newline
col btree_space          newline
col used_space    	 newline
col pct_used             newline
col rows_per_key         newline
col blks_gets_per_access newline

validate index &ownr..&name;

select
  name,
  '----------------------------------------------------------'    headsep,
  'height               '||to_char(height,     '999,999,990')     height,
  'blocks               '||to_char(blocks,     '999,999,990')     blocks,
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows,
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys,
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space,
  'used_space           '||to_char(used_space,'999,999,990')        used_space,
  'pct_used                     '||to_char(pct_used,'990')          pct_used,
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key,
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows,
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks,
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len,
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                                Index Statistics
S_EMP_USERID_UK
----------------------------------------------------------
height                          1
blocks                          5
del_lf_rows                     0
del_lf_rows_len                 0
distinct_keys                  	25
most_repeated_key               1
btree_space                 	1,876
used_space                    	447
pct_used                       	24
rows_per_key                    1
blks_gets_per_access            2
lf_rows                		25
br_rows                 	0
lf_blks				1
br_blks                 	0
lf_rows_len           		447
br_rows_len            		0
lf_blk_len          		1,876
br_blk_len              	0




==========
Script #3:
==========

SET ECHO off
REM NAME:   TFSIKEYS.SQL
REM USAGE:"@path/tfsikeys idx_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Shows the index keys for a particular table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM             Index Keys Summary
REM
REM    Uniqueness                Index Name                    Column Name
REM    ---------- ----------------------------------------  ------------------
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID
REM
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ixowner	= &&1
def tabname	= &&2

ttitle -
   center  'Index Keys Summary'  skip 2

col uniq    format a10 heading 'Uniqueness'  justify c trunc
col indname format a40 heading 'Index Name'  justify c trunc
col colname format a25 heading 'Column Name' justify c trunc

break -
  on indname skip 1 -
  on uniq

select
  ind.uniqueness                  uniq,
  ind.owner||'.'||col.index_name  indname,
  col.column_name                 colname
from
  dba_ind_columns  col,
  dba_indexes      ind
where
  ind.owner = upper('&ixowner')
    and
  ind.table_name = upper('&tabname')
    and
  col.index_owner = ind.owner
    and
  col.index_name = ind.index_name
order by
  col.index_name,
  col.column_position
/

undef ixowner
undef tabname
set verify on


==============
Sample Output:
==============


         Index Keys Summary


Uniqueness                Index Name                    Column Name
---------- ---------------------------------------- ----------------------
UNIQUE                SCOTT.S_EMP_ID_PK                        ID

UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID
Filed Under: Oracle, Oracle脚本script Tagged With: ,

Script:when transaction will finish rollback

2007/04/19 by Leave a Comment 
-------------------------------------------------------------------------------
--
-- Script:	rolling_back.sql
-- Purpose:	to predict when transactions will finish rolling back
-- For:		9.0+
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

prompt
@restore_sqlplus_settings
Filed Under: Oracle, Oracle Internal Research内部原理研究, Oracle脚本script Tagged With: , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号