help with sql

[Douglas]

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

[Halo]

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;

[Douglas]

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??

[marist89]

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;

[denevge]

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

[tycho]

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