时间戳

首页 > 解决方案 > Processing time given by Oracle explain plan not matching execution time

sql - Processing time given by Oracle explain plan not matching execution time

问题描述

I have, what I consider to be, a moderately complex query that is giving me trouble. Functionally identical dummy code:

   SELECT 'COL_A' AS Column_Name
          ,a.COL_A AS ID
          ,COUNT(DISTINCT (a.TRNSCT_NBR||a.LNE_NBR)) AS Transactions
          ,COUNT(DISTINCT a.TRNSCT_DTS) AS Transaction_Dates
          ,SUM(a.AMOUNT_PAID) AS Total_Paid

   FROM LARGE_TRANSACTION_VIEW a
   INNER JOIN SMALL_TABLE c
          ON a.COL_A  = c.COL
   WHERE a.COL_X >= '1000'
          AND a.COL_Y BETWEEN ('T100') AND ('T900')
   GROUP BY 'COL_A', a.COL_A 

  UNION

   SELECT 'COL_B' AS Column_Name
          ,b.COL_B AS ID
          ,COUNT(DISTINCT (b.TRNSCT_NBR||b.LNE_NBR)) AS Transactions
          ,COUNT(DISTINCT b.TRNSCT_DTS) AS Transaction_Dates
          ,SUM(b.AMOUNT_PAID) AS Total_Paid

   FROM LARGE_TRANSACTION_VIEW b
   INNER JOIN SMALL_TABLE c
          ON b.COL_B  = c.COL
   WHERE b.COL_X >= '1000'
          AND b.COL_Y BETWEEN ('T100') AND ('T900')
   GROUP BY 'COL_B', b.COL_B 
;

I have been running into serious performance issues with this query with regards to temp space, runtime, etc. However, after narrowing parameters I'm no longer experience temp space errors, but run time has verged on 14 hours.

My explain plan seems to indicate that total runtime should be around 5 minutes. Can anyone advise as to where to look in the explain plan to determine the source of the issue, or what obvious mistakes I may have made here in light of query efficiency?

Edit: Adding output of explain plan below. I am currently fixing my indexes and so haven't run the query again w/ SQL monitor report, but if it'd be better not to fix indexes than let me know...

Explain plan

标签： sqloracledbvisualizer

解决方案

我想知道是否grouping sets会有所帮助：

   SELECT (CASE WHEN GROUPING(a.Col_A) = 1 THEN 'COL_A' ELSE 'COL_B' END) AS Column_Name,
          a.COL_A AS ID,
          COUNT(DISTINCT (a.TRNSCT_NBR||a.LNE_NBR)) AS Transactions,
          COUNT(DISTINCT a.TRNSCT_DTS) AS Transaction_Dates,
          SUM(a.AMOUNT_PAID) AS Total_Paid

   FROM LARGE_TRANSACTION_VIEW a JOIN
        SMALL_TABLE c
         ON a.COL_A  = c.COL
   WHERE a.COL_X >= '1000' AND
         a.COL_Y BETWEEN ('T100') AND ('T900')
   GROUP BY GROUPING SETS ( (a.COL_A), (a.COL_B) )

推荐阅读