Re: CBO tuning help needed

On Jan 24, 4:32 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

Thank you all for your quick responses, I am looking into all 3 suggestions and hope to post more info tomorrow. Received on Thu Jan 24 2008 - 16:02:05 CST