Thanks for the question, Nikoloz.

Asked: July 03, 2024 - 7:05 am UTC

Last updated: July 24, 2024 - 2:50 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0

Viewed 1000+ times

You Asked

hello ,
i have a table TDRCORE.FCM_CCBF ,
PARTITION BY RANGE (RQO_PROC_UTC_DATETIME TIMESTAMP(6))
INTERVAL( NUMTODSINTERVAL(7, 'DAY'))

tehre is an SQL running on LIVE db that comtains many unions but i will not post full script , i will post just 1 part of the union (all the other selects in this union are exatly the same , just different tables)

/* Formatted on 03-Jul-24 10:36:19 (QP5 v5.294) */

ALTER SESSION SET CURRENT_SCHEMA=TDRCORE;


EXPLAIN PLAN
   FOR
      WITH currTime
           AS (SELECT TO_TIMESTAMP (
                         TO_CHAR (SYS_EXTRACT_UTC (CURRENT_TIMESTAMP),
                                  'YYYY-MM-DD HH24:MI:SS'),
                         'YYYY-MM-DD HH24:MI:SS')
                         AS endts
                 FROM DUAL)
      (SELECT 'FCM_CCBF'                        AS DBTABLE,
              t26.CMX_TRAN_ID                   AS CMX_TRAN_ID,
              t26.CMX_CREATE_TIMESTAMP,
              currTime.endts,
              CASE
                 WHEN    ( (   COALESCE (t26.WAX_USER_FRAUD_A_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_B_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_C_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_D_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_H_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_M_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_O_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_P_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_R_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_T_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_W_IND, '0')
                            || COALESCE (t26.WAX_USER_FRAUD_X_IND, '0'))) > 0
                      OR (   t26.WAX_SCORE_1_TAG IN ('1', '3')
                          OR t26.WAX_SCORE_2_TAG IN ('1', '3')
                          OR t26.WAX_SCORE_3_TAG IN ('1', '3')
                          OR t26.WAX_SCORE_4_TAG IN ('1', '3'))
                 THEN
                    1
                 ELSE
                    0
              END
                 AS FRAUD_IND,
              TRIM (t26.RRR_MODEL_ID_1)         AS "RRR_MODEL_ID_1",
              TRIM (t26.RRR_MODEL_ID_2)         AS "RRR_MODEL_ID_2",
              TRIM (t26.RRR_MODEL_ID_3)         AS "RRR_MODEL_ID_3",
              TRIM (t26.RRR_MODEL_ID_4)         AS "RRR_MODEL_ID_4",
              TRIM (t26.RRR_MODEL_SCORE_1)      AS "RRR_MODEL_SCORE_1",
              TRIM (t26.RRR_MODEL_SCORE_2)      AS "RRR_MODEL_SCORE_2",
              TRIM (t26.RRR_MODEL_SCORE_3)      AS "RRR_MODEL_SCORE_3",
              TRIM (t26.RRR_MODEL_SCORE_4)      AS "RRR_MODEL_SCORE_4",
              TRIM (t26.RRR_MODEL_VERSION_1)    AS "RRR_MODEL_VERSION_1",
              TRIM (t26.RRR_MODEL_VERSION_2)    AS "RRR_MODEL_VERSION_2",
              TRIM (t26.RRR_MODEL_VERSION_3)    AS "RRR_MODEL_VERSION_3",
              TRIM (t26.RRR_MODEL_VERSION_4)    AS "RRR_MODEL_VERSION_4",
              TRIM (t26.RRR_SCORE_1)            AS "RRR_SCORE_1",
              TRIM (t26.RRR_SCORE_2)            AS "RRR_SCORE_2",
              TRIM (t26.RRR_SCORE_3)            AS "RRR_SCORE_3",
              TRIM (t26.RRR_SCORE_4)            AS "RRR_SCORE_4",
              TRIM (t26.SMH_ACCT_TYPE)          AS "SMH_ACCT_TYPE",
              TRIM (t26.SMH_ACTIVITY_TYPE)      AS "SMH_ACTIVITY_TYPE",
              TRIM (t26.SMH_MULTI_ORG_NODE_KEY) AS "SMH_MULTI_ORG_NODE_KEY",
                 t26.CMX_TRAN_ID
              || '|'
              || TO_CHAR (t26.RQO_PROC_UTC_DATETIME,
                          'YYYY-MM-DD HH24:MI:SS.FF')
                 AS "_id"
         FROM FCM_CCBF t26, currTime
        WHERE     t26.CMX_CREATE_TIMESTAMP >
                     TO_TIMESTAMP ('2024-03-22 11:41:53.992000',
                                   'YYYY-MM-DD HH24:MI:SS.FF')
              AND t26.CMX_CREATE_TIMESTAMP <= currTime.endts
              AND t26.RQO_PROC_UTC_DATETIME BETWEEN   TRUNC (
                                                         TO_TIMESTAMP (
                                                            '2024-03-22 11:41:53.992000',
                                                            'YYYY-MM-DD HH24:MI:SS.FF'))
                                                    - 10
                                                AND currTime.endts);

SET LINESIZE 130
SET PAGESIZE 0

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);


the possible issue is TABLE ACCESS FULL part : 

-------------------------------------------------------------------------------------------------------                           
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                           
-------------------------------------------------------------------------------------------------------                           
|   0 | SELECT STATEMENT           |          |   131M|    21G|    26M  (1)| 00:00:54 |       |       |                           
|*  1 |  FILTER                    |          |       |       |            |          |       |       |                           
|   2 |   NESTED LOOPS             |          |   131M|    21G|    26M  (1)| 00:00:54 |       |       |                           
|   3 |    FAST DUAL               |          |     1 |       |     2   (0)| 00:00:01 |       |       |                           
|   4 |    PARTITION RANGE ITERATOR|          |   131M|    21G|    26M  (1)| 00:00:54 |  2829 |   KEY |                           
|*  5 |     TABLE ACCESS FULL      | FCM_CCBF |   131M|    21G|    26M  (1)| 00:00:54 |  2829 |   KEY |                           
-------------------------------------------------------------------------------------------------------         

there is an partitioned index on CMX_CREATE_TIMESTAMP

CREATE INDEX TDRCORE.NDX5_CCBF ON TDRCORE.FCM_CCBF
(CMX_CREATE_TIMESTAMP)
  TABLESPACE TDR_INDEX
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
  STORAGE    (
              BUFFER_POOL      DEFAULT
             )
LOCAL

there are no GLOBAL indexes on the table .
all the statistics are fresh .

is it possible that issue is with convertion , if not i am at loss , how can i eliminate TABLE ACCESS FULL ?
thank you

and Connor said...

Assuming your stats are up to date, I think its very wise that the database decided *not* to use your index.

We are getting 130million rows from that table - 130million index probes could be massively expensive.

The "PARTITION RANGE ITERATOR" says it used to the timestamp predicate to make sure we started at partition 2829, so we bypassed the first 2828 partitions.

Looks to me like the query is bring run exactly as it should be

Rating

(2 ratings)

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

Comments

some additional Info

Rajeshwaran, Jeyabal, July 05, 2024 - 5:51 pm UTC

given your predicates like this

FROM FCM_CCBF t26, currTime
WHERE     t26.CMX_CREATE_TIMESTAMP >
  TO_TIMESTAMP ('2024-03-22 11:41:53.992000',
       'YYYY-MM-DD HH24:MI:SS.FF')
AND t26.CMX_CREATE_TIMESTAMP <= currTime.endts
AND t26.RQO_PROC_UTC_DATETIME BETWEEN   TRUNC ( TO_TIMESTAMP ('2024-03-22 11:41:53.992000','YYYY-MM-DD HH24:MI:SS.FF'))- 10 
 AND currTime.endts );

have it modified like this

FROM FCM_CCBF t26
WHERE     t26.CMX_CREATE_TIMESTAMP >
  TO_TIMESTAMP ('2024-03-22 11:41:53.992000',
       'YYYY-MM-DD HH24:MI:SS.FF')
AND t26.CMX_CREATE_TIMESTAMP <= SYS_EXTRACT_UTC( current_timestamp )
AND t26.RQO_PROC_UTC_DATETIME BETWEEN   TRUNC ( TO_TIMESTAMP ('2024-03-22 11:41:53.992000','YYYY-MM-DD HH24:MI:SS.FF'))- 10 
 AND SYS_EXTRACT_UTC( current_timestamp ) );

eliminate "currTime" all together, that will get rid of the NESTED LOOP in the above plan.

also given the size as 131M, possible to have some set of parallelism added there ? and when you do that there are chances that each query block can be executed concurrently

more details on that @ https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-1F4C90F9-3EF5-423A-B55B-2593FB3F1433

Please check if that helps.

A reader, July 23, 2024 - 9:04 am UTC

OK , understood , thank you very much , makes sense .
one more question , how to determine exact partition from "Pstart value : 2829" , there are no partitions with such number in my tables , so i guess it uses some sort of id , but i cant seem to find it .

as for the statistics i have separate problem with that and i will open a new question for that .

thank you

July 24, 2024 - 2:50 pm UTC

This comes from the partition_position column in the *_tab_partitions view. It's an internal number to order the partitions.

For interval partitions, the database calculates Pstart/Pstop numbers based on the partitions that could be created, even if they're not yet.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

some additional Info

More to Explore

VLDB

VLDB