Hello everyone,
I'm encountering a timeout exception within 5 minutes on a particular page.
This page allows users to select criteria and retrieve results from table “MSG_EXC_REC”.
There is a specific functionality using two limits as displayed below :
If the results fall below limit1, I display them on the page.
If the results fall between limit1 and limit2, I download them to Excel.
If the results exceed limit2, I notify the user that the results exceed a certain number and request them to redefine the criteria.
So, I need to run an initial query with a limit of 9000 to retrieve the results.
SELECT /*+ FIRST_ROWS(9001) */ REQ.CCN_UNIQUE_ID,REQ.MRN,REQ.SENDER,REQ.RECIPIENT,REQ.MSG_TYPE
FROM MSG_EXC_REC REQ
WHERE REQ.DOMAIN = 'NCTS'
AND REQ.M_MIN_DATE <= to_timestamp('2015-02-01 00:00:58', 'yyyy-mm-dd hh24:mi:ss')
AND REQ.M_MIN_DATE >= to_timestamp('2015-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND REQ.IS_IECA = 0
AND ROWNUM <= 9001
ORDER BY M_MIN_DATE DESC ;
If the query returns results equal to limit2, it indicates that more data exists. In this case, I then run a second query to check how many additional results there are. Because I am using sample(20) then I multiply with the 5 (=100/20) to get the results.
SELECT COUNT(REQ.CCN_UNIQUE_ID)
FROM MSG_EXC_REC sample(20) seed(1000) REQ
WHERE REQ.DOMAIN = 'NCTS'
AND REQ.M_MIN_DATE <= to_timestamp('2015-02-01 00:00:58', 'yyyy-mm-dd hh24:mi:ss')
AND REQ.M_MIN_DATE >= to_timestamp('2015-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND REQ.IS_IECA = 0
AND ROWNUM <= 9001 ;
The table "MSG_EXC_REC" is partitioned by range on the "M_MIN_DATE" column with a monthly interval (NUMTOYMINTERVAL(1, 'MONTH')) and the below columns :
Moreover, I have the below indexes
CREATE INDEX "IDX_HAS_COA_COD" ON "MSG_EXC_REC" ("HAS_COA_COD");
CREATE INDEX "IDX_HAS_FAILED_SEND" ON "MSG_EXC_REC" ("HAS_FAILED_SENDS");
CREATE INDEX "IDX_MSG_EXC_ISIECA" ON "MSG_EXC_REC" ("IS_IECA") ;
CREATE INDEX "IDX_MSG_EXC_ISIECA_SND_DATE" ON "MSG_EXC_REC" ("IS_IECA", "MSG_SEND_DATE") ;
CREATE INDEX "IDX_MSG_EXC_MMINDATE_SEN_REC" ON "MSG_EXC_REC" ("M_MIN_DATE", "SENDER", "RECIPIENT") ;
CREATE INDEX "IDX_MSG_EXC_REC_CORREL_ID" ON "MSG_EXC_REC" ("CORREL_ID") ;
CREATE INDEX "IDX_MSG_EXC_REC_CRN" ON "MSG_EXC_REC" ("CRN") ;
CREATE INDEX "IDX_MSG_EXC_REC_HAS_COA_COD_SND_DATE" ON "MSG_EXC_REC" ("HAS_COA_COD", "MSG_SEND_DATE") ;
CREATE INDEX "IDX_MSG_EXC_REC_HAS_FAILED_SENDS_SND_DATE" ON "MSG_EXC_REC" ("HAS_FAILED_SENDS", "MSG_SEND_DATE") ;
CREATE INDEX "IDX_MSG_EXC_REC_MIN_DATE" ON "MSG_EXC_REC" ("M_MIN_DATE") ;
CREATE INDEX "IDX_MSG_EXC_REC_MRN" ON "MSG_EXC_REC" ("MRN") ;
CREATE INDEX "IDX_MSG_EXC_REC_MRN_DOMAIN_ENV" ON "MSG_EXC_REC" ("MRN", "DOMAIN", "ENV") ;
CREATE INDEX "IDX_MSG_EXC_REC_MSG_ID" ON "MSG_EXC_REC" ("MSG_ID") ;
CREATE INDEX "IDX_MSG_EXC_REC_MSG_TYPE" ON "MSG_EXC_REC" ("MSG_TYPE") ;
CREATE INDEX "IDX_MSG_EXC_REC_MSG_TYPE_MIN_DATE" ON "MSG_EXC_REC" ("MSG_TYPE", "M_MIN_DATE") ;
CREATE INDEX "IDX_MSG_EXC_REC_MSG_TYPE_SEND_DATE" ON "MSG_EXC_REC" ("MSG_TYPE", "MSG_SEND_DATE") ;
CREATE INDEX "IDX_MSG_EXC_REC_SIZE" ON "MSG_EXC_REC" ("MSG_SIZE") ;
CREATE INDEX "IDX_MSG_EXC_REC_SNDDT" ON "MSG_EXC_REC" ("RECIPIENT", "MSG_SEND_DATE") ;
CREATE INDEX "IDX_MSG_EXC_REC_SND_DATE" ON "MSG_EXC_REC" ("MSG_SEND_DATE") ;
CREATE INDEX "IDX_MSG_EXC_SEN_SNDDT" ON "MSG_EXC_REC" ("SENDER", "MSG_SEND_DATE") ;
and it's usage (DBA_INDEX_USAGE) on PROD :
So, I understand that I can drop the indexes having TOTAL_ACCESS_COUNT from 0 - 91.
Question 1
As I have a performance issue in the page and gaining size deleting the above which is the index you suggest me to create ?
I have a page which user selects criteria with most common cases to be :
- DOMAIN, M_MIN_DATE
- DOMAIN, M_MIN_DATE, SENDER, RECIPIENT
- DOMAIN, M_MIN_DATE, SENDER
- DOMAIN, M_MIN_DATE, SENDER, MSG_TYPE
- DOMAIN, M_MIN_DATE, RECIPIENT
- DOMAIN, M_MIN_DATE, RECIPIENT, MSG_TYPE
Question 2
The main problem I think that is caused due to the second query, is there any way to improve it ?
For example a materialized view having pre-calculated some metrics (but the problem I have is that the m_min_date is timestamp if I do it until minutes not second )
Moreover, can I use somehow the first query which is already executed and make an offset 9000 on the second ?
Any ideas appreciate it.
Thanks in advance