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

From: lsllcm <lsllcm_at_gmail.com>
Date: Sun, 22 Nov 2009 03:36:48 -0800 (PST)
Message-ID: <f2008c0c-ae04-4c2f-b8e4-e3b7924b732d_at_u8g2000prd.googlegroups.com>



On 11月22日, 上午10时12分, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 21, 3:27 pm, Randolf Geist <mah..._at_web.de> wrote:
>
>
>
>
>
> > Charles,
>
> > it looks like the generated predicates appear in both traces I think,
> > the "good" and the "bad" one.
>
> > To the OP: You might encounter something along the lines I've outlined
> > here:http://oracle-randolf.blogspot.com/2009/10/multi-column-joins.html
>
> > Depending on the way you perform multi-column joins, the multi-column
> > sanity checks built into recent versions of the CBO might get bypassed
> > or not - that is one possible explanation, but with the amount of
> > information provided, these are only guesses.
>
> > Note that in both cases, the "good" and the "bad", you're not really
> > joining A->B->B->C or A->B->A->C, but both are different mixtures,
> > because if these join predicates:
>
> > Statement 1) AND F.REC_STATUS = X.REC_STATUS
>
> > Statement 2) AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
>
> > which you would need to turn into
>
> > Statement 1) AND F.REC_STATUS = S.REC_STATUS
>
> > Statement 2) AND F.SERV_PROV_CODE = X.SERV_PROV_CODE
>
> > to have achieved what you've described.
>
> > Can you confirm that these are actually the PK/FK relations between
> > those tables, no spurious join predicates on non-FK/PK columns?
>
> > You should run the statement with STATISTICS_LEVEL = ALL (or the
> > GATHER_PLAN_STATISTICS hint) to compare the estimates to the actual
> > cardinalities - you might have correlated columns among the join
> > columns that are not recognized by the optimizer by default.
>
> > It seems to be odd that you get these OPT_ESTIMATE hints but no SQL
> > Profile seems to be enabled - may be this is something new in 11.2,
> > but usually you should see a corresponding note in the "Notes" section
> > of an DBMS_XPLAN.DISPLAY output and in the 10053 trace file (e.g. a
> > reference to a plan found in the SPM - SQL Plan Management feature
> > introduced in 11.1). May be these hints get introduced by an Outline
> > or SQL Baseline from SPM.
>
> > Regards,
> > Randolf
>
> > Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> > Co-author of the upcoming "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430226684http://www.amazon.de/Expert......
>
> Randolf,
>
> Thanks for the correction - I think I just need a taller monitor.  It
> appears that I compared the optimizer's transformed version of the SQL
> statement that executed efficiently with the original version of the
> SQL statement submitted by the OP that executed slowly.
>
> lsllcm,
>
> Please double-check to make certain that a SQL profile does not exist
> for the good performing query.  I created a test here (with 11.1.0.7,
> but I could have used 11.2.0.1) with a SQL statement that executed
> slowly due to a couple problems (inaccurate statistics on one of the
> tables was the primary problem).  The 10053 trace for my test query
> included the following:
> Final query after transformations:******* UNPARSED QUERY IS *******
> SELECT "VM_NWVW_1"."$vm_col_2" "TOP_LEVEL_PART_ID",DECODE(DECODE(DECODE
> (DECODE(DECODE(DECODE(NVL("VM_NWVW_1"."$vm_col_20",'NONE'), ...
>
> I then used the SQL tuning (DBMS_SQLTUNE) features in Enterprise
> Manager to "fix" the performance of the SQL statement.  Once finished,
> I accepted the changed execution plan, flushed the shared pool, and
> created another 10053 trace for the SQL statement.  The 10053 trace
> contained the following:
> Final query after transformations:******* UNPARSED QUERY IS *******
> SELECT /*+ OPT_ESTIMATE (JOIN ("PL" "P" "R2" "R")
> SCALE_ROWS=23.197274 ) OPT_ESTIMATE (JOIN ("PL" "R2" "R")
> SCALE_ROWS=23.197274 ) OPT_ESTIMATE (JOIN ("P" "R2" "R")
> SCALE_ROWS=25.482612 ) OPT_ESTIMATE (INDEX_FILTER "R6" "SYS_C0011548"
> SCALE_ROWS=30.516792 ) OPT_ESTIMATE (INDEX_FILTER "R6"
> "X_REQUIREMENT_5" SCALE_ROWS=30.516792 ) OPT_ESTIMATE (INDEX_SKIP_SCAN
> "R6" "X_REQUIREMENT_5" SCALE_ROWS=0.000985 ) OPT_ESTIMATE
> (INDEX_SKIP_SCAN "R6" "SYS_C0011548" SCALE_ROWS=0.000985 )
> OPT_ESTIMATE (TABLE "R6" SCALE_ROWS=32.231755 ) ...
>
> You will notice that much like your good performing SQL statement, my
> transformed SQL statement also contains many similar hints added by
> the SQL tuning task.
>
> For SQL profiles to work, the text of the SQL statements much match
> exactly (if I recall correctly, SQL profiles will tolerate varying
> amounts of white space).  That would explain why you see a different
> plan when you "JOIN B and A JOIN C" than you do when you "JOIN B and B
> JOIN C" - the text of the two SQL statements does not match exactly,
> so an existing SQL profile would apply to only one of the SQL
> statements.
>
> It is a bit odd that you did not see a difference in the performance
> with all of the hints appended from the good performing version of the
> SQL statement.  If I recall correctly, the queries actually produce
> many more rows than what the optimizer is predicting, so you might be
> experiencing the problem identified by Randolf.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- 隐藏被引用文字 -
>
> - 显示引用的文字 -

Hi Randolf,

Thanks for your comments:

I have tried the hidden parameter, the execution plan is as same as bad plan.

Table SETDETAILS, X4PAYMENT_FEEITEM, F4FEEITEM does not have PK/FK relationship. But they have below relationship.

Table SETDETAILS and X4PAYMENT_FEEITEM has same columns (SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3) Table SETDETAILS and F4FEEITEM has same columns (SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3)
Table X4PAYMENT_FEEITEM and F4FEEITEM has same columns (SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3,FEEITEM_SEQ_NBR) Another note: in oracle 10.2.0.4, the execution plan is as same as good plan. After test on 11.2.0.1, the execution plan is changed. We need to rewrite the sql for 11.2.0.1. We want to find the root cause. Because maybe there are many other queries has such issue.

Below are test case:

SQL> SELECT /*+ opt_param('_optimizer_join_sel_sanity_check', 'false') */

  2         X.SERV_PROV_CODE,
  3         X.B1_PER_ID1,
  4         X.B1_PER_ID2,
  5         X.B1_PER_ID3,
  6         SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
  7 FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F   8 WHERE
  9         S.SERV_PROV_CODE = 'SACRAMENTO'
 10     AND S.SET_ID = 'CONNIE'
 11     AND S.REC_STATUS = 'A'
 12     AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
 13     AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
 14     AND S.B1_PER_ID1 = X.B1_PER_ID1
 15     AND S.B1_PER_ID2 = X.B1_PER_ID2
 16     AND S.B1_PER_ID3 = X.B1_PER_ID3
 17     AND F.B1_PER_ID1 = X.B1_PER_ID1
 18     AND F.B1_PER_ID2 = X.B1_PER_ID2
 19     AND F.B1_PER_ID3 = X.B1_PER_ID3
 20     AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
 21     AND S.REC_STATUS = X.REC_STATUS
 22     AND F.REC_STATUS = X.REC_STATUS
 23     AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
 24 X.PAYMENT_FEEITEM_STATUS IS NULL)
 25   AND "X"."SERV_PROV_CODE"='SACRAMENTO'
 26   AND "F"."SERV_PROV_CODE"='SACRAMENTO'
 27   AND "X"."REC_STATUS"='A'
 28   AND "F"."REC_STATUS"='A'

 29 GROUP BY
 30    X.SERV_PROV_CODE,
 31    X.B1_PER_ID1,
 32    X.B1_PER_ID2,
 33    X.B1_PER_ID3

 34 /

Elapsed: 00:00:13.43
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST')) ; PLAN_TABLE_OUTPUT


SQL_ID d6g1xv7mjuymy, child number 0



SELECT /*+ opt_param('_optimizer_join_sel_sanity_check', 'false') */
    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 PLAN_TABLE_OUTPUT

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_

Plan hash value: 2372462434


PLAN_TABLE_OUTPUT


| Id  | Operation                       | Name                 | E-
Rows | OMem | 1Mem | Used-Mem |
|   0 | SELECT STATEMENT                |
|        |       |       |          |
|   1 |  HASH GROUP BY                  |                      |
1 |   745K|   745K| 1148K (0)|
|*  2 |   HASH JOIN                     |                      |
21543 |   744K|   744K| 1426K (0)|
|   3 |    NESTED LOOPS                 |
|        |       |       |          |
|   4 |     NESTED LOOPS                |                      |
95522 |       |       |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| SETDETAILS           |
24 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | SETDETAILS_SETID1_IX |
24 |       |       |          |
|*  7 |      INDEX RANGE SCAN           | X4PAYMENT_FEEITEM_PK |
1 |       |       |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM    |
3907 |       |       |          |

PLAN_TABLE_OUTPUT


|*  9 |    TABLE ACCESS FULL            | F4FEEITEM            |
1173K|       |       |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - access("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" 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               "F"."REC_STATUS"="X"."REC_STATUS")    5 - filter("S"."REC_STATUS"='A')

PLAN_TABLE_OUTPUT


   6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND "S"."SET_ID"='CONNIE')
   7 - access("X"."SERV_PROV_CODE"='SACRAMENTO' 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")
       filter("S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE")
   8 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR
              "X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND
"X"."REC_STATUS"='A' AND
              "S"."REC_STATUS"="X"."REC_STATUS"))
   9 - filter(("F"."SERV_PROV_CODE"='SACRAMENTO' AND "F"."REC_STATUS"='A')) Note

PLAN_TABLE_OUTPUT


  • Warning: basic plan statistics not available. These are only collected when:
    • hint 'gather_plan_statistics' is used for the statement or
    • parameter 'statistics_level' is set to 'ALL', at session or system level

59 rows selected.

Elapsed: 00:00:05.65
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Thanks
lsllcm Received on Sun Nov 22 2009 - 05:36:48 CST

Original text of this message