Hash Clusters

I was just going through a post from Jonathan Lewis about hash cluster and thought of trying
to test on my enironment as Jonathan has tested on 12c and my version is 10g. I tried to
leave a comment with my result on that post but wordpress does not seem to allow a comment
on that post. I was getting “Sorry, this comment could not be posted”, so i thought to share
my result through a post on my blog. So here it goes

First the environment setup

set serveroutput off

define v_keys = 60

drop cluster hash_cluster;
create cluster hash_cluster(
	id number(5)
)
single table
hashkeys &v_keys
hash is id
size 120
;

create table hash_table(
id		number(5) constraint ht_pk primary key
, owner		varchar2(30)
, object_name	varchar2(120)
, object_id	number
, object_type	varchar2(18)
, created 	date
, last_ddl_time	date
, timestamp	varchar2(19)
, status	varchar2(7)
)
cluster hash_cluster(id)
;

insert into hash_table
select
	rownum	id
	, owner
	, object_name
	, object_id
	, object_type
	, created
	, last_ddl_time
	, timestamp
	, status
from
	all_objects
where
	rownum <= &v_keys
;

commit;

begin
	dbms_stats.gather_table_stats(
		user
		, 'hash_table'
		, method_opt => 'for all columns size 1'
		, cascade => true
	);
end;
/

Now first we will run a query against this clustered table and see the plan from memory
using dbms_xplan.display

select
	owner
	, object_type
	, object_name
from
	hash_table
where
	id = 50
;

select * from table(dbms_xplan.display_cursor());

Output of the above is as following

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  1kgaw3xgjghry, child number 0
-------------------------------------
select  owner  , object_type  , object_name from  hash_table where  id
= 50

Plan hash value: 4047687169

----------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 |
|*  1 |  TABLE ACCESS HASH| HASH_TABLE |     1 |    23 |       |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=50)

Note
-----
   - cpu costing is off (consider enabling it)

Here we can see that oracle cost missing on table access and it has given a cost to
select statement as we have used dbms_xplan.display_cursor, further we can see that it is
saying CPU Costing is off but as Jonathan has said in his post also that it is ON on his system
, and here i would also say it is ON on my system as it is by default on in 10g and i have not fiddled
with any of the default parameters. You can check it by on your system by checking the value of parameter
“_optimizer_cost_model” and it is CHOOSE on my system which means CPU costing is ON. Now
for further checking we can use explain plan and plan_table

explain plan set statement_id = 'Pre-Stats' for
select
	owner
	, object_type
	, object_name
from
	hash_table
where
	id = 50
;

Now we will compute statistics using analyze command and check our plans again

analyze cluster hash_cluster compute statistics;

select
	owner
	, object_type
	, object_name
from
	hash_table
where
	id = 50
;

select * from table(dbms_xplan.display_cursor());

Output of the above is as following:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  1kgaw3xgjghry, child number 0
-------------------------------------
select  owner  , object_type  , object_name from  hash_table where  id
= 50

Plan hash value: 4047687169

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|*  1 |  TABLE ACCESS HASH| HASH_TABLE |     1 |    19 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=50)

19 rows selected.

Now in this plan we can see that it is missing the line having “- cpu costing is off (consider enabling it)”
and it has cost for table access.
And again further check this using explain plan and plan_table

explain plan set statement_id = 'Post-Stats' for
select
	owner
	, object_type
	, object_name
from
	hash_table
where
	id = 50
;

And now we will check the details from plan_table for both the executions

break on statement_id skip 1
select
	statement_id
	, operation
	, cost
	, io_cost
	, cpu_cost
from
	plan_table
where
	statement_id in ('Pre-Stats', 'Post-Stats')
;

Output of the above is as following

STATEMENT_ID                   OPERATION                            COST    IO_COST   CPU_COST
------------------------------ ------------------------------ ---------- ---------- ----------
Pre-Stats                      SELECT STATEMENT                        0          0          0
                               TABLE ACCESS

Post-Stats                     SELECT STATEMENT                        1          1       7121
                               TABLE ACCESS                            1          1       7121


4 rows selected.

From this output we can see that when we are not having stats on the table show null cost, io_cost
and cpu cost for the table access and when we have the stats after analyze cluster we are
having all the cost for table access shown. This is anomaly that someone might be raising a SR
to MOS as it is quite an anomalie and might have a impact on selection of an exection plan.

For further reading and to see the results from the following link
Jonathan Lewis – Hash Clusters – 3

Cheers!!!
Jagdeep Sanwgan

About these ads

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