Fran wrote:
When you gather statistics, the next time you run the query, the optimizer automatically chooses a new execution plan based on the new statistics. That's why you are getting a new execution plan.

I have two Queries ( The Difference between Them Only 940 and 584 ) When I Generate Explain Plan Different Output Why ?

But it's not the only difference.
See the date literal.

You have two queries that are expected to return a different number of rows.
It's no surprise at all that these should return different execution plans.

Rowsource selectivities and cardinalities are a key part of the optimizer calculations and any difference in parameters can make one access path or join mechanism appear more favourable.

In general, if you want reusable, shareable SQL you use bind variables (and I will conveniently ignore bind variable peeking).
If you want specific execution plans for the specifically supplied values, use literals.

The critical point is that the optimizer tends to make good decisions providedthat the estimates are broadly accurate.

The main features of note in your two query plans are the skip scan in the first versus the btree bitmap conversion in the second.
Both are noted for being particularly ineffective if the estimates are not accurate.

Will the order by affect query time?
It may.
An order by may require an additional sort operation. It may not.
It may be executed optimally, it may not.
Depends.

For indexes it's a balance.
If you have more than one query - the norm - you have to find the minimal set of indexes that provide the best/required query performance levels with an accepted overhead of storing and maintaining those indexes.

But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?

The effect on CPU time is minimal - the total elapsed time predicted changes from 3 seconds to 32 seconds, the percentage of the cost due to CPU changes from 3% to 1%, the latter being the smallest non-zero value available for the percentage. Since predicted time is simply a scaled representation of predicted cost, the expected change in CPU is tiny, most of the change is due to predicted I/O.

Apart from checking the system stats to see if they are realistic (or just the defaults), I would look at the date and type columns, checking low and high values, number of distinct values, and whether either column had a histogram - and I'm guessing that histograms are having an effect here. (Check the partition stats, not the table stats).

The change in plan is the same basic reason that usually appears - different input values result in different cardinality estimates. The use of a skip scan is interesting given the access and filter predicates - I think it might be telling us something about the number of distinct values for crea_date across the ranges requested.

>
But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?
>
Dom was trying to be kind and point out that your query predicates (note the plural) are different. You said
>
The Difference between Them Only 940 and 584
>
1. There is no '940' or '584' in the code you posted.
2. The dates in the where clause are different
3. the m.mesg_sender_x1 value is different

The first query has

AND m.mesg_sender_x1 = 'SOGEFRPPXXX'

but the second query has

AND m.mesg_sender_x1 = 'SOGEFRPPGSS'

It is hard to keep things straight when the text of the question doesn't match the code or plan that was posted.

Both the different date ranges and the different sender values could grossly affect the plan used.

What question are you really trying to address? And why aren't you using bind variables?

rp0428 wrote:
>
But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?
>
Dom was trying to be kind and point out that your query predicates (note the plural) are different. You said
>
The Difference between Them Only 940 and 584
>
1. There is no '940' or '584' in the code you posted.
2. The dates in the where clause are different
3. the m.mesg_sender_x1 value is different

The first query has

AND m.mesg_sender_x1 = 'SOGEFRPPXXX'

but the second query has

AND m.mesg_sender_x1 = 'SOGEFRPPGSS'

It is hard to keep things straight when the text of the question doesn't match the code or plan that was posted.

Both the different date ranges and the different sender values could grossly affect the plan used.

What question are you really trying to address? And why aren't you using bind variables?

Thank you for your reply.

What question are you really trying to address? And why aren't you using bind variables?

I understand Dom , But My question is in index IX_RMESG For the first explain plan its used "INDEX SKIP SCAN" But in the second one "INDEX RANGE SCAN"
i will check with developers about that actually.

>
But My question is in index IX_RMESG For the first explain plan its used "INDEX SKIP SCAN" But in the second one "INDEX RANGE SCAN"
>
I still think you are comparing apples to oranges due to the different filter predicates.

You mention a range scan for the second query but in the context of the IX_RMESG index.

To me the key difference in the second query is

| 14 | BITMAP AND

It is that 'BITMAP AND' that is driving the second query. And it seems to me that the reason for that is this

That is the INDEX RANGE SCAN that caught my eye and it appears to be for the SENDER predicate: m.mesg_sender_x1 = 'SOGEFRPPGSS'

Referring back to what I said in my first reply
>
3. the m.mesg_sender_x1 value is different

The first query has

AND m.mesg_sender_x1 = 'SOGEFRPPXXX'

but the second query has

AND m.mesg_sender_x1 = 'SOGEFRPPGSS'

The question I would explore is why the value 'SOGEFRPPGSS' uses the index but the value 'SOGEFRPPXXX' does not.

I think the answer to that would answer your question about why the plans are different.

It is the use of the IX_SENDER index that is causing the use of a range scan for the IX_REMSG index. Oracle may be doing the range scans of both indexes because that allows the indexes to be bitmap converted and 'ANDed' together to get the result.