>>> [EMAIL PROTECTED] 02/05/03 11:59AM >>>
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'
--------------------------------------------