Refresh of Autorefresh Cache Group Shows Excessive Wait on Resources

eDuring a refresh of a cache group with autorefresh, there can be excessive buffer busy waits, row lock waits, and deadlocks on updates in the Oracle database, which can negatively affect the throughput performance. When there are multiple deadlocks on updates in the Oracle database involving the autorefresh log tables, the following may appear in the support log:

Oracle native error code = 60, msg = ORA-00060: deadlock detected while waiting
for resource
An error occurred while preparing or executing the following Oracle sql 
statement: <some statement involving <cache admin user>.TT_##_#######_L  where 
the # is some number>

You can improve your performance by modifying the INITRANS and FREELISTS settings, which can affect the concurrent inserts into the autorefresh log table and internal maintenance of these tables. The application updating the base table that is being autorefreshed encounters a throughput performance hit when these settings are not appropriately configured.

You can automatically or manually manage these settings as follows:

Use ASSM tablespace, which automatically manages FREELISTS.
Manually adjust FREELISTS and INITRANS for the autorefresh log table on the Oracle database.

The following details how to manually modify INITRANS and FREELISTS for the autorefresh log table on the Oracle database:

Retrieve the name of the autorefresh log table that is on the Oracle database.

Under the cache administration user login, execute the SQL*Plus script cacheInfo.sql that lists the autorefresh change log table name, along with other items. The following example executes the cacheInfo.sql script that lists the autorefresh change log table name as tt_06_1216726_L, as shown in bold:

SQL> @cacheInfo.sql
*************Autorefresh Objects Information  ***************
Host name: syst
Timesten datastore name: /users/OracleCache/alone1
Cache table name: ORATT.ORDERS
Change log table name: tt_06_1216726_L 
Number of rows in change log table: 1
Maximum logseq on the change log table: 2
Timesten has autorefreshed updates upto logseq: 1
Number of updates waiting to be autorefreshed: 1
Number of updates that has not been marked with a valid logseq: 0
****************************
Host name: consyst
Timesten datastore name: /users/OracleCache/alone1
Cache table name: ORATT.ITEMS
Change log table name: tt_06_1279699_L
Number of rows in change log table: 7
Maximum logseq on the change log table: 0
Timesten has autorefreshed updates upto logseq: 0
Number of updates waiting to be autorefreshed: 5
Number of updates that has not been marked with a valid logseq: 5
****************************

Manually alter the table on the Oracle database. The following example uses the table from the previous example. This example alters the INITRANS and FREELISTS settings for the bar.tt_06_1279699_L table.
```
ALTER TABLE BAR.TT_06_1279699_L INITRANS 10;
ALTER TABLE BAR.TT_06_1279699_L STORAGE(FREELISTS 5);
or
ALTER TABLE BAR.TT_06_1279699_L MOVE STORAGE(FREELISTS 5);
```
Alter the INITRANS and FREELISTS settings for the index for this table, which have the same name as the autorefresh change log table with an additional "L" at the end of it. For example, the index for table bar.tt_06_1279699_L is bar.tt_06_1279699_LL.
These settings should be the same as what you set for the autorefresh change log table.
```
ALTER INDEX BAR.TT_06_1279699_LL INITRANS 10;
ALTER INDEX BAR.TT_06_1279699_LL STORAGE(FREELISTS 5);
```