Waits in a SQL

When a SQL runs, the time it takes to run is basically come from the CPU time needed plus the time for waits.

The following SQL looks into gv$active_session_history and finds the time the SQL spends on each wait event or CPU

The Script

--
--
-- sqlwaits.sql
-- Parameters - none
--
-- Purpose: Identify all wait events related to a particular sql from gv$active_session_history
-- 
set verify on
set echo on
set lines 250
set head on
set tab off

col event format a30 wrap
col PCT format 99.99

  SELECT sql_id,
         event,
         event_cnt,
         ROUND ( (event_cnt / 60), 2) event_time,
         round((event_cnt/tot_cnt) * 100,2) PCT,
         execs_for_event,
         total_execs,
         ROUND ( (tot_cnt / 60), 2) tot_time_mins,
         ROUND (tot_cnt / total_execs) avg_secs_per_exec
    FROM (SELECT DISTINCT sql_id
                          , NVL (event, 'ON CPU') event
                          , COUNT (DISTINCT sql_id || sql_exec_id || sql_exec_start) OVER (PARTITION BY event) execs_for_event
                          , COUNT (*) OVER (PARTITION BY event) event_cnt
                          , COUNT (*) OVER () tot_cnt
                          , COUNT (DISTINCT sql_id || sql_exec_id || sql_exec_start) OVER () total_execs
            FROM gv$active_session_history
           WHERE sql_id = '&1')
ORDER BY pct DESC
/

CLEAR COLUMNS

A brief explanation of the columns is given below:

SQL_ID — SQL ID
EVENT — Name of the wait event
EVENT_CNT — Count of the event
EVENT_TIME — Total time spent on the event
PCT — Percentage
EXECS_FOR_EVENT — How many executions contributed to this event
TOTAL_EXECS –Total number executions of the SQL
TOT_TIME_MINS — Total time from all executions
AVG_SECS_PER_EXEC — Average time per single execution

Sample Run

Enter value for 1: 9ht3rsgxfjks0
old  17:            WHERE sql_id = '&1')
new  17:            WHERE sql_id = '9ht3rsgxfjks0')

SQL_ID        EVENT                           EVENT_CNT EVENT_TIME    PCT EXECS_FOR_EVENT TOTAL_EXECS TOT_TIME_MINS AVG_SECS_PER_EXEC
------------- ------------------------------ ---------- ---------- ------ --------------- ----------- ------------- -----------------
9ht3rsgxfjks0 ON CPU                              48116     801.93  99.24             270         311         808.1               156
9ht3rsgxfjks0 db file sequential read               144        2.4    .30              53         311         808.1               156
9ht3rsgxfjks0 enq: SQ - contention                   85       1.42    .18              17         311         808.1               156
9ht3rsgxfjks0 gc current grant 2-way                 54         .9    .11              16         311         808.1               156
9ht3rsgxfjks0 gc current multi block request         45        .75    .09              22         311         808.1               156
9ht3rsgxfjks0 log file sync                           8        .13    .02               5         311         808.1               156
9ht3rsgxfjks0 gc current grant busy                   6         .1    .01               4         311         808.1               156
9ht3rsgxfjks0 gc buffer busy acquire                  3        .05    .01               1         311         808.1               156
9ht3rsgxfjks0 enq: TX - contention                    6         .1    .01               4         311         808.1               156
9ht3rsgxfjks0 row cache lock                          4        .07    .01               4         311         808.1               156
9ht3rsgxfjks0 library cache pin                       4        .07    .01               4         311         808.1               156
9ht3rsgxfjks0 undo segment extension                  2        .03    .00               2         311         808.1               156
9ht3rsgxfjks0 library cache: mutex X                  2        .03    .00               2         311         808.1               156
9ht3rsgxfjks0 gc current block 2-way                  1        .02    .00               1         311         808.1               156
9ht3rsgxfjks0 gc current block 3-way                  2        .03    .00               1         311         808.1               156
9ht3rsgxfjks0 enq: FB - contention                    1        .02    .00               1         311         808.1               156
9ht3rsgxfjks0 log file switch completion              2        .03    .00               2         311         808.1               156
9ht3rsgxfjks0 gc current grant congested              1        .02    .00               1         311         808.1               156

Conclusion

Looking at the output, we can easly tell what contributed to the time spent on the SQL. In the example above it is mostly CPU. Other waits are insignificant.

db file sequential read is index reads wich took only less than 1 percent. Also enq: SQ – contention is relate to sequence that is also less than 1 %

Here we need to reduce the CPU time to improve this SQL.

88 thoughts on “Waits in a SQL”

  1. F*ckin¦ amazing things here. I am very happy to peer your article. Thank you a lot and i am having a look ahead to touch you. Will you kindly drop me a mail?

    Reply

  2. I have been exploring for a little for any high quality articles or weblog posts on this sort of house . Exploring in Yahoo I finally stumbled upon this website. Studying this info So i am satisfied to exhibit that I have an incredibly good uncanny feeling I found out just what I needed. I such a lot surely will make sure to do not put out of your mind this website and give it a glance on a constant basis.

    Reply

  3. Hi, just required you to know I he added your site to my Google bookmarks due to your layout. But seriously, I believe your internet site has 1 in the freshest theme I??ve came across. It extremely helps make reading your blog significantly easier.

    Reply

  6. Hello, Neat post. There’s an issue with your web site in internet explorer, might test this… IE nonetheless is the market leader and a large component of folks will leave out your magnificent writing due to this problem.

    Reply

  7. I am glad for commenting to make you understand what a excellent discovery my girl went through checking your webblog. She picked up too many details, not to mention what it is like to possess a great coaching spirit to let many people really easily comprehend some problematic subject matter. You truly surpassed readers’ expectations. Thanks for imparting those warm and helpful, dependable, explanatory as well as easy tips about this topic to Ethel.

    Reply

  10. What Is FitSpresso? The effective weight management formula FitSpresso is designed to inherently support weight loss. It is made using a synergistic blend of ingredients chosen especially for their metabolism-boosting and fat-burning features.

    Reply

  12. You could certainly see your enthusiasm in the work you write. The world hopes for even more passionate writers like you who aren’t afraid to say how they believe. Always go after your heart.

    Reply

  13. I really like what you guys tend to be up too. Such clever work and coverage! Keep up the excellent works guys I’ve included you guys to our blogroll.

    Reply

  15. You actually make it seem so easy along with your presentation however I in finding this matter to be really one thing that I believe I’d never understand. It seems too complex and very broad for me. I’m having a look ahead for your next submit, I?¦ll attempt to get the grasp of it!

    Reply

  16. Hey, you used to write magnificent, but the last few posts have been kinda boring… I miss your great writings. Past few posts are just a bit out of track! come on!

    Reply

  20. It’s the best time to make some plans for the longer term and it is time to be happy. I’ve read this publish and if I may I want to recommend you few attention-grabbing things or advice. Perhaps you can write next articles referring to this article. I want to read more issues about it!

    Reply

  21. I used to be very happy to seek out this internet-site.I wanted to thanks on your time for this excellent learn!! I positively enjoying every little little bit of it and I have you bookmarked to check out new stuff you blog post.

    Reply

  22. Howdy! I know this is kinda off topic but I was wondering which blog platform are you using for this site? I’m getting tired of WordPress because I’ve had issues with hackers and I’m looking at options for another platform. I would be awesome if you could point me in the direction of a good platform.

    Reply

  23. Hmm is anyone else encountering problems with the pictures on this blog loading? I’m trying to figure out if its a problem on my end or if it’s the blog. Any responses would be greatly appreciated.

    Reply

  24. Howdy! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche. Your blog provided us valuable information to work on. You have done a wonderful job!

    Reply

  25. hey there and thank you for your information – I’ve definitely picked up something new from right here. I did however expertise some technical issues using this site, since I experienced to reload the site lots of times previous to I could get it to load correctly. I had been wondering if your web host is OK? Not that I am complaining, but sluggish loading instances times will very frequently affect your placement in google and could damage your quality score if advertising and marketing with Adwords. Anyway I am adding this RSS to my e-mail and can look out for a lot more of your respective interesting content. Make sure you update this again very soon..

    Reply

  26. Fantastic goods from you, man. I have take into account your stuff prior to and you’re just extremely excellent. I really like what you’ve got here, really like what you’re saying and the best way through which you assert it. You make it entertaining and you still take care of to keep it sensible. I can’t wait to read far more from you. That is really a wonderful web site.

    Reply

  27. Throughout the great design of things you actually get an A+ for effort. Where exactly you actually lost me ended up being in your details. You know, they say, details make or break the argument.. And that could not be much more correct here. Having said that, permit me reveal to you what did do the job. The text is highly persuasive which is probably why I am making the effort in order to opine. I do not make it a regular habit of doing that. 2nd, while I can easily see a jumps in reasoning you make, I am not really confident of exactly how you appear to connect the ideas which help to make your conclusion. For the moment I shall yield to your position however wish in the near future you actually connect your dots much better.

    Reply

  29. Hello, you used to write great, but the last few posts have been kinda boring… I miss your tremendous writings. Past few posts are just a little bit out of track! come on!

    Reply

  30. What i do not realize is in fact how you are no longer really much more neatly-favored than you might be now. You are very intelligent. You know therefore significantly with regards to this topic, made me for my part imagine it from a lot of numerous angles. Its like women and men aren’t fascinated except it’s one thing to do with Girl gaga! Your personal stuffs great. All the time care for it up!

    Reply

  43. Sumatra Slim Belly Tonic reviews

    Reply

  56. Dentavim is a revolutionary dietary supplement designed to promote oral health by addressing two major concerns: teeth’ cleanliness and gums’ health. Unlike typical oral hygiene products that focus solely on surface treatment, Dentavim dives deeper into the issues often caused by environmental factors, especially particulate matter, which can lead to persistent bad breath and stubborn stains. This product contains a proprietary blend of six potent nutrients derived from natural sources to enhance dental hygiene and overall well-being.

    Reply

  66. Undeniably believe that which you stated. Your favourite justification seemed to be at the web the simplest thing to be mindful of. I say to you, I certainly get irked at the same time as folks think about worries that they plainly do not recognise about. You controlled to hit the nail upon the top as neatly as outlined out the whole thing without having side-effects , other folks can take a signal. Will probably be back to get more. Thanks

    Reply

  67. Excellent post. Keep writing such kind of info on your page.
    Im really impressed by it.
    Hi there, You have performed a fantastic job. I’ll certainly digg it and individually
    recommend to my friends. I am confident they’ll be benefited from
    this website.

    Also visit my web site :: neotonics review

    Reply

  78. Fitspresso is a brand-new natural weight loss aid designed to work on the root cause of excess and unexplained weight gain. The supplement uses an advanced blend of vitamins, minerals, and antioxidants to support healthy weight loss by targeting the fat cells’ circadian rhythm

    Reply

  86. Fitspresso is a brand-new natural weight loss aid designed to work on the root cause of excess and unexplained weight gain. The supplement uses an advanced blend of vitamins, minerals, and antioxidants to support healthy weight loss by targeting the fat cells’ circadian rhythm

    Reply

  87. Fitspresso is a brand-new natural weight loss aid designed to work on the root cause of excess and unexplained weight gain. The supplement uses an advanced blend of vitamins, minerals, and antioxidants to support healthy weight loss by targeting the fat cells’ circadian rhythm

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *