DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: analyzing the tables corrects the problem.

  1. #11
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    May I open a second front?

    SqlID=3dg8cdtp3b47f called my attention for two reasons.

    1- Executed 2808 times, parsed 2808 times - even when using bind variables

    Is this a Java application? if this is the case code may be issuing too many PrepareStatement/Close which causes Oracle to (soft) parse it as many times as it is executed.

    2- statement hits table MAM_ASSET_ATTR_DOMAIN_VALUES which is your buffer_gets overall champion.

    I would check indexing strategy and be sure query reads what's needed and nothing more.
    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.

  2. #12
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    My Answers inline.
    Quote Originally Posted by mahajanakhil198 View Post
    1. This 3 hour duration of AWR is non peak hour duration. The query itself takes more than 30 minutes to execute. For 45 minute duration, I will have to take remote connectivity of client again. I will check if it is possible.

    OK.
    2. No, I took AWR report after the query finished execution in SQLPLUS.

    May be you are not aware and some other SQL executing. YOu may check V$SQL to get those SQLs.

    3. The query I have mentioned in attached file 'problematic_query.txt' already refers to tables MAM_ASSET_ATTR_STRING_VALUES and MAM_ASSET_ATTR_DOMAIN_VALUES.

    Can you look for the exact table name. They are not there.

    4. Yes, I will see if it is possible to increase SGA_TARGET.

    5. CPU cores, CPU usage and SAN related is unknow as of now. I will try to capture it. But it won't be of much help as same query was running good few days back and it has been working fine for last 5 years. I know that the data growth patterns have to be taken into account but still it works fine after restart.

    May be it's time for histograms then.

    6. I don't know much about latches. Did you find anything about latches from AWR that may alleviate the problem?
    No there are no latch issue. All IO reated issue, that's why i asked for CPU usage info?
    http://www.perf-engg.com
    A performance engineering forum

  3. #13
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    May I open a second front?

    SqlID=3dg8cdtp3b47f called my attention for two reasons.

    1- Executed 2808 times, parsed 2808 times - even when using bind variables

    Is this a Java application? if this is the case code may be issuing too many PrepareStatement/Close which causes Oracle to (soft) parse it as many times as it is executed.

    2- statement hits table MAM_ASSET_ATTR_DOMAIN_VALUES which is your buffer_gets overall champion.

    I would check indexing strategy and be sure query reads what's needed and nothing more.
    1. Yes, it is a large java application.
    2. I cannot do anything with SQL tuning now except rebuilding the indexes. The development on this application stopped 3 years back.

    I exactly know the problematic query i.e. SQL_ID is 3dg8cdtp3b47f. As already stated, it took more than 34 minutes to execute on SQLPLUS. This SQL was obtained after analyzing the application logs for the time when the application session failed and logged off.
    lucky

  4. #14
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by malay_biswal View Post
    My Answers inline.
    Two things I want to know regarding I/O:

    1. What should be the I/O speed of the system? From where do we find this out?

    2. What is the current speed of I/O when I manually execute the problematic query through SQLPLUS?

    It is Solaris box hosting Oracle and Application.
    lucky

  5. #15
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    I do not know the answer.
    It depends on what hardware architecture you are using.
    http://www.perf-engg.com
    A performance engineering forum

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