Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: [SPAM] - RE: Optimizer - Bayesian Filter detected spam

RE: [SPAM] - RE: Optimizer - Bayesian Filter detected spam

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 1 Oct 2004 11:14:38 +0200
Message-ID: <2CF83791A616BB4DA203FFD13007824A018D0C27@MSXVS02.trivadis.com> 





Hi Robert



>>> 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.




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, 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.




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....






SQL> select /*+ leading(emp) */ count(ename) from dept, emp where =
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 =
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







SQL> select /*+ leading(dept) */ * from dept, emp where emp.deptno =3D =
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 =
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 =
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 =
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


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


Received on Fri Oct 01 2004 - 04:10:17 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US