--------------------case 1--------------------------------------------
1. What cost / plan do you get if you request to use the index
SETDETAILS_SETID_IX?

Result: as same as no index hint

--------------------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:

--------------------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

--------------------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

--------------------item 6--------------------------------------------
6. system statistics: There are about 10 scheduled jobs. I stop them
at first.

The trace file is too long, I use three parts.

*************************************
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

First K Rows: K = 100.00, N = 40640900.00
First K Rows: Setup end

"lsllcm" <lsl... at (no spam) gmail.com> wrote in message

news:871566ee-22ef-4895-bcc6-753269a7bc3a at (no spam) f18g2000prf.googlegroups.com...

The trace file is too long, I use three parts.

 *************************************
 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

Sorry about picking this one up so late.

I've had a quick read through the posts I can see at the moment.
The critical issue is (as Charles and Randolf have pointed out)
the use of first_rows_100, combined with Randolf's observation
that the expected result set is 40 million rows.

First K Rows: K = 100.00, N = 40640900.00
First K Rows: Setup end

When you don't have the order by clause, Oracle is working on a
plan that will pick up any 100 rows as quickly as possible.  When
you add the order by the optimizer "knows" it has to acquire 40M
rows and sort them before returning the first 100. Consequently
any path that avoids doing the sorting is likely to be a good path -
hence the choice of index to drive the query.

--
Regards

Jonathan Lewishttp://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

I've had a quick read through the posts I can see at the moment.
The critical issue is (as Charles and Randolf have pointed out)
the use of first_rows_100, combined with Randolf's observation
that the expected result set is 40 million rows.

First K Rows: K = 100.00, N = 40640900.00
First K Rows: Setup end

On Nov 7, 10:31 am, "Jonathan Lewis" <jonat... at (no spam) jlcomp.demon.co.uk
wrote:

I've had a quick read through the posts I can see at the moment.
The critical issue is (as Charles and Randolf have pointed out)
the use of first_rows_100, combined with Randolf's observation
that the expected result set is 40 million rows.

First K Rows: K = 100.00, N = 40640900.00
First K Rows: Setup end

Jonathan,

that was one of my first thoughts as well but I was a bit reluctant to
draw this conclusion because - probably due to the "dynamic proration"
feature - there are more ALL_ROWS plans further down the trace file,
leading e.g. to this one:

First K Rows: K = 100.00, N = 73255.00

which makes the sort operation very likely look much cheaper, so I
wasn't entirely sure that this is the root cause of the problem, given
that we obviously don't see the complete trace file.

Regards,
Randolf

Oracle related stuff blog:http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/

On Nov 7, 10:31 am, "Jonathan Lewis" <jonat... at (no spam) jlcomp.demon.co.uk
wrote:

I've had a quick read through the posts I can see at the moment.
The critical issue is (as Charles and Randolf have pointed out)
the use of first_rows_100, combined with Randolf's observation
that the expected result set is 40 million rows.

First K Rows: K = 100.00, N = 40640900.00
First K Rows: Setup end

Jonathan,

that was one of my first thoughts as well but I was a bit reluctant to
draw this conclusion because - probably due to the "dynamic proration"
feature - there are more ALL_ROWS plans further down the trace file,
leading e.g. to this one:

First K Rows: K = 100.00, N = 73255.00

which makes the sort operation very likely look much cheaper, so I
wasn't entirely sure that this is the root cause of the problem, given
that we obviously don't see the complete trace file.

Regards,
Randolf

Oracle related stuff blog:http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/

The trace file is too long, I have saperated into 6 parts, to see
whole information of each part, please click read more link in the
left bottom.

Thanks Charles and Randolf for your comments:

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

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

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.

I use below command to gather statistics.

exec dbms_stats.gather_schema_stats
('TEST',estimate_percent=>100,cascade=> TRUE);

Below are histogram of SYS_NC00017$

------------------------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.

------------------------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.

10------------------------------------------------
10. As you suggested, I change the optimizer setting into default as
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).

On Nov 8, 2:54 am, lsllcm <lsl... at (no spam) gmail.com> wrote:

The trace file is too long, I have saperated into 6 parts, to see
whole information of each part, please click read more link in the
left bottom.

Well, if you check your posts you might realize that "part 4" is
missing. So either you managed to post everything in 5 parts, or one
part is missing. The numbering suggests the latter, that's why I and
Charles mentioned that probably a part of the trace file is not
available. I'm aware of the "read more->download" procedure, but it
doesn't help if part 4 has not been posted.

Regards,
Randolf

Oracle related stuff blog:http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/

On Nov 7, 9:13 am, lsllcm <lsl... at (no spam) 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­­
222948165808559332123348274797826204144723168738177180919299881250404026184­­
124858368.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.....
"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------------------------------------------------
10. As you suggested, I change the optimizer setting into default as
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.- Hide quoted text -

- Show quoted text -

On Nov 7, 9:13 am, lsllcm <lsl... at (no spam) 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­­
222948165808559332123348274797826204144723168738177180919299881250404026184­­
124858368.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.....
"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------------------------------------------------
10. As you suggested, I change the optimizer setting into default as
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.- Hide quoted text -

- Show quoted text -

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: