Thank You so much.
This is an existing old code, so I need to see exactly what SUBSTR is
doing. Below are the existing histograms on those columns used in query
predicates. And I tried capturing binds for one execution and they are as
below. But yes it may vary for other executions I think.
You mentioned " *"I.WOF_DATE IS NULL" is one of the most selective
predicates - it gives only **83154 nulls.", *I was mainly looking into the
NUM_DISTINCT part only. Am I wrong in that perspective? Num_distinct wise
there are other columns which seem to be in better position as compared to
WOF_DATE, so wondering , how did you come to that conclusion?
I was thinking if column ED_AMT is an amount column, if we should index
that and it would be more beneficial?
There already exists two composite index 1- on column
(EF_ID,Different_column,PT_CODE) and another one on (PE,PT_CODE) but it
seems these are not helpful here. I am also looking if without creating an
additional new index this can be tuned but seems no way.
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM Density
PP_IN_TAB EF_ID 39515 6151686 HEIGHT BALANCED 3.28E-04
PP_IN_TAB PE 103074806 647050 HEIGHT BALANCED 1.05E-08
PP_IN_TAB PT_Code 24 0 FREQUENCY 4.47E-09
PP_IN_TAB PT_MCODE 20 0 FREQUENCY 4.47E-09
PP_IN_TAB D_CUR_CODE 13 592784 FREQUENCY 4.50E-09
PP_IN_TAB ED_AMT 320892 6 HEIGHT BALANCED 0.00068306
PP_IN_TAB WOF_DATE 2572 83154 HEIGHT BALANCED 8.83E-04
PP_IN_TAB DR_CR_CD 2 86 FREQUENCY 4.47E-09
PP_IN_TAB PR_CTGRY 6 0 FREQUENCY 4.47E-09
PP_IN_TAB MA_FLG 2 648172 FREQUENCY 4.50E-09
PP_IN_TAB M_TXT 29460248 9118572 HEIGHT BALANCED 2.19E-04
PP_IN_TAB D_UNMTCH 1 111766716 FREQUENCY 3.66E-05
exec :b1:= '11';
exec :b2:= 200.11;
exec :b3:= 'USD';
exec :b4:= 'A';
exec :b5:= 'BB';
exec :b6:= null;
exec :b7:= 'SGP';
exec :b8:= null;
select
NVL(I.MA_FLG, 'N'),NVL(I.D_UNMTCH, 'N'),I.DC_CODE,count(*)
FROM PP_IN_TAB I
group by NVL(I.MA_FLG, 'N'),NVL(I.D_UNMTCH, 'N'),I.DC_CODE;
NVL(I.MA_FLG,'N') NVL(I.D_UNMTCH,'N') DC_CODE COUNT(*)
N N D 98348098
N N C 7510750
Y Y D 224
N Y D 9319
N N 78
N Y C 3977
Y N D 5931061
Y N C 69458
Y Y C 195
On Wed, Aug 25, 2021 at 7:09 PM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Hi Lok,
SUBSTR(:B8,0*.50*)Looks like this query should be analyzed and tested better.
You haven't provided histograms and bind values statistics, so not
enough info to analyze it properly.
For now it looks like "I.WOF_DATE IS NULL" is one of the most selective
predicates - it gives only 83154 nulls.
In addition to histogram statistics(dba_tab_histograms) and most often
binds values, I would like also to see what does return this query:
select
NVL(I.MA_FLG, 'N'),NVL(I.D_UNMTCH, 'N'),I.DC_CODE,count(*)
FROM PP_IN_TAB I
group by NVL(I.MA_FLG, 'N'),NVL(I.D_UNMTCH, 'N'),I.DC_CODE;
On Wed, Aug 25, 2021 at 4:14 PM Lok P <loknath.73@xxxxxxxxx> wrote:
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 DR_CR_CD
2 86
PP_IN_TAB PR_CTGRY
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')
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
