Conference eps::oracle

1536.0. "DBwriter holds up other activity" by NZOV02::FARQUHARSON () Mon Mar 24 1997 23:13

    Howdy,
    	I have a customer setting up a Data Warehouse, and we are in
    the process of performing the initial data load. I was wondering if
    experiences of others might shed some light on the performance
    characteristics we are seeing during the load process, where the
    DBwriter process activity seems to be delaying activity on the load
    process.  
    
    =======================================================================
    
    The environment details are :
    
    UNIX 4.0a - plus a couple of ADVFS patches
    Oracle 7.3.2.3
    
    System Details -
    Alpha Server 8200 4/233
    2 CPUs
    Memory 2 Gb
    Disk - heaps configured in 20 Gb controller based RAID sets
    Controllers - HSZ40 * 2
    Configured to use ADVFS for all Oracle database disks, write back cache
    is however disabled 
    
    Database Details -
    Asynchronous I/O enabled
    Size 60 Gig
    32k block size
    Multi_block_count = 2
    
    =======================================================================
    
    Scenario :
    
    A PL/SQL package is being used to perform data cleansing during DW 
    load.  The program does not do much read I/O, but has a high insert
    rate.  The PL/SQL package typically runs at 98-100% CPU utilisation,
    (i.e. all of one CPU), but it drops off significantly to 5-6% CPU 
    utilisation when the Oracle DB writer process fires up to clean up
    the dirty blocks in the SGA.  This has been identified during 
    system monitoring.  We have a theory that this has become noticed
    because we have turned off writeback cache (a long story but 
    necessary), and I/O has become a bottleneck when the DB writer is
    active. 
    
    =======================================================================
    
    Questions :
    
    Why does the Oracle DBwriter impact the execution of the PL/SQL 
    package, given that there is another CPU idle.
    
    Can Oracle be tuned so that the DBwriter performs activity in
    an asynchronous mode alongside the load process. 
    
    How does the Oracle DB writer work, does it take out a lock or
    latch when it performs its activity, to halt activity related
    to other processing. 
    
    =======================================================================
    
    Thanks in advance for any answers.
    
    				Colin...