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

 

February 13, 2013

STS, OFE and SPM

Filed under: dbms_xplan,Execution plans,Hints,Oracle,Upgrades — Jonathan Lewis @ 9:19 am BST Feb 13,2013

That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.

There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from 10.2.0.3 to 11.2.0.3 – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):

(more…)

January 17, 2013

dbms_xplan bug

Filed under: Bugs,dbms_xplan,Oracle — Jonathan Lewis @ 6:22 pm BST Jan 17,2013

Here’s a very long post (which is mainly an example) demonstrating a little bug in the “explain plan” functionality. It’s a variation of a bug which I thought had been fixed in 11g, but it still appears in some cases. Take a look at this execution plan, which comes from explaining “select * from dba_tab_cols” – the bit I want to emphasise is in lines 1 to 10:

(more…)

January 4, 2012

Index size bug

Filed under: Bugs,dbms_xplan,Indexing,Oracle — Jonathan Lewis @ 5:29 pm BST Jan 4,2012

Here’s a follow-up to a post I did some time ago about estimating the size of an index before you create it. The note describes dbms_stats.create_index_cost() procedure, and how it depends on the results of a call to explain plan. A recent question on the OTN database forum highlighted a bug in explain plan, however, which I can demonstrate very easily. I’ll start with a small amount of data to demonstrate the basic content that is used to calculate the index cost.
(more…)

August 16, 2011

dbms_xplan (4)

Filed under: dbms_xplan,Oracle — Jonathan Lewis @ 11:51 am BST Aug 16,2011

This little note on how dbms_xplan behaves was prompted by a very simple question on OTN which raised a point that I often manage to forget (temporarily). I’ve chosen to explain it through a little demonstration.
(more…)

June 8, 2011

How to hint – 1

Filed under: dbms_xplan,Execution plans,Hints,Oracle,subqueries,Troubleshooting — Jonathan Lewis @ 3:00 pm BST Jun 8,2011

Here’s a quick tutorial in hinting, promped by a question on the OTN database forum.
The OP has a hash semi-join and Oracle appears to be ignoring a hint to use a nested loop:

(more…)

May 11, 2011

dbms_xplan reprise

Filed under: dbms_xplan,lateral view,Oracle — Jonathan Lewis @ 5:28 pm BST May 11,2011

One of the questions that pops up on the internet from time to time is the one about finding SQL that’s doing full tablescans.

Since the appearance of the dynamic performance view v$sql_plan in Oracle 9i this has been quite easy – provided you can check memory soon enough. A query like the following will give you the sql_id (hash_value, if you’re still on 9i) and child_number of any query that has a full tablescan (or index fast full scan) in its execution path.

(more…)

April 15, 2010

Predicate (again)

Filed under: dbms_xplan — Jonathan Lewis @ 6:17 pm BST Apr 15,2010

I often make a fuss about making sure that people include the predicate section when looking at execution plans. Here’s another example demonstrating why it can be so helpful. We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):

(more…)

January 27, 2010

Plan Notes

Filed under: dbms_xplan — Jonathan Lewis @ 7:07 pm BST Jan 27,2010

Nothing terribly important or exciting in this post, but you’ve probably seen the “Notes” section at the end of an execution plan from time to time. The notes are extracted from the “other_xml” column of (usually) the first line of the execution plan data using calls to the extractvalue() XML function.

It’s interesting to see from a trace of a call to dbms_xplan.display_cursor() how the number of things that can appear as notes is slowly growing over recent versions of Oracle. The statements below are extracted from the trace files for 10.2.0.3, 11.1.0.6, and 11.2.0.1 in that order: (more…)

January 25, 2010

Old plan_table

Filed under: dbms_xplan — Jonathan Lewis @ 8:27 am BST Jan 25,2010

When using “explain plan” with “dbms_xplan.display()”, have you ever seen the following note at the end of the output:

Note
-----
   - 'PLAN_TABLE' is old version

It’s something I see surprisingly frequently, travelling as I do to many different sites, but it’s usually easy to deal with. (more…)

May 5, 2009

Dependent Plans

Filed under: dbms_xplan,Execution plans,lateral view — Jonathan Lewis @ 6:09 pm BST May 5,2009

I’ve written several posts about dbms_xplan, and the display_cursor function in 10g. One of the nice feature of this function is that it is a “pipelined” function – which means that you can treat the call to the function as a “virtual table”. Here’s an example (run on 10.2.0.3) of what this allows us to do: (more…)

December 3, 2008

Predicate Problems

Filed under: CBO,dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 8:47 pm BST Dec 3,2008

Whenever you look at an execution plan, you must look at the predicate section (introduced in 9i) before you start trying to work out what’s gone wrong. Here’s an example (stripped down to the basic issue from a more complex query) that shows the importance of this check.

(more…)

March 6, 2008

dbms_xplan(3)

Filed under: dbms_xplan,Execution plans — Jonathan Lewis @ 8:01 am BST Mar 6,2008

Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor()

I’ve just seen a note on Rob van Wijk’s blog where he investigates many more parameter values that can be used with the function.

(more…)

January 10, 2008

Filter plan error

Filed under: dbms_xplan,Execution plans,Oracle — Jonathan Lewis @ 7:24 pm BST Jan 10,2008

In 10g, the code to generate execution plans changed dramatically, as did the SQL used by the dbms_xplan package to report execution plans from the plan table. In 9i, the indentation for the lines of a plan was calculated by the reporting query as the level from a ‘connect by’  query; in 10g the explain plan utility itself calculates the level and populates a column called depth in the plan table.

(more…)

April 26, 2007

Heisenberg

Filed under: dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 9:14 pm BST Apr 26,2007

[See also: gather plan statistics]

Okay, so the title is pretentious; but I thought it was a snappy summary of this item [until someone pointed out that Heisenberg's Uncertainty Principle is not about measurement error].

I’ve blogged before about the improved features in 10g of the dbms_xplan package, in particular the display_cursor() procedure, with its option for displaying rowsource execution statistics.

(more…)

Next Page »

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers