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

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 07 Nov 2001 19:15:15 GMT
Message-ID: <3be986e3.681515437@news.globix.com>

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

Received on Wed Nov 07 2001 - 13:15:15 CST