-- Thanks for the question regarding "Performance Bottleneck", version 9208
Submitted on 28-Apr-2008 10:03 Central time zone
Last updated 30-Apr-2008 8:23
You Asked
Hi Tom,
How do we find out if a particular query or session is resource hogging due to which other sessions in the DB are affected.How to cofirm this, if it happens. What to check ?
How to find out for a particular active session how much it is consuming
CPU, IO ,MEMORY etc etc similar parameters ?
How does DB sessions priorotize or compete for resources ( No resource manager used )
Thanx
and we said...
well, every active session is "hogging" resources - all of them.
You can query the various v$ tables to see what each session is doing - how many IO's they have performed, how much CPU they have used - if one of them has used "a lot compared to the others", that session might be considered "a hog"
ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select sid,
5 max(decode(name, 'session logical reads', value)) LIO,
6 max(decode(name, 'CPU used by this session',value)) cpu,
7 max(decode(name, 'session uga memory',value)) uga,
8 max(decode(name, 'session uga memory max',value)) ugamax,
9 max(decode(name, 'session pga memory',value)) pga,
10 max(decode(name, 'session pga memory max',value)) pgamax
11 from (
12 select b.sid, a.name, b.value
13 from v$statname a, v$sesstat b
14 where a.statistic# = b.statistic#
15 and a.name in ( 'session logical reads',
16 'CPU used by this session',
17 'session uga memory',
18 'session uga memory max',
19 'session pga memory',
20 'session pga memory max' )
21 )
22 group by sid
23 )
24 select a.username, a.program, b.*
25 from v$session a, data b
26 where a.sid = b.sid
27 order by b.sid
28 /
USERNAME PROGRAM SID LIO CPU UGA UGAMAX
PGA PGAMAX
---------- -------------------- ---------- ---------- ---------- ---------- ----------
---------- ----------
oracle@dellpe (q000) 29 12 1 156316 156316
454228 454228
oracle@dellpe (q001) 31 0 0 90852 90852
323156 323156
oracle@dellpe (QMNC) 35 6 0 90852 90852
323156 323156
OPS$TKYTE sqlplus@dellpe (TNS 37 2410 37 1602180 1602180
2403280 2403280
V1-V3)
oracle@dellpe (MMNL) 39 320 40 90852 156316
388692 388692
oracle@dellpe (MMON) 40 43469 161 680028 999872
1109588 2158164
oracle@dellpe (CJQ0) 41 20341 26 352708 614564
650836 1502804
oracle@dellpe (RECO) 42 108 0 156316 156316
388692 388692
oracle@dellpe (SMON) 43 73146 192 287244 483636
912980 1044052
oracle@dellpe (CKPT) 44 159 2 156316 156316
544104 544104
oracle@dellpe (LGWR) 45 0 0 90852 90852
11595348 11660884
oracle@dellpe (DBW0) 46 0 0 90852 90852
2780820 2780820
oracle@dellpe (MMAN) 47 0 0 90852 90852
323156 323156
oracle@dellpe (PSP0) 48 0 0 90852 90852
323156 323156
oracle@dellpe (PMON) 49 0 0 90852 90852
323156 323156
15 rows selected.
if there is no resource manager, then pretty much "first come, first serve", we use latches to protect shared data structures in memory and enqueues for a heavy duty serialization device for things like rows in tables - but the OS schedules things to run.
What about the Total available
April 29, 2008 - 11am Central time zone
Reviewer: A reader
Tom,
Thanx for your wonderful answers.
How do I find for a an instance the Total available for each of these parameters.It may be a dumb
question.
Like the query shows how much each session is taking.
But How would I find out much is the Total availableout there for LIO PIO CPU UGA PGA
So I think if I add up individual entries It should tally with this number Followup April 29, 2008 - 11am Central time zone:
select sum(cpu), sum(lio), sum.....
from
(
24 select a.username, a.program, b.*
25 from v$session a, data b
26 where a.sid = b.sid
27 order by b.sid
)
just aggregate?? pretty straightforward...
and of course it would 'tally' with these numbers, you need to sum these numbers - it is rather a tautology...
or...
ops$tkyte%ORA9IR2> with data
2 as
3 (
4 select sid,
5 max(decode(name, 'session logical reads', value)) LIO,
6 max(decode(name, 'CPU used by this session',value)) cpu,
7 max(decode(name, 'session uga memory',value)) uga,
8 max(decode(name, 'session uga memory max',value)) ugamax,
9 max(decode(name, 'session pga memory',value)) pga,
10 max(decode(name, 'session pga memory max',value)) pgamax
11 from (
12 select b.sid, a.name, b.value
13 from v$statname a, v$sesstat b
14 where a.statistic# = b.statistic#
15 and a.name in ( 'session logical reads',
16 'CPU used by this session',
17 'session uga memory',
18 'session uga memory max',
19 'session pga memory',
20 'session pga memory max' )
21 )
22 group by sid
23 )
24 select a.sid,
25 decode( grouping(a.sid), 0, max(a.username), null) uname,
26 decode( grouping(a.sid), 0, max(a.program), null ) prog,
27 sum(lio) lio,
28 sum(cpu) cpu,
29 sum(uga) uga,
30 decode( grouping(a.sid), 0, sum(ugamax), null) ugamax,
31 sum(pga) pga,
32 decode( grouping(a.sid), 0, sum(pgamax), null) pgamax
33 from v$session a, data b
34 where a.sid = b.sid
35 group by rollup(a.sid)
36 /
SID UNAME PROG LIO CPU UGA UGAMAX PGA
PGAMAX
---- ---------- ------------------------- ---------- ---------- ---------- ---------- ----------
----------
1 oracle@dellpe (PMON) 0 0 77008 77008 224752
224752
2 oracle@dellpe (DBW0) 0 0 77008 77008 2246332
2246332
3 oracle@dellpe (LGWR) 0 0 77008 77008 5412516
5412516
4 oracle@dellpe (CKPT) 0 0 77008 77008 305788
305788
5 oracle@dellpe (SMON) 32777 0 77008 77008 438904
635512
6 oracle@dellpe (RECO) 223 0 77008 77008 224752
224752
7 oracle@dellpe (CJQ0) 113475 0 77008 77008 224752
224752
8 oracle@dellpe (QMN0) 979677 0 280984 1276076 692952
3363500
9 oracle@dellpe (ARC0) 0 0 77008 77008 4496320
4496320
10 oracle@dellpe (ARC1) 0 0 77008 77008 4463016
4463016
11 OPS$TKYTE sqlplus@dellpe (TNS V1-V3 197 32 273400 273400 450572
516108
1126349 32 1247456 19180656
12 rows selected.
Performance Bottleneck
April 29, 2008 - 11am Central time zone
Reviewer: Ravi Vedala
If you are not aware of the system tables/views, you can also use Oracle Enterprise Manager to find
out the blocking sessions. Under the "Performance" tab,there is a link "Blocking Sessions". It will
show you if there are any blocking sessions. Also, I believe that's a good starting point.
Hope this helps.
Ravi Vedala Followup April 29, 2008 - 11am Central time zone:
this is not "blocking" inasmuch as "who is hogging the resources" and yes, enterprise manager would be useful there as well - top sessions, top sql reports.
CPU Calculation
April 29, 2008 - 1pm Central time zone
Reviewer: Robert from Memphis, USA.
Tom,
Isn't it true that the CPU stats for the v$ views, above, are not calculated (e.g. updated) until
the END of any given SQL statement?
In other words, you could have a long running SQL statement, using tons of CPU, but the v$ views
would not show this until AFTER the statement ended, correct?
Thanks!
Robert. Followup April 30, 2008 - 8am Central time zone:
correct. sort of.
it is the "end of the call"
So,
select * from big_table1, big_table2 where join_condition
that will result in many hundreds or thousands of 'calls' - there is the open with probably almost no cpu.
There is the first fetch (the first fetch is probably the long call - we would probably full scan one of the big tables, hash it into memory/disk, then start full scanning the second - once we got N rows where N is your array fetch size - we'd return).
Then there are the subsequent fetches (which are probably very fast at this point as we just get N rows from the second big_table and hash join them)
Now, a long running insert/update/delete/merge - that would be at the statement level since the 'call' is the statement.
Or, a stored procedure - the stored procedure is the call.
(basically, cpu is recorded when control returns to the external client application outside of the database)