speed up the query in apex oracle

Question

I have a query , in it I am looking for the maximum and minimum temperature in the silos. The problem is that it works very slowly (only 280 hanging thread). Can anyone help optimize this query (to make it work faster) ?? I will be grateful) My query :

SELECT typ.name AS silo_name,
       tr.outguid,
       tr.name AS dev_name,
       tr.id,
       typ.organization as id_org,
       rt.name as organization,
       MAX(ts.temp) AS max_temp,
       MIN(ns.temp) AS min_temp
  FROM hangingthread_silo ev 
  LEFT JOIN silo typ
    ON ev.id_silo = typ.id
  LEFT JOIN iot_devices tr
    ON ev.devices_id = tr.id
  LEFT JOIN silo_sensor ss
    ON ss.devices_id = tr.outguid
  LEFT JOIN tempr_silo ts
    ON ts.name = ss.name -- AND ts.temp 
  LEFT JOIN tempr_silo ns
    ON ns.name = ss.name -- AND ns.temp 
  LEFT JOIN organizations rt
    ON rt.id = tr.organization
 WHERE ts.id_trans IN (SELECT MAX(id_trans) FROM tempr_silo)
   AND ns.id_trans IN (SELECT MAX(id_trans) FROM tempr_silo)
   AND (:P116_ORG is null or rt.name = :P116_ORG)
 GROUP BY ev.id, typ.name, tr.outguid, tr.id, typ.organization, rt.name, tr.name;

Image result

time qwery : 26s

My demo , https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e8d16ac7d011315cb75e03d27ee3e94b

But in my TEMPR_SILO table 4500 records per ID_TRANS. And I have 280 (for 18 silos) devices. There is much more data and they take a long time to load, 25 seconds. Plan

Please check [here](https://stackoverflow.com/a/34975420/4808122) how you can post the *execution plan* (not the result of the query). — Marmite Bomber, Dec 13 '21 at 14:35
Suspect: you join `ts` and `ns` with *outer join* but you constraints them in the `WHERE` condition which effictively leads to an *inner join*. — Marmite Bomber, Dec 13 '21 at 14:39
@MarmiteBomber My demo)https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e8d16ac7d011315cb75e03d27ee3e94b — Andrii Havrylyak, Dec 13 '21 at 16:33
@OldProgrammer My demo https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e8d16ac7d011315cb75e03d27ee3e94b — Andrii Havrylyak, Dec 13 '21 at 16:34
@AndriiHavrylyak No need for that. Run the explain plan as was requested. — OldProgrammer, Dec 13 '21 at 16:43
I don't see anything odd there. When if you run the query outside of apex just in sql developer? — OldProgrammer, Dec 14 '21 at 15:10

score 1 · Answer 1 · answered Dec 14 '21 at 21:45

This will not explain your problems, but it will show you a positive example with a good performance, so you can compare it with your case.

Test Data

-- 18 silos
insert into silo (ID, NAME)
select rownum id, 'S'||rownum name from dual connect by level <= 18;

-- 20 devices per silo, total 360
insert into HANGINGTHREAD_SILO (DEVICES_ID, ID_SILO)
select  rownum DEVICES_ID, 1+trunc((rownum-1)/20) ID_SILO  from dual connect by level <= 18*20;

insert into IOT_DEVICES(ID, NAME, ORGANIZATION, OUTGUID)
select rownum ID, 'P'||rownum NAME, 1 ORGANIZATION,  rownum OUTGUID from dual connect by level <= 18*20;

-- 100 sensors per device, total 36.0000
insert into SILO_SENSOR(ID, NAME, DEVICES_ID)
select rownum ID, 'SENSOR_'||rownum NAME,   1+trunc((rownum-1)/100) DEVICES_ID from dual connect by level <= 18*20*100;

-- 1000 trans_id per sensor, total 36.000.000 rows
insert into TEMPR_SILO(NAME, TEMP, ID_TRANS)
with temp as (select rownum TEMP, rownum ID_TRANS from dual connect by level <= 1000)
select s.NAME, temp.TEMP, temp.ID_TRANS from SILO_SENSOR s
cross join temp;

The only index you need is on the table TEMPR_SILO to support the access for the last ID_TRANS

create index TEMPR_SILO_IDX1 on TEMPR_SILO(ID_TRANS, NAME, TEMP);

The query returns the 360 rows in a subseconds elapsed time (see A-Time= actual time) in the plan below

 SELECT /*+ gather_plan_statistics */
    TYP.NAME   AS SILO_NAME,
    TR.OUTGUID,
    TR.NAME    AS DEV_NAME,
    TR.id,
    TYP.ORGANIZATION as ID_ORG,
    rt.name as ORGANIZATION,
   MAX(TS.TEMP) AS MAX_TEMP,
   MIN(NS.TEMP) AS MIN_TEMP 
FROM
    HANGINGTHREAD_SILO EV
    LEFT JOIN SILO TYP ON EV.ID_SILO = TYP.ID 
    LEFT JOIN IOT_DEVICES TR ON EV.DEVICES_ID = TR.ID  
    LEFT JOIN SILO_SENSOR SS ON SS.DEVICES_ID = TR.OUTGUID
    LEFT JOIN TEMPR_SILO TS ON TS.NAME = SS.NAME
    LEFT JOIN TEMPR_SILO NS ON NS.NAME = SS.NAME
    LEFT JOIN ORGANIZATIONS rt ON rt.id = TR.ORGANIZATION  
where TS.ID_TRANS IN (
        SELECT
            MAX(ID_TRANS)
        FROM
            TEMPR_SILO 
    ) and NS.ID_TRANS IN (
        SELECT
            MIN(ID_TRANS)
        FROM
            TEMPR_SILO 
    )

GROUP BY
    EV.ID,
    TYP.NAME,
    TR.OUTGUID,
     TR.id,
      TYP.ORGANIZATION,
       rt.name,
    TR.NAME;

You get the plan and statistics from the above query right after the execution with the query

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

that returns

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |      1 |        |    360 |00:00:00.17 |    7792 |       |       |          |
|   1 |  HASH GROUP BY                     |                    |      1 |      1 |    360 |00:00:00.17 |    7792 |   888K|   888K| 1390K (0)|
|*  2 |   HASH JOIN OUTER                  |                    |      1 |      1 |  36000 |00:00:00.15 |    7792 |  3649K|  1579K| 4273K (0)|
|*  3 |    HASH JOIN                       |                    |      1 |      1 |  36000 |00:00:00.13 |    7785 |  3476K|  1686K| 4337K (0)|
|*  4 |     HASH JOIN OUTER                |                    |      1 |      1 |  36000 |00:00:00.12 |    7778 |  3458K|  1730K| 3695K (0)|
|*  5 |      HASH JOIN                     |                    |      1 |      1 |  36000 |00:00:00.10 |    7771 |  3339K|  2324K| 3320K (0)|
|   6 |       NESTED LOOPS                 |                    |      1 |      1 |  36000 |00:00:00.10 |    7764 |       |       |          |
|*  7 |        HASH JOIN                   |                    |      1 |      1 |  36000 |00:00:00.03 |     332 |  3385K|  2007K| 2553K (0)|
|*  8 |         INDEX RANGE SCAN           | TEMPR_SILO_IDX1    |      1 |      1 |  36000 |00:00:00.01 |     157 |       |       |          |
|   9 |          SORT AGGREGATE            |                    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|  10 |           INDEX FULL SCAN (MIN/MAX)| TEMPR_SILO_IDX1    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|  11 |         TABLE ACCESS FULL          | SILO_SENSOR        |      1 |  24745 |  36000 |00:00:00.01 |     175 |       |       |          |
|* 12 |        INDEX RANGE SCAN            | TEMPR_SILO_IDX1    |  36000 |      1 |  36000 |00:00:00.05 |    7432 |       |       |          |
|  13 |         SORT AGGREGATE             |                    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|  14 |          INDEX FULL SCAN (MIN/MAX) | TEMPR_SILO_IDX1    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|  15 |       TABLE ACCESS FULL            | IOT_DEVICES        |      1 |    360 |    360 |00:00:00.01 |       7 |       |       |          |
|  16 |      TABLE ACCESS FULL             | ORGANIZATIONS      |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|  17 |     TABLE ACCESS FULL              | HANGINGTHREAD_SILO |      1 |    360 |    360 |00:00:00.01 |       7 |       |       |          |
|  18 |    TABLE ACCESS FULL               | SILO               |      1 |     18 |     18 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EV"."ID_SILO"="TYP"."ID")
   3 - access("EV"."DEVICES_ID"="TR"."ID")
   4 - access("RT"."ID"="TR"."ORGANIZATION")
   5 - access("SS"."DEVICES_ID"="TR"."OUTGUID")
   7 - access("TS"."NAME"="SS"."NAME")
   8 - access("TS"."ID_TRANS"=)
  12 - access("NS"."ID_TRANS"= AND "NS"."NAME"="SS"."NAME")

Some Discussion

Your query is more an analytic than an OLTP, so don't care much about indexes, you scan in the most tables full data, so full table scan and hash join are OK.

The only exception is the TEMPR_SILO where you have lot of ID_TRANS and you need only the last one. Here helps index, but partitioning on the ID_TRANS may be even better.

You typically do not wan't to see in the execution plan for such queries a mixture of hash joins and nested loops (which is what you observe) - this may be an indication that your object statistics are not up to date.

It seems that your outer join to the table TEMPR_SILO is meaningless as you overide it to an inner join with the where condition

where TS.ID_TRANS IN (SELECT MAX(ID_TRANS) FROM TEMPR_SILO)

even worst you use the exact same where condition for the joins to tsand ns - you get therefore the exact same result in both joins. Please check if this is a typo and either modify the where condition or remove one join.

Last but not least - to post explain plans as image is suboptimal as you mostly post only part of the information (e.g. predicate information is missing). Pls check th elink in my comment how to get execution plan in a text form.

speed up the query in apex oracle

1 Answers1