query tuning from secs to ms

Kedar Barde

Table Info.

Count of records: 42111471

DES_EVENT. The table has PK on ID column and composite index on (CREATEDTIME, ENTITYNAME )

Name             Null?  Type        

------------------ -------- -------------

ID               NOT NULL VARCHAR2(255)

DATAEVENTID               VARCHAR2(255)

DATAEVENTINDEX            NUMBER(38)  

CREATEDTIME               TIMESTAMP(6)

STAGINGTIME               TIMESTAMP(6)

EVENTTYPE                 VARCHAR2(255)

ENTITYNAME                VARCHAR2(40)

The following query is taking 25s to execute. The query is run for a 1hr time frame where we populate 1000 records and get the latest id and then again populate the next 1000 for that time frame. the time frame to execute it very high should be running ms. we want to first tune the query before we can make it more generic

select * from (

          With sub1 as (

                      SELECT ID, ENTITYID, ENTITYNAME, CREATEDTIME              FROM DES_EVENT                      WHERE

                      CREATEDTIME between to_timestamp('2021-04-04 22:00:00', 'yyyy-mm-dd hh24:mi:ss') AND to_timestamp('2021-04-04 23:00:00', 'yyyy-mm-dd hh24:mi:ss')

                      AND ENTITYNAME = 'ACCOUNT'

                      )

          SELECT a.ID, a.ENTITYID, a.ENTITYNAME, a.CREATEDTIME

                  FROM sub1 A

                  WHERE

                  A.ID > '00000158-d603-4174-b7f5-da30475e72f0_1'

                  ORDER BY ID                  FETCH NEXT 1000 ROWS ONLY          )    ;

Can provide more information.

Thank you

Kedar Barde

Just to update, the order by id is causing slowness of query to execute

Thank you

Kedar Barde

Issue resolved used the hint /*+ FIRST_ROWS(1000) */