Re: Fixing Performance issue with less selective columns

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;

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_at_gmail.com> 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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l