| v$sql and resource hungry query [message #527652] |
Wed, 19 October 2011 10:37  |
 |
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 #527839 is a reply to message #527828] |
Thu, 20 October 2011 06:51  |
John Watson
Messages: 8960
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 #528023 is a reply to message #528007] |
Fri, 21 October 2011 04:56 |
John Watson
Messages: 8960
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 |
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
