Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit

From: lsllcm <lsllcm_at_gmail.com>
Date: Sat, 21 Nov 2009 07:37:37 -0800 (PST)
Message-ID: <c7bc0065-fd47-4eb8-a7fe-69177608385b_at_t11g2000prh.googlegroups.com>

On 11月21日, 上午12時15分, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 20, 10:30 am, lsllcm <lsl..._at_gmail.com> wrote:
>
> > Hi Charles,
>
> > I have put the 10053 trace here along with query
> > 1. Good plan
> (snip)
> > from 10053 trace file
> > Final query after transformations:******* UNPARSED QUERY IS *******
> > SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
> > "F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
> > MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
> > MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
> > (INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
> > (INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
>
> (snip)
>
> > 2. Poor plan
> (snip)
> > from 10053 trace file
> > Final query after transformations:******* UNPARSED QUERY IS *******
> > SELECT "X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
>
> (snip)
>
> Notice in the good plan all of the OPT_ESTIMATE hints.  It appears
> that someone used the (extra cost) SQL Tuning Advisor feature in the
> database to create a SQL profile for one of the SQL statements, but
> not the other.  This essentially added hints to the SQL statement to
> help the optimizer correct cardinality and selectivity problems which
> caused poor performance problems in the past.  See this link for a
> quick explanation:http://jonathanlewis.wordpress.com/2007/02/11/profiles/
>
> Longer descriptions are available from the documentation:http://download.oracle.com/docs/cd/E11882_01/server.112/e10822/tdppt_...http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sql_tu...
>
> Compare the WHERE clauses between the Good execution:
> WHERE
>       "S"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "S"."SET_ID"='CONNIE'
>   AND "S"."REC_STATUS"='A'
>   AND "S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE"
>   AND "F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE"
>   AND "S"."B1_PER_ID1"="X"."B1_PER_ID1"
>   AND "S"."B1_PER_ID2"="X"."B1_PER_ID2"
>   AND "S"."B1_PER_ID3"="X"."B1_PER_ID3"
>   AND "F"."B1_PER_ID1"="S"."B1_PER_ID1"
>   AND "F"."B1_PER_ID2"="S"."B1_PER_ID2"
>   AND "F"."B1_PER_ID3"="S"."B1_PER_ID3"
>   AND "F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR"
>   AND "S"."REC_STATUS"="X"."REC_STATUS"
>   AND "F"."REC_STATUS"="X"."REC_STATUS"
>   AND ("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
> "X"."PAYMENT_FEEITEM_STATUS" IS NULL)
>   AND "X"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "F"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "X"."REC_STATUS"='A'
>   AND "F"."REC_STATUS"='A'
>
> And the Bad execution:
>        S.SERV_PROV_CODE = 'SACRAMENTO'
>    AND S.SET_ID = 'CONNIE'
>    AND S.REC_STATUS = 'A'
>    AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
>    AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
>    AND S.B1_PER_ID1 = X.B1_PER_ID1
>    AND S.B1_PER_ID2 = X.B1_PER_ID2
>    AND S.B1_PER_ID3 = X.B1_PER_ID3
>    AND F.B1_PER_ID1 = X.B1_PER_ID1
>    AND F.B1_PER_ID2 = X.B1_PER_ID2
>    AND F.B1_PER_ID3 = X.B1_PER_ID3
>    AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
>    AND S.REC_STATUS = X.REC_STATUS
>    AND F.REC_STATUS = X.REC_STATUS
>    AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
> X.PAYMENT_FEEITEM_STATUS IS NULL)
>
> You will probably notice that these generated predicates appear in the
> Good execution plan due to transitive closure - I do not know why they
> do not appear in the bad pan (maybe an altered optimizer parameter?):
>   AND "X"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "F"."SERV_PROV_CODE"='SACRAMENTO'
>   AND "X"."REC_STATUS"='A'
>   AND "F"."REC_STATUS"='A'
>
> As an experiment, test the performance of this SQL statement which
> uses the hints and generated predicates from the fast execution:
> SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
> "F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
> MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
> MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
> (INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
> (INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
>        X.SERV_PROV_CODE,
>        X.B1_PER_ID1,
>        X.B1_PER_ID2,
>        X.B1_PER_ID3,
>        SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
>   FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
>  WHERE
>        S.SERV_PROV_CODE = 'SACRAMENTO'
>    AND S.SET_ID = 'CONNIE'
>    AND S.REC_STATUS = 'A'
>    AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
>    AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
>    AND S.B1_PER_ID1 = X.B1_PER_ID1
>    AND S.B1_PER_ID2 = X.B1_PER_ID2
>    AND S.B1_PER_ID3 = X.B1_PER_ID3
>    AND F.B1_PER_ID1 = X.B1_PER_ID1
>    AND F.B1_PER_ID2 = X.B1_PER_ID2
>    AND F.B1_PER_ID3 = X.B1_PER_ID3
>    AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
>    AND S.REC_STATUS = X.REC_STATUS
>    AND F.REC_STATUS = X.REC_STATUS
>    AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
> X.PAYMENT_FEEITEM_STATUS IS NULL)
>  AND "X"."SERV_PROV_CODE"='SACRAMENTO'
>  AND "F"."SERV_PROV_CODE"='SACRAMENTO'
>  AND "X"."REC_STATUS"='A'
>  AND "F"."REC_STATUS"='A'
> GROUP BY
>   X.SERV_PROV_CODE,
>   X.B1_PER_ID1,
>   X.B1_PER_ID2,
>   X.B1_PER_ID3;
>
> If the SQL statement completes as quickly as the Good execution,
> remove the following transitive closure generated predicates from the
> WHERE clause and test again:
>  AND "X"."SERV_PROV_CODE"='SACRAMENTO'
>  AND "F"."SERV_PROV_CODE"='SACRAMENTO'
>  AND "X"."REC_STATUS"='A'
>  AND "F"."REC_STATUS"='A'
>
> If performance is still slow, and you are licensed to use the SQL
> Tuning Advisor, try to create a SQL Profile for the original slow
> version of the query to essentially lock the hinted cardinality and
> selectivity hints into the SQL statement.
>
> --
> Suggestions from anyone else?
> --
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Thanks Charles,

I have checked sql profiles, there is no sql profiles in the db, below sql returns no rows

select name, status
from dba_sql_profiles
where status = 'ENABLED'
;

I have tried below sql, the execution plan is same as bad plan

SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE "F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK" MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK" MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE (INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE (INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */

       X.SERV_PROV_CODE,
       X.B1_PER_ID1,
       X.B1_PER_ID2,
       X.B1_PER_ID3,
       SUM(NVL(X.Fee_Allocation, 0)) GF_FEE

  FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F  WHERE

       S.SERV_PROV_CODE = 'SACRAMENTO'
   AND S.SET_ID = 'CONNIE'
   AND S.REC_STATUS = 'A'
   AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
   AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
   AND S.B1_PER_ID1 = X.B1_PER_ID1
   AND S.B1_PER_ID2 = X.B1_PER_ID2
   AND S.B1_PER_ID3 = X.B1_PER_ID3
   AND F.B1_PER_ID1 = X.B1_PER_ID1
   AND F.B1_PER_ID2 = X.B1_PER_ID2
   AND F.B1_PER_ID3 = X.B1_PER_ID3
   AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
   AND S.REC_STATUS = X.REC_STATUS
   AND F.REC_STATUS = X.REC_STATUS

   AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR X.PAYMENT_FEEITEM_STATUS IS NULL)

 AND "X"."SERV_PROV_CODE"='SACRAMENTO'
 AND "F"."SERV_PROV_CODE"='SACRAMENTO'
 AND "X"."REC_STATUS"='A'
 AND "F"."REC_STATUS"='A'

GROUP BY

  X.SERV_PROV_CODE,
  X.B1_PER_ID1,
  X.B1_PER_ID2,
  X.B1_PER_ID3;

Thanks
lsllcm Received on Sat Nov 21 2009 - 09:37:37 CST