CODEWITHSUNDEEP
sqloracle-database
kr.pradeep
kr.pradeep

Reputation: 115

Oracle query to check for failure more than 90 %

I have a situation where I need to write a monitoring query to run every 2 hour to raise alert when processed count becomes less than 90%.

Lets say we have a Table Incoming Message where all incoming messages are captured and another table where all processed messages are captured.

This is what I came up with, this works but I am wondering if there is better way of doing this?

SELECT (CASE WHEN PROCESSEDCOUNT <= INCOMINGCOUNT * .9 
THEN 'ALERT:: Process Count ' || PROCESSEDCOUNT || ' is less than 90% of Incoming count ' || INCOMINGCOUNT || '. '
ELSE 'FINE:: Process Count ' || PROCESSEDCOUNT || ' is more than or equal to 90% of Incoming count ' || INCOMINGCOUNT || '. '
END ) as Status
from 
(SELECT 
(SELECT COUNT(*) 
FROM INCOMING_TABLE D WHERE INSERTION_TIME > SYSDATE - (1/12) 
AND EXISTS (SELECT * FROM PROCESSED_TABLE C WHERE ( D.MESSAGE_ID = C.MESSAGE_ID)
AND C.PROCESSED_TIME > SYSDATE- (1/12))) AS PROCESSEDCOUNT,
(SELECT COUNT(*) FROM INCOMING_TABLE WHERE INSERTION_TIME > SYSDATE - (1/12)) AS INCOMINGCOUNT
FROM DUAL);

Upvotes: 0

Views: 120

Answers (1)

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

You are querying the same data from INCOMING_TABLE twice, which isn't really efficient ;-)

One possibility could be to outer join:

SELECT
   CASE
      WHEN COUNT(C.MESSAGE_ID) <= COUNT(*) * .9 
      THEN 'ALERT:: Process Count ' || COUNT(C.MESSAGE_ID) || ' is less than 90% of Incoming count ' || COUNT(*) || '. '
      ELSE 'FINE:: Process Count ' || COUNT(C.MESSAGE_ID) || ' is more than or equal to 90% of Incoming count ' || COUNT(*) || '. '
   END as Status
FROM INCOMING_TABLE D
LEFT OUTER JOIN PROCESSED_TABLE C
      ON C.MESSAGE_ID = D.MESSAGE_ID
     AND C.PROCESSED_TIME > SYSDATE- (1/12)
WHERE D.INSERTION_TIME > SYSDATE - (1/12)
/

That will work if you can be sure either zero or one record exists in PROCESSED_TABLE for each message_id. Maybe you can add a AND C.PROCESS_TYPE = ... or something to make that condition come true.

If you cannot guarantee that a join to PROCESSED_TABLE returns at most one row, you can move your EXISTS to inside a COUNT instead of the WHERE clause and thereby again avoid accessing INCOMING_TABLE twice:

SELECT (CASE WHEN PROCESSEDCOUNT <= INCOMINGCOUNT * .9 
THEN 'ALERT:: Process Count ' || PROCESSEDCOUNT || ' is less than 90% of Incoming count ' || INCOMINGCOUNT || '. '
ELSE 'FINE:: Process Count ' || PROCESSEDCOUNT || ' is more than or equal to 90% of Incoming count ' || INCOMINGCOUNT || '. '
END ) as Status
from 
(
SELECT COUNT(*) INCOMINGCOUNT
     , COUNT(
          CASE 
             WHEN EXISTS (SELECT * FROM PROCESSED_TABLE C
                          WHERE D.MESSAGE_ID = C.MESSAGE_ID
                          AND C.PROCESSED_TIME > SYSDATE- (1/12))
             THEN 1
          END
       ) PROCESSEDCOUNT
FROM INCOMING_TABLE D
WHERE D.INSERTION_TIME > SYSDATE - (1/12)
)
/

(PS. If you are at the start of writing a lot of code to handle a messaging queue, I would also suggest like @DARK_A to look into Advanced Queues instead of building your own. There is a lot of issues you need to handle in a messaging system, so why have that trouble if you can use what Oracle has already built ;-)

Upvotes: 1

Related Questions