[Oracle] Insights into SQL hints - Embedded global...

stefan_koehler · ‎08-21-2015

Introduction

The idea for this blog post started a few weeks ago when i had to troubleshoot some Oracle database / SQL performance issues at client site. The SQL itself included several views and so placing hints (for testing purpose) into the views was not possible, especially as the views were used widely and not only by the SQL with the performance issue. In consequence this blog post is about the difference between embedded global and local hints and how to use them.

I highly recommend to read Jonathan Lewis's article "Basics of the Cost Based Optimizer – Part 4" first, if you are not familiar with query blocks or query block names. It is also crucial to have a basic understanding of query transformations as we talk about hinting in case of views and so query transformations may happen. Please check out my blog article "[Oracle] DB Optimizer Part VIII - Looking under the hood of query transformation (done by CBO) with ...", if you are not familiar with query transformations.

The example i will use in this blog post is based on a SAP SELECT statement and a SAP view definition. I have no SAP system at hand right now, but i still have the definition of the tables AFKO / AUFK (including corresponding indexes) and the corresponding view CAUFV in a SQLd360 report. SQLd360 is a free tool created and maintained by Mauro Pagano, that provides a 360-degree overview around a SQL statement (and its used objects, object statistics, runtime information, etc.). It is similar to the "SQL_ID DATA COLLECTOR for Oracle" by Martin Frauendorfer (SAP), but it includes a lot more information and it also supported with Oracle 12c. It does not require any installation and can be executed by any user that has access to dictionary views.

Embedded global and local hints - What are they and how do they differ

At first a general warning. In my opinion applying (manual) hints should be the last resort in case of fixing a cost based optimizer / execution plan issue, but sometimes you just have to due to bugs or cost based optimizer limitations. But even in these cases you still can use SQL patches (or some other techniques) for applying hints, which are much more flexible than hard coding hints in source code.

What are embedded hints in general?

Embedded hints are hints that are included in the SQL statement. Now you may ask - are there any other hints as well? Yes, they are called outline hints. Outline hints are stored in outlines and SQL plan baselines or can also be included in the SQL itself. They are flanked by the special key words BEGIN_OUTLINE_DATA and END_OUTLINE_DATA.

You may already have seen the hint "IGNORE_OPTIM_EMBEDDED_HINTS" in ST05 or DBMS_XPLAN and asked what this means. This special hint instructs the CBO to ignore most of all the other supplied hints (there are some exceptions to this, but let's disregard this right now) except the ones that are flanked by BEGIN_OUTLINE_DATA and END_OUTLINE_DATA (= outline hints). We will see an example of this later on.

What are (embedded) local and global hints?

Local (embedded) hints

Local (embedded) hints apply to the query block in which they are placed. So for example, the SAP DBSL generates a local embedded hint "SELECT /*+ FULL("T_00") * FROM TABLE T_00", if you use an ABAP code extension like this "%_HINTS ORACLE 'FULL( T_00 )'". Unfortunately real applications are not that simple.

The SQLs may use views and query transformations are applied (under the hood) and in consequence such local (embedded) hints may not work anymore. If you look closely into the SQL outline section in ST05 or DBMS_XPLAN, you won't see such hints neither.

Global (embedded) hints

Global (embedded) hints are starting with a "@" symbol. Hints in outlines are always global hints as they are stored in one common location and need to apply to any possible query block. Global hints can also be used as embedded hints and are essential from time to time.

When do we need global (embedded) hints?

If you want to hint a query block based on an Oracle DDIC view (e.g. like SAP view CAUFV). Otherwise you need to alter the view itself with a local hint, if you want change some behavior in there. This may not be tolerable as this view is used by various SQLs.
If you want to hint a query block that is based on a query transformation.
If you have a very complex query and you want to consolidate all hints in one place (comparable to outline hints). Spreading hints in various query blocks (for large SQL statements) can be very confusing and sometimes some hints can become invalid as well.

Let's have a look at an example with a SAP SELECT and the SAP view CAUFV.

Demo for using (embedded) local and global hints

The following demo was run on an Oracle database (12.1.0.1) on OEL 6.4 (2.6.39-400.109.1.el6uek.x86_64).

The SAP table, index and view definition is deployed in a schema called "TEST". The database object definition can be downloaded from here to re-run the test cases.

The following code is the SAP SELECT and the SAP bind variable definition that i will use for the demos:

VAR A0 CHAR(32)

VAR A1 CHAR(128)

VAR A2 CHAR(32)

VAR A3 CHAR(32)

VAR A4 CHAR(32)

VAR A5 CHAR(32)

VAR A6 CHAR(32)

VAR A7 CHAR(32)

SQL> SELECT * FROM "CAUFV" WHERE "MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND "LOEKZ"=:A6 AND "IDAT2"=:A7;

Let's have a look at the default execution plan, the query blocks and the outline data.

Plan hash value: 438111849

----------------------------------------------------------------------------------------

-------------------------------------------------------- -------------------------------

| 0 | SELECT STATEMENT | | | | 1 (100)|

|* 1 | FILTER | | | | |

| 2 | NESTED LOOPS | | | | |

| 3 | NESTED LOOPS | | 1 | 2799 | 0 (0)|

|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | AFKO | 1 | 1729 | 0 (0)|

|* 5 | INDEX RANGE SCAN | AFKO~0 | 1 | | 0 (0)|

|* 6 | INDEX RANGE SCAN | AUFK~A | 1 | | 0 (0)|

|* 7 | TABLE ACCESS BY INDEX ROWID | AUFK | 1 | 1070 | 0 (0)|

----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$F5BB74E1

4 - SEL$F5BB74E1 / T0001@SEL$2

5 - SEL$F5BB74E1 / T0001@SEL$2

6 - SEL$F5BB74E1 / T0002@SEL$2

7 - SEL$F5BB74E1 / T0002@SEL$2

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('12.1.0.1')

DB_VERSION('12.1.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$F5BB74E1")

MERGE(@"SEL$2")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

INDEX_RS_ASC(@"SEL$F5BB74E1" "T0001"@"SEL$2" ("AFKO"."MANDT" "AFKO"."AUFNR"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F5BB74E1" "T0001"@"SEL$2")

INDEX(@"SEL$F5BB74E1" "T0002"@"SEL$2" ("AUFK"."MANDT" "AUFK"."KOKRS"

"AUFK"."ABKRS" "AUFK"."AUART"))

LEADING(@"SEL$F5BB74E1" "T0001"@"SEL$2" "T0002"@"SEL$2")

USE_NL(@"SEL$F5BB74E1" "T0002"@"SEL$2")

NLJ_BATCHING(@"SEL$F5BB74E1" "T0002"@"SEL$2")

END_OUTLINE_DATA

*/

The first thing you may notice is that no VIEW operation is listed in the execution plan although our SELECT was simple as "SELECT * FROM <VIEW_NAME>". The only objects that we can see in the execution plan are the underlying table and index objects. You also notice that this is the result of a query transformations (SEL$F5BB74E1), if you look closely at the query block name.

What kind of query transformation does kick in here? A CBO (10053) trace reveals the secret of course:

…

CVM - complex view merging

SPJ - select-project-join

…

CVM: Checking validity of merging in query block SEL$2 (#0)

CVM: Considering view merge in query block SEL$2 (#0)

OJE: Begin: find best directive for query block SEL$2 (#0)

OJE: End: finding best directive for query block SEL$2 (#0)

CVM: Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xea272578 (VIEW MERGE SEL$1; SEL$2)

…

The original query block "SEL$2" (= SAP view CAUFV itself) is merged into query block "SEL$1" ("SELECT * FROM") and the transformed query block is named "SEL$F5BB74E1". You can notice this transformation (simple view merging) by looking at the outline data section as well (the hint "MERGE(@"SEL$2")"). However you also see that all hints in the outline data section are global hints (leading @ symbol). You also spot the previously mentioned bracket hints BEGIN_OUTLINE_DATA / END_OUTLINE_DATA and the hint IGNORE_OPTIM_EMBEDDED_HINTS in there.

Finally let's imagine the following scenario to get the whole idea of this blog post. You execute a SAP SELECT like "SELECT * FROM "CAUFV" WHERE …" in your ABAP coding, but the cost based optimizer has chosen a wrong execution plan. The SQL runs forever and you want to change the execution plan by adding a hint to your SQL.

What do you specify in the ABAP extension ("%_HINTS ORACLE") in this case?

Let's say you want to perform a full table scan on table AFKO instead of the index range scan on AFKO~0 plus table access (this makes no sense in real life, but it is a pretty simple example for demonstration). Let's try the common hinting syntax (= local embedded hint) at first.

SQL> SELECT /*+ FULL("T0001") */ * FROM "CAUFV" WHERE "MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND "LOEKZ"=:A6 AND "IDAT2"=:A7;

Plan hash value: 438111849

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 1 (100)|

|* 1 | FILTER | | | | |

| 2 | NESTED LOOPS | | | | |

| 3 | NESTED LOOPS | | 1 | 2799 | 0 (0)|

|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | AFKO | 1 | 1729 | 0 (0)|

|* 5 | INDEX RANGE SCAN | AFKO~0 | 1 | | 0 (0)|

|* 6 | INDEX RANGE SCAN | AUFK~A | 1 | | 0 (0)|

|* 7 | TABLE ACCESS BY INDEX ROWID | AUFK | 1 | 1070 | 0 (0)|

-----------------------------------------------------------------------------------------

Nothing changed. Let's verify, if the applied SQL hint was valid at all (for more details please check my blog post [Oracle] DB Optimizer Part IV - What the heck ... Troubleshooting why hints are not considered).

…

Dumping Hints

=============

atom_hint=(@=0x7f9019773968 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("T0001") )

…

The hint "FULL("T0001")" was detected, its syntax was ok and it was used, but the execution plan did not change at all, although it is a valid operation. The main issue here is that it is applied as a local embedded hint to query block SEL$1, but the table AFKO (alias T0001) is specified in query block SEL$2. Finally these two query blocks are transformed into query block SEL$F5BB74E1 due to (simple) view merging.

You basically have two options, if you want to hint this SQL / execution plan based on SAP view CAUFV:

You need to alter the SAP view CAUFV (which is usually not possible)
You use a global embedded hint

In this case you can implement the second option in 2 ways. Both ways are valid and result in the same execution plan:

1) Add a global hint that applies to query block SEL$2 (= SAP view CAUFV)

SQL> SELECT /*+ FULL(@"SEL$2" "T0001") */ * FROM "CAUFV" WHERE "MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND "LOEKZ"=:A6 AND "IDAT2"=:A7;

2) Add a global hint that applies to transformed query block SEL$F5BB74E1

SQL> SELECT /*+ FULL(@"SEL$F5BB74E1" "T0001") */ * FROM "CAUFV" WHERE "MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND "LOEKZ"=:A6 AND "IDAT2"=:A7;

Plan hash value: 3565488190

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | FILTER | | | | | |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 1 | 2799 | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | AFKO | 1 | 1729 | 2 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | AUFK~A | 1 | | 0 (0)| |

|* 6 | TABLE ACCESS BY INDEX ROWID | AUFK | 1 | 1070 | 0 (0)| |

------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$F5BB74E1

4 - SEL$F5BB74E1 / T0001@SEL$2

5 - SEL$F5BB74E1 / T0002@SEL$2

6 - SEL$F5BB74E1 / T0002@SEL$2

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('12.1.0.1')

DB_VERSION('12.1.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$F5BB74E1")

MERGE(@"SEL$2")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

FULL(@"SEL$F5BB74E1" "T0001"@"SEL$2")

INDEX(@"SEL$F5BB74E1" "T0002"@"SEL$2" ("AUFK"."MANDT" "AUFK"."KOKRS"

"AUFK"."ABKRS" "AUFK"."AUART"))

LEADING(@"SEL$F5BB74E1" "T0001"@"SEL$2" "T0002"@"SEL$2")

USE_NL(@"SEL$F5BB74E1" "T0002"@"SEL$2")

NLJ_BATCHING(@"SEL$F5BB74E1" "T0002"@"SEL$2")

END_OUTLINE_DATA

*/

Summary

I hope you get the main difference between local and global embedded hints and how to use them with this blog post. There are many little quirks as well, but hopefully you can troubleshoot such issues the next time, if you try to add a (local) hint and it is not considered at all, although it would be a valid operation.

If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database (performance) issues. You can also follow or drop me a note on Twitter of course.