Тормозящий Prepared statement на Oracle

Question

[t1myrkq]

Коллеги,

Есть следующий sql запрос, который выбирает 20 записей отсортированных по дате и транзакции:

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 сгенерировал такой план ge.tt/3uUd44b/v/0?c
Работает замечательно. Отмечу, что OUT_SMS секционированная таблица по TRX_ID полю, а OUT_SMS_CREATE_TS_TRX_ID_IX секционированный индекс по CREATE_TS DESC, TRX_ID DESC.

Но если я сделаю из этого запроса 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

из плана ( ge.tt/3uUd44b/v/1?c ) пропадает COUNT STOPKEY после анализа индекса и oracle сортирует все данные во всех секциях, а надо, чтобы как в первом плане он выбирал по 20 из каждой и затем только сортировал.

Помогите разобраться с проблемой, уже 3 дня никак не могу побороть.

Спасибо.

Answer 1

[iscsi]

Посмотрите тут, например.

Comments

[t1myrkq]

Спасибо, но не особо помогло. В статье написано, что надо использовать Prepared statements, но я бы с радостью ), если бы план был нужный.

[iscsi]

Итог статьи в её последней строке: «Fortunately, PL/SQL avoids these problems by using bind variables and statement caching automatically.»

[t1myrkq]

Имеется введу, что ораклу не надо проверять синтаксис, симантику, искать в кеше план запроса. но план генерится такой же, как и обычный Prepared statement (попробовал сделать функцию на PL/SQL).

Answer 2

[agathis]

Непонятно, кстати, откуда в первом плане эстимейт в 312 миллионов строк, если партиций всего 48 и из каждой по 20 строк.

А вообще это не новость, что план с литеральными переменными обычно чуть (или даже не чуть) производительнее, чем с bind variables.

Вам тут смотреть в сторону фиксации плана, наверное.

Comments

[t1myrkq]

Да, это тоже меня смущает. А что Вы имеете введу под фиксацией плана?

[agathis]

гм, а
select rd from ( select /*+ FIRST_ROWS(20) 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

не работает? прежде чем начинать эксперименты с baselines?

[t1myrkq]

нет, к сожалению не работает, план не меняется.

[agathis]

и, кстати, второй план на живых данных действительно работает медленнее?
первый какой-то был не очень убедительный со своими эстимейтами.

(скриншоты, кстати, протухли)

[t1myrkq]

Скриншоты обновил.
Несмотря на план работает быстро, 100-200 мсек.

Answer 3

[agathis]

Ну так план же не самоцель (и Oracle CBO вовсе не такой глупый, например :)).
Я предлагаю посмотреть статистику реального выполнения по buffer gets, и если там все хорошо, расслабиться и получать удовольствие.

Comments

[t1myrkq]

По buffer gets все хорошо, Buffer Gets = 16.
Хочется, чтобы был 1 скомпилированный план для запроса поиска, как никак в производительности будет быстрее, чем на каджый чих по новому Hard Parse.

[t1myrkq]

имею введу Buffer Gets первого запроса. во втором он зашкаливает.

[agathis]

а, ну тогда фиксировать план, наверное. я не ДБА, так что тут без подробностей. ссылка для начала ознакомления: docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#autoId0

[t1myrkq]

спасибо за ссылку, это еще не изучал )