Search

Top 60 Oracle Blogs

Recent comments

Hints

Index Hints

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))
index(@sel$1 prd@sel$1(product_group  id))

CBO isn’t perfect

And you should remember that. Here is a nice example how Cost Based Optimizer can miss an obvious option (which is available to human eye and Oracle run-time with a hint) while searching for the best plan. CBO simply doesn’t consider Index Skip Scan with constant ‘in list’ predicates in the query, although it costs skip scan for a join. Such bits are always popping up here and there, so you just can’t say “The Cost Based Optimizer examines all of the possible plans for a SQL statement …”, even if Optimizer Team tells you CBO should do so. There will always be places where CBO will do less than possible to come to the best plan and will need a help from your side, such as re-written SQL or a hint.

Hinting

As I’ve often pointed out, this blog isn’t AskTom, or the OTN forum, so I don’t expect to have people asking me to solve their problems; neither do I answer email questions about specific problems. Occasionally, though, questions do appear that are worth a little public airing, and one of these came in by email a couple of weeks ago. The question is longer than the answer I sent, my contribution to the exchange doesn’t start until the heading: “My Reply”.

Last week I find a very interesting thing about use_hash hint accidentally. That is when you have join two tables using unique column from one table and you have a equal predicate on that column, you cannot use hint to make them using hash join.  I know that it does not make sense to use hash join in this case because nested loop is the best way to do it. The point is why Oracle ignore the hint here.

Editing/Adding HInts in Stored Outlines

Introduction 
This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.
 
CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines? 

Editing/Adding HInts in Stored Outlines

Introduction 
This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.
 
CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines? 

How to hint – 1

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:

    > I tried forcing the optimizer to not use hash join by adding NO_USE_HASH, USE_NL to my sql but it doesn’t seem to work.
    > Can anyone please help check what I have done wrong.
    > select /*+ NO_USE_HASH(C2)  USE_NL(C2) */
    >         SC.SID, SC.MID, SC.INDATE, SC.EXDATE, SC.AUDATE
    > FROM    SSCLASS SC
    > WHERE   SC.SID = 0
    > AND     SC.CID = 0
    > AND     SC.MID = 1
    > AND     SC.INDATE <= SC.EXDATE
    > AND     EXISTS (
    >                 SELECT  SSCID FROM SSCLASS C2
    >                 WHERE   C2.SSCID = SC.SSCID
    >                 AND     C2.AUDATE >= to_date('2009-01-01','yyyy-MM-dd')
    >         )
    > ORDER BY
    >        SSCID, INDATE, EXDATE
    >
    > PLAN_TABLE_OUTPUT
    > Plan hash value: 1476588646
    >
    > ------------------------------------------------------------------------------------------------------
    > | Id  | Operation                     | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    > ------------------------------------------------------------------------------------------------------
    > |   0 | SELECT STATEMENT              |              |   204K|    10M|       | 35799   (1)| 00:07:10 |
    > |   1 |  SORT ORDER BY                |              |   204K|    10M|    25M| 35799   (1)| 00:07:10 |
    > |*  2 |   HASH JOIN SEMI              |              |   204K|    10M|    10M| 33077   (1)| 00:06:37 |
    > |*  3 |    TABLE ACCESS BY INDEX ROWID| SSCLASS      |   204K|  7983K|       |  9110   (1)| 00:01:50 |
    > |*  4 |     INDEX RANGE SCAN          | X5_SSCLASS   |   204K|       |       |   582   (1)| 00:00:07 |
    > |*  5 |    INDEX RANGE SCAN           | X6_SSCLASS   |  4955K|    66M|       | 17276   (1)| 00:03:28 |
    > ------------------------------------------------------------------------------------------------------
    

I’m not going to argue about what plans might be good or bad, and I’m going to assume the OP simply wants a nested loop semi join using a “good” index into the table aliased as C2; so I’m just going to demonstrate on this simple example how to approach that specific problem. The critical error the OP has made is that the join he’s trying to affect doesn’t exist in the query block where he’s put his hint – so he needs to find out what query will exist after the subquery has been nested and the optimizer is looking at the semi-join.

Here’s initial query, with default execution plan, I’ll point out that there is an index on the n1 column that I’m using in the existence test:

select
	*
from
	t2
where	t2.n2 = 15
and	exists (
		select
			null
		from	t1
		where	t1.n1 = t2.n1
	)
;

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |    15 |  2865 |    26   (4)| 00:00:01 |
|*  1 |  HASH JOIN SEMI       |       |    15 |  2865 |    26   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL   | T2    |    15 |  2805 |    22   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| T1_I1 |  3000 | 12000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."N1"="T2"."N1")
   2 - filter("T2"."N2"=15)

So I’ve emulated the hash semi-join into the second table that the OP wants to get rid of, and I’m not using the target index in a “precision” fashion.

I happen to know that there is a hint that I can use to make the subquery operate as a nested loop semijoin. It’s /*+ nl_sj */ and it has to go in the subquery. Unfortunately it’s a hint that’s deprecated in 10g, but never mind that for the moment. I’m also going to adopt “sensible practice” and give each of my query blocks a name. Let’s see what we get from dbms_xplan with the hint.

explain plan
set statement_id = 'sj_hinted'
for
select
	/*+
		qb_name(main)
	*/
	*
from
	t2
where	t2.n2 = 15
and	exists (
		select
			/*+
				qb_name(subq) nl_sj
			*/
			null
		from	t1
		where	t1.n1 = t2.n1
	)
;

select * from table(dbms_xplan.display(null,'sj_hinted','outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 635111780

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    15 |  2865 |    37   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |       |    15 |  2865 |    37   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2    |    15 |  2805 |    22   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T1_I1 |  3000 | 12000 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$A93AFAED" "T1"@"SUBQ")
      LEADING(@"SEL$A93AFAED" "T2"@"MAIN" "T1"@"SUBQ")
      INDEX(@"SEL$A93AFAED" "T1"@"SUBQ" ("T1"."N1"))
      FULL(@"SEL$A93AFAED" "T2"@"MAIN")
      OUTLINE(@"SUBQ")
      OUTLINE(@"MAIN")
      UNNEST(@"SUBQ")
      OUTLINE_LEAF(@"SEL$A93AFAED")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T2"."N2"=15)
   3 - access("T1"."N1"="T2"."N1")

Note how I’ve used a statement_id to label my plan, and I’ve added the extra predicate ‘outline’ to the call to dbms_xplan. The outline shows me the complete set of hints I need to reproduce the execution plan; technically it’s the information that would be stored by Oracle as an outline or an SQL Baseline.

There are a few session-level parameter settings I don’t really need included, and a couple of things which can’t qualify as “legal” SQL hints, though, and I’m going to ignore those. (Don’t you love the “ignore the hints” hint, though!)

So let’s take the minimum set of hints back into the SQL:

explain plan
set statement_id = 'full_hints'
for
select
	/*+
		qb_name(main)
		unnest(@subq)
		leading(@sel$a93afaed t2@main t1@subq)
		use_nl(@sel$a93afaed t1@subq)
		full(@sel$a93afaed t2@main)
		index(@sel$a93afaed t1@subq(t1.n1))
	*/
	*
from
	t2
where	t2.n2 = 15
and	exists (
		select
			/*+
				qb_name(subq)
			*/
			null
		from	t1
		where	t1.n1 = t2.n1
	)
;

select * from table(dbms_xplan.display(null,'full_hints','outline'));

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    15 |  2865 |    37   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |       |    15 |  2865 |    37   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2    |    15 |  2805 |    22   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T1_I1 |  3000 | 12000 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$A93AFAED" "T1"@"SUBQ")
      LEADING(@"SEL$A93AFAED" "T2"@"MAIN" "T1"@"SUBQ")
      INDEX(@"SEL$A93AFAED" "T1"@"SUBQ" ("T1"."N1"))
      FULL(@"SEL$A93AFAED" "T2"@"MAIN")
      OUTLINE(@"SUBQ")
      OUTLINE(@"MAIN")
      UNNEST(@"SUBQ")
      OUTLINE_LEAF(@"SEL$A93AFAED")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T2"."N2"=15)
   3 - access("T1"."N1"="T2"."N1")

Job done – we used a bit of hackery to get the plan we wanted, then used the legal hints to reproduce the plan.

It is important to name your query blocks as this helps you to identify what transformations apply when, and how to label your tables correctly in your code; and you have to remember that the “strange” query block names that appear (such as @”SEL$A93AFAED”) are dependent on the query block names you originally supplied.

The method isn’t perfect since (a) sometimes hints that are needed don’t get into the outline, and (b) sometimes the outline actually doesn’t reproduce the plan if all you use are the “legal” hints – but it may help you in most cases.

Things worth to mention and remember (III) - Parallel Execution Control 3

Continuing from the previous part of this series I'll cover in this post some further basics about parallel execution control:

- Keep in mind that there are two classes of parallel hints: PARALLEL and PARALLEL_INDEX. One is about the costing of parallel full table / index fast full scans, the other one about costing (driving) parallel index scans, which are only possible with partitioned indexes (PX PARTITION granule vs. PX BLOCK granule)

Things worth to mention and remember (II) - Parallel Execution Control 2

Continuing from the previous installment of this series I'll cover in this post some of the inevitable classics regarding Parallel Execution Control. So forgive me if you're bored by the repetition of known facts - however I still see these things too often used incorrectly, therefore I decided: This is worth to mention and remember!

- Up to and including version 10.2 PARALLEL (without any parameters) and NOLOGGING are valid keywords only in DDL commands

- Applies to all versions: NOLOGGING can not be used as a hint. It can only be specified as part of DDL, for example ALTER INDEX ... REBUILD PARALLEL NOLOGGING.

Distinct placement

As a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the technique called distinct placement.

PQ_DISTRIBUTE enhancement – Part 1

Oracle 11g has introduced a hell lot of new features related to Parallel Execution – like statement queuing, in-memory PX and auto-DOP, to name a few. There are also small bits of enhancements here and there – like default value of the PARALLEL_EXECUTION_MESSAGE_SIZE and an introduction of an easy way to stick PX slaves to a current RAC node with PARALLEL_FORCE_LOCAL.