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 04:07:37 -0800 (PST)
Message-ID: <cddc6895-aa42-4f5b-9c3f-ef9de7939386_at_m33g2000pri.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 Charles,

Thanks for your comments.

Maybe I miss something, I use below sql to check again. there is no records returned. Is there other method to check sql profile.

I did not use dbms_sqltune package to create sql profile. Maybe 11g create it automatically.

SQL>
SQL> select name, status
  2 from dba_sql_profiles
  3 ;

NAME                           STATUS
------------------------------ --------

Thanks
lsllcm Received on Sun Nov 22 2009 - 06:07:37 CST