Один и тот же запрос с одним и тем же планом выполнения выполняется по-разному в разных средах Oracle

Скорее всего, Oracle выполняет хэш-соединение в одной среде, а не вложенные циклы в другой. Очень часто запрос ведет себя по-разному при выполнении в другой среде. Существует множество факторов, которые оптимизатор учитывает при разработке своего плана, поэтому нельзя ожидать, что каждый раз в двух разных базах данных будет один и тот же план.

Вот пара вещей, которые стоит попробовать:

1. Вы действительно хотите FETCH NEXT, а не FETCH FIRST?

2. Вероятно, вам вообще не следует использовать этот внутренний блок запросов. Упрощать:

   SELECT t.*
   FROM transactions t
   ORDER BY t.transaction_date DESC, t.id DESC
   FETCH FIRST 11 ROWS ONLY
   

3. Для максимальной скорости переопределите индекс как (transaction_date,id). Это означает удаление ключевого слова DESC из определения индекса. Тогда намекните:

   SELECT /*+ INDEX_DESC(t) */ t.*
   FROM transactions t
   ORDER BY t.transaction_date DESC, t.id DESC
   FETCH FIRST 11 ROWS ONLY
   

Проверьте свой план и убедитесь, что там указано INDEX FULL SCAN DESCENDING (не INDEX FAST FULL SCAN), а над ним вы видите операцию WINDOW NOSORT STOPKEY (не WINDOW SORT PUSHED RANK). Вы пытаетесь избежать подобного.

Если вам нужно сделать это во вложенном блоке запроса, поскольку вам требуется присоединение к другим таблицам в родительском блоке, попробуйте что-то вроде этого:

SELECT /*+ USE_NL(t t2 t3) */ t.*,t2.*,t3.*
  FROM (SELECT /*+ NO_MERGE INDEX_DESC(t) */ ROWID row_id, t.id, t.transaction_date
         FROM transaction t 
        ORDER BY t.transaction_date DESC, t.id DESC
        FETCH FIRST 11 ROWS ONLY) transactions_table
       JOIN transaction t ON transactions_table.row_id = t.ROWID -- if self-joining
       LEFT OUTER JOIN transaction t2 ON t2.id = transactions_table.id -- other table join
       LEFT OUTER JOIN transaction t3 ON t3.id = transactions_table.id -- other table join
 ORDER BY transactions_table.transaction_date DESC, transactions_table.id DESC    

Обратите внимание на подсказки USE_NL и NO_MERGE. NO_MERGE предотвращает слияние представлений Oracle, которое сделало бы недействительным ваш INDEX_DESC-подсказку, а USE_NL обещает Oracle, что действительно лучше использовать вложенные циклы, а не хеш-соединение для этих объединений с другими таблицами. Обратите внимание, что я также дополнительно использовал ROWID для самосоединения. Это позволяет избежать повторного обращения к индексу, хотя вполне вероятно, что индекс был тем же самым, который использовался во внутреннем запросе, поэтому конечные блоки кэшируются, и выгода от его пропуска на втором проходе может быть незаметной.

Самообъединение действительно не требуется, поскольку вы можете вернуть все нужные столбцы из внутреннего запроса. Единственная ситуация, в которой я считаю полезным такое самостоятельное присоединение, — это когда я могу дисквалифицировать строки на основе критериев из других таблиц и хочу избежать затрат на чтение блоков из сегмента таблицы, пока я этого не сделаю. Ограничение внутреннего запроса ссылкой только на столбцы, являющиеся частью индекса, — вот что позволит реализовать этот трюк с производительностью.

Примечание dynamic statistics used: dynamic sampling (level=2) подразумевает, что в таблице отсутствует статистика по одному из ваших сред. Соберите статистику с помощью такого блока PL/SQL:

begin
    dbms_stats.gather_table_stats(ownname => user, tabname => 'TRANSACTIONS');
end;
/

Если это решит проблему, вам следует выяснить, почему статистика не собиралась автоматически. По умолчанию база данных Oracle каждый день автоматически собирает статистику по таблице, в которой есть новые данные или которая изменилась более чем на 10%. К сожалению, многие организации предпочитают отключать автозадачу по умолчанию и внедрять собственное решение.

Если это не решит проблему или вам интересно, почему это решило проблему, сверьте план выполнения с фактическими цифрами, а не с использованием догадок плана объяснения. Соберите данные, используя select dbms_sqltune.report_sql_monitor(sql_id => 'your SQL_ID') from dual;, и добавьте результаты к своему вопросу. Отчет о мониторинге SQL сообщит вам, какая операция занимает больше всего времени и каковы ожидания.

Я предполагаю, что для этой проблемы ваша «Активность (%)» не составит в сумме 100%. Когда это происходит, это означает, что время выполнения было потрачено на выполнение рекурсивного запроса; запрос, предназначенный для предоставления полезной информации, редко может занять больше времени, чем сам запрос. В частности, в редких случаях запрос динамической выборки может выполняться медленно. Возможно, сбор статистики позволит избежать необходимости выполнять дорогостоящий запрос динамической выборки.

Кроме того, я согласен с идеей Пола В. о переписывании запроса. Замена самосоединения более сложным запросом часто позволяет сэкономить много времени.