Reputation: 364
Query execution takes long time when doing aggregate operations in oracle
I'm trying to generate a report using a query which have aggregate functions involved.
The query execution takes long time (more than a hour) and it haven't completed its execution even for one time.
Here is the query.
SELECT ope,ffp,Sum(ACC_APPROVED_GOLD) ACC_APPROVED_GOLD,Sum(ACC_APPROVED_SILVER) ACC_APPROVED_SILVER,Sum(ACC_APPROVED_MISC) ACC_APPROVED_MISC,
Sum(RET_APPROVED_GOLD) RET_APPROVED_GOLD,Sum(RET_APPROVED_SILVER) RET_APPROVED_SILVER,Sum(RET_APPROVED_MISC) RET_APPROVED_MISC
FROM
(
SELECT
ffpprogram FFP,OPERATINGAIRLINECODE OPE,
CASE WHEN (ACCRUALPOSTINGSTATUS IN ('01','03') AND tier_level =1) THEN 1 ELSE 0 END ACC_APPROVED_GOLD,
CASE WHEN (ACCRUALPOSTINGSTATUS IN ('01','03') AND tier_level =2) THEN 1 ELSE 0 END ACC_APPROVED_SILVER,
CASE WHEN (ACCRUALPOSTINGSTATUS IN ('01','03') AND tier_level IS NULL ) THEN 1 ELSE 0 END ACC_APPROVED_MISC,
0 AS RET_APPROVED_GOLD,0 AS RET_APPROVED_SILVER, 0 AS RET_APPROVED_MISC
FROM
(SELECT ffpprogram ,OPERATINGAIRLINECODE,ACCRUALPOSTINGSTATUS,
CASE WHEN (prof.tierlevel IS NULL AND ffpprogram = 'SK') THEN
(SELECT Nvl(tierlevel,0) FROM pcd_profile WHERE SubStr(FFPNUMBER,4) = resp.FFPMEMBERNUMBER AND airlinecode=resp.ffpprogram AND actioncode<> 'D')
ELSE prof.tierlevel END tier_level
FROM
(
(SELECT b.ffpprogram,b.OPERATINGAIRLINECODE,B.ACCRUALPOSTINGSTATUS,b.FFPMEMBERNUMBER
FROM PAI_FILE A , PAI_VALID_ACCRUAL B,
(SELECT DISTINCT c.TRANSACTIONID,d.received_date FROM pai_valid_accrual c, pai_file d
WHERE Trunc(d.RECEIVED_DATE) BETWEEN TO_DATE('2015/01/01', 'yyyy/mm/dd') AND TO_DATE('2015/01/31', 'yyyy/mm/dd')
and d.filetype=1 AND c.file_id=d.file_id AND c.TRANSACTIONID IS NOT NULL ) req
WHERE A.FILE_ID = B.FILE_ID
AND a.filetype=2 AND b.TRANSACTIONID = req.TRANSACTIONID
AND a.received_date - req.received_date <=10
AND Trunc(a.received_date) >= TO_DATE('2015/01/01', 'yyyy/mm/dd')
) resp
left OUTER JOIN pcd_profile prof ON
prof.FFPNUMBER = resp.FFPMEMBERNUMBER
AND prof.airlinecode = resp.ffpprogram
AND prof.actioncode<> 'D'
))
UNION ALL
SELECT
ffpprogram FFP,OPERATINGAIRLINECODE OPE,
0 AS ACC_APPROVED_GOLD,0 AS ACC_APPROVED_SILVER, 0 AS ACC_APPROVED_MISC,
CASE WHEN (retroaccrualauthorization IN ('01','03') AND tier_level =1) THEN 1 ELSE 0 END RET_APPROVED_GOLD,
CASE WHEN (retroaccrualauthorization IN ('01','03') AND tier_level =2) THEN 1 ELSE 0 END RET_APPROVED_SILVER,
CASE WHEN (retroaccrualauthorization IN ('01','03') AND tier_level IS NULL ) THEN 1 ELSE 0 END RET_APPROVED_MISC
FROM
(SELECT ffpprogram ,OPERATINGAIRLINECODE,retroaccrualauthorization,
CASE WHEN (prof.tierlevel IS NULL AND ffpprogram = 'SK') THEN
(SELECT Nvl(tierlevel,0) FROM pcd_profile WHERE SubStr(FFPNUMBER,4) = resp.FFPMEMBERNUMBER AND airlinecode=resp.ffpprogram AND actioncode<> 'D')
ELSE prof.tierlevel END tier_level
FROM
(
(SELECT b.ffpprogram,b.OPERATINGAIRLINECODE,B.retroaccrualauthorization,b.FFPMEMBERNUMBER
FROM PAI_FILE A , PAI_VALID_retro B,
(SELECT DISTINCT c.TRANSACTIONID,d.received_date FROM pai_valid_retro c, pai_file d
WHERE Trunc(d.RECEIVED_DATE) BETWEEN TO_DATE('2015/01/01', 'yyyy/mm/dd') AND TO_DATE('2015/01/31', 'yyyy/mm/dd')
and d.filetype=3 AND c.file_id=d.file_id ) req
WHERE a.file_id=b.file_id AND a.filetype=4 AND b.TRANSACTIONID = req.TRANSACTIONID
AND a.received_date - req.received_date <=10
AND Trunc(a.received_date) >= TO_DATE('2015/01/01', 'yyyy/mm/dd')
) resp
left OUTER JOIN pcd_profile prof ON
prof.FFPNUMBER = resp.FFPMEMBERNUMBER
AND prof.airlinecode = resp.ffpprogram
AND prof.actioncode<> 'D'
))
) GROUP BY ope,ffp
;
However,when the aggregate functions are removed(i.e the outer select query) then the execution completes in 30 seconds.
I have checked the explain plan for the query with and with out aggregate function.
Here is the explain plan.
with aggregate function:
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 72 | 157K (1)| 00:31:31 |
| 1 | HASH GROUP BY | | 3 | 72 | 157K (1)| 00:31:31 |
| 2 | VIEW | | 3 | 72 | 157K (1)| 00:31:31 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 2 | 40 | 6 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C0011841 | 4069 | | 4 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 2 | 216 | 136K (1)| 00:27:20 |
|* 7 | HASH JOIN | | 2 | 176 | 136K (1)| 00:27:20 |
|* 8 | TABLE ACCESS FULL | PAI_FILE | 438 | 5694 | 206 (1)| 00:00:03 |
| 9 | NESTED LOOPS | | | | | |
| 10 | NESTED LOOPS | | 13836 | 1013K| 136K (1)| 00:27:17 |
| 11 | VIEW | | 7697 | 210K| 113K (1)| 00:22:39 |
| 12 | HASH UNIQUE | | 7697 | 293K| 113K (1)| 00:22:39 |
|* 13 | HASH JOIN | | 7697 | 293K| 113K (1)| 00:22:39 |
|* 14 | TABLE ACCESS FULL | PAI_FILE | 22 | 286 | 206 (1)| 00:00:03 |
|* 15 | INDEX FAST FULL SCAN | ACCRUAL_IDX7 | 58M| 1452M| 112K (1)| 00:22:33 |
|* 16 | INDEX RANGE SCAN | ACCRUAL_IDX7 | 2 | | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| PAI_VALID_ACCRUAL | 2 | 94 | 5 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 1 | 20 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | SYS_C0011841 | 1 | | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 2 | 40 | 6 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | SYS_C0011841 | 4069 | | 4 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 110 | 20901 (1)| 00:04:11 |
|* 23 | HASH JOIN | | 1 | 90 | 20898 (1)| 00:04:11 |
|* 24 | TABLE ACCESS FULL | PAI_FILE | 438 | 5694 | 206 (1)| 00:00:03 |
| 25 | NESTED LOOPS | | | | | |
| 26 | NESTED LOOPS | | 2089 | 157K| 20692 (1)| 00:04:09 |
| 27 | VIEW | | 1201 | 34829 | 17071 (1)| 00:03:25 |
| 28 | HASH UNIQUE | | 1201 | 48040 | 17071 (1)| 00:03:25 |
|* 29 | HASH JOIN | | 1201 | 48040 | 17070 (1)| 00:03:25 |
|* 30 | TABLE ACCESS FULL | PAI_FILE | 22 | 286 | 206 (1)| 00:00:03 |
| 31 | INDEX FAST FULL SCAN | RETRO7_IDX | 8205K| 211M| 16825 (1)| 00:03:22 |
|* 32 | INDEX RANGE SCAN | RETRO7_IDX | 2 | | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID| PAI_VALID_RETRO | 2 | 96 | 4 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 1 | 20 | 3 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | SYS_C0011841 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
with out aggregate function:
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 326 | 157K (14)| 00:31:31 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 2 | 40 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0011841 | 4069 | | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 2 | 216 | 136K (1)| 00:27:20 |
|* 5 | HASH JOIN | | 2 | 176 | 136K (1)| 00:27:20 |
|* 6 | TABLE ACCESS FULL | PAI_FILE | 438 | 5694 | 206 (1)| 00:00:03 |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 13836 | 1013K| 136K (1)| 00:27:17 |
| 9 | VIEW | | 7697 | 210K| 113K (1)| 00:22:39 |
| 10 | HASH UNIQUE | | 7697 | 293K| 113K (1)| 00:22:39 |
|* 11 | HASH JOIN | | 7697 | 293K| 113K (1)| 00:22:39 |
|* 12 | TABLE ACCESS FULL | PAI_FILE | 22 | 286 | 206 (1)| 00:00:03 |
|* 13 | INDEX FAST FULL SCAN | ACCRUAL_IDX7 | 58M| 1452M| 112K (1)| 00:22:33 |
|* 14 | INDEX RANGE SCAN | ACCRUAL_IDX7 | 2 | | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| PAI_VALID_ACCRUAL | 2 | 94 | 5 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 1 | 20 | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | SYS_C0011841 | 1 | | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 2 | 40 | 6 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | SYS_C0011841 | 4069 | | 4 (0)| 00:00:01 |
| 20 | NESTED LOOPS OUTER | | 1 | 110 | 20901 (1)| 00:04:11 |
|* 21 | HASH JOIN | | 1 | 90 | 20898 (1)| 00:04:11 |
|* 22 | TABLE ACCESS FULL | PAI_FILE | 438 | 5694 | 206 (1)| 00:00:03 |
| 23 | NESTED LOOPS | | | | | |
| 24 | NESTED LOOPS | | 2089 | 157K| 20692 (1)| 00:04:09 |
| 25 | VIEW | | 1201 | 34829 | 17071 (1)| 00:03:25 |
| 26 | HASH UNIQUE | | 1201 | 48040 | 17071 (1)| 00:03:25 |
|* 27 | HASH JOIN | | 1201 | 48040 | 17070 (1)| 00:03:25 |
|* 28 | TABLE ACCESS FULL | PAI_FILE | 22 | 286 | 206 (1)| 00:00:03 |
| 29 | INDEX FAST FULL SCAN | RETRO7_IDX | 8205K| 211M| 16825 (1)| 00:03:22 |
|* 30 | INDEX RANGE SCAN | RETRO7_IDX | 2 | | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID| PAI_VALID_RETRO | 2 | 96 | 4 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | PCD_PROFILE | 1 | 20 | 3 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | SYS_C0011841 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
On comparing the explain plans, difference i have observed is the additional 'hash group by' operation in the explain plan for the query which have aggregate function.
I couldn't get the exact reason why the hash group by operation is making the query execution time to be very high.
Any suggestions please..
Thanks in advance!
Upvotes: 1
Views: 3797
Answers (2)
Reputation: 5636
This is an extremely convoluted query to read from only four tables. I'm sure it can be greatly simplified as it seems to the the same code just with hardcoded checks for filetype. In the meantime, are a few things you could look into.
There are a couple of places where you have
WHERE Trunc(d.RECEIVED_DATE) BETWEEN TO_DATE('2015/01/01', 'yyyy/mm/dd') AND TO_DATE('2015/01/31', 'yyyy/mm/dd')
The call to trunc makes this non-sargable and forces a complete table or index scan. It can be changed to make it more efficient (assuming the RECEIVED_DATE column is indexed).
where d.received_date >= date '2015-01-01'
and d.received_date < date '2015-02-01'
Use to_date or date. I prefer the latter but neither affects efficiency. Changing the second date to the first day of the next month is preferred because while every month has a day 1, not every month has a day 31, or even 30. This standardizes within-month checks.
Also in a couple of places:
AND Trunc(a.received_date) >= TO_DATE('2015/01/01', 'yyyy/mm/dd')
Again, the call to trunc makes it unsargable. To make matters worse (or ironic) is that it is completely unnecessary.
AND a.received_date >= TO_DATE('2015/01/01', 'yyyy/mm/dd')
produces exactly the same results and is sargable.
There is one place where I think you have a bug. There are two almost identical segments of code on each side of the union all. In each of these there are two places where the check actioncode <> 'D' is made. Except that in the last place, the actual check is airlinecode <> 'D'.
Upvotes: 2
Reputation: 7344
I think this line is your problem:
* 15 | INDEX FAST FULL SCAN | ACCRUAL_IDX7 | 58M| 1452M| 112K (1)| 00:22:33 |
That's going to look through 58,000,000 keys which it says is going to take 22 minutes i.e. about 66% of the total time it thinks it's going to take. Is there any way you can limit the range of the key it needs to search?
My experience of Oracle - in fact almost any RDBMS - is that the times and row counts in a query plan are not necessarily accurate, but are correct on an order of magnitude: it might not be exactly 58M rows but it is millions, it might not be 22 minutes but it will be about 66%, and so on.
If you can't limit the values of the keys in that index, is there an alternative index the query could use? Forcing it to use that index via a hint might make a difference.
Sadly I don't think a definitive answer is possible as a) we don't have the indexes on the tables b) the meanings of the fields in the tables and c) the tables with representative data in to try things. A lot of query tuning requires these things, and is quite often guess-work.
Upvotes: 1
Related Questions
- Speeding up Queries that Use Aggregate Functions by Using Indexes
- Oracle Dynamic SQL Procedure with Aggregate Function takes long time to call via ORDS, but runs quickly in SQL Developer
- Slow response of Oracle query, using functions
- performance for sum oracle
- Speeding up aggregations for a large table in Oracle
- Optimizing aggregate function in Oracle
- Performance update in Aggregate query
- Query Performance while using Oracle aggregate function
- Is there a more efficient way to count the number of aggregate records in Oracle SQL?
- Query aggregate faster than MAX