Re: CBO tuning help needed
From: bhonaker <bhonaker_at_gmail.com>
Date: Fri, 25 Jan 2008 13:32:47 -0800 (PST)
Message-ID: <bc6731bc-d972-40fd-836b-de80ee95fbe8@v17g2000hsa.googlegroups.com>
Plan hash value: 1533480498
| Id | Operation |
19 - filter(("B2"."PARKING_SPACE_TYPE_ID"<>4 AND "B2"."PARKING_SPACE_TYPE_ID"<>21 AND
"B2"."PARKING_SPACE_TYPE_ID"<>41))
filter(MIN(NVL("H2"."CREATED_DT",SYSDATE@!))>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
SQL_ID ac5j72kugr8b9, child number 0
select /*+ gather_plan_statistics */ 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),
| Id | Operation |
| 1 | SORT GROUP BY
| | 1 | 1 | 6 |00:02:53.24
| 304K| 130K| 75701 | 18432 | 18432 |16384 (0)| |
12 - access("TEMP0"."COL1"="V"."ID")
13 - filter(("V"."RECORD_START_DT"<NEW_TIME(TO_DATE('2008-01-01 23:59:59', 'yyyy-mm-dd
hh24:mi:ss'),INTERNAL_FUNCTION("I2"."TIME_ZONE"),'fmiyy') AND
access("TRANSACTIONS"."ID"="TRANSACTION_ACCOUNT_XREF"."TRANSACTION_ID") 35 - access("ACCOUNTS"."ID"="TRANSACTION_ACCOUNT_XREF"."ACCOUNT_ID") 39 -
filter(MIN(NVL("H2"."CREATED_DT",SYSDATE@!))>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Date: Fri, 25 Jan 2008 13:32:47 -0800 (PST)
Message-ID: <bc6731bc-d972-40fd-836b-de80ee95fbe8@v17g2000hsa.googlegroups.com>
Matthias: I found that there are many, many more histogram buckets on
server B than on server A. More is better, right :-) ?
David: Looking at DBMS_XPLAN output, it seems like the statistics are more complete on server B than on Server A.
Charles: Block size was changed as this is the "new" warehouse server to be used for reporting (no OLTP) and we decided larger would be better for this type of system. Perhaps not. System statistics are not gathered on either system, looking into the impact of this. Please find DBMS_XPLAN info below. I do not understand why the recursion is occurring on server B. I would like to try the NOT EXISTS option, but a naysayer here says that the fact the same query performs sooooooooo much worse on server B is proof that there is something inherently wrong with the server. I am starting to think he is right...
Here are the things I was able to try today.
I tried gathering statistics on the entire schema using
dbms_stats.gather_schema_stats (ownname=> 'OURSCHEMA', options=>
'GATHER', estimate_percent=> dbms_stats.auto_sample_size, method_opt=>
'for all columns size auto', cascade=> true, degree=> 5), but still
the same plan is used.
I tried exporting the statistics from server A and importing them on
server B, no luck.
Finally, I tried gathering maximum histogram info on server B using
dbms_stats.gather_table_stats(ownname => 'OURSCHEMA', tabname=>
'OURTABLE', method_opt=> 'for all indexed columns size 254', cascade=>
TRUE) for the tables that it seems to be having trouble making
decisions about (TRANSACTIONS, REQUESTS, VISITS, BERTHS) and still no
luck. Still banging away...
Here is the DBMS_XPLAN output for each of the queries, sorry if the formatting gets wrecked. The problems seems to be that server B is not aware of this equality "REQUESTS"."VISIT_ID"="V"."ID" but instead is using this filter: "REQUESTS"."VISIT_ID"=:B1 - but there is no bind variable there in the query.
Brian
Server A Plan
PLAN_TABLE_OUTPUT
Plan hash value: 1533480498
| Id | Operation |
Name | E-Rows | A-Rows | Buffers | Reads | Writes | A-Time | --------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY
| | 1 | 6 | 218K| 1
| 0 |00:00:07.36 |
|* 2 | HASH JOIN OUTER
| | 1 | 170 | 218K| 1
| 0 |00:00:07.35 |
|* 3 | HASH JOIN OUTER
| | 1 | 170 | 215K| 1
| 0 |00:00:01.82 |
| 4 | NESTED LOOPS
| | 1 | 170 | 38 | 0
| 0 |00:00:00.01 |
| 5 | TABLE ACCESS BY INDEX ROWID |
INSTALLATIONS | 1 | 1 | 2 | 0
| 0 |00:00:00.01 |
|* 6 | INDEX UNIQUE SCAN | INSTALLATIONS_PK | 1 | 1 | 1 | 0
| 0 |00:00:00.01 |
| 7 | TABLE ACCESS BY INDEX ROWID |
BERTHS | 1 | 170 | 36 | 0
| 0 |00:00:00.01 |
|* 8 | INDEX RANGE SCAN | BERTH_INSTALLATION_FK_I | 1 | 170 | 2 | 0
| 0 |00:00:00.01 |
| 9 | VIEW
| | 15 | 60 | 215K| 1
| 0 |00:00:01.82 |
| 10 | SORT GROUP BY
| | 15 | 60 | 215K| 1
| 0 |00:00:01.82 |
| 11 | VIEW
| | 15 | 90 | 215K| 1
| 0 |00:00:01.82 |
| 12 | SORT UNIQUE
| | 15 | 90 | 215K| 1
| 0 |00:00:01.82 |
| 13 | NESTED LOOPS
| | 1 | 730 | 215K| 1
| 0 |00:00:00.03 |
| 14 | NESTED LOOPS
| | 1 | 715 | 213K| 1
| 0 |00:00:01.94 |
| 15 | NESTED LOOPS
| | 5 | 156 | 208K| 0
| 0 |00:00:00.02 |
| 16 | NESTED LOOPS
| | 1 | 167 | 38 | 0
| 0 |00:00:00.01 |
| 17 | TABLE ACCESS BY INDEX ROWID |
INSTALLATIONS | 1 | 1 | 2 | 0
| 0 |00:00:00.01 |
|* 18 | INDEX UNIQUE SCAN | INSTALLATIONS_PK | 1 | 1 | 1 | 0
| 0 |00:00:00.01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | BERTHS | 1 | 167 | 36 | 0
| 0 |00:00:00.01 |
|* 20 | INDEX RANGE SCAN | BERTH_INSTALLATION_FK_I | 2 | 170 | 2 | 0
| 0 |00:00:00.01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | VISITS | 4 | 156 | 208K| 0
| 0 |00:00:01.31 |
|* 22 | INDEX RANGE SCAN | VIS_BER_FK_I | 1656 | 240K| 836 | 0
| 0 |00:00:00.25 |
| 23 | TABLE ACCESS BY INDEX ROWID |
REQUESTS | 1 | 715 | 5112 | 1
| 0 |00:00:00.04 |
|* 24 | INDEX RANGE SCAN | REQUEST_VISIT_FK_I | 1 | 715 | 4441 | 1
| 0 |00:00:00.04 |
| 25 | NESTED LOOPS
| | 7 | 63 | 4127 | 1
| 0 |00:00:00.03 |
| 26 | NESTED LOOPS
| | 7 | 63 | 3938 | 1
| 0 |00:00:00.03 |
| 27 | NESTED LOOPS
| | 7 | 63 | 3686 | 1
| 0 |00:00:00.03 |
| 28 | TABLE ACCESS BY INDEX ROWID|
REQUESTS | 7 | 778 | 1193 | 1
| 0 |00:00:00.01 |
|* 29 | INDEX RANGE SCAN | REQUEST_VISIT_FK_I | 7 | 778 | 459 | 0
| 0 |00:00:00.01 |
|* 30 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 1 | 63 | 2493 | 0
| 0 |00:00:00.02 |
|* 31 | INDEX RANGE SCAN | TRA_REQ_FK_I | 1 | 793 | 1714 | 0
| 0 |00:00:00.01 |
| 32 | TABLE ACCESS BY INDEX ROWID |
TRANSACTION_ACCOUNT_XREF | 1 | 63 | 252 | 0
| 0 |00:00:00.01 |
|* 33 | INDEX RANGE SCAN | TAX_TRA_FK_I | 1 | 63 | 189 | 0
| 0 |00:00:00.01 |
|* 34 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | 63 | 189 | 0
| 0 |00:00:00.01 |
|* 35 | INDEX RANGE SCAN | TRA_REQ_FK_I | 1 | 730 | 1436 | 0
| 0 |00:00:00.01 |
| 36 | VIEW
| | 625 | 8803 | 2693 | 0
| 0 |00:00:05.55 |
| 37 | SORT GROUP BY
| | 625 | 8803 | 2693 | 0
| 0 |00:00:05.54 |
| 38 | VIEW
| | 625 | 8809 | 2693 | 0
| 0 |00:00:05.52 |
|* 39 | FILTER
| | | 8809 | 2693 | 0
| 0 |00:00:05.50 |
| 40 | SORT GROUP BY
| | 625 | 70246 | 2693 | 0
| 0 |00:00:05.55 |
|* 41 | HASH JOIN OUTER
| | 12495 | 2732K| 2693 | 0
| 0 |00:00:02.83 |
|* 42 | TABLE ACCESS FULL | BERTH_HISTORY | 12495 | 70584 | 2201 | 0
| 0 |00:00:00.07 |
| 43 | INDEX FAST FULL SCAN |
BERTH_HISTORY_BERTH_ID_DT_N1 | 151K| 151K| 492 | 0
| 0 |00:00:00.15 |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - access("B1"."ID"="A"."BERTH_ID")
3 - access("B1"."ID"="U"."BERTH_ID")
6 - access("I1"."ID"=24)
8 - access("B1"."INSTALLATION_ID"=24)
filter("I1"."ID"="B1"."INSTALLATION_ID")
18 - access("I2"."ID"=24)
19 - filter(("B2"."PARKING_SPACE_TYPE_ID"<>4 AND "B2"."PARKING_SPACE_TYPE_ID"<>21 AND
"B2"."PARKING_SPACE_TYPE_ID"<>41))
20 - access("B2"."INSTALLATION_ID"=24)
filter("I2"."ID"="B2"."INSTALLATION_ID")
21 - filter(("V"."RECORD_START_DT"<NEW_TIME(TO_DATE('2008-01-01
23:59:59', 'yyyy-mm-dd hh24:mi:ss'),"I2"."TIME_ZONE",'fmiyy') AND
NVL("V"."RECORD_END_DT",SYSDATE@!)>NEW_TIME(TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),"I2"."TIME_ZONE",'fmiyy')))
22 - access("B2"."ID"="V"."BERTH_ID")
filter("V"."BERTH_ID" IS NOT NULL)
24 - access("REQUESTS"."VISIT_ID"="V"."ID")
filter( IS NULL)
29 - access("REQUESTS"."VISIT_ID"=:B1)
30 - filter("TRANSACTIONS"."FLEET_ID"=2)
31 - access("REQUESTS"."ID"="TRANSACTIONS"."REQUEST_ID")
33 -
access("TRANSACTIONS"."ID"="TRANSACTION_ACCOUNT_XREF"."TRANSACTION_ID")
34 - access("ACCOUNTS"."ID"="TRANSACTION_ACCOUNT_XREF"."ACCOUNT_ID")
35 - access("REQUESTS"."ID"="TRANSACTIONS"."REQUEST_ID")
39 -
filter(MIN(NVL("H2"."CREATED_DT",SYSDATE@!))>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
41 - access("H"."BERTH_ID"="H2"."BERTH_ID")
filter("H2"."CREATED_DT">"H"."CREATED_DT")
42 - filter(("H"."STATUS"='ACTIVE' AND
"H"."CREATED_DT"<TO_DATE('2008-01-01 23:59:59', 'yyyy-mm-dd
hh24:mi:ss')))
Server B Plan
PLAN_TABLE_OUTPUT
SQL_ID ac5j72kugr8b9, child number 0
select /*+ gather_plan_statistics */ 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('1/01/2008 23:59:59', 'MM/DD/
YYYY HH24:MI:SS')) -
GREATEST(v.start_dt, TO_DATE('1/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 idle2.requests.visit_id
col1 FROM idle2.transactions,
Plan hash value: 1051046198
PLAN_TABLE_OUTPUT
| Id | Operation |
Name | Starts | E-Rows | A-Rows | A-Time |Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
| 1 | SORT GROUP BY
| | 1 | 1 | 6 |00:02:53.24
| 304K| 130K| 75701 | 18432 | 18432 |16384 (0)| |
|* 2 | HASH JOIN OUTER
| | 1 | 1 | 170 |00:02:53.24
| 304K| 130K| 75701 | 655K| 655K| 1111K (0)| |
|* 3 | HASH JOIN OUTER
| | 1 | 1 | 170 |00:02:50.76
| 302K| 130K| 75701 | 660K| 660K| 1096K (0)| |
| 4 | NESTED LOOPS
| | 1 | 1 | 170 |00:00:00.01
| 19 | 0 | 0 | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID |
INSTALLATIONS | 1 | 1 | 1 |00:00:00.01
| 2 | 0 | 0 | | | | |
|* 6 | INDEX UNIQUE SCAN | INSTALLATIONS_PK | 1 | 1 | 1 |00:00:00.01
| 1 | 0 | 0 | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID |
BERTHS | 1 | 1 | 170 |00:00:00.01
| 17 | 0 | 0 | | | | |
|* 8 | INDEX RANGE SCAN | BERTH_INSTALLATION_FK_I | 1 | 1 | 170 |00:00:00.01
| 2 | 0 | 0 | | | | |
| 9 | VIEW
| | 1 | 2 | 60 |00:02:50.75
| 302K| 130K| 75701 | | | | |
| 10 | HASH GROUP BY
| | 1 | 2 | 60 |00:02:50.75
| 302K| 130K| 75701 | | | | |
|* 11 | FILTER
| | 1 | | 90 |00:02:36.61
| 302K| 130K| 75701 | | | | |
|* 12 | HASH JOIN
| | 1 | 2 | 153 |00:02:32.82
| 298K| 130K| 75701 | 686K| 686K| 1105K (0)| |
|* 13 | TABLE ACCESS BY INDEX ROWID | VISITS | 1 | 4 | 156 |00:00:01.32
| 210K| 0 | 0 | | | | |
| 14 | NESTED LOOPS
| | 1 | 5 | 240K|00:00:01.70
| 768 | 0 | 0 | | | | |
| 15 | NESTED LOOPS
| | 1 | 1 | 167 |00:00:00.01
| 18 | 0 | 0 | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID|
INSTALLATIONS | 1 | 1 | 1 |00:00:00.01
| 2 | 0 | 0 | | | | |
|* 17 | INDEX UNIQUE SCAN | INSTALLATIONS_PK | 1 | 1 | 1 |00:00:00.01
| 1 | 0 | 0 | | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID| BERTHS | 1 | 1 | 167 |00:00:00.01
| 16 | 0 | 0 | | | | |
|* 19 | INDEX RANGE SCAN | BERTH_INSTALLATION_FK_I | 1 | 2 | 170 |00:00:00.01
| 2 | 0 | 0 | | | | |
|* 20 | INDEX RANGE SCAN | VIS_BER_FK_I | 167 | 1840 | 240K|00:00:01.46
| 750 | 0 | 0 | | | | |
| 21 | VIEW
| | 1 | 3661K| 8013K|00:04:29.44
| 87762 | 130K| 75701 | | | | |
| 22 | HASH UNIQUE
| | 1 | 3661K| 8013K|00:03:17.32
| 87762 | 130K| 75701 | | | | |
|* 23 | HASH JOIN
| | 1 | 26M| 28M|00:04:14.21
| 87762 | 109K| 54656 | 545M| 18M| 16M (1)| 868K|
| 24 | INDEX FAST FULL SCAN |
TRA_REQ_FK_I | 1 | 28M| 28M|00:02:49.08
| 32906 | 0 | 0 | | | | |
| 25 | TABLE ACCESS FULL |
REQUESTS | 1 | 26M| 26M|00:02:38.55
| 54856 | 54431 | 0 | | | | |
| 26 | NESTED LOOPS
| | 153 | 7 | 63 |00:00:00.08
| 4766 | 0 | 0 | | | | |
| 27 | NESTED LOOPS
| | 153 | 7 | 63 |00:00:00.08
| 4577 | 0 | 0 | | | | |
| 28 | NESTED LOOPS
| | 153 | 7 | 63 |00:00:00.07
| 4324 | 0 | 0 | | | | |
| 29 | TABLE ACCESS BY INDEX ROWID |
REQUESTS | 153 | 7 | 778 |00:00:00.02
| 1208 | 0 | 0 | | | | |
|* 30 | INDEX RANGE SCAN | REQUEST_VISIT_FK_I | 153 | 7 | 778 |00:00:00.01
| 459 | 0 | 0 | | | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | TRANSACTIONS | 778 | 1 | 63 |00:00:00.04
| 3116 | 0 | 0 | | | | |
|* 32 | INDEX RANGE SCAN | TRA_REQ_FK_I | 778 | 1 | 793 |00:00:00.02
| 2336 | 0 | 0 | | | | |
| 33 | TABLE ACCESS BY INDEX ROWID |
TRANSACTION_ACCOUNT_XREF | 63 | 1 | 63 |00:00:00.01
| 253 | 0 | 0 | | | | |
|* 34 | INDEX RANGE SCAN | TAX_TRA_FK_I | 63 | 1 | 63 |00:00:00.01
| 189 | 0 | 0 | | | | |
|* 35 | INDEX UNIQUE SCAN | ACCOUNT_PK | 63 | 1 | 63 |00:00:00.01
| 189 | 0 | 0 | | | | |
| 36 | VIEW
| | 1 | 625 | 8803 |00:00:02.62
| 1349 | 0 | 0 | | | | |
| 37 | HASH GROUP BY
| | 1 | 625 | 8803 |00:00:02.54
| 1349 | 0 | 0 | | | | |
| 38 | VIEW
| | 1 | 625 | 8809 |00:00:02.61
| 1349 | 0 | 0 | | | | |
|* 39 | FILTER
| | 1 | | 8809 |00:00:02.52
| 1349 | 0 | 0 | | | | |
| 40 | HASH GROUP BY
| | 1 | 625 | 70246 |00:00:02.87
| 1349 | 0 | 0 | | | | |
|* 41 | HASH JOIN OUTER
| | 1 | 12495 | 2732K|00:00:16.47
| 1349 | 0 | 0 | 3024K| 949K| 5728K (0)| |
|* 42 | TABLE ACCESS FULL | BERTH_HISTORY | 1 | 12495 | 70584 |00:00:00.42
| 1105 | 0 | 0 | | | | |
| 43 | INDEX FAST FULL SCAN |
BERTH_HISTORY_BERTH_ID_DT_N1 | 1 | 151K| 151K|00:00:00.91
| 244 | 0 | 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("B1"."ID"="A"."BERTH_ID")
3 - access("B1"."ID"="U"."BERTH_ID")
6 - access("I1"."ID"=24)
8 - access("B1"."INSTALLATION_ID"=24)
filter("I1"."ID"="B1"."INSTALLATION_ID")
11 - filter( IS NULL)
12 - access("TEMP0"."COL1"="V"."ID")
13 - filter(("V"."RECORD_START_DT"<NEW_TIME(TO_DATE('2008-01-01 23:59:59', 'yyyy-mm-dd
hh24:mi:ss'),INTERNAL_FUNCTION("I2"."TIME_ZONE"),'fmiyy') AND
NVL("V"."RECORD_END_DT",SYSDATE@!)>NEW_TIME(TO_DATE('2008-01-01
00:00:00', 'yyyy-mm-dd
hh24:mi:ss'),INTERNAL_FUNCTION("I2"."TIME_ZONE"),'fmiyy')))
17 - access("I2"."ID"=24)
18 - filter(("B2"."PARKING_SPACE_TYPE_ID"<>4 AND
"B2"."PARKING_SPACE_TYPE_ID"<>21 AND
"B2"."PARKING_SPACE_TYPE_ID"<>41))
19 - access("B2"."INSTALLATION_ID"=24)
filter("I2"."ID"="B2"."INSTALLATION_ID")
20 - access("B2"."ID"="V"."BERTH_ID")
filter("V"."BERTH_ID" IS NOT NULL)
23 - access("REQUESTS"."ID"="TRANSACTIONS"."REQUEST_ID")
30 - access("REQUESTS"."VISIT_ID"=:B1)
31 - filter("TRANSACTIONS"."FLEET_ID"=2)
32 - access("REQUESTS"."ID"="TRANSACTIONS"."REQUEST_ID")
34 -
access("TRANSACTIONS"."ID"="TRANSACTION_ACCOUNT_XREF"."TRANSACTION_ID") 35 - access("ACCOUNTS"."ID"="TRANSACTION_ACCOUNT_XREF"."ACCOUNT_ID") 39 -
filter(MIN(NVL("H2"."CREATED_DT",SYSDATE@!))>TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
41 - access("H"."BERTH_ID"="H2"."BERTH_ID")
filter("H2"."CREATED_DT">"H"."CREATED_DT")
42 - filter(("H"."STATUS"='ACTIVE' AND
"H"."CREATED_DT"<TO_DATE('2008-01-01 23:59:59', 'yyyy-mm-dd
hh24:mi:ss')))
Received on Fri Jan 25 2008 - 15:32:47 CST