Here’s a quick and dirty script to create a procedure (in the SYS schema – so be careful) to check the Hakan Factor for an object. If you’re not familiar with the Hakan Factor, it’s the value that gets set when you use the command “alter table minimize records_per_block;”.
I was prompted to publish this note by an item on the OTN SQL forum describing a problem with partition exchange with a table when there were bitmap indexes in place and the table had been changed to have some extra columns added. (Problem as yet unresolved as I publish).
If you start playing with the Hakan Factor, you’ll find that there are some odd little bugs in what gets stored and how it gets used. [Edit: SQL updated to use bitand() to reflect comments below and Karsten Spang's blog note; also edited following a comment on OTN to show the rest of the spare1 flag bits.]
create or replace procedure show_hakan( i_table in varchar2, i_owner in varchar2 default user ) as m_obj number(8,0); m_flags varchar2(12); m_hakan number(8,0); begin /* created by show_hakan.sql */ select obj#, /* case when (spare1 > 5 * power(2,15)) then (spare1 - 5 * power(2,15)) when (spare1 > power(2,17)) then (spare1 - power(2,17)) when (spare1 > power(2,15)) then (spare1 - power(2,15)) else spare1 end hakan */ to_char( bitand( spare1, to_number('ffff8000','xxxxxxxx') ), 'xxxxxxxx' ) flags, bitand(spare1, 32767) hakan -- 0x7fff into m_obj, m_flags, m_hakan from tab$ where obj# in ( select object_id from dba_objects where object_name = upper(i_table) and object_type = 'TABLE' and owner = upper(i_owner) ) ; dbms_output.put_line( 'Hakan factor - 1 (see bug history) for object ' || m_obj || ' (' || i_owner || '.' || i_table || ') is ' || m_hakan || ' with flags ' || m_flags ); end; / drop public synonym show_hakan; create public synonym show_hakan for show_hakan; grant execute on show_hakan to public;
You’ll notice that I’ve done an upper() on the table and owner – that means you’re in trouble if you have created any schemas or tables with mixed-case names (but you wouldn’t do that in a production system, would you?)
Update – A little bug history
One of the odd details of the Hakan factor is that the value it shows is one less than the number of rows that will be stored in a block; and since it looks as if the factor is not allowed to drop to zero, you can’t hack the Hakan factor to force one row per block.
So here’s a (trivial and sub-optimal) piece of code to check current number of rows per block in a simple heap table (assuming the tablespace consists of a single file):
select ct rows_per_block, count(*) blocks from ( select dbms_rowid.rowid_block_number(rowid), count(*) ct from t1 group by dbms_rowid.rowid_block_number(rowid) ) group by ct order by ct ;
Here’s the output of a session, running under 220.127.116.11, cut and pasted from the screen:
SQL> @afiedt.buf ROWS_PER_B BLOCKS ---------- ---------- 9 1 16 1 SQL> alter table t1 nominimize records_per_block; SQL> alter table t1 minimize records_per_block; SQL> execute show_hakan('t1') Hakan factor for object 48865 (TEST_USER.t1) is: 15 SQL> alter table t1 move; SQL> @afiedt.buf ROWS_PER_B BLOCKS ---------- ---------- 10 1 15 1 SQL> alter table t1 nominimize records_per_block; SQL> alter table t1 minimize records_per_block; SQL> execute show_hakan('t1') Hakan factor for object 48865 (TEST_USER.t1) is: 14
Every time you moved the table Oracle 18.104.22.168 (and earlier versions) would use the actual stored value of the Hakan Factor to rebuild the table; but if you regenerated the Hakan Factor the stored value was one less than the actual row count. So if you kept repeating the process the number of rows per block would decrease by one each time and the table would get bigger and bigger.
It’s a silly example – but the real-world relevance was that a direct path insert behaved differently from a normal insert and this could result in a significant amount of wasted space if you were doing bulk loads in your overnight batch; so the code changed in 10g to make the normal and direct path inserts consistent with each other, but the change went “the wrong way” and, as a side effect, you get one more row per block than suggested by the Hakan Factor – so you can’t trick the Hakan factor into enforcing one row per block any more.