Re: Fixing Performance issue with less selective columns

Can you check in an active sql monitor report the details of the offloading? Are there any storage indexes used?

Maybe if you give more priority to flash cache to this table might improve the response time:
ALTER TABLE PP_IN_TAB STORAGE (CELL_FLASH_CACHE KEEP); Or maybe you can partition the table after: WOF_DATE and have a separate partition for null values, this will partition prune and will be faster than an index I think (and you avoid any negative impact of additional index)

În mie., 25 aug. 2021 la 16:15, Lok P <loknath.73_at_gmail.com> a scris:

> 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')
>

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