hi Friends,
i was trying to understand stats/events used by a particular query and found this statistics session logical reads.
note: we are seeing any issues but i want to know for my knowledge only.
i ran a query which does full table scan . It does direct path reads mostly (6572 waits) and very few db file sequential read ( just 4 ).
So if query is doing direct path read why should it do so many session logical reads , as logical sessions are all about buffer cache?
Demonstration:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
New session:
10:05:00 SQL> @mysevt
| | | | System
| | | Avg| Avg
| | Time| Wait| Wait
EVENT |Total Waits| (Secs)| (ms)| (ms)
-----------------------------------|-----------|-----------|----------|--------------
SQL*Net message from client | 14| 12.28| 877.3000| 1497.1000
CPU Used | | .01| |
Disk file operations I/O | 1| .00| .3000| .1000
SQL*Net message to client | 15| .00| .0000| .0000
gc current block 2-way | 1| .00| .5000| .3000
10:05:03 SQL> @mystats
Enter value for event_name: %logical read%
NAME | VALUE
----------------------------------------------------------------------|---------------
session logical reads | 149
session logical reads in local numa group | 0
session logical reads in remote numa group | 0
logical read bytes from cache | 1220608
10:05:14 SQL> select /*+ monitor full(a) */ count(S_CUS_ID) from customers a where customer_ser <1000;
COUNT(S_CUS_ID)
--------------------
965
10:06:05 SQL> @mystats
Enter value for event_name: %logical read%
NAME | VALUE
----------------------------------------------------------------------|---------------
session logical reads | 836631 <<<<<<
session logical reads in local numa group | 0
session logical reads in remote numa group | 0
logical read bytes from cache | 17137664
4 rows selected.
10:06:30 SQL> @mysevt
| | | | System
| | | Avg| Avg
| | Time| Wait| Wait
EVENT |Total Waits| (Secs)| (ms)| (ms)
-----------------------------------|-----------|-----------|----------|--------------
SQL*Net message from client | 22| 66.35| 3016.0000| 1497.1000
direct path read | 6572| 37.58| 5.7000| 6.3000
CPU Used | | 6.52| |
Disk file operations I/O | 211| .03| .1000| .1000
db file sequential read | 4| .03| 6.5000| 1.9000
events in waitclass Other | 2| .01| 4.3000|
SQL*Net message to client | 23| .00| .0000| .0000
enq: KO - fast object checkpoint | 2| .00| .2000| 2.4000
gc cr grant 2-way | 4| .00| .2000| .1000
gc current block 2-way | 1| .00| .5000| .3000
library cache lock | 4| .00| .4000| 14.8000
library cache pin | 4| .00| .2000| .3000
row cache lock | 3| .00| .3000| .2000
[myscripts]$ cat mystats.sql
from v$mystat s, v$statname n
where n.statistic# = s.statistic#
and name like nvl('&event_name',name)
/
[myscripts]$ cat mysevt.sql
select event, TOTAL_WAITS, TIME_WAITED/100 TIME_WAITED, AVERAGE_WAIT*10 average_wait ,
(select average_wait*10 from v$system_event y where y.event = e.event) sys_avg
from v$session_event e,v$mystat m
where e.sid = m.sid
union
select 'CPU Used', null, t.value/100, null, null
from v$sesstat t, v$statname n,v$mystat m
where t.statistic# = n.statistic#
and lower(n.name) = 'cpu used by this session'
and t.sid = m.sid
order by 3 desc
/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Few details:
1. DB version
2. size of the table 6.5GB and 836736 blocks.
3. execution plan of the query
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 45857 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 428 | 7276 | 45857 (2)| 00:00:02 |
-------------------------------------------------------------------------------------