Блог

Моя среда:

• Oracle Enterprise Edition 12.2 на Azure IaaS
• Linux Red Hat версии 7
• Объем памяти: 32 ГБ
• ПРОЦЕССОР: 8 VCPU
• Целевой объем памяти установлен на 16 ГБ.

Позвольте мне объяснить проблему. У меня возникли некоторые проблемы с большим запросом, который выполняется через службу REST в Azure Kubernetes. Запрос использует много таблиц, и это занимает много времени, когда переменные привязки не установлены. На самом деле, когда сообщается о переменных привязки, запрос занимает всего 1,5 секунды. Я пытался убедить команду, которая разработала запрос, чтобы принудительно сообщать параметры, но они говорят, что требование таково, как оно есть, и что его нельзя оспорить.

Я улучшил запрос настолько, насколько мог, создав некоторые индексы, которых не было, даже я запустил задачу настройки sql, чтобы собрать еще больше деталей. Я пытался даже создать новый профиль sql, основываясь на рекомендациях советника по настройке, но это сделало запрос еще медленнее, поэтому в конце я отказался от него. Когда запрос без значений привязки выполняется отдельно, для завершения требуется 14 секунд. Однако, когда один и тот же запрос является частью масштабного стресс-теста (от 10 до 15 угроз одновременно), некоторые процессы заканчиваются по таймауту, так что запрос достигает 60 секунд без завершения.

Все время я вижу одно и то же событие ожидания: операция с памятью PGA (В связи с этим я также попытался установить больше PGA, я даже увеличил значения по умолчанию для скрытых параметров _pga_max_size и _smm_max_size . Я также пытался запустить его с настройками рабочей области вручную, я отключил pga_aggregated_limit , но ничто из этого не заставляло запрос выполняться быстрее или медленнее.

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

Моя последняя идея — создать материализованный вид с быстрым обновлением при фиксации. В базе данных очень мало активности DML, поэтому совпадение данных не будет проблемой, и я полагаю, что в результате не будет такой большой блокировки. Я читал, что Oracle значительно улучшила быстрое обновление при фиксации со времен 11g. Однако я не знаю точно, как мне следует его решить. Должен ли я создать один MVIEW со всем запросом или мне следует разделить его?

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

Большое спасибо за вашу поддержку.

Запрос выглядит следующим образом:

 WITH extDealer
AS (SELECT thirdPartynumber
      FROM    ALFAODS.ODSEXTERNALSYSTEMREFERENCE odsExt
           INNER JOIN
              ALFAODS.OdsThirdParty thirdParty
           ON odsExt.THIRDPARTYID = thirdParty.id
     WHERE odsExt.reference = :1),
selection
AS 
(  SELECT schedule.maturityDate,
                  schedule.id AS schedule_id,
                  schedule.alfascheduleidentifier AS scheduleId,
                  asset.assetidentifier AS assetId,
                  agreement.agreementNumber AS loanId,
                  proposalStatus.DETAIL AS proposalStatusCode,
                  schedule.scheduleStatus AS scheduleStatus,
                  schedule.terminationDate,
                  (CASE
                      WHEN supplier.thirdPartyNumber =
                              thirdPartyDealer.thirdPartyNumber
                      THEN
                         TO_CHAR (supplier.thirdPartyNumber)
                      ELSE
                         '0'
                   END)
                     AS "supplierGPNr",
                  (CASE
                      WHEN proposalStatus.DETAIL = 'RESERVED'
                      THEN
                         '0'
                      WHEN proposalStatus.DETAIL = 'APPROVED'
                      THEN
                         '1'
                      WHEN proposalStatus.DETAIL = 'FINANCED'
                      THEN
                         '2'
                      WHEN proposalStatus.DETAIL = 'CANCELLED'
                      THEN
                         '3'
                      WHEN proposalStatus.DETAIL = 'PRECANCELLED'
                      THEN
                         '3'
                      WHEN proposalStatus.DETAIL = 'CREDITNOTECANCELLED'
                      THEN
                         '3'
                      WHEN proposalStatus.DETAIL = 'WAITING'
                      THEN
                         '4'
                      ELSE
                         '5'
                   END)
                     AS loanState,
                  productId1.DETAIL AS productId1,
                  productId2.DETAIL AS productId2,
                  productId3.DETAIL AS productId3,
                  productId4.DETAIL AS productId4,
                  productId.DETAIL AS wmProductId,
                  asset.serialNumber AS vin,
                  asset.modelName AS modelName,
                  schedule.totalAssetCostFinanced AS loanAmount,
                  (CASE
                      WHEN schedule.capitalOutstanding IS NULL
                      THEN
                         0
                      WHEN schedule.scheduleStatus = 'Proposal'
                      THEN
                         (CASE
                             WHEN proposalStatus.DETAIL = 'WAITING' THEN 0
                             ELSE schedule.capitalOutstanding * (-1)
                          END)
                      ELSE
                         schedule.capitalOutstanding * (-1)
                   END)
                     AS balance,
                  thirdPartyDealer.thirdPartyNumber AS dealerGPNr,
                  thirdPartyDealer.name AS dealerNameShort,
                  TO_DATE (
                     CAST (
                        CASE
                           WHEN (creationDate.DATEFIELD = 0) THEN NULL
                           ELSE creationDate.DATEFIELD
                        END AS NVARCHAR2 (8)),
                     'yyyymmdd')
                     AS creationDate,
                  schedule.activationDate AS activationDate,
                  365 AS "KBSTATE",
                  assetValuationInp.narrative AS invoiceId,
                  (CASE
                      WHEN assetValuationInp.valuation IS NULL THEN 0
                      ELSE assetValuationInp.valuation
                   END)
                     AS invoiceAmount,
                  assetValuationInp.valueDate AS invoiceDate,
                  assetUsageHistory.readingValue AS kilometers,
                  installSchema.DETAIL AS installmentSchemaId,
                  installSchemaVer.DETAIL AS installmentSchemaVersion,
                  interestSchema.DETAIL AS interestSchemaId,
                  interestSchemaVer.DETAIL AS interestSchemaVersion,
                  SUBSTR (asset.serialNumber, -6) AS vinLast6,
                  stockId.DETAIL AS stockId,
                  deliveryLink.DETAIL AS deliveryLink,
                  TO_DATE (
                     CAST (
                        CASE
                           WHEN (deliveryDate.DATEFIELD = 0) THEN NULL
                           ELSE deliveryDate.DATEFIELD
                        END AS NVARCHAR2 (8)),
                     'yyyymmdd')
                     AS deliveryDate,
                  CASE
                     WHEN UPPER (CONCAT (submitter.thirdPartyNumber, '')) IN
                             ('<NONE>', '', 'NULL')
                     THEN
                        0
                     ELSE
                        TO_NUMBER (submitter.thirdPartyNumber)
                  END
                     AS submitterId,
                  agreementAlert.dunningBlock AS dunningBlock,
                  odsInvoicingCompany.code AS "brandId",
                  odsInvoicingCompany.name AS "brand",
                  agrCompany.id AS "mandantId",
                  modelHSN.DETAIL AS hsn,
                  modelTSN.DETAIL AS tsn,
                  briefNumber.DETAIL AS documentNr,
                  schedule.MATURITYDATE AS finalDate
             FROM ALFAODS.OdsScheduleMain schedule
                  INNER JOIN ALFAODS.OdsAgreement agreement
                     ON schedule.agreementId = agreement.id
                        AND agreement.productCode = 'WHS'
                  INNER JOIN ALFAODS.OdsAsset asset
                     ON asset.scheduleId = schedule.id
                  INNER JOIN ALFAODS.OdsThirdParty thirdPartyDealer
                     ON schedule.invCusId = thirdPartyDealer.id
                  INNER JOIN ALFAODS.OdsThirdParty submitter
                     ON schedule.dealerid = submitter.id
                  JOIN ALFAODS.OdsTmpSystemDate dates
                     ON 1 = 1
                  LEFT JOIN ALFAODS.OdsThirdParty supplier
                     ON supplier.id = asset.supplierId
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS installSchema
                     ON installSchema.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND installSchema.informationType = 'WINSS'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS interestSchema
                     ON interestSchema.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND interestSchema.informationType = 'WINTS'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS application
                     ON application.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND application.informationType = 'WAPID'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS productId1
                     ON productId1.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND productId1.informationType = 'WPID1'
                        AND (:2 IS NULL
                             OR productId1.DETAIL = NVL (:3, productId1.DETAIL))
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS productId2
                     ON productId2.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND productId2.informationType = 'WPID2'
                        AND (:4 IS NULL
                             OR productId2.DETAIL = NVL (:5, productId2.DETAIL))
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS productId3
                     ON productId3.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND productId3.informationType = 'WPID3'
                        AND (:6 IS NULL
                             OR productId3.DETAIL = NVL (:7, productId3.DETAIL))
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS productId4
                     ON productId4.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND productId4.informationType = 'WPID4'
                        AND (:8 IS NULL
                             OR productId4.DETAIL = NVL (:9, productId4.DETAIL))
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS stockID
                     ON stockID.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND stockID.informationType = 'WSTID'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS proposalStatus
                     ON proposalStatus.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND proposalStatus.informationType = 'WPRST'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS creationDate
                     ON creationDate.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND creationDate.informationType = 'WCRDT'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS installSchemaVer
                     ON installSchemaVer.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND installSchemaVer.informationType = 'WINSV'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS interestSchemaVer
                     ON interestSchemaVer.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND interestSchemaVer.informationType = 'WINTV'
                  --           LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS cancellationDate      ON cancellationDate.alfaScheduleIdentifier = schedule.alfaScheduleIdentifier AND cancellationDate.informationType = 'WCD'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS deliveryLink
                     ON deliveryLink.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND deliveryLink.informationType = 'WDLNK'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS productId
                     ON productId.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND productId.informationType = 'WPRID'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS deliveryDate
                     ON deliveryDate.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND deliveryDate.informationType = 'WDELD'
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS netting
                     ON netting.alfaScheduleIdentifier =
                           schedule.alfaScheduleIdentifier
                        AND netting.informationType = 'WNETT'
                  LEFT JOIN ALFAODS.OdsAssetValuation assetValuationInp
                     ON (    assetValuationInp.assetId = asset.id
                         AND assetValuationInp.valueTypeCode = 'INP'
                         AND assetValuationInp.isCurrentForTypeAndSource = 1)
                  LEFT JOIN ALFAODS.OdsAssetUsageHistory assetUsageHistory
                     ON asset.assetidentifier = assetUsageHistory.assetId
                  LEFT JOIN ALFAODS.OdsAgreementAlert agreementAlert
                     ON agreementAlert.agreementId = agreement.id
                  LEFT JOIN ALFAODS.OdsCompany odsInvoicingCompany
                     ON odsInvoicingCompany.id = schedule.invCompanyId
                  LEFT JOIN ALFAODS.OdsCompany agrCompany
                     ON agrCompany.id = schedule.agrCompanyId
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOASSET modelHSN
                     ON     modelHSN.assetIdentifier = asset.assetidentifier
                        AND modelHSN.informationType = u'MANUN'
                        AND modelHSN.DETAIL = NVL (:10, modelHSN.DETAIL)
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOASSET modelTSN
                     ON     modelTSN.assetIdentifier = asset.assetidentifier
                        AND modelTSN.informationType = u'MODC'
                        AND modelTSN.DETAIL = NVL (:11, modelTSN.DETAIL)
                  LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOASSET briefNumber
                     ON     briefNumber.assetIdentifier = asset.assetidentifier
                        AND briefNumber.informationType = u'BNR'
                        AND briefNumber.DETAIL = NVL (:12, briefNumber.DETAIL)
            WHERE (:13 IS NULL
                   OR SUBSTR (asset.serialNumber, -6) IN
                         (    SELECT REGEXP_SUBSTR (NVL (:14, ''),
                                                    '[^,] ',
                                                    1,
                                                    LEVEL)
                                FROM DUAL
                          CONNECT BY REGEXP_SUBSTR (NVL (:15, ''),
                                                    '[^,] ',
                                                    1,
                                                    LEVEL)
                                        IS NOT NULL))
                  AND (:16 IS NULL
                       OR asset.serialNumber IN
                             (    SELECT REGEXP_SUBSTR (NVL (:17, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                    FROM DUAL
                              CONNECT BY REGEXP_SUBSTR (NVL (:18, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                            IS NOT NULL))
                  AND (:19 IS NULL
                       OR thirdPartyDealer.thirdPartyNumber IN
                             (    SELECT REGEXP_SUBSTR (NVL (:20, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                    FROM DUAL
                              CONNECT BY REGEXP_SUBSTR (NVL (:21, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                            IS NOT NULL))
                  AND (:22 IS NULL
                       OR supplier.thirdPartyNumber IN
                             (    SELECT REGEXP_SUBSTR (NVL (:23, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                    FROM DUAL
                              CONNECT BY REGEXP_SUBSTR (NVL (:24, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                            IS NOT NULL))
                  AND (:25 IS NULL
                       OR thirdPartyDealer.thirdPartyNumber IN
                             (SELECT thirdpartynumber FROM extDealer))
                  AND (:26 IS NULL
                       OR UPPER (proposalStatus.DETAIL) IN
                             (    SELECT REGEXP_SUBSTR (NVL (:27, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                    FROM DUAL
                              CONNECT BY REGEXP_SUBSTR (NVL (:28, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                            IS NOT NULL))
                  AND (:29 IS NULL
                       OR agreement.agreementNumber IN
                             (    SELECT REGEXP_SUBSTR (NVL (:30, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                    FROM DUAL
                              CONNECT BY REGEXP_SUBSTR (NVL (:31, ''),
                                                        '[^,] ',
                                                        1,
                                                        LEVEL)
                                            IS NOT NULL))
                  AND (:32 IS NULL OR asset.modelName LIKE '%' || :33 || '%')
                  AND (   :34 IS NULL
                       OR assetValuationInp.narrative = :35
                       OR stockId.DETAIL = :36)
                  AND (:37 IS NULL
                       OR productId1.DETAIL = NVL (:38, productId1.DETAIL))
                  AND (:39 IS NULL
                       OR productId2.DETAIL = NVL (:40, productId2.DETAIL))
                  AND (:41 IS NULL
                       OR productId3.DETAIL = NVL (:42, productId3.DETAIL))
                  AND (:43 IS NULL
                       OR productId4.DETAIL = NVL (:44, productId4.DETAIL))
                  AND ( (:45 IS NULL)
                       OR assetValuationInp.valueDate >=
                             TO_DATE (:46, 'yyyy-MM-dd'))
                  AND ( (:47 IS NULL)
                       OR assetValuationInp.valueDate <=
                             TO_DATE (:48, 'yyyy-MM-dd'))
                  AND ( (:49 IS NULL)
                       OR schedule.activationDate >=
                             TO_DATE (:50, 'yyyy-MM-dd'))
                  AND ( (:51 IS NULL)
                       OR schedule.activationDate <=
                             TO_DATE (:52, 'yyyy-MM-dd'))
                  AND (:53 IS NULL
                       OR modelHSN.DETAIL = NVL (:54, modelHSN.DETAIL))
                  AND (:55 IS NULL
                       OR modelTSN.DETAIL = NVL (:56, modelTSN.DETAIL))
                  AND (:57 IS NULL
                       OR briefNumber.DETAIL = NVL (:58, briefNumber.DETAIL))
                  AND (:59 IS NULL
                       OR (TO_DATE (:60, 'yyyy-MM-dd') <= schedule.MATURITYDATE))
                  AND (:61 IS NULL
                       OR (schedule.SCHEDULESTATUS = 'Live (Primary)'
                           AND TO_DATE (:62, 'yyyy-MM-dd') >=
                                  schedule.MATURITYDATE
                           OR schedule.SCHEDULESTATUS = 'Terminated'
                              AND ADD_MONTHS (
                                     TRUNC (TO_DATE (:63, 'yyyy-MM-dd'),
                                            'MONTH'),
                                     2) >= schedule.MATURITYDATE))
         ORDER BY CASE
                     WHEN UPPER (:64) = 'LOANID' AND UPPER (:65) = 'ASC'
                     THEN
                        agreement.agreementNumber
                  END ASC,
                  CASE
                     WHEN UPPER (:66) = 'LOANID' AND UPPER (:67) <> 'ASC'
                     THEN
                        agreement.agreementNumber
                  END DESC,
                  CASE
                     WHEN UPPER (:68) <> 'LOANID' AND UPPER (:69) = 'ASC'
                     THEN
                        thirdPartyDealer.thirdPartyNumber
                  END ASC,
                  CASE
                     WHEN UPPER (:70) <> 'LOANID' AND UPPER (:71) <> 'ASC'
                     THEN
                        thirdPartyDealer.thirdPartyNumber
                  END DESC       
 OFFSET :72  ROWS FETCH NEXT :73  ROWS ONLY
),
Selection2 
AS (
SELECT
LOANID,
PROPOSALSTATUSCODE,
SCHEDULESTATUS,
"supplierGPNr",
LOANSTATE,
PRODUCTID1,
PRODUCTID2,
PRODUCTID3,
PRODUCTID4,
WMPRODUCTID,
VIN,
MODELNAME,
LOANAMOUNT,
BALANCE,
DEALERGPNR,
DEALERNAMESHORT,
CREATIONDATE,
ACTIVATIONDATE,
CASE WHEN (scheduleStatus = 'Live (Primary)') THEN finalDate
     ELSE (SELECT max(receivable.dueDate)
                   FROM ALFAODS.OdsReceivable receivable
                        INNER JOIN ALFAODS.OdsChargeType chargeType on receivable.recvChargeTypeId = chargeType.id AND chargeType.code = 2
                   WHERE receivable.scheduleId = schedule_id) END AS CANCELLATIONDATE,
KBSTATE,
DOCUMENTNR,
documentStateLoan.DETAIL                   as documentStateLoan,
loanDocumentLocation.DETAIL                as documentLocation,
TO_DATE(CAST(CASE WHEN (loanDocumentDate.DATEFIELD = 0) THEN null
                  ELSE loanDocumentDate.DATEFIELD  END AS NVARCHAR2(8)),  'yyyymmdd')   as documentStatusDate,
typeId.DETAIL             as schwackeCodes,
assetValuation.valuation  as schwackeValue,
INVOICEID,
INVOICEAMOUNT,
INVOICEDATE,
KILOMETERS,
INSTALLMENTSCHEMAID,
0 AS INSTALLMENTSCHEMAVERSION,
INTERESTSCHEMAID,
0 AS INTERESTSCHEMAVERSION,
VINLAST6,
HSN,
TSN,
STOCKID,
NULL AS DELIVERYLINK,
DELIVERYDATE,
SUBMITTERID,
docRepositary.DETAIL as docRepositaryId,
DUNNINGBLOCK,
"brandId",
"brand",
"mandantId",
NULL AS vatDeduction,
NULL AS schwackeDeduction,
NULL AS ignoreRetention,
500 AS TOTAL
FROM selection sel
         LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS documentStateLoan ON documentStateLoan.alfaScheduleIdentifier = sel.scheduleId AND documentStateLoan.informationType = 'WDCLS' AND documentStateLoan.DETAIL= NVL(:74 , documentStateLoan.DETAIL)
         LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS loanDocumentDate  ON loanDocumentDate.alfaScheduleIdentifier = sel.scheduleId AND loanDocumentDate.informationType = 'WCRDT'
         LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS loanDocumentLocation ON loanDocumentLocation.alfaScheduleIdentifier = sel.scheduleId AND loanDocumentLocation.informationType = 'WLDLC' AND loanDocumentLocation.DETAIL = NVL(:75 , loanDocumentLocation.DETAIL)
         LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOS docRepositary ON docRepositary.alfaScheduleIdentifier = sel.scheduleId AND docRepositary.informationType = 'WDRID'
         LEFT JOIN ALFAODS.ODSTMPCURRENTMISCINFOASSET typeId ON typeId.assetIdentifier = sel.assetId AND typeId.informationType = u'WTYID'
         LEFT JOIN ALFAODS.OdsAssetValuation assetValuation on (
                   assetValuation.assetId = sel.assetId
                   AND assetValuation.valueTypeCode = 'TTV'
                   AND assetValuation.isCurrentForTypeAndSource = 1)
WHERE
        (:76  IS NULL OR documentStateLoan.DETAIL = NVL(:77 ,documentStateLoan.DETAIL))
      AND
        (:78  IS NULL OR loanDocumentLocation.DETAIL = NVL(:79 ,loanDocumentLocation.DETAIL))
)
SELECT selection2.CANCELLATIONDATE as FINALDATE, selection2.* FROM selection2
where
scheduleStatus = 'Live (Primary)'
or (scheduleStatus  = 'Terminated' and cancellationdate BETWEEN TO_DATE(NVL(:80 , '1900-01-01'), 'yyyy-MM-dd') AND TO_DATE(NVL(:81 ,'9999-12-31'), 'yyyy-MM-dd'))
  

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

Это,

 CREATE MATERIALIZED VIEW mat1 AS
SELECT ... all the data, no bind variables ...
  

и во время выполнения:

 SELECT ... FROM mat1 WHERE ... bind variable conditions ...
  

В вашем запросе много соединений. refresh fast on commit Материализованный вид может быть сложным. Если у вас Oracle 12.2, изучите ENABLE ON QUERY COMPUTATION функцию материализованных представлений. В принципе, вы периодически обновляете (возможно, каждый день или каждые несколько часов, в зависимости от того, насколько (не) стабильны данные и сколько времени требуется для выполнения запроса), но Oracle по-прежнему гарантирует свежие результаты в режиме реального времени, дополняя (слегка устаревшие) данные материализованного вида данными из журналов материализованного вида.

Тем не менее, я бы пока не стал переходить к материализованному виду. Вместо этого я бы переключился на динамическое построение и выполнение запроса на основе условий, которые не являются нулевыми. Как бы то ни было, вы уделяете много внимания оптимизатору Oracle, у которого в основном есть только два инструмента для выполнения хорошей работы здесь: «NVL-ИЛИ expansion» и курсоры, поддерживающие привязку.

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

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

Когда я говорю, создавайте запросы динамически, я имею в виду что-то вроде этого:

 IF p_param_3rd_party_list IS NULL THEN
  l_sql := l_sql || ' AND NVL(:23,''XXX'') IS NOT NULL';
  l_sql := l_sql || ' AND NVL(:24,''XXX'') IS NOT NULL';
ELSE 
  -- I'm not bothering to convert all the single quotes to double quotes.  Syntax won't 
  -- work, but you get the idea...
  l_sql := l_sql || ' AND (supplier.thirdPartyNumber IN
                         (    SELECT REGEXP_SUBSTR (NVL (:23, ''),
                                                    '[^,] ',
                                                    1,
                                                    LEVEL)
                                FROM DUAL
                          CONNECT BY REGEXP_SUBSTR (NVL (:24, ''),
                                                    '[^,] ',
                                                    1,
                                                    LEVEL)
                                        IS NOT NULL))
END IF;

EXECUTE IMMEDIATE l_sql USING ... all your bind variables
  

Очень важно, чтобы вы по-прежнему использовали переменные привязки, как показано выше, даже если вы строите запрос динамически, по соображениям масштабируемости.

Смысл фиктивных условий, добавляемых, когда переменная привязки равна NULL, заключается в том, что конечный запрос имеет тот же список переменных привязки в том же порядке, независимо от того, какие параметры фактически заданы. Таким образом, вы можете выполнить его с помощью одной EXECUTE IMMEDIATE...USING... команды. В противном случае это огромная боль.