Oracle Scratchpad

May 10, 2013

Hakan Factor

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 3:52 pm BST May 10,2013

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.]

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 - 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 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 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 9.2.0.8 (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.

12 Comments »

  1. […] in index order. Stale Stats: A simple piece of pl/sql to report objects with stale or missing stats Hakan Factor: A procedure – owned by SYS – that reports the Hakan factor for an […]

    Pingback by Simple scripts | Oracle Scratchpad — May 10, 2013 @ 4:08 pm BST May 10,2013 | Reply

  2. Amazing!
    A few times I used “minimize record_per_block”; the job could be done better by setting PCTFREE, just to try.
    I’m wondering is “Hakan Factor” can cause any problem in “reference partitioning, row movement enabled” along with Bitmap Join index.

    Thanks,
    Andjelko

    Comment by Andjelko Miovcic — May 10, 2013 @ 6:42 pm BST May 10,2013 | Reply

    • Andjelko,

      From time to time there are reports of surprises that people have had with things like partitioning and bitmap indexes thanks to the Hakan factor – even when they haven’t been doing anything with the Hakan factor. Amongst other things, this is probably why all partitions of a partitioned table are supposed to use the same database block size, and why you’re supposed to drop all bitmap indexes before trying to set (via “minimize_records_per_blocks”) the Hakan factor.

      Comment by Jonathan Lewis — June 9, 2013 @ 12:35 pm BST Jun 9,2013 | Reply

      • Jonathan,
        I know locking is problem with Bitmap Join index.
        Thanks for answering and your time
        Andjelko

        Comment by andjelko miovcic — June 24, 2013 @ 2:08 pm BST Jun 24,2013 | Reply

  3. see also
    http://oracleoddsandends.wordpress.com/2012/08/26/bitmap-indexes-and-the-hakan-factor/

    Karsten has a slightly broader analysis of the bits SPARE1 consists of..

    Comment by Matthias Rogel — May 10, 2013 @ 7:29 pm BST May 10,2013 | Reply

    • Mathias,

      Thanks for the link.

      I see that Karsten has identified the use for bit 16 – so I’ll have to bring my code up to the 21st Century rather than sticking in another case.

      Comment by Jonathan Lewis — May 10, 2013 @ 10:57 pm BST May 10,2013 | Reply

  4. I had previously read Karsten’s post had been using this query to find the hakan factor

    using BITAND is a little simpler than the case and more explicitly highlights the bit selection

    SELECT t.obj#,
           o.owner,
           o.object_name table_name,
           BITAND(t.spare1, TO_NUMBER('7fff', 'xxxx')) hakan_factor
      FROM sys.tab$ t INNER JOIN dba_objects o 
        ON t.obj# = object_id 
        AND object_type = 'TABLE';
    

    Comment by Sean D. Stuber — May 10, 2013 @ 8:13 pm BST May 10,2013 | Reply

  5. Sean,

    My code’s so old (the date on the script is June 2001) it went through a couple of versions of Oracle where bitand() didn’t work properly thanks to some odd coercion effect – it is the tidier implementation, though, especially since I now know from Karsten’s blog that I have to allow for bit 16 as well. (The bitand should be against 0x7fff, by the way – the top bit of 0xffff is the flag bit).

    Comment by Jonathan Lewis — May 10, 2013 @ 11:00 pm BST May 10,2013 | Reply

  6. […] Oracle 8i”). The error message is telling me that I have 2017 rows in a block when the Hakan Factor (maximum expected) is […]

    Pingback by Row Directory | Oracle Scratchpad — June 2, 2021 @ 8:43 pm BST Jun 2,2021 | Reply

  7. […] book then goes on to describe how you might mess about with the minimize records_per_block option of the alter table command to prepare an empty table which would only allow you to insert […]

    Pingback by Table Rebuilds | Oracle Scratchpad — June 2, 2021 @ 8:56 pm BST Jun 2,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.