I saw in the past EXA systems with storage index disabled, so just wanted
to check this for you as well. KEEP will not help you at all basically.
Is up to you to check how many queries are using this table and in what
way. You can choose monthly range if you think that you`ll have too many
partitions.
My personal opinion is with EXA systems you can benefit from the smart
capabilities and you don`t have to create indexes as full scans like this
one you showed us are super fast. If you partition , the query will have to
scan a single partition of 83154 rows. You can basically compare that now
in 3 seconds you scan through 111million rows and estimate how fast the
query will be. There is a high probability other queries might benefit as
well as date range queries are something often seen , your inserts are not
impacted. and you don't consume extra storage with another
additional structure.
Good luck.
În joi, 26 aug. 2021 la 15:54, Lok P <loknath.73@xxxxxxxxx> a scris:
Yes Laurentiu, As i posted just now it does show ~99.99% cell offload in
the sql monitoring report. But I am not able to interpret other v$sessstat
stats if they are pointing to something like putting this base table fully
in flash cache i.e. making cell_flash_cache as KEEP will help us or not?
Partitioning on wof_date is something we need to look into the usage in
other queries, as we will also have to go for making indexes local for
smooth maintenance and in that case , the all index segment will also be
divided into multiple pieces. So not sure how they will impact other
queries where we scan across multiple wof_date data.
On Thu, Aug 26, 2021 at 1:13 PM Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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')
