ORACLE-BASE Blog Aggregator

Here’s a little quiz: If I take the average row length of the rows in a table, multiply by the number of rows, and convert the result to the equivalent number of blocks, how can the total volume of data in the table be greater than the total number of blocks below the table high water mark ? I’ve got three tables in a schema, and they’re all in the same (8KB block, 1M uniform extent, locally managed) tablespace, but here’s a query, with results, showing their space utilisation – notice that I gather schema stats immediately before running my query:

execute dbms_stats.gather_schema_stats(user, method_opt=>'for all columns size 1');

spool waste_space

select
        table_name,
        avg_row_len,
        num_rows,
        round(avg_row_len * num_rows / 8100)    used_space,
        blocks
from
        user_tables
order by
        table_name
;

TABLE_NAME           AVG_ROW_LEN   NUM_ROWS USED_SPACE     BLOCKS
-------------------- ----------- ---------- ---------- ----------
T1                            85       5000         52         63
T2                            85       5000         52         18
T3                           107       5000         66         32

The division by 8,100 is a fairly crude approximation to convert to from bytes to blocks – it allows for the basic block overhead, but doesn’t make any allowance for PCTFREE, ITL entries, etc. It’s generally good enough for a ball-park figure though. So one of my tables uses about 52 blocks’ worth of space, below a highwater mark of 63 blocks – but two of the tables are, apparently, using more space than has been made available !

There are probably several mechanisms that could produce these results – without using any elaborately contrived sequences of activity – all I did was “create as select” to come up with two very simple options which fell outside the boundaries of what the simple SQL diagnostic was able to handle. Any ideas ? I’ll be posting my examples later on today.

A recent posting on OTN came up with a potentially interesting problem – it started roughly like this:

I have two queries like this:

select * from emp where dept_id=10 and emp_id=15;
select * from emp where dept_id=10 and emp_id=16;

When I run them separately I get the execution plan I want, but when I run a union of the two the plans change.

This, of course, is extremely unlikely – even if we assume that the two queries are more complex than the text shown. On the other hand you might, after a little thought, come up with the idea that perhaps the optimizer had done something really clever like join factorization (moving a join that’s common to the two parts of the UNION from inside to outside the UNION), or maybe there’s some really new trick the optimizer had played because a UNION ultimately requires a SORT UNIQUE, and the optimizer had chosen a different path that returned the data from each part of the UNION in sorted order to decrease the cost of that final sort.

In fact it turned out to be a lot simpler than that. The query looked more like this:

select
	/*+
		index(@qb_view_a t1)
		index(@qb_view_b t1)
	*/
	*
from
	t2, qb_view
where
	t2.n1 = 10
and	qb_view.n2  = t2.n2
union
select
	/*+
		index(@qb_view_a t1)
		index(@qb_view_b t1)
	*/
	*
from
	t2, qb_view
where
	t2.n1 = 12                    -- the real code referenced an alternative column here.
and	qb_view.n2  = t2.n2
;

Of particular note is the fact that it’s a join, the join involves a view (guessing from the names in the FROM clause) and there are hints that reference query block names for query blocks that don’t exist – but perhaps are present inside the view. So what does the view look like.

create or replace view qb_view
as
select /*+ qb_name(qb_view_a) */ * from t1
union all
select /*+ qb_name(qb_view_b) */ * from t1
;

It’s a union all view – and the two query blocks named from the outside query are the two halves of the inner union.

Here’s an important thought – it’s quite easy to get Oracle to do what you want in a simple query (at least in the short term) by sticking in a few hints – especially if you create and reference query block names; but when you start compounding queries by combining bits of code that currently do what you want, you may find that Oracle introduces extra query blocks during transformation, and perhaps some of the query blocks you’ve referenced originally cease to exist, so the hints no longer apply.

Let’s look at the execution plan – including the ALIAS and OUTLINE sections – for the final query, and compare it with the execution plan for just one of the two pieces; starting with the single piece first:

explain plan for
select
	/*+
		index(@qb_view_a t1)
		index(@qb_view_b t1)
	*/
	*
from
	t2, qb_view
where
	t2.n1 = 10
and	qb_view.n2  = t2.n2
;

select * from table(dbms_xplan.display(null,null,'basic +outline +alias'));

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  HASH JOIN                     |         |
|   2 |   TABLE ACCESS FULL            | T2      |
|   3 |   VIEW                         | QB_VIEW |
|   4 |    UNION-ALL                   |         |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1      |
|   6 |      INDEX FULL SCAN           | T1_I1   |
|   7 |     TABLE ACCESS BY INDEX ROWID| T1      |
|   8 |      INDEX FULL SCAN           | T1_I1   |
--------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1     / T2@SEL$1
   3 - SET$1     / QB_VIEW@SEL$1
   4 - SET$1
   5 - QB_VIEW_A / T1@QB_VIEW_A
   6 - QB_VIEW_A / T1@QB_VIEW_A
   7 - QB_VIEW_B / T1@QB_VIEW_B
   8 - QB_VIEW_B / T1@QB_VIEW_B

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"QB_VIEW_A" "T1"@"QB_VIEW_A" ("T1"."N1"))
      INDEX(@"QB_VIEW_B" "T1"@"QB_VIEW_B" ("T1"."N1"))
      USE_HASH(@"SEL$1" "QB_VIEW"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "QB_VIEW"@"SEL$1")
      NO_ACCESS(@"SEL$1" "QB_VIEW"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE(@"QB_VIEW_B")
      OUTLINE(@"QB_VIEW_A")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"QB_VIEW_B")
      OUTLINE_LEAF(@"QB_VIEW_A")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan shows us that we have used an index to access table t1 (with an index full scan) in both halves of the QB_VIEW’s union all; and the alias section shows us that we have a table t1 in a query block name qb_view_a, and the outline section shows that we have a hint applied in that query block that directs the optimizer to use an index on that table: INDEX(@”QB_VIEW_A” “T1″@”QB_VIEW_A” (“T1″.”N1″)); and we can see the same strategy appearing for a table t1 in query block qb_view_b. By the way, I checked the plan without the hints, and the optimizer chose to do full tablescans on t1 – so the hints were actually having an effect.

So what happens when we check the plan for the UNION of the two variants of the query:

------------------------------------------
| Id  | Operation              | Name    |
------------------------------------------
|   0 | SELECT STATEMENT       |         |
|   1 |  SORT UNIQUE           |         |
|   2 |   UNION-ALL            |         |
|   3 |    HASH JOIN           |         |
|   4 |     TABLE ACCESS FULL  | T2      |
|   5 |     VIEW               | QB_VIEW |
|   6 |      UNION-ALL         |         |
|   7 |       TABLE ACCESS FULL| T1      |
|   8 |       TABLE ACCESS FULL| T1      |
|   9 |    HASH JOIN           |         |
|  10 |     TABLE ACCESS FULL  | T2      |
|  11 |     VIEW               | QB_VIEW |
|  12 |      UNION-ALL         |         |
|  13 |       TABLE ACCESS FULL| T1      |
|  14 |       TABLE ACCESS FULL| T1      |
------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1
   3 - SEL$1
   4 - SEL$1 / T2@SEL$1
   5 - SET$2 / QB_VIEW@SEL$1
   6 - SET$2
   7 - SEL$2 / T1@SEL$2
   8 - SEL$3 / T1@SEL$3
   9 - SEL$4
  10 - SEL$4 / T2@SEL$4
  11 - SET$3 / QB_VIEW@SEL$4
  12 - SET$3
  13 - SEL$5 / T1@SEL$5
  14 - SEL$6 / T1@SEL$6

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$5" "T1"@"SEL$5")
      FULL(@"SEL$6" "T1"@"SEL$6")
      FULL(@"SEL$2" "T1"@"SEL$2")
      FULL(@"SEL$3" "T1"@"SEL$3")
      USE_HASH(@"SEL$1" "QB_VIEW"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "QB_VIEW"@"SEL$1")
      NO_ACCESS(@"SEL$1" "QB_VIEW"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$4" "QB_VIEW"@"SEL$4")
      LEADING(@"SEL$4" "T2"@"SEL$4" "QB_VIEW"@"SEL$4")
      NO_ACCESS(@"SEL$4" "QB_VIEW"@"SEL$4")
      FULL(@"SEL$4" "T2"@"SEL$4")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SET$3")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan is completely different. We’ve lost the index full scans and we’ve reverted to the tablescans that we would have got from an unhinted query. When you look at the outline you can see why – the query blocks qb_view_a and qb_view_b have disappeared – so the hints are no longer valid. As you can see we now have four occurrences of table t1, but as the alias section shows they come from query blocks sel$2, sel$3, sel$5 and sel$6).

Is this a bug ? I don’t think so. When the optimizer produces the outline information (which can be stored as an SQL Baseline in 11g) it’s producing a set of hints that will be applied at the outermost query block, with hints that point, as necessary, to inner query blocks; this means you can’t use the same query block name twice in a query or the optimizer wouldn’t be able to identify which query block a hint was supposed to apply to. So Oracle has eliminated duplicate query block names and replaced them with the standard internally generated ones – the user’s hints no longer apply.

Footnote: Checking the clock – it took me about 15 minutes to create a simplified model based on the information available on OTN: it’s taken me 75 minutes to describe what I did and what I learned as a result. With a little practice you can get very good at creating models that help you to identify and solve problems very quickly.