Hakan Factor

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 a posting 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. [Update: version 12.2 introduced the “create table for exchange” syntax to address this type of problem.]

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 the comments below and Karsten Spang’s blog note; also edited following a comment on OTN to show the rest of the spare1 flag bits.]

rem
rem     Script:         show_hakan.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2001
rem     Purpose:        Checking the Hakan factor for a table.
rem

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 (minus one) 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 9.2.0.8, 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 (minus one) 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 (minus one) for object 48865 (TEST_USER.t1) is: 14

Every time you moved the table Oracle 9.2.0.8 (and earlier versions) would use the stored value to rebuild the table so if you regenerated the Hakan Factor the stored value was one less than the actual maximum 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.

To confuse the issue slightly, a direct path array insert would use the correct (i.e. stored value plus one) Hakan Factor, while a normal array insert would use the value exactly as it had been stored.

Update

The decreasing Hakan Factor anomaly had disappeared by 10.2.0.4, possibly as early as 10.1.0.1; however the value stored is still one less than the actual number of rows that will be allowed into the block.

Normal inserts and direct path inserts no longer behave differently with respect to the Hakan Factor, but you may still be fooled temporarily by the extra ITL entry that direct path inserts introduce to the block.