Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Trying to tune an SQL query

Peter77Mar 25 2024 — edited Mar 25 2024

DB version : 19c

The below query is system generated and it is taking roughly 10 to 12 seconds to complete.
Business wants to run this much quicker.

The real-life query has around 95 columns in the SELECT. But, I have removed most of the columns for readability.
Following is the query and execution plan.

SELECT *
FROM (
SELECT "ENFILE"."ID" o0, 
"ENFILE"."NAME" o1, 
"ENFILE"."DATE" o2, 
"ENFILE"."MUNICIPALITY" o3, 
"ENFILE"."CREATIONDATE" o4, "ENFILE"."FILESTATEID" o5, 
<Several columns snipped for readability ..........................>
"ENMASTERFILETYPE"."IS_ACTIVE" o111, 
((to_char((extract(month FROM "ENLINE"."FUTUREDATE")))
|| '/')
|| to_char((extract(year FROM "ENLINE"."FUTUREDATE")))) "MONTHFUTUREDATE"
FROM ((((("PKMS_ADMIN_BRD"."MANHK_ZUZ_LINE" "ENLINE"
    LEFT JOIN "PKMS_ADMIN_BRD"."MANHK_ZUZ_FILETYPE" "ENFILETYPE" ON ("ENLINE"."FILETYPEID" = "ENFILETYPE"."ID")) 
    LEFT JOIN "PKMS_ADMIN_BRD"."MANHK_ZUZ_FILE" "ENFILE" ON ("ENLINE"."FILEID" = "ENFILE"."ID")) 
    INNER JOIN "PKMS_ADMIN_BRD"."MANHK_ZUZ_FILETYP1" "ENFILETYPEPROPERTIES" ON ("ENFILETYPE"."ID" = "ENFILETYPEPROPERTIES"."ID")) 
    LEFT JOIN "PKMS_ADMIN_BRD"."MANHK_ZUZ_FILETYPE" "ENMASTERFILETYPE" ON ("ENLINE"."MASTERFILETYPEID" = "ENMASTERFILETYPE"."ID")) 
    LEFT JOIN "PKMS_ADMIN_BRD"."MANHK_ZUZ_GROUP" "ENGROUP" ON ("ENLINE"."GROUPID" = "ENGROUP"."ID")) 
WHERE (NOT ("ENLINE"."COMPLETED" = 1))
    AND (("ENLINE"."MASTERLINEID" IS NULL) OR ("ENLINE"."MASTERLINEID" = 0))
    AND (("ENGROUP"."FEDERATIONID" = :1) AND ("ENGROUP"."FEDERATIONID" IS NOT NULL))
    AND ("ENLINE"."LINESTATEID" <> 5)
    AND (((instr(NVL(:2, ' '), (('|' || to_char("ENLINE"."ID")) || '|')) -1)) = (-1))
ORDER BY "ENFILE"."DATE", "ENLINE"."DATETIMEINITIALIZED" ASC )
WHERE ROWNUM <= 11;

Plan hash value: 2723328426

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                |    11 |   178K|       | 52542   (1)| 00:00:03 |
|*  1 |  COUNT STOPKEY                            |                                |       |       |       |            |          |
|   2 |   VIEW                                    |                                |  6663 |   105M|       | 52542   (1)| 00:00:03 |
|*  3 |    SORT ORDER BY STOPKEY                  |                                |  6663 |  6721K|  7624K| 52542   (1)| 00:00:03 |
|*  4 |     HASH JOIN RIGHT OUTER                 |                                |  6663 |  6721K|       | 51096   (1)| 00:00:02 |
|   5 |      TABLE ACCESS FULL                    | MANHK_ZUZ_FILE                 |  7945 |   682K|       |   102   (0)| 00:00:01 |
|*  6 |      HASH JOIN RIGHT OUTER                |                                |  6638 |  6125K|       | 50994   (1)| 00:00:02 |
|   7 |       TABLE ACCESS FULL                   | MANHK_ZUZ_FILETYPE             |   134 |  2412 |       |     3   (0)| 00:00:01 |
|*  8 |       HASH JOIN                           |                                |  6638 |  6009K|       | 50991   (1)| 00:00:02 |
|   9 |        TABLE ACCESS BY INDEX ROWID BATCHED| MANHK_ZUZ_GROUP                |     9 |   180 |       |     2   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN                  | OSIDX_MANHK_ZUZ_GROUP_12F16273 |     9 |       |       |     1   (0)| 00:00:01 |
|* 11 |        HASH JOIN                          |                                | 10157 |  8996K|       | 50989   (1)| 00:00:02 |
|* 12 |         HASH JOIN                         |                                |   134 | 33366 |       |     6   (0)| 00:00:01 |
|  13 |          TABLE ACCESS FULL                | MANHK_ZUZ_FILETYPE             |   134 |  2412 |       |     3   (0)| 00:00:01 |
|  14 |          TABLE ACCESS FULL                | MANHK_ZUZ_FILETYP1             |   134 | 30954 |       |     3   (0)| 00:00:01 |
|* 15 |         TABLE ACCESS FULL                 | MANHK_ZUZ_LINE                 | 10157 |  6526K|       | 50983   (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------------------------------------

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

  1 - filter(ROWNUM<=11)
  3 - filter(ROWNUM<=11)
  4 - access("ENLINE"."FILEID"="ENFILE"."ID"(+))
  6 - access("ENLINE"."MASTERFILETYPEID"="ENMASTERFILETYPE"."ID"(+))
  8 - access("ENLINE"."GROUPID"="ENGROUP"."ID")
 10 - access("ENGROUP"."FEDERATIONID"=TO_NUMBER(:1))
 11 - access("ENLINE"."FILETYPEID"="ENFILETYPE"."ID")
 12 - access("ENFILETYPE"."ID"="ENFILETYPEPROPERTIES"."ID")
 15 - filter(("ENLINE"."MASTERLINEID"=0 OR "ENLINE"."MASTERLINEID" IS NULL) AND "ENLINE"."COMPLETED"<>1 AND 
             "ENLINE"."LINESTATEID"<>5 AND INSTR(NVL(:2,' '),'|'||TO_CHAR("ENLINE"."ID")||'|')-1=(-1))

There are 5 Full table scans happening. But,

MANHK_ZUZ_FILE is only 3 MB
MANHK_ZUZ_FILETYPE is only 64 KB
MANHK_ZUZ_FILETYP1 is only 64 KB

MANHK_ZUZ_LINE is 1.5 GB table

So, I am trying to avoid the full table scan on MANHK_ZUZ_LINE table.

I tried creating the following index. But, optimizer ignored it even I tried to use a hint.

CREATE INDEX PKMS_ADMIN_BRD.MANHK_ZUZ_LINE_IDX4 ON PKMS_ADMIN_BRD.MANHK_ZUZ_LINE (MASTERLINEID) ONLINE ;

Hint I tried, but optimizer refused to use.

/*+ INDEX (ENLINE MANHK_ZUZ_LINE_IDX4) */

There are already single column indexes on ID, FILEID, FILETYPEID, GROUPID, LINESTATEID, COMPLETED columns.

Following are the count of distinct values of other columns in MANHK_ZUZ_LINE table involved in this query.

Any other columns or combination of columns (for composite column index) I should try for an index ?

Or any other suggestions to tune this query ?

Due to some technical issues, I couldn't generate extended stats using GATHER_PLAN_STATISTICS hint.

select count(distinct id),
count(distinct FILEID),
count(distinct GROUPID),
count(distinct masterlineid),
count(distinct FILETYPEID),
count(distinct LINESTATEID),
count (distinct completed)
from PKMS_ADMIN_BRD.MANHK_ZUZ_LINE ;

COUNT(DISTINCTID) COUNT(DISTINCTFILEID) COUNT(DISTINCTGROUPID) COUNT(DISTINCTMASTERLINEID) COUNT(DISTINCTFILETYPEID) COUNT(DISTINCTLINESTATEID) COUNT(DISTINCTCOMPLETED)
----------------- --------------------- ---------------------- --------------------------- ------------------------- -------------------------- ------------------------
          2295431                  8026                     13                      219508                       102                          3                        2

1 row selected.
Comments
Post Details
Added on Mar 25 2024
7 comments
134 views