Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Sun, 8 Nov 2009 05:36:34 -0800 (PST)
Message-ID: <20fc239e-1740-4a33-820f-da3a77f2ac80_at_k17g2000yqh.googlegroups.com>
On Nov 7, 9:13 am, lsllcm <lsl..._at_gmail.com> wrote:
> Thanks Charles and Randolf for your comments:
Thanks for running all of the test cases. Many of the plans produced are identical, but none show the same plan as you posted for 11.1.0.6 and 11.2.0.1.
> Below are test cases and results.
>
> ------------------------case
> 1------------------------------------------------
> 1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */
>
> Result: the result is same as no index hint
It appears that the optimizer was originally using the SETDETAILS_PID_IX index based on your first post in this thread, so it does appear that the index hint did actually change the plan when using your original OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING parameter settings.
(snip)
> SQL> exec :efd:='2008-12-04 23:59:59';
> PL/SQL procedure successfully completed.
> Elapsed: 00:00:00.37
>
> SQL> rem exec :p1:='254413'
> SQL> exec :p1:='SET07'
> PL/SQL procedure successfully completed.
> Elapsed: 00:00:00.03
>
> SQL> exec :p2:='Contractor'
> PL/SQL procedure successfully completed.
> Elapsed: 00:00:00.18
It seems a little odd that some of these commands to set bind variable values requires 370ms - about 1/2 as long as it takes to actually execute the SQL statement with OPTIMIZER_MODE set to ALL_ROWS. I am not sure if there is significance to this observation, or not.
(snip)
> 6------------------------------------------------
> 6. If 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.
> [lsllcm] There are many queries like the case in our environment. So
> we use FIRST_ROWS_100. I will test different queries in both
> FIRST_ROWS_100 and ALL_ROWS in 10g db and see difference.
>
> ------------------------item
> 7------------------------------------------------
> Is it a special case that no rows are returned due to some of the
> bind
> values used? How many rows does this kind of statement return
> typically?
>
> [lsllcm] The data in table SETDETAILS is skew, most of them have less
> than 100 rows returned
> based on SETDETAILS_SETID_IX. 10 of them have from 10,000 - 40,000
> rows returned.
Skewed data and bind variables may lead to problems with the re-use of plans, especially if histograms are present. Oracle 11.2.0.1 (with OPTIMIZER_MODE = ALL_ROWS) might be trying to build a "safer" plan which will work reasonably well for 10 or 40,000 rows, while 11.1.0.6 and 10.2.0.4 appear to be relying on a short-circuit in the plan which required only 3 logical IOs. Any idea how the plan produced on the older releases of Oracle might have handled the situations where the plan returned 40,000 rows? In other words, while the one case you have identified with returning 0 rows executes quickly, the case where 40,000 rows are returned might execute much slower than the 11.2.0.1 execution.
> I use below command to gather statistics.
>
> exec dbms_stats.gather_schema_stats
> ('TEST',estimate_percent=>100,cascade=> TRUE);
OK, the above collects table and index statistics, but I am not sure if that will collect the statistics on the hidden columns (Randolf or Jonathan should be able to answer this question).
> Below are histogram of SYS_NC00017$
What is the purpose of this index? This is the same index that I
mentioned previously that showed a strange IO cost in the 10053 trace
file:
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780 028538760589558632766878171540458953514382464234321326889464182768467546703 537516986049910576551282076245490090389328944075868508455133942304583236903 222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
>
> ------------------------item
> 8------------------------------------------------
> [lsllcm] The reason to set "_optimizer_cost_based_transformation is to
> check if
> the issue is related to the setting. Now I have set it back to
> default.
OK, so you temporarily set _optimizer_cost_based_transformation to false to check for changes in performance, and have returned the parameter to its original value. I might be remembering incorrectly, but I believe that there was a bug in 10.2.0.4, and the work-around for the bug required this hidden parameter to be set to false. That might explain why you saw that parameter set to false in 10.2.0.4.
> ------------------------item
> 9------------------------------------------------
> 9. 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)
>
> [lsllcm] I did not gather system statistics, and I check again
> scheduled job, no job
> gather system staitstics.
There is no automated job for *system* (CPU) statistics gathering -
the DBA must tell Oracle when to collect the statistics using
DBMS_STATS.GATHER_SYSTEM_STATS.
From:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#i41496
"System statistics describe the system's hardware characteristics,
such as I/O and CPU performance and utilization, to the query
optimizer. When choosing an execution plan, the optimizer estimates
the I/O and CPU resources required for each query. System statistics
enable the query optimizer to more accurately estimate I/O and CPU
costs, enabling the query optimizer to choose a better execution
plan."
Jonathan has also written several articles on the topic, here are a couple of those articles:
http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/ http://jonathanlewis.wordpress.com/2007/05/20/system-stats-strategy/ http://jonathanlewis.wordpress.com/2007/10/17/system-statistics-3/> 10. As you suggested, I change the optimizer setting into default as
> 10------------------------------------------------
> combination ALL_ROWS + OICA (100) + OIC (0) in test environment. The
> result is
> system looks like to become stable. Even if the query does not choose
> best
> execution plan, it chooses not worst execution plan. Like the test
> case above,
> it has 3 consistent reads in 10g db (it should be best execution
> plan). In 11g
> (ALL_ROWS), it has 3656 consistent reads (it is not best, but it is
> not worst
> too). In 11g (FIRST_ROWS_100), it has 2891740 consistent gets(like
> worst one).
It is good that you found a suitable plan, which might be suitable if 0 rows, 10 rows, or 40,000 rows are returned.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Nov 08 2009 - 07:36:34 CST