Forum Stats

  • 3,770,159 Users
  • 2,253,079 Discussions
  • 7,875,349 Comments

Discussions

query tuning from secs to ms

Kedar Barde
Kedar Barde Member Posts: 9 Red Ribbon
edited May 23, 2021 9:48AM in SQL & PL/SQL

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

Tagged:

Answers