Help needed to understand the message and tune the query which is causing high cpu/io

I see this message in the OEM sql advisor recommendation :- The optimizer could not merge the view at line ID 2 of the execution plan

the sql which is causing high cpu/io is, how to go about finding what is the issue and fine tune this?

SELECT *
FROM
(SELECT /*+FIRST_ROWS(10)*/ *
FROM VERIWISE.TB_EXT1_EVENT A
WHERE A.IS_PROCESSED = 'N' AND A.DEVICE_ID = :B1 ORDER BY 1)
WHERE ROWNUM <= GET_PARAMETER('PKG_GSMD200_MESSAGE_DISPATCHER', 'EVENTS_LIMIT')

Quote:

---

Originally Posted by aj_usa

I see this message in the OEM sql advisor recommendation :- The optimizer could not merge the view at line ID 2 of the execution plan

the sql which is causing high cpu/io is, how to go about finding what is the issue and fine tune this?

SELECT *
FROM
(SELECT /*+FIRST_ROWS(10)*/ *
FROM VERIWISE.TB_EXT1_EVENT A
WHERE A.IS_PROCESSED = 'N' AND A.DEVICE_ID = :B1 ORDER BY 1)
WHERE ROWNUM <= GET_PARAMETER('PKG_GSMD200_MESSAGE_DISPATCHER', 'EVENTS_LIMIT')

---

Message is clear, query has an inline view a.k.a. subquery on it and optimizer couldn't handle it.

What is hitting you system is the inner query therefore I would start by checking inner query execution plan. Just to be sure I would also trace whole query - including wrap-up query.

How big is TB_EXT1_EVENT table? how many rows on it?
Is there an index on DEVICE_ID?
How many rows are expected to be returned for the specific value you are testing for :B1?