Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, manisha.

Asked: May 25, 2020 - 9:00 am UTC

Last updated: May 27, 2020 - 1:19 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,
i have updated the plan.i was unaware how to submit the further information on the questions asked, thats why put the questions again.

this is with reference to question asked at below link
https://asktom.oracle.com/pls/apex/asktom.search?tag=index-scan-on-timestamp-column

Attaching the explain of actual query i am using after changing the index order as you suggested.

WITH LABEL_COUNT AS ( 
    
        select     /*+ gather_plan_statistics  */     CLPT.CLIENT_ID AS CLIENT_ID , CLPT.GROWER_ID AS  CGRW_ID,CLPT.SHIPPER_LOT_ID,SUM(CLPT.LABEL_COUNT) AS VALUE
        FROM T_CASE_LABEL_PRINT_TRNS CLPT
        WHERE  CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1 
        AND CLPT.CREATED_ON >= TO_TIMESTAMP('01/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')  
        AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/29/2018 23:59:59', 'MM/DD/YYYY HH24:MI:SS') 
        GROUP BY  CLPT.CLIENT_ID ,CLPT.GROWER_ID,CLPT.SHIPPER_LOT_ID
        
         ) 
         
        SELECT  NVL(SLGRWR.ID, 0) AS entityId, NVL(SLGRWR.NAME, 'Unspecified') AS label , SUM(LC.VALUE)
        FROM LABEL_COUNT LC
        INNER JOIN T_SHIPPER_LOTS SL ON LC.SHIPPER_LOT_ID = SL.ID AND LC.CLIENT_ID = SL.CLIENT_ID
        LEFT JOIN T_GROWERS SLGRWR ON   (LC.CGRW_ID = SLGRWR.ID  OR SL.GROWER_ID = SLGRWR.ID ) AND LC.CLIENT_ID=SLGRWR.CLIENT_ID
        GROUP BY NVL(SLGRWR.ID, 0), NVL(SLGRWR.NAME, 'Unspecified')
        ORDER BY SUM(LC.VALUE) DESC
        FETCH FIRST 10 ROWS ONLY;
        
    select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


SQL_ID  5vw2uz51qv2w9, child number 1
-------------------------------------
WITH LABEL_COUNT AS (               select     /*+ 
gather_plan_statistics  */     CLPT.CLIENT_ID AS CLIENT_ID , 
CLPT.GROWER_ID AS  CGRW_ID,CLPT.SHIPPER_LOT_ID,SUM(CLPT.LABEL_COUNT) AS 
VALUE         FROM T_CASE_LABEL_PRINT_TRNS CLPT         WHERE  
CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1          AND CLPT.CREATED_ON 
>= TO_TIMESTAMP('01/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')         
  AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/29/2018 23:59:59', 
'MM/DD/YYYY HH24:MI:SS')          GROUP BY  CLPT.CLIENT_ID 
,CLPT.GROWER_ID,CLPT.SHIPPER_LOT_ID           )           SELECT  
NVL(SLGRWR.ID, 0) AS entityId, NVL(SLGRWR.NAME, 'Unspecified') AS label 
, SUM(LC.VALUE)         FROM LABEL_COUNT LC         INNER JOIN 
T_SHIPPER_LOTS SL ON LC.SHIPPER_LOT_ID = SL.ID AND LC.CLIENT_ID = 
SL.CLIENT_ID         LEFT JOIN T_GROWERS SLGRWR ON   (LC.CGRW_ID = 
SLGRWR.ID  OR SL.GROWER_ID = SLGRWR.ID ) AND 
LC.CLIENT_ID=SLGRWR.CLIENT_ID         GROUP BY NVL(SLGRWR.ID, 0), 
NVL(SLGRWR.NAME, 'Unspecified')         ORDER
 
Plan hash value: 3231218950
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |      1 |        |     10 |00:00:17.98 |     139K|    122K|       |       |          |
|   1 |  SORT ORDER BY                      |                         |      1 |     10 |     10 |00:00:17.98 |     139K|    122K|  2048 |  2048 | 2048  (0)|
|*  2 |   VIEW                              |                         |      1 |     10 |     10 |00:00:17.98 |     139K|    122K|       |       |          |
|*  3 |    WINDOW SORT PUSHED RANK          |                         |      1 |      3 |     10 |00:00:17.98 |     139K|    122K|  2048 |  2048 | 2048  (0)|
|   4 |     HASH GROUP BY                   |                         |      1 |      3 |     78 |00:00:17.98 |     139K|    122K|   898K|   898K| 1361K (0)|
|   5 |      NESTED LOOPS OUTER             |                         |      1 |  16538 |  16538 |00:00:17.54 |     139K|    122K|       |       |          |
|*  6 |       HASH JOIN                     |                         |      1 |  16538 |  16538 |00:00:17.31 |     125K|    122K|  2055K|  1934K| 1768K (0)|
|   7 |        VIEW                         |                         |      1 |  16539 |  16539 |00:00:17.06 |     122K|    122K|       |       |          |
|   8 |         HASH GROUP BY               |                         |      1 |  16539 |  16539 |00:00:17.04 |     122K|    122K|  2064K|  1501K| 1594K (0)|
|*  9 |          TABLE ACCESS FULL          | T_CASE_LABEL_PRINT_TRNS |      1 |    184K|    168K|00:00:19.50 |     122K|    122K|       |       |          |
|  10 |        INDEX FAST FULL SCAN         | INDX_SLOT_ID_CLN_GID    |      1 |    741K|    741K|00:00:00.42 |    2264 |      0 |       |       |          |
|  11 |       VIEW                          | VW_LAT_A8413AFA         |  16538 |      1 |   2384 |00:00:00.29 |   14306 |      0 |       |       |          |
|  12 |        CONCATENATION                |                         |  16538 |        |   2384 |00:00:00.25 |   14306 |      0 |       |       |          |
|* 13 |         FILTER                      |                         |  16538 |        |   2384 |00:00:00.06 |    7153 |      0 |       |       |          |
|* 14 |          TABLE ACCESS BY INDEX ROWID| T_GROWERS               |   2384 |      1 |   2384 |00:00:00.03 |    7153 |      0 |       |       |          |
|* 15 |           INDEX UNIQUE SCAN         | PK_GROWER               |   2384 |      1 |   2384 |00:00:00.01 |    4769 |      0 |       |       |          |
|* 16 |         TABLE ACCESS BY INDEX ROWID | T_GROWERS               |  16538 |      1 |      0 |00:00:00.11 |    7153 |      0 |       |       |          |
|* 17 |          INDEX UNIQUE SCAN          | PK_GROWER               |  16538 |      1 |   2384 |00:00:00.04 |    4769 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("from$_subquery$_007"."rowlimit_$$_rownumber"<=10)
   3 - filter(ROW_NUMBER() OVER ( ORDER BY SUM("LC"."VALUE") DESC )<=10)
   6 - access("LC"."SHIPPER_LOT_ID"="SL"."ID" AND "LC"."CLIENT_ID"="SL"."CLIENT_ID")
   9 - filter(("CLPT"."CREATED_ON">=TIMESTAMP' 2018-01-01 00:00:00.000000000' AND "CLPT"."CLIENT_ID"=3104 AND "CLPT"."CREATED_ON"<=TIMESTAMP' 
              2018-12-29 23:59:59.000000000' AND "CLPT"."STATUS"=1))
  13 - filter("SL"."GROWER_ID" IS NOT NULL)
  14 - filter("LC"."CLIENT_ID"="SLGRWR"."CLIENT_ID")
  15 - access("SL"."GROWER_ID"="SLGRWR"."ID")
  16 - filter(("LC"."CLIENT_ID"="SLGRWR"."CLIENT_ID" AND (LNNVL("SL"."GROWER_ID" IS NOT NULL) OR LNNVL("SL"."GROWER_ID"="SLGRWR"."ID"))))
  17 - access("LC"."CGRW_ID"="SLGRWR"."ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - 1 Sql Plan Directive used for this statement


total count in CLPT table with the date and status filter is :168031.

-- IF I USE SKIP SCAN ON INDEX: IDX_CSLBLPRNTTRX_CIDDIDCON(CLIENT_ID, DEVICE_ID, CREATED_ON) AS A HINTS IT WORKS FINE IN TERMS OF RESPONSE TIME BUT FOR FEW CLIENTS COST IS MUCH MORE. I AM NOT ABLE TO DECIDE WEATHER TO USE SKIP SCAN HINT OR NOT.

and Chris said...

To clarify, you created an index on:

(CLIENT_ID,STATUS,CREATED_ON)


right?

Pretty much all the time is going on full scanning T_CASE_LABEL_PRINT_TRNS. The row estimates for this are accurate enough (expecting 184K rows and getting 168K).

So the optimizer thinks that the full table scan is the fastest way to do this. If the index is in fact faster, you want to help the optimizer discover this.

You may be able to make the index on (CLIENT_ID,STATUS,CREATED_ON) more attractive to the optimizer by:

- Compressing the leading columns (alter index ... rebuild compress 2)
- Setting TABLE_CACHED_BLOCKS parameter to a value ~16 and regathering stats:

exec dbms_stats.set_table_prefs(user, 'T_CASE_LABEL_PRINT_TRNS', pname=>'TABLE_CACHED_BLOCKS', pvalue=>16);

This will help the optimizer spot if the index is "mostly clustered".

I AM NOT ABLE TO DECIDE WEATHER TO USE SKIP SCAN HINT OR NOT.

You should avoid hints as much as possible. Particularly if it's fast for some values and slow for others.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.