Re: CBO tuning help needed

On Jan 24, 2:15 pm, bhonaker <bhona..._at_gmail.com> wrote:
> Server A Oracle version: 10.1.0.3, 8k block size
> Server B Oracle version: 10.2.0.3, 16k block size
>
> Server A: Red Hat Enterprise Linux ES release 3 (Taroon Update 8), 2
> 3Ghz CPUs w/1M cache, 4G RAM
> Server B: Red Hat Enterprise Linux ES release 4 (Nahant Update 4), 4
> 3Ghz CPUs w/4M cache, 8G RAM
>
> Can someone please help me understand why a query that returns in less
> than 10 seconds on server A (after several executions) takes
> approximately 10 - 15 minutes on server B time after time after time?
> Please don't comment on the gruesome query, I didn't write it - I'm
> just trying to migrate it as-is from A to B.
>
> Stats on the objects are functionally equivalent between the servers
> (minor differences in row_count, num_distinct, etc, but the big
> picture is the same).  Tablespaces are the same other than block
> size.  Tables are created in same manner on both servers.  The root of
> the problem IMHO seems to be the full table scan of REQUESTS and the
> index fast full scan of TRA_REQ_FK_I.  Even when I dramatically reduce
> INDEX_OPTIMIZER_COST_ADJ or force via hint server B to utilize the
> REQUEST_VISIT_FK_I index instead of doing a full table scan of
> REQUESTS, the optimizer still chooses to perform an index fast full
> scan instead of a range scan on TRA_REQ_FK_I.  I've tried making all
> relevant parms listed in V$SES_OPTIMIZER_ENV match between the
> servers, but the same poor execution path is chosen.  I have tried
> fiddling with DB_FILE_MULTIBLOCK_READ_COUNT, setting
> OPTIMIZER_FEATURES_ENABLED to 10.1.0.3, and various other things, to
> no avail.  I am pulling my hair out and I don't have much hair to
> start with.  Please help!
>
> Below find the query, the execution plans and the statistics from
> autotrace.  Let me know if I have forgotten something or if I ccan
> post anything else to help figure this out.
>
> SELECT sum(nvl(u.used, 0)) as used, nvl(sum(a.avail), 0) as avail,
>        nvl(round((sum(nvl(u.used, 0)) / GREATEST(sum(a.avail), 1)) *
> 100, 0), 0) as pct,
>        '0' as visitnew, I1.ia_id as installation_id, I1.name as
> installation_name,
>        substr(B1.space_id, 0, 1) as truss_row
>   FROM idle2.berths B1, idle2.installations I1,
>        (SELECT B2.id as berth_id, round(sum(nvl(least(nvl(v.end_dt,
> sysdate),
>                TO_DATE('01/01/2008 23:59:59', 'MM/DD/YYYY
> HH24:MI:SS')) - greatest(v.start_dt, TO_DATE('01/01/2008 00:00:00',
> 'MM/DD/YYYY HH24:MI:SS')), 0) * 24 * 60), 0) as used
>           FROM idle2.berths B2, idle2.installations I2, idle2.visits
> v,
>                (SELECT DISTINCT REQUESTS1.visit_id COL1
>                   FROM idle2.transactions TRANSACTIONS1,
> idle2.requests REQUESTS1
>                  WHERE REQUESTS1.id = TRANSACTIONS1.request_id) TEMP0
>          WHERE B2.id = v.berth_id AND I2.id = B2.installation_id AND
> 24 = I2.id AND B2.parking_space_type_id not in (4, 21, 41)
>            AND v.record_start_dt < new_time(TO_DATE('01/01/2008
> 23:59:59', 'MM/DD/YYYY HH24:MI:SS'), I2.time_zone, 'EST')
>            AND nvl(v.record_end_dt, sysdate) >
> new_time(TO_DATE('01/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
> I2.time_zone, 'EST')
>            AND TEMP0.COL1 = v.id AND B2.installation_id = 24
>            AND TEMP0.COL1 NOT IN (SELECT REQUESTS2.visit_id
>                                     FROM idle2.transactions
> TRANSACTIONS2, idle2.transaction_account_xref, idle2.accounts,
> idle2.requests REQUESTS2
>                                    WHERE REQUESTS2.id =
> TRANSACTIONS2.request_id AND TRANSACTIONS2.id =
> transaction_account_xref.transaction_id
>                                      AND accounts.id =
> transaction_account_xref.account_id AND 2 = TRANSACTIONS2.fleet_id)
>          GROUP BY B2.id) u,
>        (SELECT history.berth_id,
>                sum(round((least(history.end, TO_DATE('01/01/2008
> 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) - greatest(history.begin,
> TO_DATE('01/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))) * 24 * 60,
> 0)) as avail
>           FROM (SELECT h.berth_id, h.status, h.created_dt as begin,
> min(nvl(h2.created_dt, sysdate)) as end
>                   FROM idle2.berth_history h, idle2.berth_history h2
>                  WHERE h.berth_id = h2.berth_id (+) AND h2.created_dt
> (+) > h.created_dt
>                  GROUP BY h.berth_id, h.status, h.created_dt
>                  ORDER BY h.berth_id, h.created_dt) history
>           WHERE history.end > TO_DATE('01/01/2008 00:00:00', 'MM/DD/
> YYYY HH24:MI:SS') AND history.begin < TO_DATE('01/01/2008 23:59:59',
> 'MM/DD/YYYY HH24:MI:SS')
>             AND 'ACTIVE' = history.status
>           GROUP BY history.berth_id) a
>  WHERE B1.id = u.berth_id (+) AND B1.id = a.berth_id (+) AND I1.id =
> B1.installation_id AND 24 = I1.id AND B1.installation_id = 24
>  GROUP BY I1.ia_id, I1.name, substr(B1.space_id, 0, 1)
>  ORDER BY I1.ia_id, substr(B1.space_id, 0, 1)
>
> Server A
> 6 rows selected.
>
> Elapsed: 00:00:06.57
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2617 Card=1
> Bytes=84)
>    1    0   SORT (GROUP BY) (Cost=2617 Card=1 Bytes=84)
>    2    1     HASH JOIN (OUTER) (Cost=2616 Card=1 Bytes=84)
>    3    2       HASH JOIN (OUTER) (Cost=2095 Card=1 Bytes=66)
>    4    3         NESTED LOOPS (Cost=17 Card=1 Bytes=48)
>    5    4           TABLE ACCESS (BY INDEX ROWID) OF
> 'INSTALLATIONS' (TABLE) (Cost=1 Card=1 Bytes=34)
>    6    5             INDEX (UNIQUE SCAN) OF 'INSTALLATIONS_PK' (INDEX
> (UNIQUE)) (Cost=0 Card=1)
>    7    4           TABLE ACCESS (BY INDEX ROWID) OF 'BERTHS' (TABLE)
> (Cost=16 Card=1 Bytes=14)
>    8    7             INDEX (RANGE SCAN) OF
> 'BERTH_INSTALLATION_FK_I'(INDEX) (Cost=1 Card=1)
>    9    3         VIEW (Cost=2077 Card=15 Bytes=270)
>   10    9           SORT (GROUP BY) (Cost=2077 Card=15 Bytes=345)
>   11   10             VIEW (Cost=2077 Card=15 Bytes=345)
>   12   11               SORT (UNIQUE) (Cost=2077 Card=15 Bytes=1515)
>   13   12                 NESTED LOOPS (Cost=1524 Card=1 Bytes=101)
>   14   13                   NESTED LOOPS (Cost=1522 Card=1 Bytes=95)
>   15   14                     NESTED LOOPS (Cost=1513 Card=4
> Bytes=332)
>   16   15                       NESTED LOOPS (Cost=17 Card=1 Bytes=34)
>   17   16                         TABLE ACCESS (BY INDEX ROWID) OF
> 'INSTALLATIONS' (TABLE) (Cost=1 Card=1 Bytes=15)
>   18   17                           INDEX (UNIQUE SCAN) OF
> 'INSTALLATIONS_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
>   19   16                         TABLE ACCESS (BY INDEX ROWID) OF
> 'BERTHS' (TABLE) (Cost=16 Card=1 Bytes=19)
>   20   19                           INDEX (RANGE SCAN) OF
> 'BERTH_INSTALLATION_FK_I' (INDEX) (Cost=1 Card=2)
>   21   15                       TABLE ACCESS (BY INDEX ROWID) OF
> 'VISITS' (TABLE) (Cost=1496 Card=4 Bytes=196)
>   22   21                         INDEX (RANGE SCAN) OF
> 'VIS_BER_FK_I' (INDEX) (Cost=2 Card=1625)
>   23   14                     TABLE ACCESS (BY INDEX ROWID) OF
> 'REQUESTS' (TABLE) (Cost=3 Card=1 Bytes=12)
>   24   23                       INDEX (RANGE SCAN) OF
> 'REQUEST_VISIT_FK_I' (INDEX) (Cost=2 Card=1)
>   25   24                         NESTED LOOPS (Cost=74 Card=9
> Bytes=405)
>   26   25                           NESTED LOOPS (Cost=65 Card=9
> Bytes=351)
>   27   26                             NESTED LOOPS (Cost=38 Card=9
> Bytes=243)
>   28   27                               TABLE ACCESS (BY INDEX
> ROWID)OF 'REQUESTS' (TABLE) (Cost=11 Card=9 Bytes=108)
>   29   28                                 INDEX (RANGE SCAN) OF
> 'REQUEST_VISIT_FK_I' (INDEX) (Cost=3 Card=9)
>   30   27                               TABLE ACCESS (BY INDEX
> ROWID)OF 'TRANSACTIONS' (TABLE) (Cost=3 Card=1 Bytes=15)
>   31   30                                 INDEX (RANGE SCAN) OF
> 'TRA_REQ_FK_I' (INDEX) (Cost=2 Card=1)
>   32   26                             TABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTION_ACCOUNT_XREF' (TABLE) (Cost=3 Card=1 Bytes=12)
>   33   32                               INDEX (RANGE SCAN) OF
> 'TAX_TRA_FK_I' (INDEX) (Cost=2 Card=1)
>   34   25                           INDEX (UNIQUE SCAN) OF
> 'ACCOUNT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=6)
>   35   13                   INDEX (RANGE SCAN) OF
> 'TRA_REQ_FK_I' (INDEX) (Cost=2 Card=1 Bytes=6)
>   36    2       VIEW (Cost=521 Card=625 Bytes=11250)
>   37   36         SORT (GROUP BY) (Cost=521 Card=625 Bytes=21875)
>   38   37           VIEW (Cost=521 Card=625 Bytes=21875)
>   39   38             FILTER
>   40   39               SORT (GROUP BY) (Cost=521 Card=625
> Bytes=23125)
>   41   40                 HASH JOIN (OUTER) (Cost=518 Card=12483
> Bytes=461871)
>   42   41                   TABLE ACCESS (FULL) OF
> 'BERTH_HISTORY' (TABLE) (Cost=424 Card=12483 Bytes=299592)
>   43   41                   INDEX (FAST FULL SCAN) OF
> 'BERTH_HISTORY_BERTH_ID_DT_N1' (INDEX) (Cost=90 Card=151538
> Bytes=1969994)
>
> Statistics
> ----------------------------------------------------------
>           0  recursive calls;
>           0  db block gets
>      217923  consistent gets
>           0  physical reads
>           0  redo size
>         894  bytes sent via SQL*Net to client
>        1798  bytes received via SQL*Net from client
>           2  SQL*Net roundtrips to/from client
>           5  sorts (memory)
>           0  sorts (disk)
>           6  rows processed
>
> Server B
> 6 rows selected.
>
> Elapsed: 00:12:07.62
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1051046198
>
> ---------------------------------------------------------------------------­---------------------------------------------------
> | Id  | Operation                             |
> Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)|
> Time     |
> ---------------------------------------------------------------------------­---------------------------------------------------
> |   0 | SELECT STATEMENT
> |                              |     2 |   168 |       |   134K  (2)|
> 00:31:21 |
> |   1 |  SORT GROUP BY
> |                              |     2 |   168 |       |   134K  (2)|
> 00:31:21 |
> |*  2 |   HASH JOIN OUTER
> |                              |     2 |   168 |       |   134K  (2)|
> 00:31:21 |
> |*  3 |    HASH JOIN OUTER
> |                            
> ...
>
> read more »

There could be any number of reasons for this behaviour --

differences in clustering factor
presence (or absence) of histograms
computed vs. estimated statistics

You might try digging into the trace from setting event 10053 on both servers and see what the optimizer is doing in each case; the 'decision trees' may shed more light than the resulting execution plans.

David Fitzjarrell Received on Thu Jan 24 2008 - 15:13:39 CST