hi, there are queries which are performing slow from past 1 day, earlier same queries were giving output in 22 mins and not it takes 55 mins, this is basically the EOD process.
Query 1:
SELECT DISTINCT DECODE(:B1 ,'C',IR.REASON_CODE,'D',IR.DESCRIPTION)
REASON FROM DEA_REQN_REASONS RR INNER JOIN DEA_INVESTMENT_REASON IR
ON UPPER(RR.REASON_CODE) = UPPER(IR.REASON_CODE) WHERE RR.REQ_ID IN
(SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC WHERE APP_REFER = :B2 UNION
ALL SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC_HIST WHERE APP_REFER =
:B2 UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC WHERE APP_REFER =
:B2 UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC_HIST WHERE
APP_REFER = :B2 ) AND IR.RECTYPE='L'
Query 2:
select * from v_chkposint where exists (select scheme from scheme
where amc_code = v_chkposint.amc_code and scheme = v_chkposint.scheme
and project_y_n = 'Y' and rectype = 'L')
what's the scope of tuning, if you expert advise is given, will help to improve the performance.
BEGIN_INTERVAL_TIME PLAN_HASH_VALUE OPTIMIZER_ SQL_PROFIL DISK_READS_TOTAL BUFFER_GETS_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL
------------------------------ --------------- ---------- ---------- ---------------- ----------------- ----------------- ------------------ -----------------
20-OCT-23 04.30.46.535 PM 1596829921 ALL_ROWS 45897 11332241 18512737 68903861 8012199
20-OCT-23 01.30.04.043 PM 1596829921 ALL_ROWS 680 7598713 12852262 47355121 428789
19-OCT-23 03.30.03.269 PM 1596829921 ALL_ROWS 27854 393591114 729369364 2402058351 781202
19-OCT-23 11.30.11.237 AM 1596829921 ALL_ROWS 27854 379549234 706629192 2327540410 781202
18-OCT-23 06.30.23.024 PM 1596829921 ALL_ROWS 13936 126131290 220169362 733216565 418827
Please find below execution plan for SQL_ID [bjhx2g3b6krmf and ff0sjh69smwx2].
+++++++++++++Execution Plan for SQL_ID [bjhx2g3b6krmf]
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('bjhx2g3b6krmf'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bjhx2g3b6krmf
--------------------
SELECT DISTINCT DECODE(:B1 ,'C',IR.REASON_CODE,'D',IR.DESCRIPTION)
REASON FROM DEA_REQN_REASONS RR INNER JOIN DEA_INVESTMENT_REASON IR ON
UPPER(RR.REASON_CODE) = UPPER(IR.REASON_CODE) WHERE RR.REQ_ID IN
(SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC WHERE APP_REFER = :B2 UNION
ALL SELECT DEAL_ID FROM DEA_SMD_DEALS_ALLOC_HIST WHERE APP_REFER = :B2
UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC WHERE APP_REFER = :B2
UNION ALL SELECT DEAL_ID FROM DEA_PMDEAL_ALLOC_HIST WHERE APP_REFER =
:B2 ) AND IR.RECTYPE='L'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1596829921
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16806 (100)| |
| 1 | HASH UNIQUE | | 31 | 2108 | 16806 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 31 | 2108 | 16805 (1)| 00:00:01 |
| 3 | HASH JOIN RIGHT SEMI | | 38 | 1292 | 16802 (1)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | | | 585 (1)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| DEA_SMD_DEALS_ALLOC | 1 | 30 | 2 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | DBA_APP_REFER | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | DEA_SMD_DEALS_ALLOC_HIST | 2 | 60 | 12016 (1)| 00:00:01 |
| 9 | TABLE ACCESS FULL | DEA_PMDEAL_ALLOC | 1 | 30 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | DEA_PMDEAL_ALLOC_HIST | 35 | 1050 | 3072 (1)| 00:00:01 |
| 11 | TABLE ACCESS FULL | DEA_REQN_REASONS | 1925K| 34M| 1699 (2)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | DEA_INVESTMENT_REASON | 1 | 34 | 3 (0)| 00:00:01 |
| 13 | INDEX SKIP SCAN | PK_DEA_INVESTMENT_REASON | | | | |
--------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
36 rows selected.
+++++++++++++Execution Plan for SQL_ID [ff0sjh69smwx2]
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('ff0sjh69smwx2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ff0sjh69smwx2
--------------------
select * from v_chkposint where exists (select scheme from scheme where
amc_code = v_chkposint.amc_code and scheme = v_chkposint.scheme and
project_y_n = 'Y' and rectype = 'L')
Plan hash value: 1849539290
--------------------------------------------------------------------------------
---------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 13566 (100)| |
| 1 | NESTED LOOPS | | 2 | 192 | | 13566 (1)| 00:00:01 |
| 2 | HASH JOIN | | 47 | 3290 | | 1242 (1)| 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 14 | | 55 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 14 | | 55 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SCHEME | 1 | 14 | | 55 (0)| 00:00:01 |
| 6 | VIEW | | 88499 | 4839K| | 1185 (1)| 00:00:01 |
| 7 | HASH GROUP BY | | 88499 | 2852K| 4520K| 1185 (1)| 00:00:01 |
| 8 | JOIN FILTER USE | :BF0000 | 88499 | 2852K| | 392 (2)| 00:00:01 |
| 9 | TABLE ACCESS FULL | POSITIONS | 88499 | 2852K| | 392 (2)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 26 | | 262 (1)| 00:00:01 |
| 11 | FILTER | | | | | | |
| 12 | SORT AGGREGATE | | 1 | 40 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| POSITIONINT | 126 | 5040 | | 262 (1)| 00:00:01 |
| 14 | INDEX SKIP SCAN | I_POSITIONINT | 125 | | | 134 (2)| 00:00:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ff0sjh69smwx2
--------------------
select * from v_chkposint where exists (select scheme from scheme where
amc_code = v_chkposint.amc_code and scheme = v_chkposint.scheme and
project_y_n = 'Y' and rectype = 'L')
Plan hash value: 2909981087
--------------------------------------------------------------------------------
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 179K(100)| |
| 1 | HASH JOIN RIGHT SEMI | | 3034 | 373K| | 179K (2)| 00:00:08 |
| 2 | TABLE ACCESS FULL | SCHEME | 66 | 924 | | 57 (0)| 00:00:01 |
| 3 | HASH JOIN | | 3034 | 331K| 8288K| 179K (2)| 00:00:08 |
| 4 | VIEW | | 124K| 6823K| | 177K (2)| 00:00:07 |
| 5 | HASH GROUP BY | | 124K| 4873K| 1395M| 177K (2)| 00:00:07 |
| 6 | TABLE ACCESS FULL| POSITIONINT | 24M| 926M| | 87741 (1)| 00:00:04 |
| 7 | VIEW | | 133K| 7281K| | 1869 (1)| 00:00:01 |
| 8 | HASH GROUP BY | | 133K| 4291K| 6800K| 1869 (1)| 00:00:01 |
| 9 | TABLE ACCESS FULL| POSITIONS | 133K| 4291K| | 679 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ff0sjh69smwx2
--------------------
select * from v_chkposint where exists (select scheme from scheme where
amc_code = v_chkposint.amc_code and scheme = v_chkposint.scheme and
project_y_n = 'Y' and rectype = 'L')
Plan hash value: 3152787882
--------------------------------------------------------------------------------
---------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 184K(100)| |
| 1 | HASH JOIN RIGHT SEMI | | 16577 | 2039K| | 184K(2)| 00:00:08 |
| 2 | TABLE ACCESS FULL | SCHEME | 66 | 924 | | 57(0)| 00:00:01 |
| 3 | MERGE JOIN | | 16577 | 1813K| | 184K(2)| 00:00:08 |
| 4 | SORT JOIN | | 679K| 36M| | 180K(2)| 00:00:08 |
| 5 | VIEW | | 679K| 36M| | 180K(2)| 00:00:08 |
| 6 | HASH GROUP BY | | 679K| 25M| 1395M| 180K(2)| 00:00:08 |
| 7 | TABLE ACCESS FULL | POSITIONINT | 24M| 926M| | 87741(1)| 00:00:04 |
| 8 | FILTER | | | | | | |
| 9 | SORT JOIN | | 133K| 7276K| 18M| 3685(1)| 00:00:01 |
| 10 | VIEW | | 133K| 7276K| | 1868(1)| 00:00:01 |
| 11 | SORT GROUP BY | | 133K| 4287K| 6792K| 1868(1)| 00:00:01 |
| 12 | TABLE ACCESS FULL| POSITIONS | 133K| 4287K| | 679(2)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement
SQL> DEF
DEFINE _DATE = "20-OCT-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "mfundprd" (CHAR)
DEFINE _USER = "SYS" (CHAR)
There are three plans for the second query; they're all very different so I would expect them all to perform differently.
Ensure it uses the fastest plan, use SQL Plan Management to create a baseline using that plan. This ensures the optimizer will only use that plan.
The first query has the same plan every time, so there is some other reason for the performance difference.
Is it processing (lots) more data for some executions compared to others?
You can see how much data it processes at each step by getting the plan like this:
set serveroutput off
alter session set statistics_level = all;
select ...
select *
from dbms_xplan.display_cursor(format => 'ALLSTATS LAST');