Re: select table_name from user_tables : db file sequential read wait on a NON-SYSTEM tablespace

NetComrade wrote:
>
> HOCKEY_FANdb file sequential read(file#:13.block#:259760.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> HOCKEY_FANdb file sequential read(file#:13.block#:73668.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> HOCKEY_FANdb file sequential read(file#:14.block#:107882.blocks:1)
>
> 1 select rpad(username,10)||rpad(event,
> 70)||chr(10)||bad_sql||chr(10)||'Total: '|
> |count(*) from
> 2 (
> 3 select username,
> event||'('||p1text||':'||p1||'.'||p2text||':'||p2||'.'||p3text|
> |':'||p3||')' event,
> 4 replace(sql_text,' ','') bad_sql
> 5 from v$session_wait sw, v$session s, v$sql sql
> 6 where
> 7 sw.sid=s.sid and
> 8 s.sql_address(+)=sql.address and
> 9 event != 'Null event' and
> 10 event != 'rdbms ipc message' and
> 11 event != 'pipe get' and
> 12 event != 'virtual circuit status' and
> 13 event not like '%timer%' and
> 14 event not like 'SQL*Net message from %' and
> 15 event not like 'SQL*Net message to %'
> 16 )
> 17 group by username, event, bad_sql
> 18* order by count(*) desc,bad_sql
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:14.block#:24023.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:22.block#:85950.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:14.block#:770.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:14.block#:8532.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:17.block#:112049.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:17.block#:187099.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> db file sequential read(file#:20.block#:60613.blocks:1)
>
> SELECT table_name FROM user_tables WHERE rownum = 1
> Total: 2
>
> files 13,17,20 belong to DATA and INDEX tablespaces that are used by
> the user that uses runs these queries (they're used by the app server
> to check whether the db is still up).
>
> shouldn't it just be selecting from some system table?
>
> thnx
> .......
> We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes
> Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630
> AOL: NetComrade ICQ: 11340726 remove NSPAM to email

Are you sure you haven't got a script error ?

I ran a few dictionary queries with a level 12 trace and the only reads I got were:

WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=24965 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=27052 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=45 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=1359 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=18968 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=24960 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=24964 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=27051 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=27055 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=47 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=1 p2=924 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=1 p2=24959 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=1 p2=27326 p3=1
WAIT #1: nam='db file sequential read' ela= 2 p1=1 p2=6594 p3=1

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."