Query runs quickly in Oracle SQL Developer, but slowly in SSRS 2008 R2

Query runs quickly in Oracle SQL Developer, but slowly in SSRS 2008 R2

It's that simple: a query that runs in just a few seconds in SQL Developer connecting to Oracle 11g takes 15-25 minutes in SSRS 2008 R2. I haven't tried other versions of SSRS. So far I'm doing all the report execution from VS 2008.

I'm using the OLE DB Provider "OraOLEDB.Oracle.1" which in the past has seemed to give me better results than using the Oracle provider.

Here's what I've been able to determine so far:

• The delay is during the DataSet execution phase and has nothing to do with the result set or rendering time. (Proving by selecting the same rowset directly from a table I inserted it to.)

• SSRS itself is not hung up. It is truly waiting for Oracle which is where the delay is (proven by terminating the DB session from the Oracle side, which resulted in a prompt error in SSRS about the session being killed).

• I have tried direct queries with parameters in the form :Parameter. Very early versions of my query that were more simple worked okay for direct querying, but it seemed like past a certain complexity, the query would start taking forever from SSRS.

• I then switched to executing an SP that inserts the query results to a table or global temp table. This helped for a little while, getting me farther than direct querying, but again, it almost seems like increased query complexity or length eventually broke this method, too. Note: running a table-populating SP works because with option "use single transaction" checked in the DataSource options, DataSets are then run in the order of their appearance in the rdl file. DataSets that return no Fields are still run, as long as all their parameters are satisfied.

• I just tried a table-returning function and this still made no improvement, even though direct calls with literal parameters in SQL Developer return in 1-5 seconds.

• The database in question does not have statistics. It is part of a product created by a vendor and we have not had the time or management buy-in to create/update statistics. I played with the DYNAMIC_SAMPLING hint to calculate statistics on the fly and got a better execution plan: without statistics the cost-based optimizer had been poorly using a LOOP join instead of a HASH join, causing similar many-minute execution times. Thus I put in query hints to force join order and also to cause it to use the strategic hash join, bringing the execution time back down to just seconds. I did not go back and try direct querying in SSRS using these execution hints.

• 추적(또는 Oracle과 동등한 요소가 무엇이든 간에)을 설정한 Oracle DBA의 도움을 받아 실행 중인 작업을 확인할 수 있었지만 아직까지 유용한 정보를 찾지 못했습니다.유감스럽게도 그의 시간은 한정되어 있고 서버측에서 무엇을 실행하고 있는지 제대로 파악할 수 없었습니다.저는 이것을 빨리 해본 경험도 없고, 어떻게 해야 하는지 스스로 공부할 시간도 없습니다.무슨 일이 일어나고 있는지 결정하기 위해 무엇을 해야 하는지에 대한 제안을 주시면 감사하겠습니다.

나의 유일한 가설은 다음과 같습니다.

• 쿼리가 어떻게든 잘못된 실행 계획을 얻고 있습니다.예를 들어, 수백 개가 아닌 수만 개의 "왼쪽" 또는 바깥쪽 루프 행이 있을 때 해시 조인 대신 LOOP 조인을 부적절하게 사용하는 것입니다.

• SSRS는 매개 변수를 합리적인 것 대신 nvarchar(4000) 등으로 제출할 수 있으며, Oracle SP & function 매개 변수는 길이 사양이 없지만 쿼리 호출에서 실행 길이를 가져오므로 매개 변수 스니핑과 같은 일부 프로세스는 이전 지점과 같이 실행 계획을 엉망으로 만들고 있습니다.

• SSRS/공급자가 쿼리를 어떻게든 다시 작성하고 있습니다.다중 값 매개 변수를 사용하고 있지만 현재는 아닙니다. 매개 변수가 Join(Parameters!) 식으로 제출되고 있습니다.다중값 매개 변수입니다.값, ",")을 다시 쓸 필요가 없습니다.단순한 제본과 제출.SP와 기능 호출에서 이것이 어떻게 사실일 수 있는지는 모르겠지만, 세상에, 또 무엇이 있을까요?

매우 복잡하고 긴 질의라는 것을 알고 있지만, 제가 필요로 하는 것을 정확히 수행합니다.데이터 요구량에 따라 1~5초 안에 실행됩니다.이처럼 복잡해지는 이유는 다음과 같습니다.

• 쉼표로 구분된 비용 센터 목록 매개 변수 제대로 처리
• 주별 내역을 선택 사항으로 허용하고 포함된 경우 데이터가 없더라도 한 달의 모든 주를 표시할 수 있습니다.
• 적절한 경우 "송장 없음"을 표시합니다.
• 요약 월의 가변 수를 허용합니다.
• YTD 토탈(옵션)이 있습니다.
• 이전/이전 비교 데이터를 포함하면 이번 달의 공급업체를 단순히 사용할 수 없다는 것을 의미하므로 모든 공급업체가 기록 열에 포함될 것임을 보여주어야 합니다.

어쨌든, 여기 질문이 있습니다, SP 버전. (큰 도움이 되지 않을 것으로 생각하지만)

create or replace
PROCEDURE VendorInvoiceSummary (
   FromDate IN date,
   ToDate IN date,
   CostCenterList IN varchar2,
   IncludeWeekly IN varchar2,
   ComparisonMonths IN number,
   IncludeYTD IN varchar2
)
AS
BEGIN

INSERT INTO InvoiceSummary (Mo, CostCenter, Vendor, VendorName, Section, TimeUnit, Amt)
SELECT
   Mo,
   CostCenter,
   Vendor,
   VendorName,
   Section,
   TimeUnit,
   Amt
FROM (
   WITH CostCenters AS (
      SELECT Substr(REGEXP_SUBSTR(CostCenterList, '[^,]+', 1, LEVEL) || '               ', 1, 15) CostCenter
      FROM DUAL
      CONNECT BY LEVEL <= Length(CostCenterList) - Length(Replace(CostCenterList, ',', '')) + 1
   ), Invoices AS (
      SELECT  /*+ORDERED USE_HASH(D)*/
         TRUNC(I.Invoice_Dte, 'YYYY') Yr,
         TRUNC(I.Invoice_Dte, 'MM') Mo,
         D.Dis_Acct_Unit CostCenter,
         I.Vendor,
         V.Vendor_VName,
         CASE
            WHEN I.Invoice_Dte >= FromDate AND I.Invoice_Dte < ToDate
            THEN (TRUNC(I.Invoice_Dte, 'W') - TRUNC(I.Invoice_Dte, 'MM')) / 7 + 1
            ELSE 0
         END WkNum,
         Sum(D.To_Base_Amt) To_Base_Amt
      FROM
         ICCompany C
         INNER JOIN APInvoice I
            ON C.Company = I.Company
         INNER JOIN APDistrib D
            ON C.Company = D.Company
            AND I.Invoice = D.Invoice
            AND I.Vendor = D.Vendor
            AND I.Suffix = D.Suffix
         INNER JOIN CostCenters CC
            ON D.Dis_Acct_Unit = CC.CostCenter
         INNER JOIN APVenMast V ON I.Vendor = V.Vendor
      WHERE
         D.Cancel_Seq = 0
         AND I.Cancel_Seq = 0
         AND I.Invoice_Dte >= Least(ADD_MONTHS(FromDate, -ComparisonMonths), TRUNC(FromDate, 'YYYY'))
         AND I.Invoice_Dte < ToDate
         AND V.Vendor_Group = '1   ' -- index help
      GROUP BY
         TRUNC(I.Invoice_Dte, 'YYYY'),
         TRUNC(I.Invoice_Dte, 'MM'),
         D.Dis_Acct_Unit,
         I.Vendor,
         V.Vendor_VName,
         CASE
            WHEN I.Invoice_Dte >= FromDate AND I.Invoice_Dte < ToDate
            THEN (TRUNC(I.Invoice_Dte, 'W') - TRUNC(I.Invoice_Dte, 'MM')) / 7 + 1
            ELSE 0
         END
   ), Months AS (
      SELECT ADD_MONTHS(Least(ADD_MONTHS(FromDate, -ComparisonMonths), TRUNC(FromDate, 'YYYY')), LEVEL - 1) Mo
      FROM DUAL
      CONNECT BY LEVEL <= MONTHS_BETWEEN(ToDate, Least(ADD_MONTHS(FromDate, -ComparisonMonths), TRUNC(FromDate, 'YYYY')))
   ), Sections AS (
      SELECT 1 Section, 1 StartUnit, 5 EndUnit FROM DUAL
      UNION ALL SELECT 2, 0, ComparisonMonths FROM DUAL
      UNION ALL SELECT 3, 1, 1 FROM DUAL WHERE IncludeYTD = 'Y'
   ), Vals AS (
      SELECT LEVEL - 1 TimeUnit
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT Max(EndUnit) FROM Sections) + 1
   ), TimeUnits AS (
      SELECT S.Section, V.TimeUnit
      FROM
         Sections S
         INNER JOIN Vals V
            ON V.TimeUnit BETWEEN S.StartUnit AND S.EndUnit
   ), Names AS (
      SELECT DISTINCT
         M.Mo,
         Coalesce(I.Vendor, '0') Vendor,
         Coalesce(I.Vendor_VName, 'No Paid Invoices') Vendor_VName,
         Coalesce(I.CostCenter, ' ') CostCenter
      FROM
         Months M
         LEFT JOIN Invoices I
            ON Least(ADD_MONTHS(M.Mo, -ComparisonMonths), TRUNC(M.Mo, 'YYYY')) < I.Mo
            AND M.Mo >= I.Mo
      WHERE
         M.Mo >= FromDate
         AND M.Mo < ToDate
   )
   SELECT
      N.Mo,
      N.CostCenter,
      N.Vendor,
      N.Vendor_VName VendorName,
      T.Section,
      T.TimeUnit,
      Sum(I.To_Base_Amt) Amt
   FROM
      Names N
      CROSS JOIN TimeUnits T
      LEFT JOIN Invoices I
         ON N.CostCenter = I.CostCenter
         AND N.Vendor = I.Vendor
         AND (
            (
               T.Section = 1 -- Weeks for current month
               AND N.Mo = I.Mo
               AND T.TimeUnit = I.WkNum
            ) OR (
               T.Section = 2 -- Summary months
               AND ADD_MONTHS(N.Mo, -T.TimeUnit) = I.Mo
            ) OR (
               T.Section = 3 -- YTD
               AND I.Mo BETWEEN TRUNC(N.Mo, 'YYYY') AND N.Mo
            )
         )
   WHERE
      N.Mo >= FromDate
      AND N.Mo < ToDate
      AND NOT ( -- Only 4 weeks when a month is less than 28 days long
         T.Section = 2
         AND T.TimeUnit = 5
         AND TRUNC(N.Mo + 28, 'MM') <> N.Mo
         AND I.CostCenter IS NULL
      ) AND (
         T.Section <> 1
         OR IncludeWeekly = 'Y'
      )
   GROUP BY
      N.Mo,
      N.CostCenter,
      N.Vendor,
      N.Vendor_VName,
      T.Section,
      T.TimeUnit
) X;
COMMIT;
END;

갱신하다

지식을 를수를 Oracle 한 에도 를 할 를 을 에서 하기 할 를 를 을 에도 먼저 실제 테이블 결과를 다음과 같이 두 단계로 실행하기 전에는 쿼리를 빠르게 실행할 수 없었습니다.GLOBAL TEMPORARY TABLE그리고 두 번째로 데이터를 추출합니다.DYNAMIC_SAMPLING실행 계획을 잘 짜서 join 및 access 힌트를 사용하여 복사했습니다.다음은 최종 SP입니다(Oracle에서는 해당 함수를 SELECT 문 내부에서 호출할 때 함수에서 DML을 수행할 수 없으므로 함수가 될 수 없음):

때때로 나는 그것이 나의 가입 힌트를 무시한 것이라고 맹세합니다.swap_join_inputs그리고.no_swap_join_inputs하지만 제가 읽은 바로는 오라클은 힌트를 실제로 사용할 수 없거나 잘못된 행동을 하고 있을 때만 무시하는 것 같습니다.스럽게도).USE_NL(CC)마지막으로 결합되었음에도 불구하고 CC 테이블을 스왑된 왼쪽 입력으로 안정적으로 배치합니다.

CREATE OR REPLACE
PROCEDURE VendorInvoicesSummary (
   FromDate IN date,
   ToDate IN date,
   CostCenterList IN varchar2,
   IncludeWeekly IN varchar2,
   ComparisonMonths IN number,
   IncludeYTD IN varchar2
)
AS
BEGIN

INSERT INTO InvoiceTemp (Yr, Mo, CostCenter, Vendor, WkNum, Amt) -- A global temporary table
SELECT /*+LEADING(C I D CC) USE_HASH(I D) USE_NL(CC)*/
   TRUNC(I.Invoice_Dte, 'YYYY') Yr,
   TRUNC(I.Invoice_Dte, 'MM') Mo,
   D.Dis_Acct_Unit CostCenter,
   I.Vendor,
   CASE
      WHEN I.Invoice_Dte >= FromDate AND I.Invoice_Dte < ToDate
      THEN (TRUNC(I.Invoice_Dte, 'W') - TRUNC(I.Invoice_Dte, 'MM')) / 7 + 1
      ELSE 0
   END WkNum,
   Sum(D.To_Base_Amt) To_Base_Amt
FROM
   ICCompany C
   INNER JOIN APInvoice I
      ON C.Company = I.Company
   INNER JOIN APDistrib D
      ON C.Company = D.Company
      AND I.Invoice = D.Invoice
      AND I.Vendor = D.Vendor
      AND I.Suffix = D.Suffix
   INNER JOIN (
      SELECT Substr(REGEXP_SUBSTR(CostCenterList, '[^,]+', 1, LEVEL) || '               ', 1, 15) CostCenter
      FROM DUAL
      CONNECT BY LEVEL <= Length(CostCenterList) - Length(Replace(CostCenterList, ',', '')) + 1
   ) CC ON D.Dis_Acct_Unit = CC.CostCenter
WHERE
   D.Cancel_Seq = 0
   AND I.Cancel_Seq = 0
   AND I.Invoice_Dte >= Least(ADD_MONTHS(FromDate, -ComparisonMonths), TRUNC(FromDate, 'YYYY'))
   AND I.Invoice_Dte < ToDate
GROUP BY
   TRUNC(I.Invoice_Dte, 'YYYY'),
   TRUNC(I.Invoice_Dte, 'MM'),
   D.Dis_Acct_Unit,
   I.Vendor,
   CASE
      WHEN I.Invoice_Dte >= FromDate AND I.Invoice_Dte < ToDate
      THEN (TRUNC(I.Invoice_Dte, 'W') - TRUNC(I.Invoice_Dte, 'MM')) / 7 + 1
      ELSE 0
   END;

INSERT INTO InvoiceSummary (Mo, CostCenter, Vendor, VendorName, Section, TimeUnit, Amt)
SELECT
   Mo,
   CostCenter,
   Vendor,
   VendorName,
   Section,
   TimeUnit,
   Amt
FROM (
   WITH Months AS (
      SELECT ADD_MONTHS(Least(ADD_MONTHS(FromDate, -ComparisonMonths), TRUNC(FromDate, 'YYYY')), LEVEL - 1) Mo
      FROM DUAL
      CONNECT BY LEVEL <= MONTHS_BETWEEN(ToDate, Least(ADD_MONTHS(FromDate, -ComparisonMonths), TRUNC(FromDate, 'YYYY')))
   ), Sections AS (
      SELECT 1 Section, 1 StartUnit, 5 EndUnit FROM DUAL
      UNION ALL SELECT 2, 0, ComparisonMonths FROM DUAL
      UNION ALL SELECT 3, 1, 1 FROM DUAL WHERE IncludeYTD = 'Y'
   ), Vals AS (
      SELECT LEVEL - 1 TimeUnit
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT Max(EndUnit) FROM Sections) + 1
   ), TimeUnits AS (
      SELECT S.Section, V.TimeUnit
      FROM
         Sections S
         INNER JOIN Vals V
            ON V.TimeUnit BETWEEN S.StartUnit AND S.EndUnit
   ), Names AS (
      SELECT DISTINCT
         M.Mo,
         Coalesce(I.Vendor, '0') Vendor,
         Coalesce(I.CostCenter, ' ') CostCenter
      FROM
         Months M
         LEFT JOIN InvoiceTemp I
            ON Least(ADD_MONTHS(M.Mo, -ComparisonMonths), TRUNC(M.Mo, 'YYYY')) <= I.Mo
            AND I.Mo <= M.Mo
      WHERE
         M.Mo >= FromDate
         AND M.Mo < ToDate
   )
   SELECT
      N.Mo,
      N.CostCenter,
      N.Vendor,
      Coalesce(V.Vendor_VName, 'No Paid Invoices') VendorName,
      T.Section,
      T.TimeUnit,
      Sum(I.Amt) Amt
   FROM
      Names N
      INNER JOIN APVenMast V ON N.Vendor = V.Vendor
      CROSS JOIN TimeUnits T
      LEFT JOIN InvoiceTemp I
         ON N.CostCenter = I.CostCenter
         AND N.Vendor = I.Vendor
         AND (
            (
               T.Section = 1 -- Weeks for current month
               AND N.Mo = I.Mo
               AND T.TimeUnit = I.WkNum
            ) OR (
               T.Section = 2 -- Summary months
               AND ADD_MONTHS(N.Mo, -T.TimeUnit) = I.Mo
            ) OR (
               T.Section = 3 -- YTD
               AND I.Mo BETWEEN TRUNC(N.Mo, 'YYYY') AND N.Mo
            )
         )
   WHERE
      N.Mo >= FromDate
      AND N.Mo < ToDate
      AND V.Vendor_Group = '1   '
      AND NOT ( -- Only 4 weeks when a month is less than 28 days long
         T.Section = 2
         AND T.TimeUnit = 5
         AND TRUNC(N.Mo + 28, 'MM') <> N.Mo
         AND I.CostCenter IS NULL
      ) AND (
         T.Section <> 1
         OR IncludeWeekly = 'Y'
      )
   GROUP BY
      N.Mo,
      N.CostCenter,
      N.Vendor,
      V.Vendor_VName,
      T.Section,
      T.TimeUnit
) X;

COMMIT;
END;

하지만 한 가지 배운 점이 있다면, 제대로 업데이트된 통계 없이 데이터베이스에서 작업하는 것은 합리적인 시간 내에 작업을 완료하고자 하는 고객에게 (벤더가 신경 쓰지 않더라도 DBA가 추가할 수 있도록 검토할 것입니다) 정말 큰 재앙이 될 수 있다는 것입니다.

쿼리를 게시하면 도움이 될 수 있습니다.

DBA는 v$session이라는 뷰에서 세션을 식별할 수 있어야 하며 EVENT 및 WAIT_CLASS 열은 Oracle 엔드에서 발생하는 상황을 나타냅니다.

또한 SQL(v$session의 SQL_ID)을 식별하여 SELECT * FROM TABLE(DBMS_XPLAN)에 사용할 수 있습니다.DISPLAY_CURSER(sql_id)를 선택하여 계획을 결정합니다.

개발/테스트 인스턴스인 경우, 바쁜 경우 직접 사용자에게 권한을 부여할지 확인합니다.

이것이 오래된 것은 알지만 우리는 비슷한 문제가 있어서 nsl_sort를 binary_ci가 아닌 binary로 설정해야 했습니다.사람들은 세션을 이진으로 설정해 볼 수 있습니다: alter session set nls_signal=signals

언급URL : https://stackoverflow.com/questions/4788987/query-runs-quickly-in-oracle-sql-developer-but-slowly-in-ssrs-2008-r2