Re: Re: High Redo Copy Contention

Look at

consistent gets                  3548686      
physical reads                   2641461      
table scan rows gotten         210765204    
table fetch by rowid               20905        

Most of your activity is handled as physical (or at least, filesystem) I/O, with subsequent high CPU tablescanning. You need to address issues like this before you worry about little things like latching.

Ricky's point about timed statistics is very important though - you need some concrete idea of where time is actually lost, so timed statistics would tell us a lot more about the wait events:

db file scattered read                  184546             0             
0


        -- multiblock read requests

db file sequential read                  38920             0             
0


        -- single block read requests

latch free                                 766             0             
0
            -- time lost on latching

A warning for others, though, under 8.1.5 and 8.1.6.x there are some severe bugs which can have a big impact on library cache re-use if you set timed statistics true.

Once you have stopped (or reduced) the massive amount of tablescanning, you may find that the buffer busy waits drop significantly too.

data block                    54420                0

The major cause of your performance problem is here, of course. Something that the Context (interMedia) option is doing is responsible for a lot of tablescans. This may make it more difficult to address the issue, since this is (to a large extent) a black box. Your fist step should be to dump out the sql from the v$SQL area that shows high disk reads roughly matching buffer gets, and see if that gives you any ideas.

DRSYS                                                                    
           218734    2625428          0         20         20          0 
       223
DRSYS                          D:\ORADATA\DRSID9.ORA                     
           218734    2625428          0         20         20          0 
       223          0         12

I note that your parameters don't report the optimizer mode - which means it is
set to CHOOSE - perhaps you need to
make a deliberate choice of RULE,
FIRST ROWS or ALL ROWS to meet
the requirements of the Context option.

-- 
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book rev.html



--