Fixing Performance issue with less selective columns
Date: Wed, 25 Aug 2021 18:44:39 +0530
Message-ID: <CAKna9VZWC+XvuFsFAV8qqeX_ooYnLc0r56M5mNbYcXxv5cKh7A_at_mail.gmail.com>
Hello , This database has version 11.2.0.4 of Oracle. We have the below query which is executed thousands of times. It's used in a plsql function which in turn gets called from a procedure. And this procedure gets called from java thousands of times. And I see from dba_hist_sqlstat , for most of the runs this below query results in zero rows. We see from the active session history for the overall process this query is consuming most time/resources and making the process run longer. So wanted to understand if we can make this individual query execution faster which would eventually make the process faster?
The base table- PP_IN_TAB is holding ~111million rows and is ~43GB in size. Column PP_ID is the primary key here. The filter predicates used in this query are below. Many of them were not very selective in nature. So I am not able to conclude if any composite index is going to help us here. Can you please guide me , what is the correct approach to tune this process in such a scenario?
Below is the column data pattern used as filter predicate in this query. Most of these are less selective in nature.
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS PP_IN_TAB EF_ID 39515 6151686 PP_IN_TAB PE 103074806 647050 PP_IN_TAB PT_Code 24 0 PP_IN_TAB PT_MCODE 20 0 PP_IN_TAB D_CUR_CODE 13 592784 PP_IN_TAB ED_AMT 320892 6 PP_IN_TAB WOF_DATE 2572 83154 PP_IN_TAB PR_CTGRY 2 86 PP_IN_TAB PDE_RSN_CAT 6 0 PP_IN_TAB MA_FLG 2 648172 PP_IN_TAB M_TXT 29460248 9118572 PP_IN_TAB D_UNMTCH 1 111766716
SELECT NVL (I.PP_ID, 0)
FROM PP_IN_TAB I
WHERE TRIM(I.M_TXT) = TRIM (SUBSTR ( :B8, 0.50)) AND I.PT_Code =
:B7
AND NVL ( :B6, I.PT_MCODE) = NVL ( :B6, :B5) AND I.DC_CODE = :B4
AND I.D_CUR_CODE = :B3 AND I.ED_AMT = :B2
AND I.PR_CTGRY = :B1 AND I.PE IS NOT NULL
AND I.EF_ID IS NULL AND I.WOF_DATE IS NULL
AND NVL (I.MA_FLG, 'N') <> 'Y' AND NVL (I.D_UNMTCH, 'N') <> 'Y'
AND ROWNUM = 1;
Global Information
Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777216 Execution Started : 08/25/2021 03:53:25 First Refresh Time : 08/25/2021 03:53:25 Last Refresh Time : 08/25/2021 03:53:28 Duration : 3s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 1
Global Stats
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
| 3.30 | 1.15 | 2.15 | 0.00 | 1 | 6M | 44379 | 43GB | 99.99% |
SQL Plan Monitoring Details (Plan Hash Value=1096440065)
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem |
Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |100.00 | cell smart table scan (3) |
==========================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | | 1 | COUNT STOPKEY | | | | | | 1 | | | | | | | | | 2 | TABLE ACCESS STORAGE FULL | PP_IN_TAB | 1 | 128K | 3 | +2 | 1 | 0 | 44379 | 43GB | 99.99% | 6M |
Predicate Information (identified by operation id):
1 - filter(ROWNUM=1)
2 - storage("I"."WOF_DATE" IS NULL AND "I"."EF_ID" IS NULL AND
"I"."PT_Code"=:B7 AND "I"."D_CUR_CODE"=:B3 AND "I"."PR_CTGRY"=:B1 AND
"I"."DC_CODE"=:B4 AND "I"."ED_AMT"=TO_NUMBER(:B2) AND
NVL(:B6,"I"."PT_MCODE")=NVL(:B6,:B5) AND
TRIM("I"."M_TXT")=TRIM(SUBSTR(:B8,0.50))
AND "I"."PE" IS NOT NULL AND NVL("I"."MA_FLG",'N')<>'Y' AND
NVL("I"."D_UNMTCH",'N')<>'Y')
filter("I"."WOF_DATE" IS NULL AND "I"."EF_ID" IS NULL AND
"I"."PT_Code"=:B7 AND "I"."D_CUR_CODE"=:B3 AND "I"."PR_CTGRY"=:B1 AND
"I"."DC_CODE"=:B4 AND "I"."ED_AMT"=TO_NUMBER(:B2) AND
NVL(:B6,"I"."PT_MCODE")=NVL(:B6,:B5) AND
TRIM("I"."M_TXT")=TRIM(SUBSTR(:B8,0.50))
AND "I"."PE" IS NOT NULL AND NVL("I"."MA_FLG",'N')<>'Y' AND
NVL("I"."D_UNMTCH",'N')<>'Y')
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 25 2021 - 15:14:39 CEST