Performance Related Issues
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Performance Related Issues

  1. #1
    Join Date
    Aug 2000
    Posts
    87

    Performance Related Issues

    Hi All,

    This is regarding performance related issue.

    1)In the process of tunning sql i am just gathering the SQL which is bad(disk_reads>50 * executions).I got some sql statements.
    I was quite surprised with a SQL statement when i was checking for the query execution plan,I could see it uses proper index.Can any one give me a hint how efficent can we tunning more to reduce the disk_reads and the large number of buffer gets.

    2)When i was checking the modules & sid which are consuming more CPU using

    select rownum as rank, a.*
    from (
    SELECT v.sid,sess.status, module,program, v.value / (100 * 60) CPUMins
    FROM v$statname s , v$sesstat v, v$session sess
    WHERE s.name = 'CPU used by this session'
    and sess.sid = v.sid
    and v.statistic#=s.statistic#
    and v.value>0
    ORDER BY v.value DESC) a
    where rownum < 1

    I got top 10 session consuming more CPU resources.I got some of the SQLs which are run by the SID & did explain plan,for me it seems ok.But why does it consumes more CPU.
    For eg 10 to 15 mins of CPU which is not acceptable.

    Regards,
    M.G.Vinod Kumar

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: Performance Related Issues

    Originally posted by vinodkmg
    Hi All,

    This is regarding performance related issue.

    1)In the process of tunning sql i am just gathering the SQL which is bad(disk_reads>50 * executions).I got some sql statements.
    I was quite surprised with a SQL statement when i was checking for the query execution plan,I could see it uses proper index.Can any one give me a hint how efficent can we tunning more to reduce the disk_reads and the large number of buffer gets.

    You need to look at your database cache hit ratio, if it is < 95% then increase it. Moreover, you may need to use multiple buffer pools and/or cache frequently used tables (alter table ... cache)

    HTH.

  3. #3
    Join Date
    Aug 2000
    Posts
    87
    Hi,
    Thanx for your reply,The hit ratio is very well >=95%.I dont see any problems in that...Please check my query once again.
    Regards,
    Vinod

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Your Physical reads may not be the problem, hence a good Buffer Hit Ratio. I'd recommend monitoring queries in your v$sql looking closely at the buffer_gets column. Then pin-point poorly preforming SQL and re-writing it. Physical reads are not neccessarily as slow as Logical reads (i.e. from cache). Therefore increasing your buffer cache potentially will not make an ounce of difference.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Jan 2003
    Location
    Pune,India
    Posts
    4
    Hi,

    To tune the query , try using the hint "FIRST_ROWS".
    By doing so, oracle uses a CBO with best response time.
    I am sure , your count of buffer gets would improve tremendously.

    Thanks,
    Sumit

  6. #6
    Join Date
    Jan 2003
    Location
    Tegucigalpa, Honduras
    Posts
    4
    As stated by sumit, you may obtain a great improvement using the CBO, but first assure you have your init.ora properly configured and your tables and indexes analyzed, or else you may get a negative impact.

    Regards,
    ltabora@bancopromerica.com

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