Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    23

    Unanswered: How to use CPU_TIME in v$SQL

    Hi,

    Is there any formula to compute cpu_time of a sql statment in v$sql to the cpu% shown in "ps -ef" on unix?

    Thanks in advance.

    RDBMS Version: 9.2.0.4
    Operating System and Version: Sun Solaris 8

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    No, they are two different views of CPU

    V$SQL shows CPU used by a specific SQL - all users of that SQL - since it was loaded (could be since database was started, or could be very recently reloaded)

    ps on a unix process shows all CPU for everything that process did - would include all SQL that session has run, sorting, overhead, etc...

  3. #3
    Join Date
    May 2004
    Posts
    23
    Hi,

    Is there a way to translate cpu_time in v$sql to % (do not have to equate those in ps -ef, meaning, a process may be taking 5% cpu as shown in ps -ef but the sql its running is maybe taking 3%)? Currently im only using cpu_time/executions and sort them to get sql with high cpu.

    Thanks for your help.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The BEST way to accurately measure CPU time for a given SQL statement is to enable SQL_TRACE (level 12 is best) & run the results thru TKPROF.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2004
    Posts
    23
    Hi anacedent,

    Have run before on test environment, we are refraining from running in Prod.
    Besides, can we translate the cpu_time in the tkprof log to %, similar to ps -ef?

    Thanks.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > can we translate the cpu_time in the tkprof log to %, similar to ps -ef?
    I give up. Can you?
    Have you ever actually seen the output from TKPROF?
    What is your obession with CPU%?
    The use of ratios for tuning metrics is sub-sub-sub-optimal.
    If statement A, runs at 90% CPU & statment B runs at 50% CPU; which is better tuned and why?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2004
    Posts
    23
    Quote Originally Posted by anacedent
    > can we translate the cpu_time in the tkprof log to %, similar to ps -ef?
    I give up. Can you?
    If i can, i wouldnt be asking here.

    Quote Originally Posted by anacedent
    Have you ever actually seen the output from TKPROF?
    Something like this?


    call count cpu elapsed disk query current rows
    ------ ------ ------ ---------- ------ -------- --------- ----------
    Parse 1 0.00 0.03 1 1 0 0
    Execute 2 0.00 0.00 0 0 1 0
    Fetch 5680 0.60 0.74 1095 6708 0 85186
    ------- ------ ------ ---------- ------- -------- ------- ----------
    total 5683 0.60 0.78 1096 6709 1 85186


    Quote Originally Posted by anacedent
    What is your obession with CPU%?
    The use of ratios for tuning metrics is sub-sub-sub-optimal.
    If statement A, runs at 90% CPU & statment B runs at 50% CPU; which is better tuned and why?
    Perhaps i should explain my situation more clearly, im trying to churn out high-cpu sql statements as my database is using a lot of cpu, was thinking if i can show the Application team cpu_time in terms of % as they may be able to relate that to what they see in top, sometimes i do not have a specific session to trace, i just need the top sql. Currently we are generating from buffer_gets/executions from v$sqlarea.

    Looks like i may not be able to get what i want.

    Thanks for your help rendered so far.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    any SQL that runs in less than 1 second should not be of any concern.
    >If statement A, runs at 90% CPU & statment B runs at 50% CPU; which is better tuned and why?
    You did NOT answer this question.
    IMO, without data from SQL_TRACE & TKPROF you are chasing ghosts.
    If statement A completes in 0.5 seconds & statement B completes in 4 HOURS, which impacts system performance more?
    IMO, ratios are Meaningless Indicators of Performance (MIPs); because they tend to obscure the element of time.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2004
    Posts
    23
    Quote Originally Posted by anacedent
    any SQL that runs in less than 1 second should not be of any concern.
    >If statement A, runs at 90% CPU & statment B runs at 50% CPU; which is better tuned and why?
    You did NOT answer this question.
    IMO, without data from SQL_TRACE & TKPROF you are chasing ghosts.
    If statement A completes in 0.5 seconds & statement B completes in 4 HOURS, which impacts system performance more?
    IMO, ratios are Meaningless Indicators of Performance (MIPs); because they tend to obscure the element of time.
    Understand what you are trying to bring across, imho, cpu and runtime are not the only indicators of performance, the frequency of the statement is also very important, therefore, i cant answer your question based on the above info.

    My responsibility is to churn out the list of "top" sql which are utilizing too much cpu (and of course, the runtime is quite substantial bec they are the ones that caused cpu to spike to 100% and stay for 1 hr), so my concern now is whether i can show the cpu% in my log, if that is not attainable, its fine, i was just curious.

  10. #10
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    I think you're still asking the wrong question - there may not be a direct correlation between unix cpu and sql cpu.

    Run statspack at 15 min intervals while your cpu's are pegged

    Then look at what those tell you is happening in the database; look at the amount of I/O, the worst performing SQL, the top waits - and go from there

  11. #11
    Join Date
    May 2004
    Posts
    23
    Thanks for your advice and help, really appreciate it.

  12. #12
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    And besides, CPU cost in Oracle can be related to many things, from bad sql's to hard parsing, parallel and such..

  13. #13
    Join Date
    May 2004
    Posts
    23
    Thanks for your 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
  •