Hi Lok,
SUBSTR(:B8,0*.50*)Looks like this query should be analyzed and tested better.
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) |
==========================================================================================================================================================================================
| 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 |
100.00 | cell smart table scan (3) |
==========================================================================================================================================================================================
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')
