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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 4 Nov 2009 14:26:03 -0800 (PST)
Message-ID: <ac1e1d14-d3ae-492e-a9a5-2dec0069e24e_at_z41g2000yqz.googlegroups.com>



On Nov 4, 7:01 am, lsllcm <lsl..._at_gmail.com> wrote:
> The trace file is too long, I use three parts.
(Snip)
> ***************************************
> PARAMETERS USED BY THE OPTIMIZER
> ********************************
> *************************************
> PARAMETERS WITH ALTERED VALUES
> ******************************
> Compilation Environment Dump
> optimizer_features_enable = 11.1.0.6
> optimizer_mode = first_rows_100
> optimizer_index_cost_adj = 10
> optimizer_index_caching = 90
> _optimizer_cost_based_transformation = off
> Bug Fix Control Environment

I agree with Randolf's points.

I attempted to run through the trace file, but I believe that there are sections of the trace file that are missing - at least from what I am able to see.

Just a couple questions:

Why is "_optimizer_cost_based_transformation = off" specified?
Why is "optimizer_mode = first_rows_100" specified?
Why is "optimizer_index_cost_adj = 10" specified?
Why is "optimizer_index_caching = 90" specified?

The system statistics show the following:



SYSTEM STATISTICS INFORMATION

  Using NOWORKLOAD Stats
  CPUSPEEDNW: 2696 millions instructions/sec (default is 100)   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)   IOSEEKTIM: 10 milliseconds (default is 10)   MBRC: -1 blocks (default is 8)

You might try collecting system statistics during a busy time period. To collect the system statistics with a 60 minute monitoring period, enter the following in SQL*Plus (the SQL*Plus command prompt will return immediately):
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60)

The SETDETAILS table should probably be one of the first tables accessed, if not the first. There are a couple interesting statistics showing up in the calculations:

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for SETDETAILS[SD]

  Column (#17):
    NewDensity:0.000065, OldDensity:0.002105 BktCnt:254, PopBktCnt:
194, PopValCnt:19, NDV:3664

  Column (#3):
    NewDensity:0.000063, OldDensity:0.001961 BktCnt:254, PopBktCnt: 196, PopValCnt:20, NDV:3665
  ColGroup (#8, Index) SETDETAILS_IX
    Col#: 1 3 4 5 6 CorStregth: 11963.23   ColGroup (#2, Index) SETDETAILS_PID_IX     Col#: 1 4 5 6 CorStregth: 7.07
  ColGroup (#1, Index) SETDETAILS_SETID1_IX     Col#: 1 3 CorStregth: 1.00
  ColGroup (#3, Index) SETDETAILS_SETID_IX     Col#: 1 17 CorStregth: 1.00
  ColGroup (#5, Index) SETDETAILS_STAT_IX     Col#: 1 15 CorStregth: -1.00
  ColGroup (#7, Index) SETDETAILS_PK
    Col#: 1 2 CorStregth: -1.00
  ColGroup (#6, Index) SETDETAILS_PAR_IX     Col#: 12 13 CorStregth: -1.00
  ColGroup (#4, Index) SETDETAILS_ADDR_IX     Col#: 1 14 CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
  • Virtual column Adjustment ****** Column name SYS_NC00017$ cost_cpu 150.00 cost_io 179769313486231570814527423731704356798070567525844996598917476803157260780­ 028538760589558632766878171540458953514382464234321326889464182768467546703­ 537516986049910576551282076245490090389328944075868508455133942304583236903­ 222948165808559332123348274797826204144723168738177180919299881250404026184­ 124858368.00

Is SYS_NC00017$ the virtual column for a function based index, maybe on UPPER("SET_ID")? I wonder if Oracle was able to calculate positive infinity correctly? :-)

--

Oracle only tried 19 join orders, ending with this:
(newjo-stop-1) k:0, spcnt:0, perm:19, maxperm:1000
*********************************
Number of join permutations tried: 19

With 4 tables involved, there are 4! (24) possible join orders for the
tables, so I am not sure if Oracle aborted the join orders early?

--

Oracle found that the join order 9 was the lowest cost per this output
in the trace file:
Final cost for query block SEL$1 (#0) - First K Rows Plan:
  Best join order: 9
  Cost: 4.5006  Degree: 1  Card: 117.0000  Bytes: 27495
  Resc: 4.5006  Resc_io: 4.5000  Resc_cpu: 20442
  Resp: 4.5006  Resp_io: 4.5000  Resc_cpu: 20442

That join order is:
Join order[9]:  F4INVOICE[FINV]#1  X4FEEITEM_INVOICE[XFI]#2
SETDETAILS
[SD]#0  F4FEEITEM[F4]#3
...
SM Join
  SM cost: 32953.73
     resc: 32953.73 resc_io: 32880.40 resc_cpu: 2372333225
     resp: 32953.73 resp_io: 32880.40 resp_cpu: 2372333225
  Outer table:  SETDETAILS  Alias: SD
    resc: 11877.47  card 90789.89  bytes: 193  deg: 1  resp: 11877.47
  Inner table:  F4FEEITEM  Alias: F4
    resc: 1107.45  card: 344923.77  bytes: 42  deg: 1  resp: 1107.45
    using dmeth: 2  #groups: 1
    Cost per ptn: 1763.63  #ptns: 1
    hash_area: 124 (max=10240) buildfrag: 2272  probefrag: 2274
ppasses: 1
  Hash join: Resc: 14748.55  Resp: 14748.55  [multiMatchCost=0.00]
HA Join
  HA cost: 14748.55
     resc: 14748.55 resc_io: 14722.80 resc_cpu: 833107904
     resp: 14748.55 resp_io: 14722.80 resp_cpu: 833107904
Join order aborted: cost > best plan cost

Without the FIRST_ROWS_100 optimizer mode, Oracle should have selected
a different plan (if I am not missing sections of the 10053 trace
between the join order start and the rejection of the join order due
to the cost of the join order before applying the adjustment for the
FIRST_ROWS_100 optimizer mode to avoid the sort).

--

Suggestions (initially just at the session level):
Gather statistics on all objects in the schema, including virtual
columns (also specify NO_INVALIDATE=>FALSE)
At the session level, Set optimizer_mode = ALL_ROWS
At the session level, Set optimizer_index_cost_adj = 90
At the session level, Set optimizer_index_caching = 10
At the session level, Set _optimizer_cost_based_transformation = on
Run the SQL statement - did the plan change, and did the execution
time change?  If not, are you able to modify the query to add a
LEADING hint?

If the plan is better, wait until the database instance is very busy,
then start the system statistics gathering process (with
DBMS_STATS.GATHER_SYSTEM_STATS).  Consider setting the
_optimizer_cost_based_transformation, optimizer_index_caching,
optimizer_index_cost_adj, and optimizer_mode back to the default
values at the system level:
Set optimizer_mode = ALL_ROWS
Set optimizer_index_cost_adj = 100
Set optimizer_index_caching = 0
Set _optimizer_cost_based_transformation = on

-

Randolf is better at reading 10053 trace files than I am, so he might
see something else that I missed in the file.

-

Randolf: regarding the optimizer bug that was fixed in 11.2.0.1 which
was related to ROWNUM and incorrect cardinality estimates, I was
unable to force 11.2.0.1 to reproduce the incorrect cardinality
estimates by changing the OPTIMIZER_FEATURES_ENABLE parameter to match
a lower release number.  So, I guess that some fixes are buried deep
in the code.  I even tried disabling some of the bug fixes without
success.  This seems to imply that changing the
OPTIMIZER_FEATURES_ENABLE to an older version may not always reproduce
the old optimizer behavior, which is a bit unfortunate.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 04 2009 - 16:26:03 CST

Original text of this message