Re: Help tuning please!

From: JimmyN1 <jimmyn1_at_aol.com>
Date: 14 Sep 2001 14:00:57 GMT
Message-ID: <20010914100057.07861.00000227@mb-cm.aol.com>

Let's figure out what operations (if any) is causing this error. When you notice the heavy disk drive activity run these scripts.

Script 1: Find the operation(s)
select * from v$session_longops where time_remaining > 0

Script2: From the info above, feed it into this next script to get the offending sql.

SELECT /*+ ORDERED */

       s.sid, s.username, s.osuser, 
       nvl(s.machine, '?') machine, 
       nvl(s.program, '?') program,
       s.process F_Ground, p.spid B_Ground, 
       X.sql_text
FROM   sys.v_$session S,
       sys.v_$process P, 
       sys.v_$sqlarea X
WHERE  s.osuser      like lower(nvl('&OS_User','%'))
AND    s.username    like upper(nvl('&Oracle_User','%'))
AND    s.sid         like nvl('&SID','%')
AND    s.paddr          = p.addr 
AND    s.type          != 'BACKGROUND'

AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
ORDER
    BY S.sid; Received on Fri Sep 14 2001 - 09:00:57 CDT