Re: performance issue after upgrade to oracle 220.127.116.11 linux 32 bit
Date: Sat, 21 Nov 2009 18:12:48 -0800 (PST)
On Nov 21, 3:27 pm, Randolf Geist <mah..._at_web.de> wrote:
> 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
> 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.
> 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-Oracle-Practices-Database-Administration/...
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.
Please double-check to make certain that a SQL profile does not exist
for the good performing query. I created a test here (with 18.104.22.168,
but I could have used 22.214.171.124) 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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Nov 21 2009 - 20:12:48 CST