Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: October 19, 2023 - 7:47 am UTC

Last updated: November 30, 2023 - 5:37 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

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)

and Chris said...

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

Rating

  (1 rating)

Comments

perhaps a SQL Monitor report.

Rajeshwaran Jeyabal, November 30, 2023 - 5:12 am UTC

or perhaps a sql monitor report for this sqlid bjhx2g3b6krmf would help.
Chris Saxon
November 30, 2023 - 5:37 pm UTC

Yes, that could also be useful for seeing what the query is processing

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.