Re: CBO tuning help needed

Comments in-line.

On Jan 24, 3: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

Why the change in 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

Have you gathered system statistics for both databases instances?

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

Are you able to provide DBMS_XPLAN in the format shown on this page, it might help determine if the optimizer is predicting the wrong number of rows returned at each stage:
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

Did you gather the statistics with CASCADE=>TRUE specified?

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

Is it odd that recursive calls is 0 on one server and 1279 on the other? Your SQL statement includes:
new_time(TO_DATE('01/01/2008 23:59:59', 'MM/DD/YYYY HH24:MI:SS'), I2.time_zone, 'EST')

I am guessing that NEW_TIME might be triggering the recursive call.

What appears to be most different in the plan is the handling of:

    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)

Your problems start with the hash join of the TRA_REQ_FK_I index and REQUESTS table, followed by the hash unique - the plan shows that the hash unique is using 767MB of temp space.

You might try rewriting the "TEMP0.COL1 NOT IN" portion of the SQL statement using NOT EXIST syntax or as an inline view.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jan 24 2008 - 15:32:39 CST