star transformation not chosen because of bind variables
Jim Reesman
where clause. It results in a star transformation being chosen and the
plan and response time are good. The second query has simply had the 2
literals replaced with bind variables. Despite including the
STAR_TRANSFORMATION hint, the CBO chooses the full scan and index
range scans (it does so seemingly without regard to _any_ hints - eg.
FACT () ).
How can I get the second query to use the first execution plan? I'm
using 9.2.
TIA,
Jim
SQL> set echo on
SQL> @lotxybincounts-lits.sql
SQL> /* lotxybincounts */
SQL> /* bin counts for each x,y in the lot */
SQL> SELECT
2 die_x,
3 die_y,
4 bin_id,
5 count(bin_id) freq
6 FROM
7 (
8 SELECT
9 /* die.x_coordinate */ die_x
10 , /* die.y_coordinate */ die_y
11 , sof.bin_id
12 , pass_count pass
13 FROM operation op
14 , lot l
15 , die_bin_result dbr
16 -- , die die
17 , software_bin sof
18 WHERE (l.lot_lnkid = dbr.lot_lnkid)
19 AND (op.operation_lnkid =
dbr.operation_lnkid)
20 -- AND (die.die_lnkid = dbr.die_lnkid)
21 AND (sof.software_bin_lnkid =
dbr.software_bin_lnkid)
22 AND (l.lot_id = '6BCZ05775.1')
23 AND (op.operation_name = 'CP1')
24 )
25 group by die_x, die_y, bin_id;
3221 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3151 Card=92 Bytes=3
588)
1 0 SORT (GROUP BY) (Cost=3151 Card=92 Bytes=3588)
2 1 HASH JOIN (Cost=3092 Card=15603 Bytes=608517)
3 2 TABLE ACCESS (FULL) OF 'SOFTWARE_BIN' (Cost=2 Card=308
Bytes=1540)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DIE_BIN_RESULT' (Cos
t=3087 Card=16077 Bytes=546603)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP MERGE
8 7 BITMAP KEY ITERATION
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'LOT' (Cost
=1 Card=1 Bytes=16)
10 9 BITMAP CONVERSION (TO ROWIDS)
11 10 BITMAP INDEX (SINGLE VALUE) OF 'LOT_IDX5
'
12 8 BITMAP INDEX (RANGE SCAN) OF 'DIE_BIN_RESULT
_I_LOT_LNKID'
13 6 BITMAP MERGE
14 13 BITMAP KEY ITERATION
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION'
(Cost=2 Card=1 Bytes=7)
16 15 INDEX (RANGE SCAN) OF 'OPERATION_AK' (UNIQ
UE) (Cost=1 Card=1)
17 14 BITMAP INDEX (RANGE SCAN) OF 'DIE_BIN_RESULT
_I_OPERATION_LNK'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
203 consistent gets
0 physical reads
0 redo size
45208 bytes sent via SQL*Net to client
1842 bytes received via SQL*Net from client
216 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3221 rows processed
SQL>
SQL> @lotxybincounts-vars.sql
SQL> /* lotxybincounts */
SQL> /* bin counts for each x,y in the lot */
SQL>
SQL> variable c_lot_id varchar2(32);
SQL> variable c_operation_name varchar2(32);
SQL> exec :c_lot_id := '6BCZ05775.1';
PL/SQL procedure successfully completed.
SQL> exec :c_operation_name := 'CP1';
PL/SQL procedure successfully completed.
SQL> SELECT
2 die_x,
3 die_y,
4 bin_id,
5 count(bin_id) freq
6 FROM /*+ STAR_TRANSFORMATION */
7 (
8 SELECT
9 /* die.x_coordinate */ die_x
10 , /* die.y_coordinate */ die_y
11 , sof.bin_id
12 , pass_count pass
13 FROM operation op
14 , lot l
15 , die_bin_result dbr
16 -- , die die
17 , software_bin sof
18 WHERE (l.lot_lnkid = dbr.lot_lnkid)
19 AND (op.operation_lnkid =
dbr.operation_lnkid)
20 -- AND (die.die_lnkid = dbr.die_lnkid)
21 AND (sof.software_bin_lnkid =
dbr.software_bin_lnkid)
22 AND (l.lot_id = :c_lot_id)
23 AND (op.operation_name =
:c_operation_name)
24 )
25 group by die_x, die_y, bin_id
26 /
3221 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2299 Card=92 Bytes=5
704)
1 0 SORT (GROUP BY) (Cost=2299 Card=92 Bytes=5704)
2 1 HASH JOIN (Cost=2212 Card=15603 Bytes=967386)
3 2 TABLE ACCESS (FULL) OF 'SOFTWARE_BIN' (Cost=2 Card=308
Bytes=1540)
4 2 HASH JOIN (Cost=2209 Card=16077 Bytes=916389)
5 4 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=23)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION' (Cost
=2 Card=1 Bytes=7)
7 6 INDEX (RANGE SCAN) OF 'OPERATION_AK' (UNIQUE) (C
ost=1 Card=1)
8 5 BUFFER (SORT) (Cost=2 Card=1 Bytes=16)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'LOT' (Cost=2 C
ard=1 Bytes=16)
10 9 INDEX (RANGE SCAN) OF 'LOT_AK' (UNIQUE) (Cost=
1 Card=1)
11 4 TABLE ACCESS (FULL) OF 'DIE_BIN_RESULT' (Cost=2185 C
ard=3472535 Bytes=118066190)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35860 consistent gets
35704 physical reads
0 redo size
45425 bytes sent via SQL*Net to client
1842 bytes received via SQL*Net from client
216 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3221 rows processed
Daniel Morgan
I can't wait to see the answer because from my experience hints are
often ignored.
And yet others have repeated posted that they are not. This looks like a
good test case.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Richard Foote
> In the following listing, the first query has 2 literal values in the
> where clause. It results in a star transformation being chosen and the
> plan and response time are good. The second query has simply had the 2
> literals replaced with bind variables. Despite including the
> STAR_TRANSFORMATION hint, the CBO chooses the full scan and index
> range scans (it does so seemingly without regard to _any_ hints - eg.
> FACT () ).
>
Hi Jim,
Try putting the hint immediately after the SELECT /*+ here */ keyword.
Cheers
Richard
Jusung Yang
There are restrictions on star transformation, one of them is that you
can not have bind variables in your query. Using bind variables in a
data warehouse environment may not be a good idea in any case.
- Jusung Yang
jimre...@yahoo.com (Jim Reesman) wrote in message news:<ef9d9e.030925...@posting.google.com>...
Jonathan Lewis
Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1064537943.68764@yasure...
> Jim Reesman wrote:
>
> >In the following listing, the first query has 2 literal values in
the
> >where clause. It results in a star transformation being chosen and
the
> >plan and response time are good. The second query has simply had
the 2
> >literals replaced with bind variables. Despite including the
> >STAR_TRANSFORMATION hint, the CBO chooses the full scan and index
> >range scans (it does so seemingly without regard to _any_ hints -
eg.
> >FACT () ).
> >
> >How can I get the second query to use the first execution plan? I'm
> >using 9.2.
>
> I can't wait to see the answer because from my experience hints are
> often ignored.
> And yet others have repeated posted that they are not. This looks
like a
> good test case.
>
9.2 Data warehousing guide, p.17-12
Star transformation is not supported for ...
queries that contain bind variables.
Hints are only obeyed if they are legal, correct,
and used in the correct context.
The major pain in the backside with Oracle is
finding the bit in the documentation where you
get told what is legal - I checked the SQL Reference
and the Performance Guide (where it mentioned that
you can't use star_transformation with cursor_sharing)
before I got to the Datawarehouse Guide. It's always
a good source of clues for optimizer issues.
Tanel Poder
"Jim Reesman" <jimre...@yahoo.com> wrote in message
news:ef9d9e.030925...@posting.google.com...
> In the following listing, the first query has 2 literal values in the
> where clause. It results in a star transformation being chosen and the
> plan and response time are good. The second query has simply had the 2
> literals replaced with bind variables. Despite including the
> STAR_TRANSFORMATION hint, the CBO chooses the full scan and index
> range scans (it does so seemingly without regard to _any_ hints - eg.
> FACT () ).
>
> How can I get the second query to use the first execution plan? I'm
> using 9.2.
Hi!
Check the following link for "Star Tranformation Restrictions"
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/schemas.htm#11975
Star transformation is not supported for tables with any of the following
characteristics:
* Queries that contain bind variables
So, do not use bind variables in your query. In DW you probably aren't
running your query that often anyway that you'd get parsing problems...
Btw, autotrace and explain plan aren't the best tools for analyzing
execution plans, especially when you're dealing with complex queries
involving rewrites, transformations and recursive execution plans. 10046
trace+tkprof is the real thing, even v$sql_plan doesn't help you with
recursive plans.
Tanel.
Tanel Poder
> often ignored.
Yes, hints are definitely ignored when they can't be fulfilled. The simplest
case would be to ignore an "index" hint when index plainly doesn't exist on
required column. A hint involves a complex set of rules, which are first
evaluated before any behaviour will be changed. Star transformation has
several documented restrictions, which disable it's use, regardless of any
hint or parameter setting.
Btw, there's a parameter _always_star_transformation which could be used in
some cases when CBO doesn't want to use ST even though all required
conditions have been fulfilled.
Tanel.
Daniel Morgan
>Hints are only obeyed if they are legal, correct,
>and used in the correct context.
>
>The major pain in the backside with Oracle is
>finding the bit in the documentation where you
>get told what is legal - I checked the SQL Reference
>and the Performance Guide (where it mentioned that
>you can't use star_transformation with cursor_sharing)
>before I got to the Datawarehouse Guide. It's always
>a good source of clues for optimizer issues.
>
>
>
One of my biggest complaints, and something I emphasize to my students,
is that Oracle does not
validate hints and report syntax errors: Which I consider as dumb as
pounding dirt.
That the following isn't caught:
SELECT /*+ David Bowie */ dummy
FROM dual;
is patently ridiculous (though Richard might disagree). This is
something Oracle should have fixed long ago.
And yes I have on rare occassion used this feature to comment a select
statement but it hardly a valid
justification for making it possible to send garbage to the SQL engine.
Hemant K Chitale
That Oracle does not complain if a Hint is invalid
is how they implemented Comments inside a SELECT.
OEM puts such comments when you run SQL commands
from OEM.
I know, there are other ways of putting in comments.
I guess some developers at Oracle were just too lazy
to specify how comments can be included to differentiate
them from Hints.
(eg something like "SELECT /*++ This is a comment */" )
OR when they developed the code to put in Hints they
encountered so many errors that they just decided not
put it in a syntax-checker !
Hemant
Niall Litchfield
> <snipped>
>
> >Hints are only obeyed if they are legal, correct,
> >and used in the correct context.
> >
> >The major pain in the backside with Oracle is
> >finding the bit in the documentation where you
> >get told what is legal - I checked the SQL Reference
> >and the Performance Guide (where it mentioned that
> >you can't use star_transformation with cursor_sharing)
> >before I got to the Datawarehouse Guide. It's always
> >a good source of clues for optimizer issues.
> >
> >
> >
> One of my biggest complaints, and something I emphasize to my students,
> is that Oracle does not
> validate hints and report syntax errors: Which I consider as dumb as
> pounding dirt.
>
> That the following isn't caught:
>
> SELECT /*+ David Bowie */ dummy
> FROM dual;
>
> is patently ridiculous (though Richard might disagree). This is
> something Oracle should have fixed long ago.
> And yes I have on rare occassion used this feature to comment a select
> statement but it hardly a valid
> justification for making it possible to send garbage to the SQL engine.
I think I disagree, what should the parser say to
select /*+ USE_NL(A,B) */
* from a,b where a.id=b.id;
Should it accept this or should it say - Are you sure you don't want to
specify ORDERED as well? Its the 'in the correct context' bit of Jonathan's
comment that is the real pain (well that and my typing). All a parser could
throw was exceptions on the basis of legality and correctness but not on the
context.