If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

What I notice here is that there were alot of blocks read (either from disk and/or from cache) to return only 31 rows (see fetch)? I'm no expert in performance diagnosing, but does this strike anyone as odd? Why would Oracle read so many blocks just to find 31 rows? It's obviously using the index.

I'm hoping this is a classic symptom that a novice like me doesn't recognize...

Please run an explain plan and see what the estimated cardinalities are. I'm guessing that the query is a lot more selective than the optimizer thinks it is. Are the contents of the columns NGUSRFLD2 and NGUSRFLD19 definitely mixed case? How selective are those two predicates? It might be worth creating a function-based index on UPPER() of one or both of them.

Guys,
correct me if I am wrong but it seems to me that the index is scanned to avoid sorting, not for selectivity.
The index seems to be primary key index on NGID and has nothing to deal with the predicates.
Please buid an index on NGPROJGUID and I belive it will improve drastically

I still think you miss an index on NGPROJGUID. The easyest way to bring back the fast plan is to hint the query.
Are the indexes NGUSRFLD6_IND and NGUSRFLD2_IND function based? If not, they still cannot be used as appropriate

Yes, there are, but they are also compound (correct my term usage) indexes. The indexes are based on the NGPROJGUID field and uses UPPER function on the NGUSRFLD6 and NGUSRFLD2 field, respectively.

After looking over this whole thread, I realized I was over looking something... something I should have asked about cuz I don't understand why its happening....

In my first post, I said response time is poor of this one query. Trace shows that there's a lot of db file seq read waits. Looking back now, I see it's using the NGTAG_PK index (as shown in the row execution) and it is having to read many many blocks to return just 31 rows.

After that, I said I realized that the indexes on the (base) table being queried did not have stats, so I got stats for the NGTAGS_PK index. I reran the query and it had significant improvement. WHAT I DIDN'T REALIZE was that it NOW using a different index - NGUSRFLD2_IND.

Question 1: When I analyzed only the NGTAGS_PK index, which improved response time, did the stats tell Oracle that NGTAGS_PK was not the best index to use, so instead it used NGUSRFLD2_IND?

Then I went ahead and got stats for the other indexes (of the same table) missing stats. When I reran the query, it is slow again. And again, WHAT I DIDN'T REALIZE was that it NOW using a different index - NGUSRFLD6_IND.

Question 2: Why did it even bother using NGUSRFLD6_IND when it was not even part of the query?? NGUSRFLD6_IND does have the NGPROJGUID indexed (and so did NGTAGS_PK), is this why Oracle would even remotely think to use this index?

It seems that for this qeury, the best index to use is NGUSRFLD2_IND and it makes sense (now) - It indexes by NGPROJGUID and UPPER("NGUSRFLD2").

Question 3: Why doesn't it use NGUSRFLD2_IND all the time, even after I got stats for all the indexes of the table??

It is hard to explain the behaviour of the optimizer :-) You can only hope it's right :-)

Originally Posted by dbbyleo

Question 1: When I analyzed only the NGTAGS_PK index, which improved response time, did the stats tell Oracle that NGTAGS_PK was not the best index to use, so instead it used NGUSRFLD2_IND?

Cannot be sure about that BUT it could be. Other possibility is that gathering statistics , you unlocled the cost based aproach of the optimizer and it ignored the ..PK index. I see the optimizer_goal is CHOOSE what means RULE in case of statistics lack and COST in case if statistics for at least one object are presented.
Q 2 and Q3 - keep in mind that the order in which the fields are present into the query mather. The index cannot be used for selectivity if the predicate field is not leading column into the index. Hence if NGPROJGUID is leading column in NGUSRFLD6_IND but is not leading in NGUSRFLD2_IND (supposing that NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B' is most selective predicarte) that could be the reason.
Again, it is really hard to say, this is just a guess. One can hardly explain the CBO even when all the data are available, and here when we do not know the exact index setup, stats etc. it can be really obly a guess

Guys,
correct me if I am wrong but it seems to me that the index is scanned to avoid sorting, not for selectivity.
The index seems to be primary key index on NGID and has nothing to deal with the predicates.
Please buid an index on NGPROJGUID and I belive it will improve drastically

Regards
Boris

how will index scan avoid scanning??

as far as ur point on selectivity is concerned.. we need to check if any of the columns involved in the where clause is a part of PK?

funky...

"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"