Identfying Chaining

From: Tony Jambu <aaj_at_cmutual.com.au>
Date: 20 Jun 92 02:36:36 GMT
Message-ID: <1992Jun20.023636.26261_at_cmutual.com.au>

This is a request for a dba utility. I am trying to identify 'chaining' in a table.

I have a couple of utilities that will do the trick but these were written for V5. What I am particularly looking for is a script/program that will use V6 objects and will accurately identify the ROWS that are chained.

If the actual rows are not able to be identified, then the blocks will do.

I have the following script which is a modified version that the Oracle DBA training course use. I am sure that it's calculation is not accurate as I have a case where a number of rows were chained and its output indicated it all fit in a single block (2k).

• Start of Attachment ============================= REM v6_autochain.sql REM REM Identifies chaining in a named table REM

col dummy noprint
REM set sqlprompt ""
accept uname prompt 'Enter Users name : ' accept tname prompt 'Enter table name : ' set heading off
set feedback off
set pagesize 5000
set linesize 180
set pause off
set verify off
set termout off
set echo off
spool calc_chain_&&tname..sql

select 'spool off' from dual;
select 'spool calc_chain_&&tname..spo' from dual;
select 'ttitle ''Calc Chaining for &&uname : && tname'' ' from dual;

select 0 dummy, 'select ' from dual
union
select 1 dummy, 'substr(rowid,1,8) "block",' from dual union
select 2 dummy, 'sum(nvl(vsize('||column_name||'),0)) + count('||column_name||') * 2 +' from dba_tab_columns
where table_name = upper('&tname')
and owner = upper('&uname')
union
select 3 dummy, 'count(*) * 4 "BYTES" ' from dual union
select 4 dummy, 'from &uname..&tname' from dual union
select 5 dummy, ' group by substr(rowid,1,8);' from dual /
spool off
ttitle off
set heading on
set feedback on
set termout on
set echo off
set linesize 80
start calc_chain_&&tname..sql
set echo on

============================== End of Attachment ==============================

Environment

---

Machine:	Pyramid 9825
O/Sys:		OS/x 5.1
RDBMS:		V6.0.33
SQL*Plus:	3.0.11

Thanks in advance

-- 
-----------------------------------------------------------------------------
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  __       /_ __ /       |Colonial Mutual Life Australia. (ACN 004021809)
 /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu_at_cmutual.com.au

Received on Sat Jun 20 1992 - 04:36:36 CEST