Home » RDBMS Server » Performance Tuning » Use of Hints in Oracle 11g to get desired Execution Plan (Oracle 11g 2.0, Linux)
| Use of Hints in Oracle 11g to get desired Execution Plan [message #610449] |
Thu, 20 March 2014 03:09  |
 |
rekha.singhal@tcs.com
Messages: 6
Registered: March 2012
Location: India
|
Junior Member |
|
|
I am using oracle 11g hints to get desired plan for one of the query in TPCH benchmark. Following is the query and the desired plan.
Query:
explain plan for select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 50
and p_type like '%COPPER'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey;
DESIRED PLAN:
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19866 | 4074K| 199K (1)| 00:39:55 |
| 1 | SORT ORDER BY | | 19866 | 4074K| 199K (1)| 00:39:55 |
|* 2 | VIEW | VW_WIF_1 | 19866 | 4074K| 199K (1)| 00:39:55 |
| 3 | WINDOW SORT | | 19866 | 5645K| 199K (1)| 00:39:55 |
|* 4 | HASH JOIN | | 19866 | 5645K| 199K (1)| 00:39:55 |
|* 5 | TABLE ACCESS FULL | REGION | 1 | 29 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|* 6 | HASH JOIN | | 99328 | 24M| 199K (1)| 00:39:55 |
| 7 | TABLE ACCESS FULL | NATION | 25 | 800 | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 99328 | 21M| 199K (1)| 00:39:55 |
| 9 | NESTED LOOPS | | | | | |
| 10 | NESTED LOOPS | | 99328 | 8342K| 192K (1)| 00:38:27 |
|* 11 | TABLE ACCESS FULL | PART | 24615 | 1370K| 118K (1)| 00:23:43 |
|* 12 | INDEX RANGE SCAN | PK_PARTSUPP | 4 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| PARTSUPP | 4 | 116 | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SUPPLIER | 1280K| 175M| 7314 (1)| 00:01:28 |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VW_COL_9" IS NOT NULL)
4 - access("N_REGIONKEY"="R_REGIONKEY")
5 - filter("R_NAME"='ASIA')
6 - access("S_NATIONKEY"="N_NATIONKEY")
8 - access("S_SUPPKEY"="PS_SUPPKEY")
11 - filter("P_SIZE"=50 AND "P_TYPE" LIKE '%COPPER' AND "P_TYPE" IS NOT NULL)
12 - access("P_PARTKEY"="PS_PARTKEY")
I tried using ORDERED hint to this plan but no use. FOllowing is the query with hint and the plan I got for it.
explain plan for select /* ORDERED USE_HASH(region) */
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
partsupp,
part,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 50
and p_type like '%COPPER'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
part,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey;
PLAN FROM ORACLE 11g
Plan hash value: 1442262758
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 548 | 6057 (1)| 00:01:13 |
| 1 | SORT ORDER BY | | 2 | 548 | 6057 (1)| 00:01:13 |
|* 2 | HASH JOIN | | 2 | 548 | 1480 (1)| 00:00:18 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 8 | 1936 | 1477 (1)| 00:00:18 |
| 5 | NESTED LOOPS | | 8 | 792 | 1469 (1)| 00:00:18 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ---------
------------------------------------------------------------------------------------------------------------
| 6 | MERGE JOIN CARTESIAN | | 176 | 14960 | 941 (1)| 00:00:12 |
|* 7 | TABLE ACCESS FULL | REGION | 1 | 29 | 3 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 176 | 9856 | 938 (1)| 00:00:12 |
|* 9 | TABLE ACCESS FULL | PART | 176 | 9856 | 938 (1)| 00:00:12 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PARTSUPP | 1 | 14 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK_PARTSUPP | 4 | | 2 (0)| 00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 61 | | |
| 13 | NESTED LOOPS | | 158K| 9438K| 4576 (1)| 00:00:55 |
|* 14 | HASH JOIN | | 160K| 8750K| 4566 (1)| 00:00:55 |
|* 15 | HASH JOIN | | 2000 | 84000 | 49 (5)| 00:00:01 |
| 16 | MERGE JOIN | | 5 | 175 | 6 (17)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ---------
------------------------------------------------------------------------------------------------------------
|* 17 | TABLE ACCESS BY INDEX ROWID| REGION | 1 | 29 | 2 (0)| 00:00:01 |
| 18 | INDEX FULL SCAN | PK_REGION | 5 | | 1 (0)| 00:00:01 |
|* 19 | SORT JOIN | | 25 | 150 | 4 (25)| 00:00:01 |
| 20 | TABLE ACCESS FULL | NATION | 25 | 150 | 3 (0)| 00:00:01 |
| 21 | VIEW | index$_join$_008 | 10000 | 70000 | 43 (3)| 00:00:01 |
|* 22 | HASH JOIN | | | | | |
| 23 | INDEX FAST FULL SCAN | PK_SUPPLIER | 10000 | 70000 | 26 (0)| 00:00:01 |
| 24 | INDEX FAST FULL SCAN | SUPP_NK | 10000 | 70000 | 26 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | PARTSUPP | 800K| 10M| 4514 (1)| 00:00:55 |
|* 26 | INDEX UNIQUE SCAN | PK_PART | 1 | 5 | 0 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PK_SUPPLIER | 1 | | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ---------
------------------------------------------------------------------------------------------------------------
| 28 | TABLE ACCESS BY INDEX ROWID | SUPPLIER | 1 | 143 | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | NATION | 25 | 800 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S_NATIONKEY"="N_NATIONKEY" AND "N_REGIONKEY"="R_REGIONKEY")
7 - filter("R_NAME"='ASIA')
9 - filter("P_SIZE"=50 AND "P_TYPE" LIKE '%COPPER' AND "P_TYPE" IS NOT NULL)
10 - filter("PS_SUPPLYCOST"= (SELECT MIN("PS_SUPPLYCOST") FROM "REGION" "REGION","NATION"
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ---------
------------------------------------------------------------------------------------------------------------
"NATION", (SELECT "S_NATIONKEY" "S_NATIONKEY","S_SUPPKEY" "S_SUPPKEY",ROWID "ROWID" FROM
"SUPPLIER" "indexjoin$_alias$_002","SUPPLIER" "indexjoin$_alias$_001" WHERE ROWID=ROWID)
"SUPPLIER","PART" "PART","PARTSUPP" "PARTSUPP" WHERE "S_SUPPKEY"="PS_SUPPKEY" AND
"P_PARTKEY"="PS_PARTKEY" AND "S_NATIONKEY"="N_NATIONKEY" AND "N_REGIONKEY"="R_REGIONKEY" AND
"R_NAME"='ASIA'))
11 - access("P_PARTKEY"="PS_PARTKEY")
14 - access("S_SUPPKEY"="PS_SUPPKEY")
15 - access("S_NATIONKEY"="N_NATIONKEY")
17 - filter("R_NAME"='ASIA')
19 - access("N_REGIONKEY"="R_REGIONKEY")
filter("N_REGIONKEY"="R_REGIONKEY")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ---------
------------------------------------------------------------------------------------------------------------
22 - access(ROWID=ROWID)
26 - access("P_PARTKEY"="PS_PARTKEY")
27 - access("S_SUPPKEY"="PS_SUPPKEY")
Kindly help me in getting the desired plan. I am working on Query ERT prediction model. This need is part of building my model. In fact, I need good documentation on hints which can be used to get desired plan for any complex query (involving 10-12 joins) like reporting queries etc.
I really appreciate any help in this regard.
Warm Regards
Rekha Singhal
--moderator update: added [code] tags
[Updated on: Sat, 22 March 2014 18:53] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Use of Hints in Oracle 11g to get desired Execution Plan [message #610693 is a reply to message #610537] |
Sat, 22 March 2014 19:20  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
There is a good deal of confusion.
1. Are you saying that you are using a query plan prediction tool and that this tool predicated the first plan you show? If so I would like to see that tool because that is a masterful query rewrite it is looking for. A rewrite that only about 1% of the DBA and Developer community would know to look for.
2. The first query plan is incomplete, at least it appears so to me. It does not show the ps_supplycost criteria being used to filter rows anywhere.
3. The first query plan also looks wrong. It appears that the window sort is being done on rows filtered by the predicates p_size = 50 and p_type like '%COPPER' but that is not what the query is asking for. Seems to me the analytic should be working on all rows from ASIA regardless of p_size and p_type since that is how the query is coded.
The first query plan is taking advantage of the overlap in rows between what you seek and what the nested select is using to get its min. You will notice please that the main query and the sub-query both look at the same tables and do the same joins. Additionally the sub-query is a SUPERSET of the rows from the main query. This allows for the use of a query rewrite that employs an analytic to do the filtering based on the aggregate value, rather than having to go back to the same data twice. You can easily do this yourself with the following rewrite of the query.
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from (
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment,
p_size,
p_type
from
part,
supplier ,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = min(ps_supplycost) over (partition by p_partkey,s_suppkey,s_nationkey,n_regionkey,r_name)
)
where
p_size = 50
and p_type like '%COPPER'
/
Notice the use of the analytic to achieve the MIN() and corresponding filtering, without doing a second pass of the data and all those joins. Because of all the joins, there is potential for this rewrite to be substantially faster than the traditional query plan would be. So try the rewrite and see what happens.
It is also quite likely that your query is wrong or that you have shown us the wrong query. This is because it is unusual for an analytic to be computed on a superset of rows. Normally it is computed on the same set of you ultimately display (which is what the first query plan is suggesting). Please check the correctness of your query.
And... to my knowledge, Oracle has no database version that will automatically do this rewrite of your query to the analytic variation. This is a rewrite that so far must be done manually by a Developer or DBA. This is one reason why I am so keen to know how you got the first query plan.
Lastly, please tell us where you actually got the first query plan from and please look at it to confirm or not if you believe the first query plan is in fact wrong or if you missed something in a cut/paste somewhere.
Kevin
|
|
|
|
| Re: Use of Hints in Oracle 11g to get desired Execution Plan [message #610737 is a reply to message #610693] |
Sun, 23 March 2014 21:54 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Wait a moment please. I see I made a slight error in my translation of the query. It in fact is doing what the first query plan suggests and which makes the most sense. My mistake was in incorrectly including the PART table in the sub-query. Since it is not there in your original query, the use of P_PARTKEY in the sub-query correlates the rows in the sub-query to the PART table rows of main query. Thus the p_size and p_type predicates are applied to the sub-query rows as well and we in turn are looking to do the analytic on the same rows as the main query is fetching. Hence this rewrite is what you are getting the first query plan for.
In my defense, I would point out that part of the problem of your query getting different plans is the fact that you have two different queries. the first query you show is different from the second one in the table list of the sub-query. I only noticed this just now. So I have provided the rewrite for the first query here into its analytic version.
As for what I said about the first query plan being wrong because it does not show the MIN() being used to filter, I am going to assume the filter predicate 2 - filter("VW_COL_9" IS NOT NULL) is taking care of this filtering in some obscured manner and thus the query plan is OK.
Now you have to figure out:
1. if someone actually ran this query rewrite or
2. how this masterful query rewrite was done because I have not seen this optimization in any version of Oracle yet. I will try to reproduce it.
3. or tell us that a tool suggested this optimization in which case what was the tool.
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier ,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and p_size = 50
and p_type like '%COPPER'
and ps_supplycost = min(ps_supplycost) over (partition by p_partkey)
/
If I were to interpret this query I would say it is asking for
Quote:the list of least expensive copper parts of size 50 from ASIA.
Kevin
[Updated on: Sun, 23 March 2014 22:07] Report message to a moderator |
|
|
|
| Re: Use of Hints in Oracle 11g to get desired Execution Plan [message #610738 is a reply to message #610737] |
Sun, 23 March 2014 22:20 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Hi again. So I am wrong. I appears Oracle does do this query rewrite. Awesome. I did the following on a 11gR2 (11.2.0.1.0) on windows 7.
create table part (p_partkey integer not null, p_size integer not null,p_type varchar2(30) not null);
create table partsupp (ps_partkey integer not null,ps_suppkey integer not null,ps_supplycost integer not null);
create table supplier (s_suppkey integer not null,s_nationkey integer not null);
create table nation (n_nationkey integer not null,n_regionkey integer not null);
create table region (r_regionkey integer not null,r_name varchar2(30) not null);
explain plan for select *
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 50
and p_type like '%COPPER'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
)
/
SELECT * FROM table(DBMS_XPLAN.DISPLAY)
/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 104864306
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 176 | 12 (17)| 00:00:01 |
|* 1 | VIEW | VW_WIF_1 | 1 | 176 | 12 (17)| 00:00:01 |
| 2 | WINDOW SORT | | 1 | 176 | 12 (17)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 176 | 11 (10)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 125 | 9 (12)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 82 | 7 (15)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN| | 1 | 56 | 4 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | REGION | 1 | 30 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SUPPLIER | 1 | 26 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | NATION | 1 | 26 | 2 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 1 | 43 | 7 (15)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | PART | 1 | 43 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | PARTSUPP | 1 | 51 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_13" IS NOT NULL)
3 - access("S_SUPPKEY"="PS_SUPPKEY" AND "P_PARTKEY"="PS_PARTKEY")
5 - access("N_REGIONKEY"="R_REGIONKEY" AND "S_NATIONKEY"="N_NATIONKEY")
7 - filter("R_NAME"='ASIA')
12 - filter("P_SIZE"=50 AND "P_TYPE" LIKE '%COPPER')
Note
-----
- dynamic sampling used for this statement (level=2)
33 rows selected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
How beautiful is that eh!
Thanks for sharing your problem. The reason your second query does not give the original query plan is because you have included the PART table in the sub-query. At least I am guessing that is the reason. Run the same query as before (as I did) and you should get the analytic rewrite.
Kevin
[Updated on: Sun, 23 March 2014 22:23] Report message to a moderator |
|
|
|
| Re: Use of Hints in Oracle 11g to get desired Execution Plan [message #610741 is a reply to message #610738] |
Mon, 24 March 2014 00:29 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
FYI, one additional update.
It appears that this specific query rewrite was introduced in 10g. Using the OPTIMIZER_FEATURES_ENABLE hint, we are able to downgrade available optimizer features on an 11gR2 instance to simulate other prior releases.
11.2.0.1 query rewrite happens.
10.2.0.3 query rewrite happens.
9.2.0.8 query rewrite does NOT happen.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 22:19:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
