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