Home » RDBMS Server » Server Administration » v$sql and resource hungry query (11GR2 RAC on Windows)
v$sql and resource hungry query [message #527652] Wed, 19 October 2011 10:37 Go to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Hi all

I noticed the resources on our server were maxed out so I queried v$sql and ordered by CPU_Time to find the SQL_ID. I then joined this to v$session to get the session ID but I found that the SQL was running in 3 different sessions with different session#

How can this be?

Also I am thinking I am going to have to kill the session is there anything I should be aware of?
Re: v$sql and resource hungry query [message #527653 is a reply to message #527652] Wed, 19 October 2011 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>How can this be?

SQL resides in the SHARED Global Area, because it is not exclusive to any single session.
Re: v$sql and resource hungry query [message #527661 is a reply to message #527653] Wed, 19 October 2011 11:33 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Hi - I did do a lot of searching before posting.

What you have said has raised more questions though, how do I know which is the offending session when I have multiple session ids. As this is RAC I was looking in the global views and can see lots for the same SQL_ID.

Thanks
Re: v$sql and resource hungry query [message #527663 is a reply to message #527661] Wed, 19 October 2011 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
  1* select name from v$statname where name like '%CPU%'
SQL> /

NAME
----------------------------------------------------------------
OS CPU Qt wait time
CPU used when call started
CPU used by this session
IPC CPU used by this session
global enqueue CPU used by this session
gc CPU used by this session

6 rows selected
Re: v$sql and resource hungry query [message #527665 is a reply to message #527663] Wed, 19 October 2011 12:09 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Ah cool thanks - from this I have found an article here

http://www.dba-oracle.com/m_cpu_used_by_this_session.htm
Re: v$sql and resource hungry query [message #527669 is a reply to message #527665] Wed, 19 October 2011 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No comment!

Regards
Michel
Re: v$sql and resource hungry query [message #527822 is a reply to message #527669] Thu, 20 October 2011 06:09 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
please do comment
Re: v$sql and resource hungry query [message #527825 is a reply to message #527822] Thu, 20 October 2011 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My opinion on this site is well known.
Some stuff are useful.
Some stuff are wrong.
Some stuff are misleading.
But you can't know which one it is when you read a page.
So keep away of it.

Regards
Michel
Re: v$sql and resource hungry query [message #527828 is a reply to message #527825] Thu, 20 October 2011 06:40 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
keep away from the forum or keep away from http://www.dba-oracle.com ?

If its http://www.dba-oracle.com his article some up in so many of my research, its hard to know for someone like me who is an Oracle beginner where to look, I guess trying to cross reference what he is saying with Oracle documentation but sometimes the documentation doesn't help with real life experiences.
Re: v$sql and resource hungry query [message #527834 is a reply to message #527828] Thu, 20 October 2011 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58933
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the site you mentioned NOT this one.
This site is very-well organized to get many references on web and wrapped Google algorithm so you often get it in the first page.
This does not mean nothing about the quality of the information.

I tend to think the only interest of this site is to show some examples of statement syntax (that can be easily verified).

Regards
Michel
Re: v$sql and resource hungry query [message #527836 is a reply to message #527834] Thu, 20 October 2011 06:50 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Ok well opinion noted, thanks Smile
Re: v$sql and resource hungry query [message #527839 is a reply to message #527828] Thu, 20 October 2011 06:51 Go to previous messageGo to next message
John Watson
Messages: 4514
Registered: January 2010
Location: Global Village
Senior Member
Kwisatz, I'm going back to your original question. You found the SQL statement in v$sql that has consumed the most CPU time, and then determined that it was being executed by three sessions concurrently. This is not necessarily a problem. The figures in v$sql will have accumulated since the instance was started, and that statement may have been executed millions of times an hour since then - but if each execution was very fast, no problem. You need to check the EXECUTIONS column to get the average.
If the statement is executed only a few times and each execution needs vast resources, then you probably need to look at tuning it.

And in the meantime, of course you can kill the sessions - though the users mught not be happy.

Re: v$sql and resource hungry query [message #527843 is a reply to message #527839] Thu, 20 October 2011 06:54 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Ah interesting thanks, the execution was only 93 times for a large CPU_TIME but knowing that this was since instance start up is useful to know.

Thanks
Re: v$sql and resource hungry query [message #528007 is a reply to message #527843] Fri, 21 October 2011 03:32 Go to previous messageGo to next message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Hi

I am still looking into this. I have queries now as follows:

select vs.SID, vs.value/100 SECONDS, vs.STATISTIC#, vn.name from v$sesstat vs
inner join v$statname vn
on vs.statistic# = vn.statistic#
where vs.statistic# = 16
order by seconds desc

which show amount of CPU time sued by a session. However just because a session has used a lot of CPU time doesn't mean that it is using most of the resource.

How can I tell which session is being the most resource hungry?

Thanks again
Re: v$sql and resource hungry query [message #528023 is a reply to message #528007] Fri, 21 October 2011 04:56 Go to previous messageGo to next message
John Watson
Messages: 4514
Registered: January 2010
Location: Global Village
Senior Member
First, you can't rely on STATISTIC# to give the counter you want:
p0orcl> select name from v$statname where statistic#=16;

NAME
----------------------------------------
cluster wait time

p0orcl> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


There are many counters in v$sesstat that you might want to look at to determine what you call "the most resource hungry" session. "session logical reads", perhaps. Or "redo size". It depends on what you think is the limiting factor in your environment. Probably "DB Time" is the best overall, but it is up to you to decide.
Re: v$sql and resource hungry query [message #528074 is a reply to message #528023] Fri, 21 October 2011 07:02 Go to previous message
Kwisatz78
Messages: 24
Registered: October 2011
Junior Member
Oh right, hadn't realised static# could be different in other environments.

As for the resource I have been looking at other counters, but we are CPU bound at present and I was trying to figure out which was consuming the most CPU resource. In my mind CPU Time doesn't necessarily mean its impacting on the resource, something could be very light weight and running for a long time.

Perhaps as you say correlating this to logical IO would give an indication of which was consuming the most resource.

Something else I have noticed also is that a session can remain Active and CPU_TIME does not increase, is this a feature of Oracle?

Thanks

[Updated on: Fri, 21 October 2011 07:03]

Report message to a moderator

Previous Topic: ASM add disk issue
Next Topic: adrci issue
Goto Forum:
  


Current Time: Thu Aug 28 18:23:09 CDT 2014

Total time taken to generate the page: 0.06438 seconds