In my previous post (Execution Plans, Hints and Outlines in Oracle 18c) we saw how to use a SQL Hint to modify the order in which the CBO joins the tables. Today I want to show you a cool feature to inject this change without re-writing your SQL statement.
Let’s use the same SQL statement:
explain plan SET statement_id = ‘ex_plan’ for SELECT
COUNT(*)
FROM
hr.employees a,
hr.departments b,
hr.locations c
WHERE
a.department_id = b.department_id
AND b.location_id = c.location_id
AND a.salary > 3000
AND c.postal_code IS NOT NULL;
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ‘ex_plan’,’ADVANCED’));
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
Plan hash value: 3098668569
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 24 | 8 (13)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | HASH JOIN | | 80 | 1920 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN | | 27 | 459 | 5 (20)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 22 | 220 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | LOC_ID_PK | 23 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 27 | 189 | 3 (34)| 00:00:01 |
| 7 | VIEW | index$_join$_002 | 27 | 189 | 2 (0)| 00:00:01 |
|* 8 | HASH JOIN | | | | | |
| 9 | INDEX FAST FULL SCAN | DEPT_ID_PK | 27 | 189 | 1 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN | DEPT_LOCATION_IX | 27 | 189 | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | EMPLOYEES | 81 | 567 | 3 (0)| 00:00:01 |
—————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
4 – SEL$1 / C@SEL$1
5 – SEL$1 / C@SEL$1
7 – SEL$FD64DD72 / B@SEL$1
8 – SEL$FD64DD72
9 – SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72
10 – SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72
11 – SEL$1 / A@SEL$1
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@”SEL$1″ “A”@”SEL$1”)
USE_MERGE(@”SEL$1″ “B”@”SEL$1”)
LEADING(@”SEL$1″ “C”@”SEL$1” “B”@”SEL$1” “A”@”SEL$1”)
FULL(@”SEL$1″ “A”@”SEL$1”)
INDEX_JOIN(@”SEL$1″ “B”@”SEL$1” (“DEPARTMENTS”.”DEPARTMENT_ID”)
(“DEPARTMENTS”.”LOCATION_ID”))
INDEX(@”SEL$1″ “C”@”SEL$1” (“LOCATIONS”.”LOCATION_ID”))
OUTLINE_LEAF(@”SEL$1″)
OUTLINE_LEAF(@”SEL$FD64DD72″)
ALL_ROWS
DB_VERSION(‘18.1.0’)
OPTIMIZER_FEATURES_ENABLE(‘18.1.0’)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
The idea is to use the Hint /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”) */ without the need to actually write it into the SQL text.
In order to do this, Oracle provides a procedure called DBMS_SQLDIAG.CREATE_SQL_PATCH.
We can invoke it using the SQL text or the SQL_ID of our statement.
In my example, I’m going to use the SQL_ID 8b2gpq57hxdws.
Here will be the syntax to create the SQL patch:
DECLARE
patch_name VARCHAR2(32767);
BEGIN
patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
sql_id => ‘8b2gpq57hxdws’,
hint_text => ‘LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)’,
name => ‘TEST_PATCH’);
END;
/
PL/SQL procedure successfully completed.
Now, let’s get the execution plan again:
explain plan SET statement_id = ‘ex_plan’ for SELECT
COUNT(*)
FROM
hr.employees a,
hr.departments b,
hr.locations c
WHERE
a.department_id = b.department_id
AND b.location_id = c.location_id
AND a.salary > 3000
AND c.postal_code IS NOT NULL;
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ‘ex_plan’,’ADVANCED’));
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
Plan hash value: 2122977163
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 24 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | HASH JOIN | | 80 | 1920 | 8 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 80 | 1120 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 81 | 567 | 3 (0)| 00:00:01 |
| 5 | VIEW | index$_join$_002 | 27 | 189 | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | | | | |
| 7 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 189 | 1 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN| DEPT_LOCATION_IX | 27 | 189 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | LOCATIONS | 22 | 220 | 3 (0)| 00:00:01 |
———————————————————————————————-
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
4 – SEL$1 / A@SEL$1
5 – SEL$FD64DD72 / B@SEL$1
6 – SEL$FD64DD72
7 – SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72
8 – SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72
9 – SEL$1 / C@SEL$1
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@”SEL$1″ “C”@”SEL$1”)
USE_HASH(@”SEL$1″ “B”@”SEL$1”)
LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)
FULL(@”SEL$1″ “C”@”SEL$1”)
INDEX_JOIN(@”SEL$1″ “B”@”SEL$1” (“DEPARTMENTS”.”DEPARTMENT_ID”)
(“DEPARTMENTS”.”LOCATION_ID”))
FULL(@”SEL$1″ “A”@”SEL$1”)
OUTLINE_LEAF(@”SEL$1″)
OUTLINE_LEAF(@”SEL$FD64DD72″)
ALL_ROWS
DB_VERSION(‘18.1.0’)
OPTIMIZER_FEATURES_ENABLE(‘18.1.0’)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Pretty cool, right?
As always, this is not the ultimate solution but it may buys us time to have the vendor or developers fix the code (hahaha, is not a joke!!!).
Anyway, another tool to keep in our radar after migrations, upgrades and major patches.
Thanks,
Alfredo