Help needed to understand the message and tune the query which is causing high cpu/io
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    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')

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by aj_usa View Post
    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
  •  



Click Here to Expand Forum to Full Width