Forum Stats

  • 3,814,364 Users
  • 2,258,865 Discussions
  • 7,892,687 Comments

Discussions

Oracle MFT: slow query: request_history table

Here is the query that is performed by the MFT server (12.2.1.3.0(170820.1700.2557)) in whatever processing it is doing in the middle of the night from about midnight until 230am...

SELECT count(*) FROM request_history rh WHERE rh.parentrequestid = :1

AND rh.requesttype IN (3,7) AND rh.processPhase NOT IN (150,500)

Our DBA team is asking what we need to do with this.  I checked and the table has data going back into 2018. It has over 9 million rows of data and Toad reports that the table size is 17.7GB so it is huge. It appears to contain all of the rows of schedules performed by MFT since we first implemented it. So I suspect that it does not have a purge schedule or any other thing configured to keep it running efficiently.  The query seems fairly efficient.  The parentrequestid and requesttype columsn are indexed and the processPhase contains a total of 3 values across the 9 million rows.

I tried consulting oracle MFT documentation for the request_history table, but came up short looking on internet and oracle resources.  The DBAs reported this problem to me because the size of the table caused the above query to chew up excessive resources. I do not know what, but suspect that it is because the table is just too full.

That being said, if true, is setting up a daily job to reduce the data to just a rolling last 60 days seem reasonable?

Richard Burk

Answers