Oracle Scratchpad

October 16, 2013

Hash Clusters – 3

Filed under: 12c,Bugs,dbms_xplan,Oracle,Upgrades — Jonathan Lewis @ 1:03 pm BST Oct 16,2013

This note is a quick summary of a costing oddity that came to light after a twitter conversation with Christian Antognini yesterday. First a little test script to get things going:


set serveroutput off

define m_keys = 10000
define m_keys = 60

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

create table hash_table (
	id                      number(5),
		constraint ht_pk primary key(id),
	owner                   varchar2(30),
	object_name             varchar2(128),
	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, rownum,
	object_type, created, last_ddl_time,
	timestamp, status
from
	all_objects
where
	rownum <= &m_keys ; commit; begin 	dbms_stats.gather_table_stats( 		ownname		 => user,
		tabname		 =>'hash_table',
		method_opt 	 => 'for all columns size 1',
		cascade		 => true
	);
end;
/

--
--  Now we can run a query and pull the execution plan from memory and
--  because of an oddity I noticed the first time around, we'll also do
--  an explain plan on it to see what Oracle puts in the plan_table
--

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

select * from table(dbms_xplan.display_cursor);

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

--
--  Now we'll use the analyse command (you know, the one that you're
--  not supposed to use to collect statistics) to collect statistics
--  and see if anything changes.
--

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);

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

--
--  And here are the results from the plan_table for the two
--  runs of the test: showing just the cost-related columns
--

break on statement_id skip 1
select statement_id, operation, cost, io_cost, cpu_cost from plan_table;

If you compare the two sets of results then, after collecting cluster stats (analyse cluster), you’ll see that the optimizer reports the cost of getting the row as 1, which is an I/O cost of one block visit (because that’s the way we hope a single table hash cluster will work) plus a tiny CPU cost:

SQL_ID  5xrn2dfr7dbyv, 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)

STATEMENT_ID                   OPERATION                            COST    IO_COST   CPU_COST
------------------------------ ------------------------------ ---------- ---------- ----------
Post-stats                     SELECT STATEMENT                        1          1       7121
                               TABLE ACCESS                            1          1       7121

But what does Oracle tell us if we don’t analyse the cluster ?

SQL_ID  5xrn2dfr7dbyv, 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)

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

Despite the absence of a cost on the table access line the statement as a whole is given a cost when we call dbms_xplan, but we’re told that we haven’t enabled CPU costing (when actually, take my word for it, we have). Looking in the plan_table (the results are the same if we look in v$sql_plan) we can see nulls for the table access costs, and zeros for the statement cost – the observation that cpu_cost = 0 may be the reason we see the note about CPU costing.

So there are some anomalies going in in costing table access by hash cluster. I suspect that very few people actual call analyse cluster, and I’m not going to try to predict what side effects may appear in more complex statements if you introduce cluster statistics; but I will say it’s worth checking carefully any code that has anything to do with clustered (or, at least, hash clustered) tables every time you upgrade in case this particular anomaly suddenly disappears and the costs of hash table access increase and execution plans change as a consequence.

 

1 Comment »

  1. […] For further reading and to see the results from the following link Jonathan Lewis – Hash Clusters – 3 […]

    Pingback by Hash Clusters | jagdeepsangwan — April 9, 2014 @ 2:59 pm BST Apr 9,2014 | 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,161 other followers