Re: CBO tuning help needed

On Jan 25, 4:32 pm, bhonaker <bhona..._at_gmail.com> wrote:
> 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
>

(Snip)

The DBMS_XPLANs helped. After reviewing the explain plans, the performance problem is very likely NOT caused by a difference in the transformation of the NOT IN clause. There is an interesting difference in the explain plans. The fast executing plan uses the 9i and older "SORT GROUP BY", while the slow running query uses the 10g style "HASH GROUP BY" which does not perform an implicit sort during the GROUP BY. This may be an indication that there are differences in the parameters between the two databases. You might try checking the value of OPTIMIZER_FEATURES_ENABLE in each database, and also see if any parameters beginning with _ are set in the SPFILEs on each of the database servers.

NOT IN CLAUSE for the slow execution

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

NOT IN CLAUSE fast execution, essentially the same
|  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 |

--

Now the interesting part.  The slow portion of the query is this:
    (SELECT DISTINCT
      REQUESTS1.visit_id COL1
    FROM
      idle2.transactions TRANSACTIONS1,
      idle2.requests REQUESTS1
    WHERE
      REQUESTS1.id = TRANSACTIONS1.request_id) TEMP0

In the slow running query, that portion of the plan looks like this:
|  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 |       |        |          |         |

In the above, Oracle is performing a full tablescan on the REQUESTS
table in order to build a unique/distinct 26 million row list of
REQUESTS.VISIT_ID values.  Things are a bit different in the fast
query - Oracle does not build a distinct list of REQUESTS.VISIT_ID 26
milltion values.  Instead, Oracle drives into the TEMP0 view using
IDLE2.VISITS.ID column, allowing Oracle to perform an index lookup on
the REQUESTS table, thus retrieving just 715 rows, rather than having
to deal with a 26 million distinct list of VISIT_IDs.  This technique
is known as pushing predicates into views.  See the following for a
clear understanding:
  http://www.jlcomp.demon.co.uk/push_pred.html

You may be able to force Oracle 10.2.0.3 to push the predicate into
the view by using a hint directly after the word SELECT.  With the
hint, that section of the query might look like this (you might
instead try the hint   /*+ PUSH_PRED(TEMP0) ORDERED */   ):
  (SELECT /*+ PUSH_PRED(TEMP0) */
    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 (
...

Changing the value of OPTIMIZER_FEATURES_ENABLE on the new server so
that it matches the old server may be enough to allow Oracle to self-
correct the problem.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.