Оптимизация производительности запросов SELECT

Ответ 1

Теория: То, что, как я полагаю, происходит, - это то, что движок должен разрешить записи 100 м + из соединения вида в 500 м записей, прежде чем он применяет предельные критерии (таким образом, он создает кросс-соединение и даже если он может использовать индексы, которые многие из записей генерируют, а затем анализируют. Поэтому, даже если вы написали это как внешнее соединение, движок не сможет обработать его таким образом (я не знаю почему)

Итак, как минимум 100 м * 500 м = 50 000 м, что много данных для генерации, а затем разбора/ограничения.

Исключив представление, двигатель может быть лучше способен оптимизировать и использовать индексы, тем самым устраняя необходимость объединения записей в 50 000 м.

Области, где я бы сосредоточил свое время на поиске и устранении неисправностей:

• Устраните представление, чтобы удалить его как потенциальную проблему.
• Невозможно определить связь между stg_scd_customers_key и V_CRM_CASE_ID_EXISTS_IN_DWH. Это означает, что двигатель может выполнять кросс-соединение ДО результатов STG_DIM_CRM_CASES для stg_scd_customers_key были разрешены.

CONSIDER, исключающий представление или использование встроенного представления

Устранение вида:

SELECT *
  FROM STG_DIM_CRM_CASES 
      ,crm_ps_rc_case t
      ,dim_crm_cases x 
      ,stg_scd_customers_key
 WHERE t.case_id=x.crm_case_id
   AND STG_DIM_CRM_CASES.CRM_CASE_ID = t.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   AND STG_DIM_CRM_CASES.Case_Create_Date 
       between  stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

с помощью встроенного представления:

SELECT *
  FROM STG_DIM_CRM_CASES 
  (select  t.case_id
   from crm_ps_rc_case t, dim_crm_cases x
   where t.case_id=x.crm_case_id) V_CRM_CASE_ID_EXISTS_IN_DWH
      ,stg_scd_customers_key
 WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   AND STG_DIM_CRM_CASES.Case_Create_Date 
       between  stg_scd_customers_key.start_date(+) and stg_scd_customers_key.end_date(+)

Что касается:  - http://www.dba-oracle.com/art_hints_views.htm

В то время как порядок предложения where НЕ ДОЛЖЕН иметь значение: в выключенном состоянии двигатель работает в указанном порядке, ограничивая 500 м вниз, а затем добавление дополнительных данных из представления логически будет быстрее.

SELECT *
  FROM STG_DIM_CRM_CASES,stg_scd_customers_key,V_CRM_CASE_ID_EXISTS_IN_DWH
 WHERE STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   and STG_DIM_CRM_CASES.Case_Create_Date between  stg_scd_customers_key.start_date(+) and  stg_scd_customers_key.end_date(+)
   and STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)

Ответ 2

Проблема заключается в сканировании всех разделов:

18 | PX SEND HASH |: TQ10001 | 515m | 14G | 507K (3) | 01:58:28 | | | | S- > P | HASH | 25 | 19 | РАЗДЕЛ ДИАПАЗОН ВСЕ | | 515m | 14G | 507K (3) | 01:58:28 | 1 | 2982 | |
| | 26 | 20 | ТАБЛИЦА ДОСТУПА ПОЛНОСТЬЮ | STG_SCD_CUSTOMERS_KEY | 515m | 14G |

Это происходит потому, что вы используете левое соединение в этой таблице. Можете ли вы выбрать 1 раздел, используя переменную привязки? Что такое ключ раздела? Я не вижу намека на параллельность, но, по вашему мнению, он использует параллель. Есть ли параллельная степень на любом уровне объекта? Можете ли вы удалить параллельный и опубликованный план объяснения без параллелирования?

Ответ 3

Я думаю, что проблема - это представление, которое, как я подозреваю, полностью выполняется и возвращает все строки до того, как будут применены условия.

Общий эффект представления заключается в добавлении столбца CASE_ID, который не является нулевым, если в нем найдено CRM_CASE_ID, в противном случае - null. Я заменил представление двумя прямыми объединениями и выражением CASE. Заменяя удобство представления логикой, вы можете напрямую подключаться к каждой таблице, поэтому избегайте одного уровня глубины соединения.

Попробуйте запустить эту версию запроса:

SELECT
  a.*, b.*, c.*,
  CASE WHEN t.case_id is not null and X.case_id is not null then t.case_id END CASE_ID
FROM STG_DIM_CRM_CASES a
LEFT JOIN crm_ps_rc_case t
  ON t.case_id = a.CRM_CASE_ID
LEFT JOIN dim_crm_cases x
  ON x.crm_case_id = a.CRM_CASE_ID
LEFT JOIN V_CRM_CASE_ID_EXISTS_IN_DWH b
  ON a.CRM_CASE_ID = b.CASE_ID
LEFT JOIN stg_scd_customers_key c
  ON a.account_number = c.account_number
 and a.Case_Create_Date between c.start_date and  stg_scd_customers_key.end_date

Если вы замените a.*, b.*, c.* только на точные столбцы, которые вам действительно нужны, вы получите ускорение, потому что просто вернуть меньше данных. Если вы также поместите индексы на поисковые ключи и все столбцы, которые вы фактически выбираете (индекс покрытия), вы значительно ускорите его, потому что можно использовать только индексный доступ.

Вы должны проверить, есть ли индексы во всех соединенных столбцах как минимум.

Ответ 4

Ваша проблема в том, что у Oracle действительно есть только два способа получить нужные ему строки из stg_scd_customers_key. Либо (A) он делает один FULL SCAN этой таблицы, а затем отфильтровывает строки, которые он не хочет, или (B) он делает 270 000 запросов индекса, от 3 до, возможно, 5 логических операций ввода-вывода каждый (в зависимости от высота вашего индекса), плюс еще один логический ввод-вывод для фактического чтения блока из таблицы.

Учитывая многоблочные чтения и другие оптимизации, доступные с помощью FULL SCAN, и на основе статистики таблицы, оптимизатор Oracle предполагает, что FULL SCAN будет быстрее. И есть хороший шанс, что это правильно.

Что вам нужно сделать, так это предоставить Oracle лучший вариант.

Если вы не можете использовать материализованные представления, где вы находитесь, хороший материализованный вид "бедных" - это то, что называется индексом покрытия. Теперь это не подходит для вашего запроса, так как вы делаете SELECT *. Но вам действительно нужен каждый столбец от stg_scd_customers_key?

Если вы можете просмотреть список столбцов, полученных из stg_scd_customers_key, вы можете создать индекс, который (A) начинается с account_number, start_date и end_date и (B) включает все остальные столбцы, которые необходимо выбрать.

Например:

SELECT stg_im_crm_cases.*, V_CRM_CASE_ID_EXISTS_IN_DWH.*, stg_scd_customers_key.column_1, stg_scd_customers_key.column_2
  FROM STG_DIM_CRM_CASES,V_CRM_CASE_ID_EXISTS_IN_DWH,stg_scd_customers_key
 WHERE STG_DIM_CRM_CASES.CRM_CASE_ID = V_CRM_CASE_ID_EXISTS_IN_DWH.CASE_ID(+)
   AND STG_DIM_CRM_CASES.account_number = stg_scd_customers_key.account_number(+)
   and STG_DIM_CRM_CASES.Case_Create_Date between  stg_scd_customers_key.start_date(+) and  stg_scd_customers_key.end_date(+)

Если вы можете сделать этот запрос и создать индекс на stg_scd_customers_key (account_number, start_date, end_date, column_1, column_2), то вы предоставите Oracle лучшую альтернативу. Теперь он может читать только индекс, а не таблицу.

С большими таблицами нет гарантий, пока вы не попробуете его. Но прикрывающие индексы часто являются тем, что заказывал доктор. (Разумеется, все обычные оговорки о новых индексах).

Ответ 5

некоторые соображения:

1) ИНДЕКС
crm_ps_rc_case не имеет индекса на case_id, это проблема, вы соединяете 270k ↔ 100m с HASH JOIN (не хорошо)

2) ВЫБРАННЫЕ КОЛОННЫ
представление V_CRM_CASE_ID_EXISTS_IN_DWH выбирает t.case_id, но вместо этого следует выбрать x.crm_case_id, по крайней мере, до тех пор, пока вы не разрешите индексирование t.case_id. Это будет распространять HASH JOIN по всему вашему плану выполнения. (Не хорошо)

3) МЕЖДУ
объединение/фильтрация диапазона всегда является проблемой, особенно на больших таблицах, но вы можете ограничить проблему добавлением условий на диапазон. позвольте мне объяснить, попробуйте добавить эти условия к вашему предложению WHERE:

AND stg_scd_customers_key.end_date =  (
       SELECT min(r.end_date)
       FROM stg_scd_customers_key r
       WHERE  r.end_date >= STG_DIM_CRM_CASES.Case_Create_Date
)
AND stg_scd_customers_key.start_date =  (
       SELECT max(r.start_date)
       FROM stg_scd_customers_key r
       WHERE  r.start_date <= STG_DIM_CRM_CASES.Case_Create_Date
)

да, он рассчитает 270k * 2 подзапросов, но окончательное соединение будет работать на гораздо меньшем количестве ребер, ограничивающих операции ввода-вывода (это должно быть лучше)

4) ЗАКАЗ ПОЛЬЗОВАТЕЛЯ
есть противоречивые отчеты, если это так, или если это не имеет значения, но по моему опыту. Это может быть лишь незначительное улучшение, но вы можете попытаться изменить индекс на stg_scd_customers_key, инвертируя порядок Start_Date и End_Date, по моему опыту я нашел более эффективным для фильтрации диапазона, чтобы иметь верхнюю границу до нижняя граница индекса.

Ответ 6

Я хотел бы создать материализованное представление (с refresh fast on demand) для соединения между Table_cases и stg_scd. Я предполагаю, что большая часть работы в соединении находится в строках, которые не меняются изо дня в день.