Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nikoloz.

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

Last updated: July 04, 2024 - 3:40 am 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

  (1 rating)

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.

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.