You need to include the index_name in the query so that we can tell if that's one index or two; and you need to add an "order by index_name, column_position" so that we can tell the column ordering for multi-column indexes.

I'm still waiting for a couple of examples of the execution plans you get from dbms_xplan().

You have patientid as the second column of an index (billid, patientid). Making a sensible guess about what the columns mean, it won't be used for a query that does:

select * from billpharmacy where patientid = {constant}

You will need another index on the table on just (patientid). As I think I've mentioned earlier though, this could make this class of queries much more efficient, but cause other queries to change execution path and slow down - so test carefully.

Your 'dbms_xplan()' example was run with autotrace still enabled. (It's worth getting it right, though, just in case the "obvious" index on patientid needs to be modified for NLS support).

Lokesh,
Please try to create the DBMS_XPLAN again. The example on this page might help:
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

Notes on the above link: the first hint in the example SQL statement is required, but do not add the ordered use_nl(t1) index(t1) hints.

Aman,
The clues that autotrace was enabled:

Elapsed: 00:00:00.00

and

COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'

The side effect of having autotrace enabled is explained clearly in the above link - in short, the call to with 'ALLSTATS LAST' does not retrieve the DBMS_XPLAN for the specific query executed. If a 10046 trace is enabled at the same time that DBMS_XPLAN is called, (PICKLER FETCH) OF 'DISPLAY' will be included in the 10046 trace, but that inclusion in the 10046 trace does not prevent the call to DBMS_XPLAN from working as expected.

For a plain English, high-level overview, just take your STATSPACK report and paste it into the free STATSPACK analyzer. It's a open-source project for replicating human analysis of performance reports:

http://www.statspackanalyzer.com

Then, if you have any questions, you can post the analysis results . . . .

No I don't feel bad. I am known to get struck inthe complicated issues or rather to make "simple issues" "complicated " ;-).
Yes I know about Statspack Analyzer. At the moment, my only doubt is regarding the dbms_xplan and autotrace option involved.Please correct me if I am wrong,doesn't need a full statspack report.This can be stimulated with a single query with autotrace on.
Regards
Aman....

In the above 10046 trace, note the STAT line containing "COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR" on the last line - that is the execution plan for the call to DBMS_XPLAN.DISPLAY_CURSOR, and not the original query that was of interest. That was the plan that Lokesh provided to Jonathan, and not the one that Jonathan requested.

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
---------------------

Charles,
Please bear with me a little more.
Here are my doubts now
1) Why did you choose to have RBO kcking in first time and than CBO with the different optimizer modes ?
2)If I understood corretly than you mean to say that what Lockesh provided was that with the help of dbms_xplan, instead of this package showing the query plan for the original query,it was showing for the dbms_xplan call itslf?Is it correct?
3) Despite you did an excellent try to show me the difference, I am not able to differentiate.Sorry for my small brain.In both the parts there is a call to dbms_xplan, collection iteration pickeler fetch isn't it?Than how the query is different from the first one where 920 optimizer is used?
I am so sorry but I get things slowly, rather very slowly.
Thanks and regards
Aman....

So, it seems that OPTIMIZER_MODE='CHOOSE' causes this to happen, but why? In my sample setup, I did not collect statistics on the table, so let's see what happens when I collect statistics on the table (and then flush the shared pool so that the change in plan may happen):

Notice that the NOTE line is no longer included in the DBMS_XPLAN output.

2)If I understood corretly than you mean to say that
what Lockesh provided was that with the help of
dbms_xplan, instead of this package showing the query
plan for the original query,it was showing for the
dbms_xplan call itslf?Is it correct?

Yes, that is what I attempted to show.

3) Despite you did an excellent try to show me the
difference, I am not able to differentiate.Sorry for
my small brain.In both the parts there is a call to
dbms_xplan, collection iteration pickeler fetch isn't
it?Than how the query is different from the first
one where 920 optimizer is used?

Yes, there was a call to DBMS_XPLAN in both cases - the reason why I posted two cases was the unexpected rule base optimizer execution in the first test case. The COLLECTION ITERATOR PICKLER FETCH appeared in the 10046 trace in both cases - you will see that line in a 10046 trace when DBMS_XPLAN is called - I received the expected plan from the call to DBMS_XPLAN, rather than seeing COLLECTION ITERATOR PICKLER FETCH, like in the plan posted by Lockesh.

The use of OPTIMIZER_FEATURES_ENABLE=9.0.1 had little to do with this experiement - it was a left over setting from a test that I perform a couple weeks ago. I set it back to the original value when I noticed that Oracle was indicating that it was deciding to use the RBO for this and other simple queries - based on the above test, this parameter was not the cause of the warning about RBO.

As I am sure that you are finding, just because something is a little complicated, it does not mean that something is difficult or impossible to understand. Use the information in the "Optimizing Oracle Performance" book to help you understand what might be happening. You might need to search a little for the parameters that cause what is happening, but there are good books and blogs/websites that will help. 10053 trace files reveal a great deal of detail and shows the effects of changes to various parameters.

Good luck, and stay patient when trying to understand why something is happening.

36,114 78 463.0 2.5 0.13 0.11 4262998487
Module: In Patient 2.1.152.exe
SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100

SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100

Charles,I did not choose to use the RBO - Oracle made that decision
I understand.Today morning when I read the post again,it was obvious.My question was incorrect.You didnt ask for RBO to kick in.It was "CHOOSE" mode who did that with the lack of statistics.But I was wondering what was the need to set the optmizer_featue to different versions.It should not have anything to do with the pickeler fetch.This also you confirmed by saying thisThe use of OPTIMIZER_FEATURES_ENABLE=9.0.1 had little to do with this experiement - it was a left over setting from a test that I perform a couple weeks ago. I set it back to the original value when I noticed that Oracle was indicating that it was deciding to use the RBO for this and other simple queries - based on the above test, this parameter was not the cause of the warning about RBO.

2)
You said,Yes, there was a call to DBMS_XPLAN in both cases - the reason why I posted two cases was the unexpected rule base optimizer execution in the first test case. The COLLECTION ITERATOR PICKLER FETCH appeared in the 10046 trace in both cases - you will see that line in a 10046 trace when DBMS_XPLAN is called - I received the expected plan from the call to DBMS_XPLAN, rather than seeing COLLECTION ITERATOR PICKLER FETCH, like in the plan posted by Lockesh.

The reason I got confused while reading the output was this only that RBO/CBO were there and also the same Pickler Fetch part than what's the difference.But I understood this that with the dbms_xplan and autotrace, the pickeler fetch part is kicking or the call to the dbms_xplan query itself but not the real query which JL wanted to see.This pickler fetch in itself is a troublesome thing.About 4 months back, I searched out for this and finally found its definition on Asktom.
As I am sure that you are finding, just because something is a little complicated, it does not mean that something is difficult or impossible to understand. Use the information in the "Optimizing Oracle Performance" book to help you understand what might be happening. You might need to search a little for the parameters that cause what is happening, but there are good books and blogs/websites that will help. 10053 trace files reveal a great deal of detail and shows the effects of changes to various parameters.
Thanks a bunch for your encouraging words and so much precious advice.Thanks a ton Charles for taking out time and preparing the demo to make me understand.Yes I never think that some thing is impossible tounderstand.When another human can "code" all this,I don't think that its too hard to just "understand" it.Yes I am having almost all the books which are showing the proof with the explanation and in my list of sites, immediately that site is added which is really good :-).Good luck, and stay patient when trying to understand why something is happening.

Thanks a ton for the encouragement.Yes I am going to stay put.That's what was told to me about 5 years back by some one who is known as the best in Oracle community(no prize for guessing who) when I asked him when I will be like him,that it took him 15 years to be where he is at the time.So don't think that you will learn in few months or years.It wont happen and its 101% true.So for sure will try out that I understand things completely and in this process will surely disturb you and lot of other guys a little too much :-).
Thanks and best regards
Aman....