Re: CBO tuning help needed

"bhonaker" <bhonaker_at_gmail.com> wrote in message news:6bb90053-6dd4-4c00-b720-dec6fb11f473_at_v29g2000hsf.googlegroups.com...
> 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
> | | 2 | 132 | | 133K (2)|
> 00:31:12 |
> | 4 | NESTED LOOPS
> | | 2 | 96 | | 3 (0)|
> 00:00:01 |
> | 5 | TABLE ACCESS BY INDEX ROWID |
> INSTALLATIONS | 1 | 34 | | 1 (0)|
> 00:00:01 |
> |* 6 | INDEX UNIQUE SCAN |
> INSTALLATIONS_PK | 1 | | | 0 (0)|
> 00:00:01 |
> | 7 | TABLE ACCESS BY INDEX ROWID |
> BERTHS | 2 | 28 | | 2 (0)|
> 00:00:01 |
> |* 8 | INDEX RANGE SCAN |
> BERTH_INSTALLATION_FK_I | 2 | | | 1 (0)|
> 00:00:01 |
> | 9 | VIEW
> | | 2 | 36 | | 133K (2)|
> 00:31:12 |
> | 10 | HASH GROUP BY
> | | 2 | 136 | | 133K (2)|
> 00:31:12 |
> |* 11 | FILTER
> | | | | |
> | |
> |* 12 | HASH JOIN
> | | 5 | 340 | | 133K (2)|
> 00:31:10 |
> |* 13 | TABLE ACCESS BY INDEX ROWID |
> VISITS | 4 | 168 | | 1779 (1)|
> 00:00:25 |
> | 14 | NESTED LOOPS
> | | 7 | 434 | | 1784 (1)|
> 00:00:25 |
> | 15 | NESTED LOOPS
> | | 2 | 40 | | 3 (0)|
> 00:00:01 |
> | 16 | TABLE ACCESS BY INDEX ROWID|
> INSTALLATIONS | 1 | 8 | | 1 (0)|
> 00:00:01 |
> |* 17 | INDEX UNIQUE SCAN |
> INSTALLATIONS_PK | 1 | | | 0 (0)|
> 00:00:01 |
> |* 18 | TABLE ACCESS BY INDEX ROWID|
> BERTHS | 2 | 24 | | 2 (0)|
> 00:00:01 |
> |* 19 | INDEX RANGE SCAN |
> BERTH_INSTALLATION_FK_I | 2 | | | 1 (0)|
> 00:00:01 |
> |* 20 | INDEX RANGE SCAN |
> VIS_BER_FK_I | 1931 | | | 2 (0)|
> 00:00:01 |
> | 21 | VIEW
> | | 5293K| 30M| | 131K (2)|
> 00:30:45 |
> | 22 | HASH UNIQUE
> | | 5293K| 90M| 767M| 131K (2)|
> 00:30:45 |
> |* 23 | HASH JOIN
> | | 28M| 483M| 483M| 73151 (1)|
> 00:17:05 |
> | 24 | INDEX FAST FULL SCAN |
> TRA_REQ_FK_I | 28M| 161M| | 9995 (2)|
> 00:02:20 |
> | 25 | TABLE ACCESS FULL |
> REQUESTS | 26M| 301M| | 16481 (1)|
> 00:03:51 |
> | 26 | NESTED LOOPS
> | | 5 | 225 | | 43 (0)|
> 00:00:01 |
> | 27 | NESTED LOOPS
> | | 5 | 195 | | 38 (0)|
> 00:00:01 |
> | 28 | NESTED LOOPS
> | | 5 | 135 | | 23 (0)|
> 00:00:01 |
> | 29 | TABLE ACCESS BY INDEX ROWID |
> REQUESTS | 5 | 60 | | 8 (0)|
> 00:00:01 |
> |* 30 | INDEX RANGE SCAN |
> REQUEST_VISIT_FK_I | 5 | | | 3 (0)|
> 00:00:01 |
> |* 31 | TABLE ACCESS BY INDEX ROWID |
> TRANSACTIONS | 1 | 15 | | 3 (0)|
> 00:00:01 |
> |* 32 | INDEX RANGE SCAN |
> TRA_REQ_FK_I | 1 | | | 2 (0)|
> 00:00:01 |
> | 33 | TABLE ACCESS BY INDEX ROWID |
> TRANSACTION_ACCOUNT_XREF | 1 | 12 | | 3 (0)|
> 00:00:01 |
> |* 34 | INDEX RANGE SCAN |
> TAX_TRA_FK_I | 1 | | | 2 (0)|
> 00:00:01 |
> |* 35 | INDEX UNIQUE SCAN |
> ACCOUNT_PK | 1 | 6 | | 1 (0)|
> 00:00:01 |
> | 36 | VIEW
> | | 8999 | 158K| | 684 (4)|
> 00:00:10 |
> | 37 | HASH GROUP BY
> | | 8999 | 202K| | 684 (4)|
> 00:00:10 |
> | 38 | VIEW
> | | 9778 | 219K| | 684 (4)|
> 00:00:10 |
> |* 39 | FILTER
> | | | | |
> | |
> | 40 | HASH GROUP BY
> | | 9778 | 353K| | 684 (4)|
> 00:00:10 |
> |* 41 | HASH JOIN OUTER
> | | 195K| 7066K| 2464K| 679 (3)|
> 00:00:10 |
> |* 42 | TABLE ACCESS FULL |
> BERTH_HISTORY | 69998 | 1640K| | 335 (1)|
> 00:00:05 |
> | 43 | INDEX FAST FULL SCAN |
> BERTH_HISTORY_BERTH_ID_DT_N1 | 151K| 1920K| | 74 (2)|
> 00:00:02 |
> ------------------------------------------------------------------------------------------------------------------------------
>
> Statistics
> ----------------------------------------------------------
> 1279 recursive calls
> 0 db block gets
> 305402 consistent gets
> 172129 physical reads
> 608 redo size
> 916 bytes sent via SQL*Net to client
> 1698 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 6 rows processed

Do you have histograms for the tables on one server and not on the other ?

select * from user_histograms where ... Received on Thu Jan 24 2008 - 14:43:24 CST