Home » RDBMS Server » Performance Tuning » Select query too slow (11.2.04 Windows 2012)
Select query too slow [message #678517] Tue, 10 December 2019 08:50 Go to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Hi,

One of my select query is not working in the new schema created on a new server whith IMPDP option

In production server it works in 30 seconds , but in the new server it is taking around 40 minutes

The server Memory and CPU is more in the new server.

All the indexes are present and I rebuild all , Validate structure is also fine

Kinldy help

Thanks and REgards,
Rajesh
Re: Select query too slow [message #678518 is a reply to message #678517] Tue, 10 December 2019 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

Re: Select query too slow [message #678519 is a reply to message #678518] Tue, 10 December 2019 09:40 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
SELECT DISTINCT
        (
                SELECT  DESCRIPTION
                FROM    DX_MST_ORGANISATION_LANG
                WHERE   ORGCODE      = DX_MST_FILE.CORRORGCODE
                        AND LANGCODE = 'EN'
        ) AS CORRORNAME    ,
        T.CORRORGCODE      ,
        CURCODES           ,
        NVL(ATYPE,'N')ATYPE,
        CASE
                WHEN ATYPE = 'Y'
                THEN 'TT'
                WHEN ATYPE = 'N'
                THEN 'DD/CREDITCARD PAYMENT'
        END TYPE
FROM    DX_MST_FILE ,
        (
                SELECT  O.CORRORGCODE
                FROM    DX_TRNS_OUTGOING_DETAILS O,
                        DX_TRNS_REMITTANCE R
                WHERE   O.CORRORGCODE       = R.CORRORGCODE
                        AND R.SERVCODE NOT IN
                        (
                                SELECT  servcode
                                FROM    dx_mst_service
                                WHERE   categcode   ='00010'
                                        AND servcode= R.SERVCODE
                        )
                        AND O.REFNO      = R.REFNO
                        AND R.AUTHFLG    = 'Y'
                        AND R.CANCELIND  = '0'
                        AND R.TRANTYPE  IN ('0','R')
                        AND R.REFNO NOT IN
                        (
                                SELECT  REFNO
                                FROM    DX_TRNS_REJECTED_ENTRY
                                WHERE   TRANDATE BETWEEN TO_DATE('19/Nov/2018 00:00:00','dd/Mon/yyyy hh24:mi:ss') AND TO_DATE('19/Nov/2019 23:59:59','dd/Mon/yyyy  hh24:mi:ss')
                        )
                        AND O.TRANDATE BETWEEN TO_DATE('19/Nov/2018 00:00:00','dd/Mon/yyyy hh24:mi:ss') AND TO_DATE('19/Nov/2019 23:59:59','dd/Mon/yyyy  hh24:mi:ss')
                        AND O.DESPATCHFLAG = 'Y'
                        AND O.FILEFLAG     = 'N'
                        AND O.CORRORGCODE IN
                        (
                                SELECT  CORRORGCODE
                                FROM    DX_MST_FILE
                                WHERE   ATYPE          = 'Y'
                                        AND CORRORGCODE= O.CORRORGCODE
                        )
        )
        T
WHERE   ACTIVEFLG                   = 'Y'
        AND DX_MST_FILE.CORRORGCODE = T.CORRORGCODE
        AND ORGCODE                 = '00001'
ORDER BY CORRORNAME;

=================================================================================================================================
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2948708020

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |     1 |   124 |    21  (10)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN                   | NDX_ORGLANG_ACTFLG       |     1 |    60 |     2   (0)| 00:00:01 |
|   2 |  SORT UNIQUE                        |                          |     1 |   124 |    20   (5)| 00:00:01 |
|   3 |   NESTED LOOPS                      |                          |     1 |   124 |    17   (0)| 00:00:01 |
|   4 |    NESTED LOOPS                     |                          |    13 |   124 |    17   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                    |                          |     1 |   104 |    11   (0)| 00:00:01 |
|   6 |      NESTED LOOPS                   |                          |     1 |    96 |    10   (0)| 00:00:01 |
|   7 |       NESTED LOOPS ANTI             |                          |     1 |    60 |     8   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS BY INDEX ROWID  | DX_TRNS_REMITTANCE       |     1 |    35 |     6   (0)| 00:00:01 |
|*  9 |         INDEX SKIP SCAN             | IDX_TT_BATCHING          |     1 |       |     5   (0)| 00:00:01 |
|* 10 |          TABLE ACCESS BY INDEX ROWID| DX_MST_SERVICE           |     1 |    12 |     2   (0)| 00:00:01 |
|* 11 |           INDEX RANGE SCAN          | PK_DX_MST_SERVICE        |     1 |       |     1   (0)| 00:00:01 |
|* 12 |        INDEX SKIP SCAN              | IDX_REJECTED_ENTRY       |  5655 |   138K|     2   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS BY INDEX ROWID   | DX_TRNS_OUTGOING_DETAILS |     1 |    36 |     2   (0)| 00:00:01 |
|* 14 |        INDEX UNIQUE SCAN            | IDX_UK_OUTGOING_REF      |     1 |       |     1   (0)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN               | IDX_FILE_ATYPE           |     2 |    16 |     1   (0)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN                | IDX_FILE_CORR            |    13 |       |     1   (0)| 00:00:01 |
|* 17 |    TABLE ACCESS BY INDEX ROWID      | DX_MST_FILE              |    16 |   320 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ORGCODE"=:B1 AND "LANGCODE"='EN')
   8 - filter("R"."TRANTYPE"='0' OR "R"."TRANTYPE"='R')
   9 - access("R"."CANCELIND"='0' AND "R"."AUTHFLG"='Y')
       filter("R"."CANCELIND"='0' AND "R"."AUTHFLG"='Y' AND  NOT EXISTS (SELECT 0 FROM "DX_MST_SERVICE"
              "DX_MST_SERVICE" WHERE "SERVCODE"=:B1 AND "CATEGCODE"='00010' AND LNNVL("SERVCODE"<>:B2)))
  10 - filter("CATEGCODE"='00010')
  11 - access("SERVCODE"=:B1)
       filter(LNNVL("SERVCODE"<>:B1))
  12 - access("R"."REFNO"="REFNO" AND "TRANDATE">=TO_DATE(' 2018-11-19 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "TRANDATE"<=TO_DATE(' 2019-11-19 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter("TRANDATE">=TO_DATE(' 2018-11-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TRANDATE"<=TO_DATE(' 2019-11-19 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "R"."REFNO"="REFNO")
  13 - filter("O"."FILEFLAG"='N' AND "O"."TRANDATE">=TO_DATE(' 2018-11-19 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "O"."DESPATCHFLAG"='Y' AND "O"."TRANDATE"<=TO_DATE(' 2019-11-19 23:59:59',
              'syyyy-mm-dd hh24:mi:ss') AND "O"."CORRORGCODE"="R"."CORRORGCODE")
  14 - access("O"."REFNO"="R"."REFNO")
  15 - access("ATYPE"='Y' AND "O"."CORRORGCODE"="CORRORGCODE")
  16 - access("DX_MST_FILE"."CORRORGCODE"="O"."CORRORGCODE")
*Blackswan added {code} tags. Please do so yourself in the future

[Updated on: Tue, 10 December 2019 09:57] by Moderator

Report message to a moderator

Re: Select query too slow [message #678520 is a reply to message #678519] Tue, 10 December 2019 09:55 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Trace File


TKPROF: Release 11.2.0.4.0 - Development on Tue Dec 10 19:53:10 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Trace file: icareprod_ora_36.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 4kvc1pjyh5n7x Plan Hash: 0

ALTER SESSION SET SQL_TRACE=TRUE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
********************************************************************************

EXPLAIN PLAN FOR
SELECT DISTINCT
(
SELECT DESCRIPTION
FROM DX_MST_ORGANISATION_LANG
WHERE ORGCODE = DX_MST_FILE.CORRORGCODE
AND LANGCODE = 'EN'
) AS CORRORNAME ,
T.CORRORGCODE ,
CURCODES ,
NVL(ATYPE,'N')ATYPE,
CASE
WHEN ATYPE = 'Y'
THEN 'TT'
WHEN ATYPE = 'N'
THEN 'DD/CREDITCARD PAYMENT'
END TYPE
FROM DX_MST_FILE ,
(
SELECT O.CORRORGCODE
FROM DX_TRNS_OUTGOING_DETAILS O,
DX_TRNS_REMITTANCE R
WHERE O.CORRORGCODE = R.CORRORGCODE
AND R.SERVCODE NOT IN
(
SELECT servcode
FROM dx_mst_service
WHERE categcode ='00010'
AND servcode= R.SERVCODE
)
AND O.REFNO = R.REFNO
AND R.AUTHFLG = 'Y'
AND R.CANCELIND = '0'
AND R.TRANTYPE IN ('0','R')
AND R.REFNO NOT IN
(
SELECT REFNO
FROM DX_TRNS_REJECTED_ENTRY
WHERE TRANDATE BETWEEN TO_DATE('19/Nov/2019 00:00:00','dd/Mon/yyyy hh24:mi:ss') AND TO_DATE('19/Nov/2019 23:59:59','dd/Mon/yyyy hh24:mi:ss')
)
AND O.TRANDATE BETWEEN TO_DATE('19/Nov/2019 00:00:00','dd/Mon/yyyy hh24:mi:ss') AND TO_DATE('19/Nov/2019 23:59:59','dd/Mon/yyyy hh24:mi:ss')
AND O.DESPATCHFLAG = 'Y'
AND O.FILEFLAG = 'N'
AND O.CORRORGCODE IN
(
SELECT CORRORGCODE
FROM DX_MST_FILE
WHERE ATYPE = 'Y'
AND CORRORGCODE= O.CORRORGCODE
)
)
T
WHERE ACTIVEFLG = 'Y'
AND DX_MST_FILE.CORRORGCODE = T.CORRORGCODE
AND ORGCODE = '00001'
ORDER BY CORRORNAME

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
********************************************************************************

SQL ID: 99qa3zyarxvms Plan Hash: 0

insert into plan_table (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
bytes, other_tag, partition_start, partition_stop, partition_id,
distribution, cpu_cost, io_cost, temp_space, access_predicates,
filter_predicates, projection, time, qblock_name, object_alias, plan_id,
depth, remarks, other_xml )
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 18 0.00 0.00 0 7 24 18
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 7 24 18

Misses in library cache during parse: 1
Misses in library cache during execute: 6
Optimizer mode: ALL_ROWS
Parsing user id: 90 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=62 us)

********************************************************************************

SQL ID: 15knr3nbjkrcw Plan Hash: 2501920895

SELECT ORA_PLAN_ID_SEQ$.NEXTVAL
FROM
DUAL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SEQUENCE ORA_PLAN_ID_SEQ$ (cr=0 pr=0 pw=0 time=11 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)

********************************************************************************

SQL ID: dbmdavb6ydwb4 Plan Hash: 0

ALTER SESSION SET SQL_TRACE=FALSE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.01 0 0 0 0

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 19 0.00 0.00 0 7 24 18
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22 0.00 0.00 0 7 24 19

Misses in library cache during parse: 2
Misses in library cache during execute: 6

4 user SQL statements in session.
1 internal SQL statements in session.
5 SQL statements in session.
********************************************************************************
Trace file: icareprod_ora_36.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
4 user SQL statements in trace file.
1 internal SQL statements in trace file.
5 SQL statements in trace file.
5 unique SQL statements in trace file.
133 lines in trace file.
51 elapsed seconds in trace file.


Re: Select query too slow [message #678521 is a reply to message #678520] Tue, 10 December 2019 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why does SQL that runs in 0.01 seconds need to be faster? How fast is fast enough?

PLEASE use
 
tags in the future.
Re: Select query too slow [message #678522 is a reply to message #678521] Tue, 10 December 2019 10:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to generate the explain plan and trace on the system where it's taking 40 minutes, not some dev/test system where it runs instantly.
Also - the trace needs to be of an execution of the select statement. NOT for an execution of "EXPLAIN PLAN FOR <statement>"
Re: Select query too slow [message #678523 is a reply to message #678521] Wed, 11 December 2019 01:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
BlackSwan wrote on Tue, 10 December 2019 15:59
Why does SQL that runs in 0.01 seconds need to be faster? How fast is fast enough?

PLEASE use
 
tags in the future.
There is no information about the run time of the statement: the trace does not include the statement, and the exec plan shows only the long outdated "Time" estimate which has been meaningless since release 8i, when CPU costing was introduced.
Re: Select query too slow [message #678524 is a reply to message #678519] Wed, 11 December 2019 01:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
On which system did you generate that plan? The old or the new? You need both for the comparison.
Re: Select query too slow [message #678533 is a reply to message #678524] Wed, 11 December 2019 09:00 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
[size=5]I have executed it on the new  system


PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation               | Name                     | Starts| E-Rows | A-Rows |   A-Time   | Buffers |  OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------- -
|   0 | SELECT STATEMENT        |                          |      1|        |      1 |00:04:36.04 |      42M|       |       |         |
|*  1 |  INDEX RANGE SCAN       | NDX_ORGLANG_ACTFLG       |      1|      1 |      1 |00:00:00.01 |       3 |       |       |         |
|   2 |  SORT UNIQUE            |                          |      1|      1 |      1 |00:04:36.04 |      42M|  2048 |  2048 | 2048 (0)|
|   3 |   NESTED LOOPS          |                          |      1|      1 |      2 |00:01:34.97 |      42M|       |       |         |
|   4 |    NESTED LOOPS         |                          |      1|     13 |      2 |00:01:34.97 |      42M|       |       |         |
|   5 |     NESTED LOOPS        |                          |      1|      1 |      2 |00:01:34.97 |      42M|       |       |        |   6 |      NESTED LOOPS       |                          |      1|      1 |      2 |00:01:34.97 |      42M|       |       |        |   7 |       NESTED LOOPS ANTI |                          |      1|      1 |   6840K|00:03:59.84 |      34M|       |       |         |*  8 |        TABLE ACCESS BY INDEX ROWID  |DX_TRNS_REMITTANCE|  1|      1 |   6840K|00:00:54.69 |    6825K|       |       |         |
|*  9 |         INDEX SKIP SCAN             | IDX_TT_BATCHING|    1|      1 |   6840K|00:00:17.30 |   59114 |       |       |         |
|* 10 |          TABLE ACCESS BY INDEX ROWID| DX_MST_SERVICE |  107|      1 |     26 |00:00:00.01 |     214 |       |       |         |
|* 11 |           INDEX RANGE SCAN          | PK_DX_MST_SERVICE|107|      1 |    107 |00:00:00.01 |     107 |       |       |         |
|* 12 |        INDEX SKIP SCAN              | IDX_REJECTED_ENTRY|6840K|   1 |      0 |00:02:55.41 |      27M|       |       |         |
|* 13 |       TABLE ACCESS BY INDEX ROWID   | DX_TRNS_OUTGOING_DETAILS|6840K|1 |   2 |00:00:38.64 |    8032K|       |       |         |
|* 14 |        INDEX UNIQUE SCAN            | IDX_UK_OUTGOING_REF|6840K     |1  3476K|00:00:21.55 |    5521K|       |       |         |
|* 15 |      INDEX RANGE SCAN               | IDX_FILE_ATYPE     |  2|    2 |  2     |00:00:00.01 |       4 |       |       |         |
|* 16 |     INDEX RANGE SCAN                | IDX_FILE_CORR      |  2|   13 |  2     |00:00:00.01 |       4 |       |       |         |
|* 17 |    TABLE ACCESS BY INDEX ROWID      | DX_MST_FILE        |  2|   16 |      2 |00:00:00.01 |       1 |       |       |         |
 ------------------------------------------------------------------------------------------------------------------------------------- 
[/size][/size]

[Updated on: Wed, 11 December 2019 09:10]

Report message to a moderator

Re: Select query too slow [message #678534 is a reply to message #678533] Wed, 11 December 2019 09:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Consistently failing to use [code] tags is extremely rude.
Re: Select query too slow [message #678535 is a reply to message #678534] Wed, 11 December 2019 09:10 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Really Sorry for the same, I have updated
Re: Select query too slow [message #678536 is a reply to message #678534] Wed, 11 December 2019 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
In the first reply, click on the 2nd link and read what it says about code tags.
Re: Select query too slow [message #678537 is a reply to message #678536] Wed, 11 December 2019 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Somethings gone horrible wrong with your formatting. Suggest you fix it.
Re: Select query too slow [message #678538 is a reply to message #678533] Wed, 11 December 2019 09:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That shows the query taking 4min 36sec, whereas in your first post you said 40 minutes. Well, never mind that. You also need to do the same thing on the old server, so that they can be compared.

[Updated on: Wed, 11 December 2019 09:14]

Report message to a moderator

Re: Select query too slow [message #679020 is a reply to message #678517] Tue, 28 January 2020 08:52 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
SELECT DISTINCT (SELECT description 
                 FROM   dx_mst_organisation_lang 
                 WHERE  orgcode = dx_mst_file.corrorgcode 
                        AND langcode = 'EN') AS CORRORNAME, 
                T.corrorgcode, 
                curcodes, 
                Nvl(atype, 'N')              ATYPE, 
                CASE 
                  WHEN atype = 'Y' THEN 'TT' 
                  WHEN atype = 'N' THEN 'DD/CREDITCARD PAYMENT' 
                END                          TYPE 
FROM   dx_mst_file, 
       (SELECT O.corrorgcode 
        FROM   (SELECT * 
                FROM   dx_trns_outgoing_details 
                WHERE  fileflag = 'N' 
                       AND despatchflag = 'Y' 
                       AND trandate BETWEEN To_date('15/Jan/2020 00:00:00', 
                                            'dd/Mon/yyyy hh24:mi:ss') 
                                            AND 
                                            To_date( 
                                                '15/Jan/2020 23:59:59', 
                                            'dd/Mon/yyyy  hh24:mi:ss') 
                       AND corrorgcode IN (SELECT corrorgcode 
                                           FROM   dx_mst_file 
                                           WHERE  atype = 'Y' 
                                                  AND orgcode = '00001')) O, 
               (SELECT * 
                FROM   dx_trns_remittance 
                WHERE  authflg = 'Y' 
                       AND cancelind = '0' 
                       AND trantype IN ( '0', 'R' ) 
                       AND trandate BETWEEN To_date('15/Jan/2019 00:00:00', 
                                            'dd/Mon/yyyy hh24:mi:ss') 
                                            AND 
                                            To_date( 
                                                '15/Jan/2020 23:59:59', 
                                            'dd/Mon/yyyy  hh24:mi:ss') 
                       AND NOT EXISTS (SELECT refno 
                                       FROM   dx_trns_rejected_entry 
                                       WHERE  trandate BETWEEN To_date( 
                                              '15/Jan/2020 00:00:00', 
                                              'dd/Mon/yyyy hh24:mi:ss' 
                                              ) AND 
                                              To_date( 
                                              '15/Jan/2020 23:59:59', 
                                              'dd/Mon/yyyy  hh24:mi:ss' 
                                              ) 
                                              AND dx_trns_rejected_entry.refno = 
                                                  dx_trns_remittance.refno) 
                       AND NOT EXISTS (SELECT NULL 
                                       FROM   dx_mst_service 
                                       WHERE  categcode = '00010' 
                                              AND dx_mst_service.servcode = 
                                                  dx_trns_remittance.servcode 
                                      )) R 
        WHERE  O.corrorgcode = R.corrorgcode 
               AND O.refno = R.refno) T 
WHERE  activeflg = 'Y' 
       AND dx_mst_file.corrorgcode = T.corrorgcode 
       AND orgcode = '00001' 
ORDER  BY corrorname   
Re: Select query too slow [message #679022 is a reply to message #679020] Tue, 28 January 2020 09:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
When you've ignored a thread for a month and a half it helps if you write some words explaining where you're up to rather than just dumping a SQL statement.
Is that a new version of the sql?
How long is it taking?
Post an update explain plan that's formatted so that it's actually readable.
Re: Select query too slow [message #679027 is a reply to message #679022] Tue, 28 January 2020 10:57 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is just a reaction of my remark in his new topic.
The explanation is in a post in this later:

Quote:
I have modified the query to imporve the performance ,Replaced NOT IN with NOT EXISTS
Previous Topic: sqlID time for every Sql id execution
Next Topic: Database performance tuning
Goto Forum:
  


Current Time: Thu Mar 28 16:27:28 CDT 2024