Processing time given by Oracle explain plan not matching execution time

Question

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...

Try adding indexes on the tables underlying LARGE_TRANSACTION_VIEW on the columns COL_A and COL_B, gather statistics, and test again. — Bob Jarvis - Слава Україні, Jun 28 '21 at 17:49
You may consider to check execution details with [DBMS_SQL_MONITOR.REPORT_SQL_MONITOR](https://docs.oracle.com/database/121/ARPLS/d_sql_monitor.htm#ARPLS74789) function with level `ALL` and format `ACTIVE`. It visually highlights most time consuming operations and shows resource usage during statement execution — astentx, Jun 28 '21 at 19:28
Thank you. Gathering statistics on COL_A and COL_B, it appears both have unusable indexes. I received ```ORA_01502: index 'string.string' or partition of such index is in unusable state``` Would I be correct to interpret that a problem with the indexes is impacting my performance? — bhbennett3, Jun 28 '21 at 19:43
For a query taking 14 hours elapsed time would be a *more educated guess* @BobJarvis-ReinstateMonica *try to find an index that you can drop*. But without seeing the explain plan *nothing can be said*. — Marmite Bomber, Jun 28 '21 at 20:58
Would you mind to share the [execution plan](https://stackoverflow.com/a/34975420/4808122), so we can comment? — Marmite Bomber, Jun 28 '21 at 21:04
I have to echo @astentx comment. You need to get an active SQL Monitor report, so you can see the runtime information and see where time is being spent. — BobC, Jun 28 '21 at 21:23
@MarmiteBomber just edited question with explain plan picture — bhbennett3, Jun 28 '21 at 23:29

score 1 · Answer 1 · answered Jun 28 '21 at 17:41

1

I wonder if grouping sets would help:

   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) )

answered Jun 28 '21 at 17:41

Gordon Linoff

1,242,037
58
646
786

will this also bring cases where Col_B matches with SMALL_TABLE into my results? I'm unfamiliar with grouping sets – bhbennett3 Jun 28 '21 at 17:45
@bhbennett3 . . . `GROUPING SETS` basically runs the `GROUP BY` twice, one on each set of columns. You can learn about it in the documentation: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets. – Gordon Linoff Jun 28 '21 at 21:38

Processing time given by Oracle explain plan not matching execution time

1 Answers1