-
Guys, tricky one to explain this, but I will give it my best shot.....
I am trying to write a nice wee script that, in the end will produce an output of the hit ratio by the user. I know I have to look at the following v$views for this query (v$sesstat, v$session and v$statname).
My problem is that selecting rows from v$sesstat and then matching it with v$statname. e.g
SELECT v.sid ,
v.username ,
s.value "Consistant Gets"
FROM v$sesstat s,
v$session v,
v$process p
WHERE s.sid = v.sid
AND 38 = s.statistic#
AND p.addr = v.paddr
ORDER BY 3
As you can see this will give the following output :-
SID USERNAME Consistant Gets
23 CMACMILLAN 233811
But the problem is tha I want to add block_gets, physical_reads and then finally work out the hit_ratio. How can I query on other statistc# and have them in the same query?
Eventually I would like the output to look something like this:-
sid username consistant_gets block_gets physical_reads...
23 cmacmilla 12123 12321 1223
I cant seem to find a way of doing this with my limited SQL skills.
Cheers
Doug
-
Use this:
-
SELECT SUBSTR(Username,1,15)"UserName",
SUBSTR(Osuser,1,15)"OS User",
SUBSTR(Terminal,1,15)"Terminal",
100*(Consistent_Gets+Block_Gets-Physical_Reads)/
(Consistent_Gets+Block_Gets) "HitRatio"
FROM V$SESSION, V$SESS_IO
WHERE V$SESSION.SID = V$SESS_IO.SID
AND (Consistent_Gets+Block_Gets)>0
AND Username IS NOT null
ORDER BY 4;
-
ok cheers, but its not quite what i am looking for.
i am would like the columns block_gets, physical_reads, and consistant_gets in the query too.
Is there a way of doing this??
-
SELECT SUBSTR(Username,1,15)"UserName",
SUBSTR(Osuser,1,15)"OS User",
SUBSTR(Terminal,1,15)"Terminal",
block_gets, physical_reads, consistent_gets,
100 *(Consistent_Gets+Block_Gets-Physical_Reads)/
(Consistent_Gets+Block_Gets) "HitRatio"
FROM V$SESSION, V$SESS_IO
WHERE V$SESSION.SID = V$SESS_IO.SID
AND (Consistent_Gets+Block_Gets)>0
AND Username IS NOT null
ORDER BY 4;
-
Maybe something like this ???
SELECT v.sid ,
v.username ,
CG.value "Consistent Gets" ,
BG.value "Db block gets"
FROM ( select sid, value
from v$sesstat
where statistic# = 38 ) CG,
( select sid, value
from v$sesstat
where statistic# = 37 ) BG,
v$session v,
v$process p
WHERE CG.sid = v.sid
AND BG.sid = v.sid
AND p.addr = v.paddr
ORDER BY 3
Gert
-
userhitratio
hi
select i.sid, u.username, i.block_gets, i.consistent_gets, i.physical_reads, round((1-(i.physical_reads/(i.block_gets+i.consistent_gets)))*100,2) ratio
from v$sess_io i, v$session u
where u.sid = i.sid
and (i.block_gets + i.consistent_gets) > 0
/
Hope this helps
Tycho
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|