A different view to tune the database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: A different view to tune the database

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    Question

    To search the most cost SQL statement , follow SQL is used

    select sql_TEXT,
    EXECUTIONS,
    ROWS_PROCESSED,
    BUFFER_GETS/100,
    DISK_READS/100
    FROM V$sql V,
    (SELECT SUM(BUFFER_GETS) TOT_GETS FROM V$SQL) S
    WHERE BUFFER_GETS > TOT_GETS * 0.1
    ORDER BY BUFFER_GETS DESC

    It shows the SQL statement which consume more than 10% of total buffer gets since db startup .

    I have few question on the result ,

    A. EXECUTIONS means what , the result show one query runs nearly 1000 times ( it's application code not oracle inner SQL) , But actually it should not be run this much of times . So what's the meaning of executions , Is it time elapsed or the number of times executed ?

    B. Some SQL statement is truncated in the result . what's the problem ?



    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    1.Executions means no of times the same statement was executed.
    2.Your sql_text column is truncated because its has a max limit and if it execeds this limit the sql text would be truncated.

    regards
    anandkl
    anandkl

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    I could under at technical point of view , but actually I am sure this sql won't be execute so many times as only few users
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    It's not lying to you. It is telling you the amount of executions made against the object since it was bought into the library cache.

    How long has the database been up and how long has the object been in the library cache ?
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Around 4 hours and only 10 users access database . THat's why I feel very confused on the execution number ( It can not be 1000 times)

    Any idea
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    as for the truncated sql, you can get the full sql statement from v$sqltext_with_newlines, according to the hash_value.

    I think oracle will maybe show less executions than actual, it won't show you more.Less executions because maybe once it was ever aged out.

    1000 executions is not something very much. You have 10 users and running 4 hours.If there is a loop, it is possible that 1000 execuitons can be done in 1 minutes. Of course it totally depend on the application.
    www.cnoug.org

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