LIST table with High Water Mark

 set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page

select
    a.owner,
    a.table_name,
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
from
    dba_tables a,
    dba_segments b
where
    a.table_name=b.segment_name
    and a.owner=b.owner
    and a.owner not in('SYS','SYSTEM')
    and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner like upper('&owner')||'%'
    and a.table_name like upper('&table_name')||'%'
order by 1,2
/

  

•  DB file sequential read Wait Event

This well knows event and today I got opportunity to explore this event. DB file sequential read Wait Event is good or bad? This would be controversial question, because some of you will say it's bad

Or good so answer would be depends upon the situation. Answer would not straight forward but let’s try to understand how this event work. Also would like to share real test case with you that db file

Sequential read Wait Event can impact your system and one way to identify your DB system performance.

 

Every time or most time we have seen  top 5 event as db file sequential read Wait Event in AWR report for particular timestamp and Most of the time we treat this event as one event which occur in DATABASE and simply overlook or directly jump to next section , this was my habit too. But really this event is impacting database? Or it’s critical to look into more depth?  Then please see below snippets from below example.

 

1) Application team ran module and saying its running very slow.

2) Here I had two option either believe blindly on application team or take experience by capturing the SQL code and run on the database directly (on test DB) not in production.

3) I captured the sql code and its bind variable value.

4) When application team running code using v$session view i see the below session ID running this code and waiting from very long time and showing the WAITING DB file sequential read  wait event.

5) I was curious why it showing and its can be one way to identify that my system or code is doing bad or slow? So I decided to get execution plan.

For more information please see attached word Document. 

 DB FILE SEQUENTIAL READ