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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 20 Nov 2009 08:15:18 -0800 (PST)
Message-ID: <12d92847-592b-46a6-8cde-3a790ff23f26_at_p35g2000yqh.googlegroups.com>



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_sqltune.htm http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sql_tune.htm

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.
Received on Fri Nov 20 2009 - 10:15:18 CST

Original text of this message