Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit
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_FEE7 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' OR24 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_ID1AND 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