Oracle Scratchpad

May 10, 2013

Hakan Factor

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 3:52 pm GMT 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.]


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.

10 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT May 10,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers