hints on hints - oracle scratchpad | just another oracle · 4 hints on hints 7 / 78 jonathan...
TRANSCRIPT
1
Hints (definition 1)
on
Hints (definition 2)
Jonathan Lewiswww.jlcomp.demon.co.uk
jonathanlewis.wordpress.com
Hints on Hints
2 / 78
Jonathan Lewis
© 2008 - 2009
Who am I ?
Independent Consultant.
23+ years in IT20+ using Oracle
Strategy, Design, ReviewBriefings, SeminarsTrouble-shooting
www.jlcomp.demon.co.ukjonathanlewis.wordpress.com
One of the directors of the UKOUGMember of the Oak Table Network.Oracle Author of the year 2006“Select” Editor’s choice 2007
2
Hints on Hints
3 / 78
Jonathan Lewis
© 2008 - 2009
Highlights
The rules of hinting
What are hints.
Query Blocks
The meaning of hints
Strategic hints
How to get lucky
Hints on Hints
4 / 78
Jonathan Lewis
© 2008 - 2009
Rules of hinting
• Don’t
– Unless you really have to
• If you do hint, stick to the ‘strategic’ hints
– and assume you’ve got it wrong
• Test hinted SQL on every patch or upgrade
• Test it after every piece of associated DDL
3
Hints on Hints
5 / 78
Jonathan Lewis
© 2008 - 2009
Effects of hints
• Modify the optimizer’s choice of actions
– E.g. /*+ use_nl() */ /*+ no_merge() */
• Change the optimizer arithmetic
– E.g. /*+ cardinality() */ /*+ opt_estimate() */
• A few special cases (run-time / feature)
– E.g. /*+ append */ /*+ no_qkn_buff */
Hints on Hints
6 / 78
Jonathan Lewis
© 2008 - 2009
Dangers of hints
• Oracle MUST obey your hints
– Unless you’ve got the syntax wrong
– Or you’ve got the spelling wrong
– Or you’ve got the context wrong
– Or the hint is illegal
– Or you have contradictory hints in one statement
• The hints are very badly documented !
– There are 236 hints in 11.1.0.6 (how many are documented ?)
– select * from v$sql_hint
4
Hints on Hints
7 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 1
create table t1(
n1 number,
n2 number,
n3 number,
constraint t1p primary key(n1),
constraint t1u unique (n2)
);
create bitmap index t1b on t1(n3);
How many ways could the optimizer choose to execute:
select count(*) from t1;
Hints on Hints
8 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 2
select /*+ full (t1) */ count(*) from t1;
TABLE ACCESS (FULL) OF 'T1'
select /*+ index (t1,t1p) */ count(*) from t1;
INDEX (FULL SCAN) OF 'T1P' (UNIQUE)
select /*+ index_ffs(t1,t1p) */ count(*) from t1;
INDEX (FAST FULL SCAN) OF 'T1P' (UNIQUE)
select /*+ index (t1,t1b) */ count(*) from t1;
BITMAP INDEX (FULL SCAN) OF 'T1B'
select /*+ index_ffs(t1,t1b) */ count(*) from t1;
BITMAP INDEX (FAST FULL SCAN) OF 'T1B' -- not 8.1
5
Hints on Hints
9 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 3
Is the optimizer ignoring hints ?
select /*+ index(t1,t1u) */ count(*) from t1;
BITMAP INDEX (FAST FULL SCAN) OF 'T1B'
select /*+ index(t1,t1u) */ count(*) from t1
where n2 is not null;
INDEX (FULL SCAN) OF 'T1U' (UNIQUE)
alter table t1 modify n2 not null;
select /*+ index(t1,t1u) */ count(*) from t1
INDEX (FULL SCAN) OF 'T1U' (UNIQUE)
Hints on Hints
10 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 4
alter session set "_optimizer_ignore_hints"=true;
/*+ IGNORE_OPTIM_EMBEDDED_HINTS */
And from one 10053 trace file I got:
Trying or-Expansion on query block SEL$1 (#0)
USE_CONCAT hint was ignored.
atom_hint=(@=221EAF74 err=0 resol=1 used=1
token=921 org=1 lvl=2 txt=USE_CONCAT (8) )
6
Hints on Hints
11 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 8
select /*+ use_concat */
small_vc
from t1
where id = 100 or id = 500;
Execution plan 8i and early 9i style.
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=32)
CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=16)
INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=16)
INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=1)
Hints on Hints
12 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 10
select /*+ use_concat */
small_vc
from t1
where id = 100 or id = 500;
Execution plan 10g .
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 2 | 32 | 4 |
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 32 | 4 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 2 | | 3 |
Predicate Information (identified by operation id):
3 - access("ID"=100 OR "ID"=500)
7
Hints on Hints
13 / 78
Jonathan Lewis
© 2008 - 2009
Nobody ignores me !
select
/*+
begin_outline_data
qb_name(main)
use_concat(@main)
outline(@main) outline_leaf(@main)
outline_leaf(@main_1) outline_leaf(@main_2)
index_rs_asc(@main_1 t1@main(t1.id))
index_rs_asc(@main_2 t1@main_2(t1.id))
end_outline_data
*/
small_vc
from t1
where id = 100 or id = 500; use_concat(1) also worked – all by itself
Hints on Hints
14 / 78
Jonathan Lewis
© 2008 - 2009
Ignoring hints – 10
Execution plan 10g .
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 2 | 32 | 4 |
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 16 | 2 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 1 | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 16 | 2 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 1 | | 1 |
Query Block Name / Object Alias (identified by operation id):
1 - MAIN
2 - MAIN_1 / T1@MAIN
3 - MAIN_1 / T1@MAIN
4 - MAIN_2 / T1@MAIN_2
5 - MAIN_2 / T1@MAIN_2
8
Hints on Hints
15 / 78
Jonathan Lewis
© 2008 - 2009
Use of /*+ qb_name() */ (a)
select /*+ qb_name(main) */
{columns}
from t1, t3
where t1.n2 = 15
and exists (select /*+ qb_name(subq2) */ null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
and t3.n1 = t1.n1
and t3.n2 = 15
and exists (select /*+ qb_name(subq4) */ null
from t4
where t4.n1 = 15
and t4.id = t3.id
)
Hints on Hints
16 / 78
Jonathan Lewis
© 2008 - 2009
Use of /*+ qb_name() */ (b)
select
/*+
qb_name(main)
unnest(@subq2)
no_unnest(@subq4)
no_push_subq(@subq4)
leading(t1@main, t3@main, t2@subq2)
index(@subq4 t4@subq4(id))
*/
{columns}
from t1, t3
where t1.n2 = 15
and exists (select /*+ qb_name(subq2) */ ... t2 ...)
and t3.n1 = t1.n1
and t3.n2 = 15
and exists (select /*+ qb_name(subq4) */ ... t4 ...)
9
Hints on Hints
17 / 78
Jonathan Lewis
© 2008 - 2009
Use of /*+ qb_name() */ (c)
.
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | 1 |
| 1 | FILTER | | |
| 2 | NESTED LOOPS SEMI | | 3 |
| 3 | HASH JOIN | | 173 |
| 4 | TABLE ACCESS FULL | T1 | 157 |
| 5 | TABLE ACCESS FULL | T3 | 157 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 |
| 7 | INDEX UNIQUE SCAN | T2_PK | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | T4 | 1 |
| 9 | INDEX UNIQUE SCAN | T4_PK | 1 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$38F5B5F8
4 - SEL$38F5B5F8 / T1@MAIN
5 - SEL$38F5B5F8 / T3@MAIN
6 - SEL$38F5B5F8 / T2@SUBQ2
7 - SEL$38F5B5F8 / T2@SUBQ2
8 - SUBQ4 / T4@SUBQ4
9 - SUBQ4 / T4@SUBQ4
Hints on Hints
18 / 78
Jonathan Lewis
© 2008 - 2009
Use of /*+ qb_name() */ (d)
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
OPT_PARAM('_optimizer_cost_model' 'io')
ALL_ROWS
OUTLINE(@"SUBQ4")
OUTLINE(@"MAIN")
OUTLINE(@"SUBQ2")
OUTLINE_LEAF(@"SUBQ4")
OUTLINE_LEAF(@"SEL$38F5B5F8")
UNNEST(@"SUBQ2")
FULL(@"SEL$38F5B5F8" "TA"@"MAIN")
FULL(@"SEL$38F5B5F8" "TC"@"MAIN")
INDEX_RS_ASC(@"SEL$38F5B5F8" "TB"@"SUBQ2" ("TB"."ID"))
LEADING(@"SEL$38F5B5F8" "TA"@"MAIN" "TC"@"MAIN" "TB"@"SUBQ2")
USE_HASH(@"SEL$38F5B5F8" "TC"@"MAIN")
USE_NL(@"SEL$38F5B5F8" "TB"@"SUBQ2")
INDEX_RS_ASC(@"SUBQ4" "TD"@"SUBQ4" ("TD"."ID"))
END_OUTLINE_DATA
*/
index_rs_asc() (and related hints) appeared in
10g specifically to deal with problems where
the index() hint resulted in index full scans.
10
Hints on Hints
19 / 78
Jonathan Lewis
© 2008 - 2009
Use of /*+ qb_name() */ (e)
explain plan for ...
select * from table(dbms_xplan.display);
select *
from table(dbms_xplan.display(null,null,'{options}'));
select *
from table(
dbms_xplan.display(
null,
null,
'outline alias -cost -bytes'
)
)
;
Hints on Hints
20 / 78
Jonathan Lewis
© 2008 - 2009
dbms_xplan (10gR2)
-----------------------------------------------------------------------------------
|Id |Operation |Name |Starts |E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.07 | 1540 |
| 2 | VIEW | | 1 | 167 | 9998 |00:00:00.10 | 1540 |
|* 3 | FILTER | | 1 | | 9998 |00:00:00.07 | 1540 |
| 4 | TABLE ACCESS FULL |EMP | 1 | 20000 | 20000 |00:00:00.04 | 220 |
| 5 | SORT AGGREGATE | | 6 | 1 | 6 |00:00:00.03 | 1320 |
|* 6 | TABLE ACCESS FULL|EMP | 6 | 3333 | 20000 |00:00:00.04 | 1320 |
-----------------------------------------------------------------------------------
set serveroutput off
select /*+ gather_plan_statistics */ ...
select *
from table(dbms_xplan.display_cursor((null, null,'ALLSTATS LAST'));
You need the privileges to access:
v$session, v$sql, v$sql_plan_statistics(_all)
11
Hints on Hints
21 / 78
Jonathan Lewis
© 2008 - 2009
What do hints mean (a)
select
/*+ ordered */
t1.v1
from t1, t3
where t3.n1 = t1.n1
and exists (
select t2.id
from t2
where t2.n1 = 15
and t2.id = t1.id
)
and exists (
select t4.id
from t4
where t4.n1 = 15
and t4.id = t3.id
);
But what is the one join order for this query ?
What is the impact of the subqueries ?
When does the hint operate ?
Hints on Hints
22 / 78
Jonathan Lewis
© 2008 - 2009
What do hints mean (b)
This was the 8i execution path
FILTER
HASH JOIN
TABLE ACCESS (FULL) OF T1
TABLE ACCESS (FULL) OF T3
TABLE ACCESS (BY INDEX ROWID) OF T2
INDEX (RANGE SCAN) OF T2_N1 (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF T4
INDEX (RANGE SCAN) OF T4_N1 (NON-UNIQUE)
12
Hints on Hints
23 / 78
Jonathan Lewis
© 2008 - 2009
What do hints mean (c)
This was the 9i execution path
NESTED LOOPS
NESTED LOOPS
MERGE JOIN (CARTESIAN)
SORT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'T4'
INDEX (RANGE SCAN) OF 'T4_N1' (NON-UNIQUE)
BUFFER (SORT)
SORT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'T2'
INDEX (RANGE SCAN) OF 'T2_N1' (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'T1'
INDEX (UNIQUE SCAN) OF 'T1_UK' (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'T3'
INDEX (UNIQUE SCAN) OF 'T3_UK' (UNIQUE)
Hints on Hints
24 / 78
Jonathan Lewis
© 2008 - 2009
What do hints mean (d)
select
/*+ ordered */
t1.v1
from (
select distinct t4.id
from t4
where t4.n1 = 15
) v4,
(
select distinct t2.id
from t2
where t2.n1 = 15
) v2,
t1, t3
where t3.n1 = t1.n1
and t2.id = t1.id
and t4.id = t3.id
;
A hint applies to a specific query block, and the optimizer transformed the
query into a single query block before (in this case) applying the hint.
13
Hints on Hints
25 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (1)
select
/*+ noparallel(tabx) */
count(*)
from
TABX
;
This query may run parallel. Why ?
SELECT STATEMENT
SORT (AGGREGATE)
SORT* (AGGREGATE)
PARTITION RANGE* (ALL)
INDEX* (FULL SCAN) OF 'PT_PK' (UNIQUE)
You didn’t say: noparallel_index(TABX)
Hints on Hints
26 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (2a)
select /*+ parallel(t1,2) */
{list of columns}
from t1
where t1.n1 = 5
and t1.n2 = 10
and t1.pt_group in (0,10)
;
t1 is list-partitioned on pt_group,
with a local index on (n1, n2).
The query visits two partitions
.
|Id|Operation |Name |Cost |Pstart| Pstop|
| 0|SELECT STATEMENT | | 48| | |
| 1| PARTITION LIST INLIST | | 48|KEY(I)|KEY(I)|
|*2| TABLE ACCESS BY LOCAL INDEX ROWID|T1 | 48|KEY(I)|KEY(I)|
|*3| INDEX RANGE SCAN |T1_I1| 3|KEY(I)|KEY(I)|
14
Hints on Hints
27 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (2b)
select /*+ full(t1) parallel(t1,2) */
{list of columns}
from t1
where t1.n1 = 5
and t1.n2 = 10
and t1.pt_group in (0,10)
;.
| Id| Operation |Name |Cost |Pstart|Pstop | TQ |IN-OUT|PQ Distrib|
| 0| SELECT STATEMENT | | 94 | | | | | |
| 1| PX COORDINATOR | | | | | | | |
| 2| PX SEND QC (RANDOM)|:TQ10| 94 | | |Q1,00| P->S |QC (RAND) |
| 3| PX BLOCK ITERATOR | | 94 |KEY(I)|KEY(I)|Q1,00| PCWC | |
|* 4| TABLE ACCESS FULL|T1 | 94 |KEY(I)|KEY(I)|Q1,00| PCWP | |
Hints on Hints
28 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (2c)
select /*+ parallel(t1,4) */
{list of columns}
from t1
where t1.n1 = 5
and t1.n2 = 10
and t1.pt_group in (0,10)
;.
| Id| Operation |Name |Cost |Pstart|Pstop | TQ |IN-OUT|PQ Distrib|
| 0| SELECT STATEMENT | | 47 | | | | | |
| 1| PX COORDINATOR | | | | | | | |
| 2| PX SEND QC (RANDOM)|:TQ10| 47 | | |Q1,00| P->S |QC (RAND) |
| 3| PX BLOCK ITERATOR | | 47 |KEY(I)|KEY(I)|Q1,00| PCWC | |
|* 4| TABLE ACCESS FULL|T1 | 47 |KEY(I)|KEY(I)|Q1,00| PCWP | |
For indexed paths, the number of
slaves used will be a maximum of one
per partition – but for tablescans this
limit can be exceeded.
15
Hints on Hints
29 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (3a)
How can Oracle do this:
HASH JOIN
TABLE ACCESS (FULL) OF 'T1'
TABLE ACCESS (FULL) OF 'T2'
select /*+ use_nl(t2) */
t1.col1, t2.col2
from t1, t2
where t2.pkcol = t1.pkcol
and t2.spare_col = 99;
The tables seem to be in the
expected join order, using
the wrong join mechanism.
Hints on Hints
30 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (3b)
Join order[1]: T1 [T1] T2 [T2]
Now joining: T2[T2] *******
NL join
Join order[2]: T2 [T2] T1 [T1]
Now joining: T1[T1] *******
NL join
SM Join
HA Join -- (sides swapped)
From the 10053 trace file:
16
Hints on Hints
31 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (3c)
Join order[1]: T1 [T1] T2 [T2]
Now joining: T2[T2] *******
NL join
Join order[2]: T2 [T2] T1 [T1]
Now joining: T1[T1] *******
NL join
It’s a short-hand
for:
/*+
use_nl(t1)
use_nl(t2)
*/
select /*+ use_nl(t1 t2) */
t1.col1, t2.col2
from t1, t2
where t2.pkcol = t1.pkcol
and t2.spare_col = 99;
Why might this appear to work ?
Hints on Hints
32 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (3d)
select /*+
ordered -- table order
use_nl(t2) -- join method
full(t1) -- access method
index(t2(pkcol)) -- access method
*/
t1.col1,
t2.col2
from t1, t2
where t2.pkcol = t1.pkcol
and t2.spare_col = 99;
The correct hints
Note the 10g style of index hint –
which lists, in order, the starting
columns of a suitable index.
17
Hints on Hints
33 / 78
Jonathan Lewis
© 2008 - 2009
Precision matters (4)
...
NESTED LOOP
TABLE ACCESS (FULL) OF T1
TABLE ACCESS BY INDEX ROWID T2
INDEX RANGE SCAN T2_IND_SECOND -- “wrong” index
...
select /*+ index(t2 t2_ind_first) */ -- hint the “right” one
...
HASH JOIN -- wrong method
TABLE ACCESS (FULL) OF T1
TABLE ACCESS BY INDEX ROWID T2
INDEX FULL SCAN T2_IND_FIRST -- “right” index
...
select /*+ index_rs_asc(t2 t2_ind_first) */ -- the right hint
Hints on Hints
34 / 78
Jonathan Lewis
© 2008 - 2009
Plans and join order (1)
select
/*+
ordered full(t4)
use_hash(t1) full(t1)
use_hash(t2) full(t2)
use_hash(t3) full(t3)
*/
count(t1.colx), count(t2.coly), ...
from
t4, t1, t2, t3
where
t1.id = t4.id1
and t2.id = t4.id2
and t3.id = t4.id3
;
How many different (serial) executions
plans are there that obey this set of hints ?
18
Hints on Hints
35 / 78
Jonathan Lewis
© 2008 - 2009
Plans and join order (2)
N – 1 simultaneous hash tables
Maximum two simultaneous hash tables
HASH JOIN
TABLE ACCESS (FULL) OF TABLE_3
HASH JOIN
TABLE ACCESS (FULL) OF TABLE_2
HASH JOIN
TABLE ACCESS (FULL) OF TABLE_1
TABLE ACCESS (FULL) OF TABLE_4
HASH JOIN
HASH JOIN
HASH JOIN
TABLE ACCESS (FULL) OF TABLE_4
TABLE ACCESS (FULL) OF TABLE_1
TABLE ACCESS (FULL) OF TABLE_2
TABLE ACCESS (FULL) OF TABLE_3
Hints on Hints
36 / 78
Jonathan Lewis
© 2008 - 2009
Plans and join order (3)
select
/*+
ordered full(t4)
use_hash(t1) full(t1) swap_join_inputs(t1)
use_hash(t2) full(t2) swap_join_inputs(t2)
use_hash(t3) full(t3) swap_join_inputs(t3)
*/
count(t1.colx), count(t2.coly), ...
from
t4, t1, t2, t3
where
t1.id = t4.id1
and t2.id = t4.id2
and t3.id = t4.id3
;
19
Hints on Hints
37 / 78
Jonathan Lewis
© 2008 - 2009
Plans and join order (4)
This join order really was: t4, t1, t2, t3
HASH JOIN
TABLE ACCESS (FULL) OF 'T3'
HASH JOIN
TABLE ACCESS (FULL) OF 'T2'
HASH JOIN
TABLE ACCESS (FULL) OF 'T1'
TABLE ACCESS (FULL) OF 'T4'
A hash join is always allowed to swap the order of its join inputs.
It takes two hints to specify a hash join completely. 9i gave us
/*+ swap_join_inputs(second_table)*/
And 10g finished the job with:
/*+ no_swap_join_inputs(second_table)*/
Hints on Hints
38 / 78
Jonathan Lewis
© 2008 - 2009
Plans and join order (5)
Join order in from clause: T4 T1 T2 T3
Now joining T1: (T4, T1)
swap_join_inputs(t1) (T1, T4)
Now joining T2: ((T1, T4), T2)
swap_join_inputs(t2) (T2, (T1, T4))
Now joining T3: ((T2, (T1, T4)), T3)
swap_join_inputs(t3) (T3, (T2, (T1, T4)))
20
Hints on Hints
39 / 78
Jonathan Lewis
© 2008 - 2009
The story so far
• “Simple” hints aren’t so simple
• You need lots more hints than you think
• If you manage to get the plan you want –
– Use dbms_xplan(null,null,’outline’) to get the full set of
hints, and use the entire list into your SQL.
• And put back any qb_name and parallelism hints
Hints on Hints
40 / 78
Jonathan Lewis
© 2008 - 2009
Experimenting (a)
select /*+
parallel(t1 3) parallel(t2 3)
full(t1) full(t2)
use_hash(t2) pq_distribute(t2 hash hash)
*/
t1.padding, t2.padding
from t1, t2
where t2.n1 = t1.n1
and t2.small_vc = t1.small_vc
Execution plan 9.2.0.8 .
| Id| Operation | Name| Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
| 0| SELECT STATEMENT | | 2 | 1444 | 164 | | | |
|* 1| HASH JOIN | | 2 | 1444 | 164 | 98,02 | P->S | QC (RAND) |
| 2| TABLE ACCESS FULL| T1 |10325 | 2127K| 24 | 98,00 | P->P | HASH |
| 3| TABLE ACCESS FULL| T2 |20212 | 9M| 118 | 98,01 | P->P | HASH |
This spilled to disk – “unnecessarily”.
21
Hints on Hints
41 / 78
Jonathan Lewis
© 2008 - 2009
Experimenting (b)
Q002
Hash join
Q000, Q001
Scan para_1, para_2
Co-ordinator
Slave P001 Slave P002Slave P000
Slave P004 Slave P005Slave P003
Total number of rows in in-memory partitions: 5029
(used as preliminary number of buckets in hash table)
Estimated max # of build rows that can fit in avail memory: 78676
### Partition Distribution ###
Partition:0 rows:618 clusters:1 slots:1 kept=1
. . .
Partition:7 rows:610 clusters:1 slots:1 kept=1
Hints on Hints
42 / 78
Jonathan Lewis
© 2008 - 2009
Experimenting (c)
Execution plan 10.2.0.3 .
|Id|Operation | Name | Rows |Bytes | Cost | TQ |IN-OUT| PQ Distrib |
| 0|SELECT STATEMENT | | 2| 1460 | 181 | | | |
| 1| PX COORDINATOR | | | | | | | |
| 2| PX SEND QC (RANDOM) | :TQ10002| 2| 1460 | 181 | Q1,02 | P->S | QC (RAND) |
|*3| HASH JOIN BUFFERED | | 2| 1460 | 181 | Q1,02 | PCWP | |
| 4| PX RECEIVE | | 10000| 2099K| 28 | Q1,02 | PCWP | |
| 5| PX SEND HASH | :TQ10000| 10000| 2099K| 28 | Q1,00 | P->P | HASH |
| 6| PX BLOCK ITERATOR | | 10000| 2099K| 28 | Q1,00 | PCWC | |
| 7| TABLE ACCESS FULL| T1 | 10000| 2099K| 28 | Q1,00 | PCWP | |
| 8| PX RECEIVE | | 20000| 9M| 131 | Q1,02 | PCWP | |
| 9| PX SEND HASH | :TQ10001| 20000| 9M| 131 | Q1,01 | P->P | HASH |
|10| PX BLOCK ITERATOR | | 20000| 9M| 131 | Q1,01 | PCWC | |
|11| TABLE ACCESS FULL| T2 | 20000| 9M| 131 | Q1,01 | PCWP | |
The complete result set for the join is “buffered” before being forwarded to the query co-ordinator.
Even though the hash table fits in memory, the result set does not – so it spilled to temp and was re-read.
22
Hints on Hints
43 / 78
Jonathan Lewis
© 2008 - 2009
Experimenting (d)
Execution plan 10.2.0.3 .
|Id|Operation | Name | Rows |Bytes | Cost | TQ |IN-OUT| PQ Distrib |
| 0|SELECT STATEMENT | | 2| 1460 | 181 | | | |
| 1| PX COORDINATOR | | | | | | | |
| 2| PX SEND QC (RANDOM) | :TQ10002| 2| 1460 | 181 | Q1,02 | P->S | QC (RAND) |
|*3| HASH JOIN | | 2| 1460 | 181 | Q1,02 | PCWP | |
| 4| PX RECEIVE | | 10000| 2099K| 28 | Q1,02 | PCWP | |
| 5| PX SEND HASH | :TQ10000| 10000| 2099K| 28 | Q1,00 | P->P | HASH |
| 6| PX BLOCK ITERATOR | | 10000| 2099K| 28 | Q1,00 | PCWC | |
| 7| TABLE ACCESS FULL| T1 | 10000| 2099K| 28 | Q1,00 | PCWP | |
| 8| PX RECEIVE | | 20000| 9M| 131 | Q1,02 | PCWP | |
| 9| PX SEND HASH | :TQ10001| 20000| 9M| 131 | Q1,01 | P->P | HASH |
|10| PX BLOCK ITERATOR | | 20000| 9M| 131 | Q1,01 | PCWC | |
|11| TABLE ACCESS FULL| T2 | 20000| 9M| 131 | Q1,01 | PCWP | |
The hint list (in 11g) showed: /*+ no_qkn_buff */ - “No Query Ko-ordiNator BUFFering” ?
The QC jammed on “PX Deq: Execute reply” – 2 second timeout
Lower slaves jammed on “PX Deq: Execution Msg” – 1/10 second timeout
Upper slaves jammed on “PX Deq: Send blkd” – 2 second timeout
Hints on Hints
44 / 78
Jonathan Lewis
© 2008 - 2009
Experimenting (e)
/*+ pq_distribute(t2 broadcast none) */
Execution plan 9.2.0.8 .
| Id| Operation | Name| Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
| 0| SELECT STATEMENT | | 2 | 1444 | 163 | | | |
|* 1| HASH JOIN | | 2 | 1444 | 163 | 16,01 | P->S | QC (RAND) |
| 2| TABLE ACCESS FULL| T1 |10000 | 2060K| 24 | 16,00 | P->P | BROADCAST |
| 3| TABLE ACCESS FULL| T2 |20000 | 9980K| 118 | 16,01 | PWCP | |
Q001
Scan para_2 & join
Q000
Scan para_1
Co-ordinator
Slave P001 Slave P002Slave P000
Slave P004 Slave P005Slave P003
23
Hints on Hints
45 / 78
Jonathan Lewis
© 2008 - 2009
“Feature” Hints
More common in 11g
Switch off new features (matched to parameters)
Probably safe to use
For example:
cursor_sharing_exact bypasses cursor_sharing = ...
no_set_to_join disable set to join conversion
no_use_hash_aggregation disable hash aggregation
no_eliminate_join disable join elimination
no_eliminate_oby disable “order by” elimination
opt_param('parameter','value')
Hints on Hints
46 / 78
Jonathan Lewis
© 2008 - 2009
“Strategic” Hints
Shape the query, without forcing details:
unnest / no_unnest whether to transform subqueries
push_subq / no_push_subq when to run subqueries
merge / no_merge control handling of complex views
push_pred / no_push_pred how to handle non-mergeable views
driving_site() where to run a distributed query
Basically these are hints about query blocks
24
Hints on Hints
47 / 78
Jonathan Lewis
© 2008 - 2009
Unnesting (a)
select
outer.*
from
emp outer
where
outer.sal > (
select
/*+ unnest redundant for 9i */
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
;
Hints on Hints
48 / 78
Jonathan Lewis
© 2008 - 2009
Unnesting (b)
select dept_no, avg(sal) av_sal
from emp
group by dept_no
select -- notional transformation (9i)
outer.*
from
(
select dept_no, avg(sal) av_sal
from emp
group by dept_no
) inner,
emp outer
where
outer.dept_no = inner.dept_no
and outer.sal > inner.av_sal;
25
Hints on Hints
49 / 78
Jonathan Lewis
© 2008 - 2009
Unnesting (c)
select -- investigated transformation (10g)
outer.dept_no dept_no, outer.sal sal,
outer.emp_no emp_no, outer.padding padding
from
test_user.emp inner, test_user.emp outer
where
inner.dept_no = outer.dept_no
group by
inner.dept_no, outer.rowid,
outer.padding, outer.emp_no,
outer.sal, outer.dept_no
having
outer.sal > avg(inner.sal)
Hints on Hints
50 / 78
Jonathan Lewis
© 2008 - 2009
Subquery caching (a)
select
/*+ qb_name(main) */
outer.*
from
emp outer
where
outer.sal > (
select
/*+ no_unnest qb_name(subq)*/
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
;
26
Hints on Hints
51 / 78
Jonathan Lewis
© 2008 - 2009
Subquery caching (b)
select
outer.*
from (
select /*+ no_merge */
emp1.*
from emp emp1
order by
emp1.dept_no
) outer
where
outer.sal > (
select /*+ no_unnest */
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
;
select
emp1.*
from emp emp1
order by
emp1.dept_no
Hints on Hints
52 / 78
Jonathan Lewis
© 2008 - 2009
Subquery caching (c)
select /*+ no_merge(@ordered) no_eliminate_oby(@ordered) */
outer.*
from (
select /*+ qb_name(ordered) */
emp1.*
from emp emp1
order by
emp1.dept_no
) outer
where
outer.sal > (
select /*+ no_unnest */
avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
)
;
27
Hints on Hints
53 / 78
Jonathan Lewis
© 2008 - 2009
Subquery pushdown (1)
select
/*+ ordered */
t1.v1
from t1, t3
where t1.n2 = 15
/*
and exists (select --+ no_unnest qb_name(subq2)
null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
*/
and t3.n1 = t1.n1
and t3.n2 = 15
;
Hints on Hints
54 / 78
Jonathan Lewis
© 2008 - 2009
Subquery pushdown (2)
Execution Plan 9.2.0.8 – without subquery .
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 173 | 3979 | 109 |
|* 1 | HASH JOIN | | 173 | 3979 | 109 |
|* 2 | TABLE ACCESS FULL | T1 | 157 | 2355 | 54 |
|* 3 | TABLE ACCESS FULL | T3 | 157 | 1256 | 54 |
Execution Plan 9.2.0.8 – with subquery .
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 9 | 252 | 127 |
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN | | 9 | 252 | 109 |
|* 3 | TABLE ACCESS FULL | T1 | 8 | 160 | 54 |
|* 4 | TABLE ACCESS FULL | T3 | 157 | 1256 | 54 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 |
28
Hints on Hints
55 / 78
Jonathan Lewis
© 2008 - 2009
Subquery pushdown (3)
select
/*+ ordered push_subq */ -- 9i use of hint
/*+ ordered push_subq(@subq2) */ -- 10g use of hint
/*+ no_unnest(@subq2) */ -- 10g use of hint
t1.v1
From t1, t3
Where t1.n2 = 15
and exists (select --+ no_unnest qb_name(subq2) push_subq
null
from t2
where t2.n1 = 15
and t2.id = t1.id
)
and t3.n1 = t1.n1
and t3.n2 = 15
;
Alternative 10g use of hint.
Hints on Hints
56 / 78
Jonathan Lewis
© 2008 - 2009
Subquery pushdown (4)
Execution Plan 10.2.0.3 – without pushing .
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 28 | 369 (2)| 00:00:05 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 173 | 4844 | 195 (2)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T1 | 157 | 3140 | 97 (2)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | T3 | 157 | 1256 | 97 (2)| 00:00:02 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |
Execution Plan 10.2.0.3 – pushed .
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
| 0 | SELECT STATEMENT | | 9 | 252 | 197 (2)| 00:00:03 |
|* 1 | HASH JOIN | | 9 | 252 | 195 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL (filter) | T1 | 8 | 160 | 97 (2)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T3 | 157 | 1256 | 97 (2)| 00:00:02 |
29
Hints on Hints
57 / 78
Jonathan Lewis
© 2008 - 2009
Non-mergeable views (a)
Customers
Orders
Suppliers
Products
Order_lines
List all orders supplied to
customers in London that
contain products from
suppliers in Leeds
There may not be many of
them, but there is no
efficient way to find them.
The only filters are at the
ends of a five table join
Hints on Hints
58 / 78
Jonathan Lewis
© 2008 - 2009
Non-mergeable views (b)
Customers
Orders
Suppliers
Products
Order_lines
select /*+ ordered
use_hash(prdsup) swap_join_inputs(prdsup)
use_hash(cusord) swap_join_inputs(cusord)
*/ cusord.*
from
order_lines orl,
(
select /*+ no_merge */ prd.id
from products prd, suppliers sup
where sup.location = 'Leeds'
and prd.supp_id = sup.id
) prdsup,
(
select /*+ no_merge */ ord.*
from customers cus, orders ord
where cus.location = 'London'
and ord.cust_id = cus.id
) cusord
where orl.order_id = cusord.id
and prdsup.id = orl.product_id;
30
Hints on Hints
59 / 78
Jonathan Lewis
© 2008 - 2009
Non-mergeable views (c)
1 HASH JOIN
2 VIEW -- (1) hash this view
3 HASH JOIN
4 TABLE ACCESS (FULL) OF 'CUSTOMERS'
5 TABLE ACCESS (FULL) OF 'ORDERS'
6 HASH JOIN
7 VIEW -- (2) hash this view
8 HASH JOIN
9 TABLE ACCESS (FULL) OF 'SUPPLIERS'
10 TABLE ACCESS (FULL) OF 'PRODUCTS'
11 TABLE ACCESS (FULL) OF 'ORDER_LINES‘ -- (3) scan this table
Order lines are passed through the products/suppliers hash, then
through the customers/orders hash. This could use a lot of memory.
Hints on Hints
60 / 78
Jonathan Lewis
© 2008 - 2009
Bushy tree
31
Hints on Hints
61 / 78
Jonathan Lewis
© 2008 - 2009
Left-deep tree
Hints on Hints
62 / 78
Jonathan Lewis
© 2008 - 2009
Pushing Join Predicates (a)
create or replace view v1 as
select
t2.id1, t2.id2, t3.small_vc, t3.padding
from t2, t3
where t3.id1 = t2.id1
and t3.id2 = t2.id2
;
select /*+ push_pred(v1) */
t1.*, v1.*
from t1, v1
where v1.id1(+) = t1.id1
and t1.n1 = 5
;
32
Hints on Hints
63 / 78
Jonathan Lewis
© 2008 - 2009
Pushing Join Predicates (b)
Plan before predicate pushing .| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 50 | 10200 | 55 |
|* 1 | HASH JOIN OUTER | | 50 | 10200 | 55 |
|* 2 | TABLE ACCESS FULL | T1 | 10 | 1190 | 10 |
| 3 | VIEW | V1 | 25000 | 2075K| 44 |
| 4 | NESTED LOOPS | | 25000 | 3149K| 44 |
| 5 | TABLE ACCESS FULL| T3 | 25000 | 2929K| 44 |
|* 6 | INDEX UNIQUE SCAN| T2_PK | 1 | 9 | |
Predicate Information (identified by operation id):
1 - access("V1"."ID1"(+)="T1"."ID1")
2 - filter("T1"."N1"=5)
6 - access("T3"."ID1"="T2"."ID1" AND "T3"."ID2"="T2"."ID2")
Hints on Hints
64 / 78
Jonathan Lewis
© 2008 - 2009
Pushing Join Predicates (c)
Plan with predicate pushing .| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 50 | 10300 | 40 |
| 1 | NESTED LOOPS OUTER | | 50 | 10300 | 40 |
|* 2 | TABLE ACCESS FULL | T1 | 10 | 1190 | 10 |
| 3 | VIEW PUSHED PREDICATE | V1 | 1 | 87 | 3 |
| 4 | NESTED LOOPS | | 1 | 133 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 5 | 600 | 3 |
|* 6 | INDEX RANGE SCAN | T3_PK | 5 | | 2 |
|* 7 | INDEX UNIQUE SCAN | T2_PK | 1 | 13 | |
Predicate Information (identified by operation id):
2 - filter("T1"."N1"=5)
6 - access("T3"."ID1"="T1"."ID1")
7 - access("T2"."ID1"="T1"."ID1" AND "T3"."ID2"="T2"."ID2")
filter("T3"."ID1"="T2"."ID1")
33
Hints on Hints
65 / 78
Jonathan Lewis
© 2008 - 2009
Distributed Effects
create table dist_home {dist_away} as
select
rownum id,
rpad(rownum,10) small_vc,
rpad(rownum,200) large_vc
from all_objects
where rownum <= 2000;
alter table dist_home {dist_away}
add constraint dh_pk {da_pk} primary key (id);
create public database link d920@loopback using 'd920';
Hints on Hints
66 / 78
Jonathan Lewis
© 2008 - 2009
Distributed Effects
select
/*+ driving_site (dh) */
dh.small_vc,
da.large_vc
from
dist_home dh,
dist_away@d920@loopback da
where
dh.small_vc like '12%'
and da.id = dh.id
;
34
Hints on Hints
67 / 78
Jonathan Lewis
© 2008 - 2009
Distributed Effects
ID Row Source
0 SELECT STATEMENT (all_rows)
1 NESTED LOOPS
2 TABLE ACCESS(analyzed)DIST_HOME full
3 REMOTE SERIAL_FROM_REMOTE
3 D920@LOOPBACK -- object_node
3 SELECT "ID","LARGE_VC" -- other
FROM "DIST_AWAY" "DA"
WHERE "ID"=:1 -- read consistency ?
Hints on Hints
68 / 78
Jonathan Lewis
© 2008 - 2009
Distributed Effects
Change the filter predicate to select more data:
dh.small_vc like '1%'
ID Row Source
0 SELECT STATEMENT (all_rows)
1 HASH JOIN
2 TABLE ACCESS(analyzed)DIST_HOME full
3 REMOTE SERIAL_FROM_REMOTE
3 D920@LOOPBACK
3 SELECT "ID","LARGE_VC"
FROM "DIST_AWAY" "DA"
35
Hints on Hints
69 / 78
Jonathan Lewis
© 2008 - 2009
Distributed Effects
ID Row Source
0 SELECT STATEMENT (all_rows)(remote)
1 HASH JOIN
2 REMOTE SERIAL_FROM_REMOTE
3 TABLE ACCESS(analyzed)DIST_AWAY full
2 ! -- where is this node ?
3 D920@LOOPBACK -- this one knows who it is
“Tune” the hash join by changing the driving site to the away d/b
/*+ driving_site (da) */
2 SELECT "ID","SMALL_VC"
FROM "DIST_HOME" "A2"
WHERE "SMALL_VC" LIKE '1%'
Hints on Hints
70 / 78
Jonathan Lewis
© 2008 - 2009
Multi-table distributed joins
select ... from
sales@d920@loopback sal,
sites sit,
products@d920@loopback prd
where ...
SELECT STATEMENT Optimizer=ALL_ROWS
HASH JOIN
INDEX (FULL SCAN) OF 'SI_PK' (UNIQUE)
REMOTE* SERIAL_FROM_REMOTE D920.JLCOMP.CO.UK@LOOPBACK
SELECT
"A1"."SALE_DATE", ... "A2"."PROMOTED"
FROM
"SALES" "A1","PRODUCTS" "A2"
WHERE ...
36
Hints on Hints
71 / 78
Jonathan Lewis
© 2008 - 2009
Multi-table distributed joins
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 NESTED LOOPS
2 NESTED LOOPS
3 REMOTE* D920.JLCOMP.CO.UK@LOOPBACK
4 INDEX (UNIQUE SCAN) OF 'SI_PK' (UNIQUE)
5 REMOTE* D920.JLCOMP.CO.UK@LOOPBACK
3 SELECT "SALE_DATE","SITE","PRODUCT","QTY","PROFIT"
FROM "SALES" "SAL"
5 SELECT /*+ INDEX("PRD") USE_NL("PRD") */ "ID","PROMOTED"
FROM "PRODUCTS" "PRD"
WHERE "ID"= :1
AND "PROMOTED">TO_DATE('2005-04-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Hints on Hints
72 / 78
Jonathan Lewis
© 2008 - 2009
Controlling distributed (a)
select bv.*
from ext_view bv -- remote view
where
bv.objectid in (
select linkid
from links_table -- local table
where projectid = 116448
and blocklist = 'N'
)
and bv.gisdatasetname = 'XXX'
order by
bv.physical_length desc
37
Hints on Hints
73 / 78
Jonathan Lewis
© 2008 - 2009
Controlling distributed (b)
SORT ORDER BY
NESTED LOOPS SEMI
REMOTE EXT_VIEW
TABLE ACCESS BY INDEX ROWID LINKS_TABLE
INDEX UNIQUE SCAN PK_PGM
When I run the (local) subquery by itself it returns a very small number of rows
When I run the main (remote) query with an in-list of
those values it executes very quickly
But I can’t make Oracle do this automatically
Hints on Hints
74 / 78
Jonathan Lewis
© 2008 - 2009
Controlling distributed (c)
select bv.*
from (
select distinct link_id
from links_table
where projectid = 116448
and blocklist = 'N'
) lk,
ext_view bv
where bv.object_id = lk.linkid
and bv.gisdatasetname = 'XXX'
order by
bv.physical_length desc
;
38
Hints on Hints
75 / 78
Jonathan Lewis
© 2008 - 2009
Controlling distributed (d)
select
/*
ordered no_merge(lk) no_merge(bv)
use_nl(bv) push_pred(bv)
*/
bv.*
from (
select distinct link_id
from links_table
where projectid = 116448 and blocklist = 'N'
) lk,
my_view bv
where bv.object_id = lk.linkid and bv.gisdatasetname = 'XXX'
order by
bv.physical_length desc
;
Hints on Hints
76 / 78
Jonathan Lewis
© 2008 - 2009
How to use hints (1)
• Set the join order– Ordered, leading (t1, t2, …) (10g version)
– But remember unnesting problems with ordered
• Join method for N-1 tables– Use_nl, use_merge, use_hash + (no_)swap_join_inputs
• Access path for every table– Index, no_index, hash, full, index_ffs etc.
• Average two hints per table to do it well– (three if you use hash joins)
39
Hints on Hints
77 / 78
Jonathan Lewis
© 2008 - 2009
How to use hints (2)
• Block or force unnesting– unnest, no_unnest - in the subquery
• Block or force early subqueries– push_subq, no_push_subq – change syntax in 10g
• Block or force merging– merge, no_merge - two forms, with or w/o alias
• Block, or force, predicate pushing– push_pred, no_push_pred (10g)
• Choosing the driving site for distributed– Driving_site()
Hints on Hints
78 / 78
Jonathan Lewis
© 2008 - 2009
Summary
Avoid ‘micro-management’ hints
“Strategic” hints can be useful and safe
“Feature” hints can be useful and safe
Keep testing on every upgrade
Use query block names from 10g onwards