We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop.
We can find no patterns to this behavior. The hang can be experienced even when there are no other
processes active in the database. Checking waits, we see a db file scattered read.
SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
----- ---------------------------- ------------------ ---------- ------------------ ---------- ------------------ ----------
1 pmon timer duration 300 0 0
12 slave wait msg ptr 5.0440E+17 0 0
13 slave wait msg ptr 5.0440E+17 0 0
14 slave wait msg ptr 5.0440E+17 0 0
15 slave wait msg ptr 5.0440E+17 0 0
28 db file scattered read file# 12 block# 21047 blocks 2
5 smon timer sleep time 300 failed 0 0
Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the
only active process in the database.
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
----- ---------- --------------- -------------- ------------- ------------------
28 6233582 60812023 36589516 4076353 115
The query looks like this:
SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE
FROM PRAssignment, SRM_RESOURCES
WHERE prModTime > TIMESTAMP '2003-02-05 09:23:56.0'
AND PRAssignment.prResourceID=SRM_RESOURCES.ID
If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows
while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we
see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows.
I'm at a loss to explain why we see such inconsistent results with this query. Thoughts?
Thanks.
--------------------------------------------
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.
Email: [EMAIL PROTECTED]
Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
Select 'Indy DBA' then 'DBA Web Pages'
--------------------------------------------