Slow Prepared statement on Oracle

Question

Oracle

Colleagues,

There is the following sql query that selects 20 records sorted by date and transaction:

select rd from(
    select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
    from OUT_SMS     
    where TRX_ID between 34621422135410688 and 72339069014638591       
         and CREATE_TS between to_timestamp('2013-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                 and to_timestamp('2013-03-06 08:57:00', 'yyyy-mm-dd hh24:mi:ss')       
    order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20

Oracle generated this plan ge.tt/3uUd44b/v/0?c
Works great. I note that OUT_SMS is a partitioned table by TRX_ID field, and OUT_SMS_CREATE_TS_TRX_ID_IX is a partitioned index by CREATE_TS DESC, TRX_ID DESC.

But if I make this request a prepared statement :

select rd from(
    select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
    from OUT_SMS     
    where TRX_ID between ? and?       
         and CREATE_TS between ? and?       
    order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20

from the plan ( ge.tt/3uUd44b/v/1?c ) COUNT STOPKEY disappears after index analysis and oracle sorts all the data in all sections, but it is necessary that, as in the first plan, it selects 20 from each and then only sorts.

Help to deal with the problem, I can not overcome it for 3 days.

Thank you.

Answer 1

Look here for example.

Answer 2

It is not clear, by the way, where does the estimate of 312 million lines come from in the first plan, if there are only 48 partitions and 20 lines from each.
In general, it is not news that a plan with literal variables is usually slightly (or even slightly) more productive than with bind variables.
You are here to look in the direction of fixing the plan, probably.

Answer 3

Well, the plan is not an end in itself (and Oracle CBO is not so stupid, for example :)).
I suggest looking at the real execution statistics for buffer gets, and if everything is good there, relax and have fun.