CBO tuning help needed
Date: Thu, 24 Jan 2008 12:15:18 -0800 (PST)
Message-ID: <6bb90053-6dd4-4c00-b720-dec6fb11f473@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
Received on Thu Jan 24 2008 - 14:15:18 CST