Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Thu, 5 Nov 2009 12:14:39 -0800 (PST)
Message-ID: <51d39d4b-5214-44cf-940b-853101717bc7_at_r24g2000yqd.googlegroups.com>
On Nov 5, 11:37 am, lsllcm <lsl..._at_gmail.com> wrote:
> Thank for your good suggestions and comments:
>
> I have done some test based on your good suggestions: below are some
> test cases
>
> --------------------case 2--------------------------------------------
> 2. What plan do you get in 11.2 if you're setting
> OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to default
> values:
>
> Result: get below execution plan. The consistent read is 3656.
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> set autotrace trace
> SQL> set timing on
> SQL>
> SQL> variable spc varchar2(15);
> SQL> variable bfd varchar2(40);
> SQL> variable efd varchar2(40);
> SQL> variable p1 varchar2(60);
> SQL> variable p2 varchar2(60);
> SQL> exec :spc:='SACRAMENTO';
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL> exec :bfd:='2005-12-05';
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL> exec :efd:='2008-12-04 23:59:59';
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL> exec :p1:='254413'
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL> exec :p2:='Contractor'
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.01
> SQL>
> SQL> --alter session set optimizer_features_enable = "11.1.0.6";
> SQL> alter session set optimizer_index_caching = 0;
>
> Session altered.
>
> Elapsed: 00:00:00.00
> SQL> alter session set optimizer_index_cost_adj = 100;
>
> Session altered.
>
> Elapsed: 00:00:00.01
> SQL> alter session set optimizer_mode = ALL_ROWS;
>
> Session altered.
>
> Elapsed: 00:00:00.00
> SQL> --alter session set optimizer_mode = FIRST_ROWS_100;
> SQL>
> SQL>
> SQL> --SELECT
> SQL> SELECT /*+ index(SD SETDETAILS_SETID_IX) */
> 2 xfi.serv_prov_code,
> 3 xfi.b1_per_id1,
(snip)
>
> no rows selected
>
> Elapsed: 00:00:00.53
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3656 consistent gets
> 0 physical reads
> 0 redo size
> 1763 bytes sent via SQL*Net to client
> 381 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
The performance of the above is about 0.02 seconds slower than on the
older release of Oracle. Please try the test again without the index
hint. Also, you might need to force the join order with SETDETAILS as
the leading table (I am not sure why this is not happening
automatically based on the old 10053 trace):
/*+ LEADING(SD) */
> --------------------item 4--------------------------------------------
> 4. A general question: Is your application actually using the
> FIRST_ROWS_100 optimizer mode properly?
> Yes, most of applications are oltp type and return first of rows. So
> we choose aggressive OPTIMIZER_INDEX_COST_ADJ and
> OPTIMIZER_INDEX_CACHING
Even in OLTP type applications an OPTIMIZER_MODE of ALL_ROWS may be
appropriate. I believe that Randolf's comment was along the lines of:
your query is expected to retrieve 100,000 rows, but the application
front-end will ONLY read just the first 100 rows and then throw away
the rest of the query results without bother fetching the remaining
rows. That is probably not happening in your environment, and I
believe that is the intention of the FIRST_ROWS_100 optimizer mode.
A case in point where FIRST_ROWS_n may lead to problems (at least with Oracle 10.2.0.x) is poor performance resulting when querying certain data dictionary views when the OPTIMIZER_MODE is not set to ALL_ROWS - I believe that this situation is most severe when fixed object statistics have not been collected.
Take a look at the following links:
http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/
Likewise, it is generally not advised to modify OPTIMIZER_INDEX_COST_ADJ due to the index costing problem mentioned by Randolf (also covered in detail in the book "Troubleshooting Oracle Performance"), but to instead verify that suitable system statistics are present - see below.
> --------------------item 5--------------------------------------------
> 5. question
> 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?
> [lsllcm] The _optimizer_cost_based_transformation parameter is set
> useless, I remove the setting
> About other three parameters, please see item 4
Does the above comment mean that you removed the _optimizer_cost_based_transformation parameter?
> --------------------item 6--------------------------------------------
> 6. system statistics: There are about 10 scheduled jobs. I stop them
> at first.
>
> Thank you again
> lsllcm
I think that there is a misunderstanding about the system statistics
comment that I made. That comment was related to this showing up in
the 10053 trace file:
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)
That information is also viewed by executing this query:
SELECT
*
FROM
SYS.AUX_STATS$;
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Nov 05 2009 - 14:14:39 CST