FAQ
I can do backup and recovery in my sleep.... I can create databases, and I
am not a bad SQL tuning fellow I must say.. But, if there were to be an
Oracle inqusition, I would have to confess that the optimizer still
befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 rows
and DEPT has 1 row. No indexes. Real simple.

I have a simple SQL statement joining these tables:

select a.empid, a.ename, b.dname
from emp a, dept b
where a.deptno=b.deptno
and a.empid < 1000;

In playing with this statement, this is the execution path the optimizer
takes:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000 Bytes=22000)
1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)

2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000 Bytes=12000)

Statistics

0 recursive calls
0 db block gets
444 consistent gets
0 physical reads
0 redo size
21517 bytes sent via SQL*Net to client
1378 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed

If I do an ORDERED hint and reverse the join order, I get these results:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000 Bytes=22000)
1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)

2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000 Bytes=12000)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

Statistics

0 recursive calls
0 db block gets
377 consistent gets
0 physical reads
0 redo size
21517 bytes sent via SQL*Net to client
1378 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed

Note that the plan the optimizer chooses results in more consistent gets,
than the plan using the ordered hint does. I would expect that for something
this basic, the optimizer would "get it right" and come up with the better
plan, which the later plan seems to be. Any thoughts on this? Did I miss
something basic in my statistics gathering? I gathered stats for all
columns, and did 100 buckets for the histograms.

I note that the cost for both plans is the same, so is there some tie
breaking going on and if so, what are the rules for this tie breaking?
Or...Is this just a "law of diminishing returns" thing, and the difference
is so slight that Oracle could just go either way? I'm going to add more
rows to both tables and see if that impacts the results....

Thoughts anyone?

RF

Search Discussions

  • Bobak, Mark at Sep 30, 2004 at 4:20 pm
    What version of Oracle?
    If >=3D9i, are system stats gathered?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman Robert - IL
    Sent: Thursday, September 30, 2004 5:17 PM
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer

    I can do backup and recovery in my sleep.... I can create databases, and =
    I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 =
    rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=3Db.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan

    0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
    Bytes=3D22000)

    1 0 HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
    Bytes=3D10)

    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
    Bytes=3D12000)

    Statistics

    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:

    Execution Plan

    0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
    Bytes=3D22000)

    1 0 HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
    Bytes=3D12000)

    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
    Bytes=3D10)

    Statistics

    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent =
    gets,
    than the plan using the ordered hint does. I would expect that for =
    something
    this basic, the optimizer would "get it right" and come up with the =
    better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.=20

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the =
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Sep 30, 2004 at 4:22 pm
    I think you are seeing dynamic role reversal at work.
    with hash joins, Oracle is able to look at the partition sizes at run time,
    and then make the smallest partition driving -- with one of your tables
    containing a single row,
    it is rather obvious which one will be driving -- regardless the ORDERED
    hint you specify.
    additions/corrections welcome,

    Kind regards,
    Lex.

    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

    skype me

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman Robert - IL
    Sent: Thursday, September 30, 2004 22:17
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer

    I can do backup and recovery in my sleep.... I can create databases, and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000 Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)

    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000 Bytes=12000)

    Statistics

    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000 Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)

    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000 Bytes=12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

    Statistics

    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent gets,
    than the plan using the ordered hint does. I would expect that for something
    this basic, the optimizer would "get it right" and come up with the better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Freeman Robert - IL at Sep 30, 2004 at 4:24 pm
    this is 9iR2... and system stats are not being gathered at the moment. That
    is a possibility I suppose. I'll try that.

    RF

    -----Original Message-----
    From: Bobak, Mark
    To: Freeman Robert - IL; oracle-l_at_freelists.org
    Sent: 9/30/2004 4:25 PM
    Subject: RE: Optimizer

    What version of Oracle?
    If >=9i, are system stats gathered?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman Robert - IL
    Sent: Thursday, September 30, 2004 5:17 PM
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer

    I can do backup and recovery in my sleep.... I can create databases, and
    I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000
    rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)

    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)

    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000

    Bytes=12000)

    Statistics

    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)

    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)

    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

    Statistics

    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent
    gets,
    than the plan using the ordered hint does. I would expect that for
    something
    this basic, the optimizer would "get it right" and come up with the
    better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Freeman Robert - IL at Sep 30, 2004 at 4:25 pm
    Interesting thought but if that were the case, then shouldn't the LIO's be
    the same for both plans??

    RF

    -----Original Message-----
    From: Lex de Haan
    To: Freeman Robert - IL; oracle-l_at_freelists.org
    Sent: 9/30/2004 4:26 PM
    Subject: RE: Optimizer

    I think you are seeing dynamic role reversal at work.
    with hash joins, Oracle is able to look at the partition sizes at run
    time,
    and then make the smallest partition driving -- with one of your tables
    containing a single row,
    it is rather obvious which one will be driving -- regardless the ORDERED
    hint you specify.

    additions/corrections welcome,

    Kind regards,
    Lex.

    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

    skype me

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman Robert - IL
    Sent: Thursday, September 30, 2004 22:17
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer

    I can do backup and recovery in my sleep.... I can create databases, and
    I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000
    rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)

    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)

    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000

    Bytes=12000)

    Statistics

    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)

    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)

    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)

    Statistics

    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent
    gets,
    than the plan using the ordered hint does. I would expect that for
    something
    this basic, the optimizer would "get it right" and come up with the
    better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF

    --
    http://www.freelists.org/webpage/oracle-l
    <>
    --
    http://www.freelists.org/webpage/oracle-l
  • Juan Carlos Reyes Pacheco at Sep 30, 2004 at 4:32 pm
    Hi could you please include the trace 10046 for that query (raw file)
    without tkprof.
    Thanks


    Juan Carlos Reyes Pacheco
    OCP

    -------Original Message-------


    From: FREEMANR_at_tusc.com
    Date: 09/30/04 17:18:22
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer


    I can do backup and recovery in my sleep.... I can create databases, and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 rows
    and DEPT has 1 row. No indexes. Real simple.


    I have a simple SQL statement joining these tables:


    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;


    In playing with this statement, this is the execution path the optimizer
    takes:


    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000 Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000 Bytes=12000)


    Statistics

    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed


    If I do an ORDERED hint and reverse the join order, I get these results:


    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000 Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000 Bytes=12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)



    Statistics

    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed


    Note that the plan the optimizer chooses results in more consistent gets,
    than the plan using the ordered hint does. I would expect that for something
    this basic, the optimizer would "get it right" and come up with the better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.


    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....


    Thoughts anyone?


    RF



    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Freeman Robert - IL at Sep 30, 2004 at 4:35 pm
    Good point.... I'll run a 10046 and dump the contents of the relevant
    SQL*Plan into the plan table and generate an execution plan off of it
    tomorrow....

    Still, the point (in my mind) is that Oracle didn't seem to select the right
    plan. The LIO's were still higher, until I used the hint. Even if dynamic
    adjustment of the join order did occur, why would it result in higher LIO's?

    RF

    -----Original Message-----
    From: Wolfgang Breitling
    To: Freeman Robert - IL
    Cc: oracle-l_at_freelists.org
    Sent: 9/30/2004 4:28 PM
    Subject: Re: Optimizer

    OK, so those are the plans. But what is REALLY happening? You only can
    see that
    if you run a sqltrace and look at the STAT records - or in v$sql_plan if
    on 9i
    or later. Also, it is my understanding that in the case of a hash join,
    the
    execution engine can switch the roles of inner and outer table on the
    fly,
    while the hash join is happening, if it determines that the original
    order is
    not the best way of doing the join.

    Quoting Freeman Robert - IL :
    I can do backup and recovery in my sleep.... I can create databases, and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has
    15,000 rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent gets,
    than the plan using the ordered hint does. I would expect that for something
    this basic, the optimizer would "get it right" and come up with the better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF


    --
    http://www.freelists.org/webpage/oracle-l
    --
    regards

    Wolfgang Breitling
    Oracle 7,8,8i,9i OCP DBA
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Oct 1, 2004 at 2:45 am
    the dynamic role reversal does not mean that Oracle ignores your ORDERED
    hint --
    the hash join algorithm starts with the row source you ask for, doing the
    hash partitioning;
    the role reversal happens later in the game, so I am not surprised to see a
    small difference in LIOs.
    if you want to see specific details of the hash join at work, you might want
    to use event 10104;
    the trace file output might be useful in combination with 10053 output.

    Kind regards,
    Lex.

    visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

    skype me

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman Robert - IL
    Sent: Thursday, September 30, 2004 22:40
    To: 'Wolfgang Breitling '
    Cc: 'oracle-l_at_freelists.org '
    Subject: RE: Optimizer

    Good point.... I'll run a 10046 and dump the contents of the relevant
    SQL*Plan into the plan table and generate an execution plan off of it
    tomorrow....

    Still, the point (in my mind) is that Oracle didn't seem to select the right
    plan. The LIO's were still higher, until I used the hint. Even if dynamic
    adjustment of the join order did occur, why would it result in higher LIO's?

    RF

    -----Original Message-----
    From: Wolfgang Breitling
    To: Freeman Robert - IL
    Cc: oracle-l_at_freelists.org
    Sent: 9/30/2004 4:28 PM
    Subject: Re: Optimizer

    OK, so those are the plans. But what is REALLY happening? You only can
    see that
    if you run a sqltrace and look at the STAT records - or in v$sql_plan if
    on 9i
    or later. Also, it is my understanding that in the case of a hash join,
    the
    execution engine can switch the roles of inner and outer table on the
    fly,
    while the hash join is happening, if it determines that the original
    order is
    not the best way of doing the join.

    Quoting Freeman Robert - IL :
    I can do backup and recovery in my sleep.... I can create databases, and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has
    15,000 rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent gets,
    than the plan using the ordered hint does. I would expect that for something
    this basic, the optimizer would "get it right" and come up with the better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF


    --
    http://www.freelists.org/webpage/oracle-l
    --
    regards

    Wolfgang Breitling
    Oracle 7,8,8i,9i OCP DBA
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Freeman Robert - IL at Sep 30, 2004 at 4:43 pm
    Sitting here thinking about it, I think my point is this....

    Does it matter what it is really doing? The question is, whatever it's
    doing, why is it not doing the best doing it can be doing UNLESS I give it a
    hint that causes it to do something different?

    Just practicing my illeteration skills on ya all...

    Is the optimizer so stupid, that even for a simple join I have to give it a
    hint to get it to perform optimally? Is it data related? Volume related?
    Does the hint change the way Oracle handles the hash join perhaps? I dunno.

    10046 and v$sql_plan output tomorrow.... perhaps that will shed some light
    on the subject. For now, I'm off to celebrate a birthday with my kiddos!

    Robert

    -----Original Message-----
    From: Wolfgang Breitling
    To: Freeman Robert - IL
    Cc: oracle-l_at_freelists.org
    Sent: 9/30/2004 4:28 PM
    Subject: Re: Optimizer

    OK, so those are the plans. But what is REALLY happening? You only can
    see that
    if you run a sqltrace and look at the STAT records - or in v$sql_plan if
    on 9i
    or later. Also, it is my understanding that in the case of a hash join,
    the
    execution engine can switch the roles of inner and outer table on the
    fly,
    while the hash join is happening, if it determines that the original
    order is
    not the best way of doing the join.

    Quoting Freeman Robert - IL :
    I can do backup and recovery in my sleep.... I can create databases, and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has
    15,000 rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent gets,
    than the plan using the ordered hint does. I would expect that for something
    this basic, the optimizer would "get it right" and come up with the better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF


    --
    http://www.freelists.org/webpage/oracle-l
    --
    regards

    Wolfgang Breitling
    Oracle 7,8,8i,9i OCP DBA
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Sep 30, 2004 at 5:23 pm

    On Thu, 30 Sep 2004 16:47:39 -0500, Freeman Robert - IL wrote:
    Sitting here thinking about it, I think my point is this....

    Does it matter what it is really doing? The question is, whatever it's
    doing, why is it not doing the best doing it can be doing UNLESS I give it a
    hint that causes it to do something different?
    One thing that I haven't seen mentioned yet is that you seem to have
    an 'edge' case for some reason. Your preferred plan has a cost of 40
    as does the plan the optimizer chooses. So how does the optimizer
    choose between them? The simple answer is I don't know - though I'd
    hope 10053 might give a clue. My *guess* would be that the CBO doesn't
    pick the second plan because the cost is no better than the first and
    the order that it evaluates them in puts the second plan second. My
    belief is that this is how the CBO *always* works i.e. it calculates
    plans and rejects those that aren't lower cost than the least cost it
    has already discovered. Others will likely know better. In any case I
    guess the question might better rephrased as why does the plan that
    requires 17% more io get costed the same. A number of possibilities
    suggest themselves that only a 10053 would answer.
  • Christian Antognini at Sep 30, 2004 at 4:55 pm
    Hi Robert

    For hash joins the optimizer creates the hash table on the smaller row =
    source. In this case it's obviously DEPT.

    Now, the difference with LIO depends on the number of fetches that are =
    performed.=20
    Just an example (notice the "set arraysize" statements)...

    SQL> set autotrace trace exp stat
    SQL> set arraysize 15
    SQL> select /*+ ordered */ * from emp, dept where emp.deptno =3D =

    dept.deptno;

    14336 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D18 Card=3D14336 =
    Bytes=3D817152)
    HASH JOIN (Cost=3D18 Card=3D14336 Bytes=3D817152)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)

    Statistics

    0 recursive calls
    0 db block gets
    98 consistent gets
    0 physical reads
    0 redo size
    607686 bytes sent via SQL*Net to client
    11000 bytes received via SQL*Net from client
    957 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    14336 rows processed

    SQL> select /*+ ordered */ * from dept, emp where emp.deptno =3D =
    dept.deptno;

    14336 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D14336 =
    Bytes=3D817152)
    HASH JOIN (Cost=3D13 Card=3D14336 Bytes=3D817152)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

    Statistics

    0 recursive calls
    0 db block gets
    1051 consistent gets
    0 physical reads
    0 redo size
    794713 bytes sent via SQL*Net to client
    11000 bytes received via SQL*Net from client
    957 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    14336 rows processed

    SQL> set arraysize 5000
    SQL> select /*+ ordered */ * from dept, emp where emp.deptno =3D =
    dept.deptno;

    14336 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D14336 =
    Bytes=3D817152)
    HASH JOIN (Cost=3D13 Card=3D14336 Bytes=3D817152)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

    Statistics

    0 recursive calls
    0 db block gets
    98 consistent gets
    0 physical reads
    0 redo size
    718473 bytes sent via SQL*Net to client
    517 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    14336 rows processed

    Chris
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =
    On Behalf Of Freeman
    Robert - IL
    Sent: 30 September 2004 23:17
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer

    I can do backup and recovery in my sleep.... I can create databases, = and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 = rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=3Db.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the = optimizer
    takes:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
    Bytes=3D22000)
    1 0 HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
    Bytes=3D10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
    Bytes=3D12000)
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these = results:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
    Bytes=3D22000)
    1 0 HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
    Bytes=3D12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
    Bytes=3D10)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent = gets,
    than the plan using the ordered hint does. I would expect that for = something
    this basic, the optimizer would "get it right" and come up with the = better
    plan, which the later plan seems to be. Any thoughts on this? Did I = miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the =
    difference
    is so slight that Oracle could just go either way? I'm going to add = more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF


    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • David at Sep 30, 2004 at 4:58 pm
    We just realized we have a corrupted db. An export revealed this. RMAN
    records corruption to v$backup_corruption in the db and
    rc_backup_corruption in the catalog.

    How can we tie that info to determine when the corruption occured so we
    can consider our options.
  • Jamie Kinney at Sep 30, 2004 at 5:56 pm
    David,

    These views/tables each have CORRUPTION_CHANGE# in them. You could
    use the SCN_TO_TIMESTAMP function to get the time that the corruption
    was detected.
    From the docs, this column stores the "Change number at which the
    logical corruption was detected. Set to 0 to indicate media
    corruption."

    -Jamie

    select scn_to_timestamp(1711819000) from dual;

    SCN_TO_TIMESTAMP(1711819000)

    30-SEP-04 03.57.42.000000000 PM
    On Thu, 30 Sep 2004 15:02:32 -0700 (PDT), David wrote:
    We just realized we have a corrupted db. An export revealed this. RMAN
    records corruption to v$backup_corruption in the db and
    rc_backup_corruption in the catalog.

    How can we tie that info to determine when the corruption occured so we
    can consider our options.
    --
    ..
    David
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Freeman Robert - IL at Sep 30, 2004 at 5:10 pm

    For hash joins the optimizer creates the hash table on the smaller row
    source. In this case it's obviously DEPT.
    If this is true, then why isn't the LIO the same regardless of the order of
    the rows in the FROM clause with or without the ORDERED hint? If a hash join
    ALWAYS uses the smallest table as the hash table, shouldn't the LIO's be the
    same in this simple join? It appears to me that the hint overrides this rule
    somehow.

    As for array size, I don't think that is an issue. While it can be used to
    reduce LIO's, that is not the point of the question. The point is, with the
    array size being the same, why didn't the optimizer take the better path?
    What changed when I used the /*+ ORDERED */ hint? Why didn't Oracle use
    whatever changed to get me the smaller numbers of LIO's to begin with.

    This isn't about tuning the statement, it's about understanding why the
    optimizer does what it does.

    Thanks a bunch for your thoughts!

    RF

    -----Original Message-----
    From: Christian Antognini
    To: Freeman Robert - IL; oracle-l_at_freelists.org
    Sent: 9/30/2004 4:59 PM
    Subject: RE: Optimizer

    Hi Robert

    For hash joins the optimizer creates the hash table on the smaller row
    source. In this case it's obviously DEPT.

    Now, the difference with LIO depends on the number of fetches that are
    performed.
    Just an example (notice the "set arraysize" statements)...

    SQL> set autotrace trace exp stat
    SQL> set arraysize 15
    SQL> select /*+ ordered */ * from emp, dept where emp.deptno =

    dept.deptno;

    14336 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=14336 Bytes=817152)
    HASH JOIN (Cost=18 Card=14336 Bytes=817152)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=10 Card=14336 Bytes=530432)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)

    Statistics

    0 recursive calls
    0 db block gets
    98 consistent gets
    0 physical reads
    0 redo size
    607686 bytes sent via SQL*Net to client
    11000 bytes received via SQL*Net from client
    957 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    14336 rows processed

    SQL> select /*+ ordered */ * from dept, emp where emp.deptno =
    dept.deptno;

    14336 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=14336 Bytes=817152)
    HASH JOIN (Cost=13 Card=14336 Bytes=817152)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=10 Card=14336 Bytes=530432)

    Statistics

    0 recursive calls
    0 db block gets
    1051 consistent gets
    0 physical reads
    0 redo size
    794713 bytes sent via SQL*Net to client
    11000 bytes received via SQL*Net from client
    957 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    14336 rows processed

    SQL> set arraysize 5000
    SQL> select /*+ ordered */ * from dept, emp where emp.deptno =
    dept.deptno;

    14336 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=14336 Bytes=817152)
    HASH JOIN (Cost=13 Card=14336 Bytes=817152)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=10 Card=14336 Bytes=530432)

    Statistics

    0 recursive calls
    0 db block gets
    98 consistent gets
    0 physical reads
    0 redo size
    718473 bytes sent via SQL*Net to client
    517 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    14336 rows processed

    Chris
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman
    Robert - IL
    Sent: 30 September 2004 23:17
    To: 'oracle-l_at_freelists.org '
    Subject: Optimizer

    I can do backup and recovery in my sleep.... I can create databases, and I
    am not a bad SQL tuning fellow I must say.. But, if there were to be an
    Oracle inqusition, I would have to confess that the optimizer still
    befuddles me sometimes. I have two tables: EMP and DEPT. EMP has 15,000 rows
    and DEPT has 1 row. No indexes. Real simple.

    I have a simple SQL statement joining these tables:

    select a.empid, a.ename, b.dname
    from emp a, dept b
    where a.deptno=b.deptno
    and a.empid < 1000;

    In playing with this statement, this is the execution path the optimizer
    takes:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
    3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    444 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    If I do an ORDERED hint and reverse the join order, I get these results:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
    Bytes=22000)
    1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
    Bytes=12000)
    3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    377 consistent gets
    0 physical reads
    0 redo size
    21517 bytes sent via SQL*Net to client
    1378 bytes received via SQL*Net from client
    68 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    999 rows processed

    Note that the plan the optimizer chooses results in more consistent gets,
    than the plan using the ordered hint does. I would expect that for something
    this basic, the optimizer would "get it right" and come up with the better
    plan, which the later plan seems to be. Any thoughts on this? Did I miss
    something basic in my statistics gathering? I gathered stats for all
    columns, and did 100 buckets for the histograms.

    I note that the cost for both plans is the same, so is there some tie
    breaking going on and if so, what are the rules for this tie breaking?
    Or...Is this just a "law of diminishing returns" thing, and the
    difference
    is so slight that Oracle could just go either way? I'm going to add more
    rows to both tables and see if that impacts the results....

    Thoughts anyone?

    RF


    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Christian Antognini at Oct 1, 2004 at 4:14 am
    Hi Robert

    Sorry for the previous email... I resend it with the correct title...
    For hash joins the optimizer creates the hash table on the smaller=20
    row source. In this case it's obviously DEPT.
    If this is true, then why isn't the LIO the same regardless of the=20
    order of the rows in the FROM clause with or without the ORDERED hint?=20
    If a hash join ALWAYS uses the smallest table as the hash table,=20
    shouldn't the LIO's be the same in this simple join? It appears to me=20
    that the hint overrides this rule somehow.
    The fact that the smallest row source is used to build the hash table =
    can be found in the documentation as well, see =
    http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/optim=
    ops.htm#76074.

    If you take event 10053 you will see that independently of the order of =
    the tables in the from clause the CBO starts to use the smaller table as =
    outer table. Since both costs are the same the first one is used.
    As for array size, I don't think that is an issue. While it can be used =
    to reduce LIO's, that is not the point of the question. The point is,=20
    with the array size being the same, why didn't the optimizer take the=20
    better path? What changed when I used the /*+ ORDERED */ hint? Why=20
    didn't Oracle use whatever changed to get me the smaller numbers of=20
    LIO's to begin with.
    The point about array size is that the LIO are generated to get the data =
    and not to join the tables. Otherwise I cannot explain myself the =
    following behavior....

    If I perform the same join *without* getting the rows, the number of =
    LIO is the same for both join orders:

    SQL> select /*+ leading(emp) */ count(ename) from dept, emp where=20
    SQL> emp.deptno =3D dept.deptno;

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D15 Card=3D1 Bytes=3D22)
    SORT (AGGREGATE)

    HASH JOIN (Cost=3D15 Card=3D1562624 Bytes=3D34377728)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 =
    Bytes=3D129024)

    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D327 Bytes=3D4251)

    Statistics

    0 recursive calls
    0 db block gets
    95 consistent gets
    0 physical reads
    0 redo size
    309 bytes sent via SQL*Net to client
    375 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL> select /*+ leading(dept) */ count(ename) from dept, emp where=20
    SQL> emp.deptno =3D dept.deptno;

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D1 Bytes=3D22)
    SORT (AGGREGATE)

    HASH JOIN (Cost=3D13 Card=3D1562624 Bytes=3D34377728)

    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D327 Bytes=3D4251)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 =

    Bytes=3D129024)

    Statistics

    0 recursive calls
    0 db block gets
    95 consistent gets
    0 physical reads
    0 redo size
    309 bytes sent via SQL*Net to client
    375 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    If I get the rows, the number of LIO for the two join orders is =
    different:

    SQL> select /*+ leading(dept) */ * from dept, emp where emp.deptno =3D=20
    SQL> dept.deptno

    28672 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D1562624 =
    Bytes=3D104695808)
    HASH JOIN (Cost=3D13 Card=3D1562624 Bytes=3D104695808)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D327 Bytes=3D9810)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

    Statistics

    0 recursive calls
    0 db block gets
    1990 consistent gets
    0 physical reads
    0 redo size
    957209 bytes sent via SQL*Net to client
    21516 bytes received via SQL*Net from client
    1913 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    28672 rows processed

    SQL> select /*+ leading(emp) */ * from dept, emp where emp.deptno =3D=20
    SQL> dept.deptno;

    28672 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D19 Card=3D1562624 =
    Bytes=3D104695808)
    HASH JOIN (Cost=3D19 Card=3D1562624 Bytes=3D104695808)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D327 Bytes=3D9810)

    Statistics

    0 recursive calls
    0 db block gets
    101 consistent gets
    0 physical reads
    0 redo size
    1214632 bytes sent via SQL*Net to client
    21516 bytes received via SQL*Net from client
    1913 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    28672 rows processed
    This isn't about tuning the statement, it's about understanding why the =
    optimizer does what it does.
    I agree on this point... I'm also only interested in understanding the =
    CBO!=20

    My understanding is the following:
    - Oracle reads the outer table and prepares the hash table in memory
    - When the first fetch comes the first block of the inner table is read =
    and the hash table is probed, as soon as the first rows is found, it is =
    sent back to the client
    - Then another fetch comes and Oracle has to find out the second row =
    which could be or not in the same block as the previous... when it is in =
    the same block sometimes it has to read the block again, thus generating =
    more LIO on the same block
    - The same appends for the subsequent fetches...

    Now, when the array size is bigger, Oracle returns all rows associated =
    to a single block of the inner table in a single fetch operation, =
    therefore only a single LIO for each block is needed! If I look at the =
    number of blocks my test tables have, I can see that Oracle has to =
    perform at minimum 95 LIO.=20

    SQL> select table_name, blocks, num_rows from user_tables where=20
    SQL> table_name in ('EMP','DEPT');

    TABLE_NAME BLOCKS NUM_ROWS
    ------------------------------ ---------- ----------
    DEPT 4 8
    EMP 91 14336

    This is exactly the number of LIO for query 1 and 2, and almost the same =
    for query 4. For query 4 the blocks of the table DEPT are probably =
    accessed a couple of times. But the outer table, i.e. EMP, only once to =
    build the hash table. Therefore, if I use a larger array size, also =
    query 3 will generate almost the same number of LIO.

    SQL> set arraysize 5000
    SQL> select /*+ leading(dept) */ * from dept, emp where emp.deptno =3D=20
    SQL> dept.deptno;

    28672 rows selected.

    Execution Plan

    SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D28672 =
    Bytes=3D1634304)
    HASH JOIN (Cost=3D13 Card=3D28672 Bytes=3D1634304)
    TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D8 Bytes=3D160)
    TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

    Statistics

    0 recursive calls
    0 db block gets
    101 consistent gets
    0 physical reads
    0 redo size
    804729 bytes sent via SQL*Net to client
    550 bytes received via SQL*Net from client
    7 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    28672 rows processed

    Therefore, in my opinion, the optimizer correctly costs the join, but, =
    since it has no idea which array size is used, it has no possibility to =
    correctly cost the retrieval of the rows. Then, it simply minimizes the =
    memory need for the hash table by choosing the smaller row source.

    Chris
  • Wolfson Larry - lwolfs at Oct 1, 2004 at 12:34 pm
    RF,

    Umm, didn't quite get what your objective is?
    Since there's no indexes on either table what happens if you make them both
    IOTs?
    Also, do you really need histograms on these tables?

    Larry

    The information contained in this communication is
    confidential, is intended only for the use of the recipient
    named above, and may be legally privileged.
    If the reader of this message is not the intended
    recipient, you are hereby notified that any dissemination,
    distribution, or copying of this communication is strictly
    prohibited.
    If you have received this communication in error,
    please re-send this communication to the sender and
    delete the original message or any copy of it from your
    computer system. Thank You.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 30, '04 at 4:12p
activeOct 1, '04 at 12:34p
posts16
users9
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase