It is useful to be able to see all SQLs that are running along with how long it has been running, and the number of parallel sessions opened by the session running it etc.
The following script can do the job. Some of the columns are self explanatory. For other columns , a brief explanation is given below:
SQL_OPNAME – type of SQL (Select, Update etc)
SQL_EXEC_START_TM – SQL start time
MINS_RUNNING – how long this SQL has been running
PRLL_SESNS — Number of paralle sessions if the SQL is running in parallel
SQL_CNT – how many sessions running the same SQL
FMS_CNT – how many sessions running SQLs with the same force matching signature(often these are sessions running SQLs w/o binds that only differ in the value provided)
FMS – force matching signature
Script
--
-- sqlnow.sql
-- Parameters - none
--
-- Purpose: Identify all sqls that are running currenly
-- scripts also list how long it has been running, number of parallel sessions etc
--
set verify on
set echo on
set lines 250
set head on
set tab off
col username format a30
col fms format a30
col sql_exec_start_tm format a17
col sql_cnt format 999999
col fms_cnt format 999999
col sql_opname format a10
WITH SQLS
AS (SELECT /*+materialize no_merge*/
DISTINCT sql_id, sql_exec_id
FROM gv$session
WHERE status = 'ACTIVE'
--AND username IS NOT NULL
--AND username NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
)
SELECT inst_id,
session_id,
SESSION_SERIAL#,
(SELECT username
FROM all_users
WHERE user_id = ash.user_id) username,
sql_id,
sql_exec_id,
sql_opname,
to_char(MIN (sql_exec_start),'dd-mon-rr hh24:mi') sql_exec_start_tm ,
CEIL ( (SYSDATE - MIN (sql_exec_start)) * 24 * 60) mins_running,
COUNT (*) OVER (PARTITION BY sql_id || '-' || sql_exec_id) prll_sesns,
COUNT (DISTINCT sql_exec_id || sql_exec_start) OVER (PARTITION BY sql_id) sql_cnt,
case when FORCE_MATCHING_SIGNATURE != 0 then
COUNT (DISTINCT sql_exec_id || sql_exec_start) OVER (PARTITION BY FORCE_MATCHING_SIGNATURE)
end fms_cnt,
TO_CHAR (FORCE_MATCHING_SIGNATURE) fms
--MIN (sample_time) sample_time
FROM gv$active_session_history ash
WHERE (sql_id, sql_exec_id) IN (SELECT sql_id, sql_exec_id FROM sqls)
GROUP BY inst_id, session_id, SESSION_SERIAL#,user_id, sql_id,sql_opname, sql_exec_id, sql_exec_start,
FORCE_MATCHING_SIGNATURE
--HAVING SYSDATE - MAX (sql_exec_start) > (1 / 24 / 60 / 2) running for more than 30 secs
ORDER BY mins_running DESC, fms
/
CLEAR COLUMNS
-- end of script
Sample Output
INST_ID SESSION_ID SESSION_SERIAL# USERNAME SQL_ID SQL_EXEC_ID SQL_OPNAME SQL_EXEC_START_ MINS_RUNNING PRLL_SESNS SQL_CNT FMS_CNT FMS
---------- ---------- --------------- ------------------------------ ------------- ----------- ---------- --------------- ------------ ---------- ------- ------- --------------------
4 4898 21199 SALES 55t1wf74z145c 67108864 SELECT 07-jan-15 12:37 219 1 2 2 12114432892951959166
4 4384 2251 SALES 55t1wf74z145c 67108865 SELECT 07-jan-15 13:07 189 1 2 2 12114432892951959166
3 4628 64895 SALES rtkcs85g8twg7 50331648 SELECT 07-jan-15 16:10 6 1 1 1 8647423045760569048
1 148 3437 SALES tygug4ak7vw7d 16777483 SELECT 07-jan-15 16:12 4 1 1 1 8416747714864488742
3 695 21423 MARKETING 7453fh3pxknh8 50331649 SELECT 07-jan-15 16:14 3 1 1 1 11026030165138208448
3 4236 50751 MARKETING 66yswb95s0chd 50331648 SELECT 07-jan-15 16:15 2 2 1 1 12290910331691816147
3 4951 43791 MARKETING ggyswb95s0chd 50331648 SELECT 07-jan-15 16:15 2 2 1 1 67477148649674771486
1 3840 3207 MARKETING 883t81m5yu0hv 17912049 SELECT 07-jan-15 16:16 1 1 1 1 7298091671828702764
Conclusion
There are a total of eight active sessions running different SQLs when the report was generated. SQL with id 55t1wf74z145c has been running for 3+ hours. There are two sessions running the same SQL. But it is not a parallel query.SQL_CNT and FMS_CNT are the the same probably because bind variables are being used, or it may be exactly same SQLs.
This gives the picture of what each active session is doing currently. You can identify long running and parallel SQLs immediately from this report.
Some truly nice and useful info on this web site, as well I believe the style holds good features.
This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!
nwcXojRSYUxh
Greetings! I know this is kinda off topic but I’d figured I’d ask. Would you be interested in exchanging links or maybe guest authoring a blog article or vice-versa? My website goes over a lot of the same subjects as yours and I feel we could greatly benefit from each other. If you’re interested feel free to send me an e-mail. I look forward to hearing from you! Terrific blog by the way!
A lot of of what you assert happens to be astonishingly appropriate and it makes me ponder why I hadn’t looked at this with this light before. Your piece really did switch the light on for me personally as far as this specific subject goes. But there is actually just one point I am not really too cozy with so whilst I make an effort to reconcile that with the actual central theme of your issue, let me observe what all the rest of your subscribers have to point out.Very well done.
Hi there, i read your blog occasionally and i own a similar one and i was just curious if you get a lot of spam responses? If so how do you prevent it, any plugin or anything you can recommend? I get so much lately it’s driving me crazy so any support is very much appreciated.
Thank you for another fantastic post. Where else could anyone get that type of info in such a perfect way of writing? I’ve a presentation next week, and I’m on the look for such information.
What is Renew? Renew is a dietary supplement designed to support blood flow while also aiming to boost testosterone levels and provide an explosive energy drive
Great article and right to the point. I don’t know if this is really the best place to ask but do you people have any ideea where to hire some professional writers? Thanks 🙂
There are some interesting deadlines in this article but I don’t know if I see all of them center to heart. There may be some validity but I’ll take maintain opinion until I look into it further. Good article , thanks and we wish extra! Added to FeedBurner as nicely
Real fantastic information can be found on blog. “Many complain of their memory, few of their judgment.” by Benjamin Franklin.
I truly appreciate this post. I have been looking all over for this! Thank goodness I found it on Bing. You have made my day! Thx again
Good write-up, I¦m regular visitor of one¦s website, maintain up the excellent operate, and It’s going to be a regular visitor for a long time.
I¦ve recently started a website, the information you provide on this web site has helped me tremendously. Thanks for all of your time & work.
Heya i am for the first time here. I found this board and I find It truly helpful & it helped me out a lot. I hope to offer something again and help others like you aided me.
Hello! I could have sworn I’ve been to this blog before but after browsing through some of the post I realized it’s new to me. Anyways, I’m definitely happy I found it and I’ll be book-marking and checking back frequently!
I went over this web site and I think you have a lot of fantastic information, saved to bookmarks (:.
When I originally commented I clicked the -Notify me when new comments are added- checkbox and now each time a comment is added I get four emails with the same comment. Is there any way you can remove me from that service? Thanks!
What Is ZenCortex? ZenCortex is a natural supplement that promotes healthy hearing and mental tranquility. It’s crafted from premium-quality natural ingredients, each selected for its ability to combat oxidative stress and enhance the function of your auditory system and overall well-being.
hello!,I love your writing very much! proportion we keep in touch extra approximately your article on AOL? I require an expert in this area to solve my problem. May be that’s you! Taking a look forward to look you.
I truly appreciate this post. I?¦ve been looking all over for this! Thank goodness I found it on Bing. You have made my day! Thx again
Thanks a bunch for sharing this with all folks you really know what you are speaking approximately! Bookmarked. Please also discuss with my website =). We could have a link exchange arrangement among us!
You have noted very interesting points! ps nice web site.
Hello! I could have sworn I’ve been to this blog before but after browsing through some of the post I realized it’s new to me. Anyways, I’m definitely happy I found it and I’ll be book-marking and checking back frequently!
After study a few of the blog posts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.
I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a designer to create your theme? Exceptional work!
I?¦ve recently started a blog, the info you offer on this site has helped me tremendously. Thank you for all of your time & work.
I do like the way you have presented this situation and it does present me personally some fodder for thought. However, because of what I have seen, I simply just hope as the feedback pile on that individuals keep on point and don’t get started on a soap box associated with some other news of the day. Still, thank you for this fantastic piece and although I do not necessarily agree with this in totality, I respect the standpoint.
An impressive share, I just given this onto a colleague who was doing a little analysis on this. And he in fact bought me breakfast because I found it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I feel strongly about it and love reading more on this topic. If possible, as you become expertise, would you mind updating your blog with more details? It is highly helpful for me. Big thumb up for this blog post!
Well I definitely liked reading it. This information provided by you is very useful for accurate planning.
Thanks for the sensible critique. Me & my neighbor were just preparing to do a little research on this. We got a grab a book from our area library but I think I learned more from this post. I am very glad to see such fantastic information being shared freely out there.
Hello very cool website!! Guy .. Beautiful .. Wonderful .. I will bookmark your web site and take the feeds also?KI’m satisfied to search out numerous helpful information right here in the post, we want work out extra strategies on this regard, thank you for sharing. . . . . .
F*ckin’ tremendous things here. I am very glad to see your article. Thanks a lot and i am looking forward to contact you. Will you please drop me a e-mail?
I like the helpful information you provide in your articles. I will bookmark your weblog and check again here regularly. I am quite certain I will learn lots of new stuff right here! Good luck for the next!
As I website possessor I conceive the subject matter here is real good, thanks for your efforts.
Its fantastic as your other content : D, appreciate it for putting up. “The art of love … is largely the art of persistence.” by Albert Ellis.
I am continually browsing online for articles that can assist me. Thanks!
TyvSUbmWL
Simply wanna comment on few general things, The website design is perfect, the articles is real fantastic : D.
Wow that was strange. I just wrote an incredibly long comment but after I clicked submit my comment didn’t appear. Grrrr… well I’m not writing all that over again. Regardless, just wanted to say excellent blog!
Some really nice and useful info on this website, besides I believe the style has wonderful features.
Great write-up, I am normal visitor of one¦s site, maintain up the excellent operate, and It’s going to be a regular visitor for a lengthy time.
I reckon something truly special in this site.
Youre so cool! I dont suppose Ive read anything like this before. So good to search out any person with some unique ideas on this subject. realy thank you for beginning this up. this web site is something that’s wanted on the net, somebody with a little bit originality. helpful job for bringing one thing new to the web!
After study a few of the blog posts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.
I saw that your artoftuning.net website may be missing out on approximately 1,000 visitors daily. Our AI powered traffic system is tailored to increase your site’s visibility: https://tinyurl.com/mrynyznw
We’re offering a free trial that includes 4,000 targeted visitors to show the potential benefits. After the trial, we can supply up to 250,000 targeted visitors per month. This solution could greatly enhance your website’s reach and visitors.
I’ve been browsing on-line greater than 3 hours today, but I by no means found any attention-grabbing article like yours. It is lovely worth enough for me. Personally, if all web owners and bloggers made excellent content as you probably did, the web will be a lot more useful than ever before. “It’s all right to have butterflies in your stomach. Just get them to fly in formation.” by Dr. Rob Gilbert.
Your place is valueble for me. Thanks!…
obviously like your web site but you have to test the spelling on quite a few of your posts. Several of them are rife with spelling issues and I find it very bothersome to inform the reality however I will surely come back again.
I’ve been surfing online more than three hours today, but I by no means found any fascinating article like yours. It is pretty price enough for me. Personally, if all site owners and bloggers made excellent content material as you did, the internet will probably be much more helpful than ever before.