|
-
 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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote