RE: Fixing Performance issue with less selective columns
Date: Wed, 25 Aug 2021 13:19:53 -0400
Message-ID: <080c01d799d5$6b9b1b90$42d152b0$_at_rsiz.com>
On Wed, Aug 25, 2021 at 8:00 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
you make a functional index on i.ma_flg that does the equivalent.
I can’t remember off the top of my head whether either way gives you a real advantage over the other in stats collections and the CBO doing something smart and that probably changed over the releases. That might be in one of my papers.
When you then index that column the nulls disappear, leaving you with a very tiny index to prune your result set immediately to very small and you can usually filter the rest fast without an index.
Remember, ORACLE cannot assign a value to NULL in anything they do. But YOU can.
When this is appropriate, it is one of the neatest and easiest “magic tricks” in the Oracle kit.
Good luck,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov
Sent: Wednesday, August 25, 2021 9:40 AM
To: Lok P
Cc: Oracle L
Subject: Re: Fixing Performance issue with less selective columns
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 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')
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 25 2021 - 19:19:53 CEST