SELECT DISTINCT 
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.client_info,
       sql_text
 FROM  v$session s
  JOIN v$process p 
    ON p.addr = s.paddr
       left outer join v$sql q ON q.SQL_ID = s.SQL_ID
WHERE  spid = 4544 

ENWEBP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                     LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- ---------- ---------- ---------
NWEBP     2012-08-08 Wednesday 6308643744   73509364      98.8
NWEBP     2012-08-09 Thursday  7946062816   78316516      99.0
NWEBP     2012-08-10 Friday    7330510848   81205405      98.9
NWEBP     2012-08-11 Saturday  6988093088   85095405      98.8
NWEBP     2012-08-12 Sunday    6952708240  211627037      97.0
NWEBP     2012-08-13 Monday    7728220896  266624156      96.7
NWEBP     2012-08-14 Tuesday   7174948288  145966131      98.0
NWEBP     2012-08-15 Wednesday 6764335664   82613590      98.8
NWEBP     2012-08-16 Thursday  4191946624   53904206      98.7

ENWEBP1P > @e ndocp1p
Connected.
ENDOCP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                     LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- ---------- ---------- ---------
NDOCP     2012-08-08 Wednesday 1459664624   93145496      94.0
NDOCP     2012-08-09 Thursday  1558740016  102841429      93.8
NDOCP     2012-08-10 Friday    1572944736  107151108      93.6
NDOCP     2012-08-11 Saturday  1621583904   89352225      94.8
NDOCP     2012-08-12 Sunday    1415023152   82021291      94.5
NDOCP     2012-08-13 Monday    1541693696   90910527      94.4
NDOCP     2012-08-14 Tuesday   1982910096  704681299      73.8
NDOCP     2012-08-15 Wednesday 1278312160   78303441      94.2
NDOCP     2012-08-16 Thursday   798850736   40793303      95.1

ENDOCP1P > @e nalfp1p
Connected.
ENALFP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                     LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- ---------- ---------- ---------
NALFP     2012-08-08 Wednesday   79650960     136172      99.8
NALFP     2012-08-09 Thursday    97558736     190804      99.8
NALFP     2012-08-10 Friday     140384592     345480      99.8
NALFP     2012-08-11 Saturday    38125824     121594      99.7
NALFP     2012-08-12 Sunday      51670896      11068     100.0
NALFP     2012-08-13 Monday      82026656     320363      99.6
NALFP     2012-08-14 Tuesday     46048560      29682      99.9
NALFP     2012-08-15 Wednesday  120670096     301510      99.8
NALFP     2012-08-16 Thursday    36570144       8372     100.0

ENALFP1P > list
  1  select d.name database,l.dat "Day",total_logical logical,total_physical physical,
  2  total_logical/(total_physical+total_logical)*100 Hit_ratio
  3  from alan_physical p, alan_logical l,v$database d
  4* where p.dat=l.dat

SET LINES 200
SET WRAP OFF
col "DAY" for a20
col hit_ratio for 999.9
set termout off
drop table alan_logical purge;
drop table alan_physical purge;
break on report
compute sum of total_daily_logical_reads on report
create table alan_logical as
select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAT",
sum(b.logical_READS_DELTA) total_logical
from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
where a.object_id=b.OBJ#
and b.logical_READS_DELTA>0
and c.instance_number=(select instance_number from v$instance)
and c.snap_id=b.snap_id
group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
order by 1,2
/
create table alan_physical as
select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAT",
sum(b.physical_READS_DELTA) total_physical
from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
where a.object_id=b.OBJ#
and b.physical_READS_DELTA>0
and c.instance_number=(select instance_number from v$instance)
and c.snap_id=b.snap_id
group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
order by 1,2
/
set termout on
select d.name database,l.dat "Day",total_logical logical,total_physical physical,
total_logical/(total_physical+total_logical)*100 Hit_ratio
from alan_physical p, alan_logical l,v$database d
where p.dat=l.dat;