Re: gc buffer busy and rac testing

> Not what I'd hope to see in the simplest case - unless the "simple query"
> is a select for update. Mladen's comments about the overheads of
> generating read-consistent blocks may be relevant though if the query
> is very busy reading blocks which are subject to lots of rapid update
> on the remote node.
>

   SELECT PRODUCTS.PRODUCT_ID,

            PRODUCT_NAME,
            PRODUCT_DESCRIPTION,
            CATEGORY_ID,
            WEIGHT_CLASS,
            WARRANTY_PERIOD,
            SUPPLIER_ID,
            PRODUCT_STATUS,
            LIST_PRICE,
            MIN_PRICE,
            CATALOG_URL,
            QUANTITY_ON_HAND
     FROM   PRODUCTS, INVENTORIES
    WHERE   PRODUCTS.CATEGORY_ID = :B1
            AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID

Yes, it's similar.

Here are more detailed stats:

(Gc buffer busy delta)

OBJECT                    STAT
SOE.INVENTORIES [TABLE]            1923481
SOE.ORDER_PK [INDEX]            100771
SOE.ORDER_ITEMS_UK [INDEX]        75944
SOE.ORDER_ITEMS_PK [INDEX]        72561
SOE.ORDERS [TABLE]            47162
SOE.ORD_STATUS_IX [INDEX]        35295
SOE.ITEM_ORDER_IX [INDEX]        12457
SOE.CUSTOMERS_PK [INDEX]        10428

(GC_CR_BLOCKS_RECEIVED_DELTA)

OBJECT                         STAT
SOE.INVENTORIES [TABLE]            426794
SOE.ORD_CUSTOMER_IX [INDEX]        41028
SOE.ORD_STATUS_IX [INDEX]        32213
SOE.ITEM_ORDER_IX [INDEX]        22916
SOE.ORDERS [TABLE]            20142
SOE.ORDER_PK [INDEX]            17339
SOE.ORDER_ITEMS [TABLE]            13218
SOE.CUSTOMERS_PK [INDEX]        960
SOE.CUSTOMERS [TABLE]            379
SOE.ORDER_ITEMS_PK [INDEX]        293

GC_CR_BLOCKS_RECEIVED_DELTA)

OBJECT                    STAT
SOE.INVENTORIES [TABLE]            125316
SOE.ORD_STATUS_IX [INDEX]        57368
SOE.CUST_EMAIL_IX [INDEX]        47218
SOE.CUST_UPPER_NAME_IX [INDEX]        46755
SOE.ORD_ORDER_DATE_IX [INDEX]        43553
SOE.ORD_CUSTOMER_IX [INDEX]        43294
SOE.ORDERS [TABLE]            32213
SOE.ITEM_ORDER_IX [INDEX]        31624
SOE.ITEM_PRODUCT_IX [INDEX]        27386

> It's also worth checking the 'Global cache transfer stats' looking at
  the
> 'busy' column, as this will give you some clues about whether the
> problem blocks were data blocks, undo blocks, or "other".

Avg global enqueue get time (ms): 7.1 Avg global cache cr block receive time (ms): 7.3 Avg global cache current block receive time (ms): 10.5 Avg global cache cr block build time (ms): 0.0 Avg global cache cr block send time (ms): 0.0 Global cache log flushes for cr blocks served %: 19.0 Avg global cache cr block flush time (ms): 11.9 Avg global cache current block pin time (ms): 2.1 Avg global cache current block send time (ms): 0.0 Global cache log flushes for current blocks served %: 5.2 Avg global cache current block flush time (ms): 22.0

Buffer Wait Statistics

Class    		Waits    Total Wait Time (s)    Avg Time (ms)
data block    		 609,973     27,470     	45
undo block     		102,528     1,478     		14
undo header     	58,156         450     		8
file header block         54     	2     		31
1st level bmb     	14        	 0     		19
2nd level bmb     	14        	 0     		9
segment header     	3       	  0     	3