Spreading Out Data – Old School Methods

In the previous post in this series we introduced a crude test case in which we had data packed in to a single block which was causing severe buffer contention. To re-cap – here is a chart of ASH data demonstrating the issue, the large grey stripe is time wasted on global cache contention.

Contention  11g normal heap table

This post is to cover some “old school” methods that can be used to spread out the data and hopefully reduce contention. I have purposefully ignored the possibility of a design flaw in the code, for the purposes of this series we are assuming the code is in good shape.

Node Affinity

Nothing to do with spreading data out but an obvious remedy for global cache contention by managing where the workload runs. I’m not going to discuss this too much as it doesn’t protect us from “buffer busy waits” within a single instance, however in some cases node affinity would be the correct solution. I have included a chart from the same test case as before but usilising only a single RAC node. You can see that the global cache contention (grey) has gone but we do still have a little “buffer busy wait” contention (red).

Contention 11g nodeaffinity

PCTFREE

Oracle documentation: PCTFREE

This option doesn’t really need any explanation but here we create the test table with the clause “PCTFREE 99” and reload the data.

create table procstate
(    
...
) pctfree 99;

And the data is now spread out.

select dbms_rowid.rowid_block_number(rowid) blockno
, count(*)
from procstate
group by dbms_rowid.rowid_block_number(rowid);

   BLOCKNO   COUNT(*)
---------- ----------
    239021          2
    239040          2
    239022          2
    239020          2
    239019          2
    239023          2

The test case row size is quite small, approximately 0.5% of the free space in a block, so we are left with two rows in each block. Looking at ASH data from the test case shows that contention is much reduced, a nice solution.

Contention 11g pctfree

Single Table Hash Cluster

Oracle documentation: Single Table Hash Cluster

This option didn’t occur to me initially. I tend to avoid Hash Clusters but when I presented on this topic at UKOUG Tech13 a leading light in the Oracle Community suggested this would also be a good solution. So here it is.

The test table has been created in a Hash Cluster stating the size of a cluster of records as 8000 bytes. This is a cheat to ensure each cluster of records ends up in its own block. We are clustering on the primary key column so each block will contain only a single row.

create cluster procstate_cluster
(proc_id number(3)) 
size 8000 single table hashkeys 100;

create table procstate
(
...
,	
)
cluster procstate_cluster(proc_id);

Before loading the data we already have 110 empty blocks. This is because we have configured the cluster to have 100 hash keys and, as stated above, ensured each key maps to a different block. Oracle then rounds up the number of cluster keys to the next prime number (109) and the extra block is the table header.

    BLOCKS
----------
       110

After loading the test data we see there is only a single row in each block.

   BLOCKNO   COUNT(*)
---------- ----------
    172398          1
    172361          1
    239067          1
    172402          1
    172365          1
    239079          1
    172389          1
    172352          1
    239075          1
    172356          1
    239071          1
    172394          1

Running the test case again shows the contention has vanished completely. All sessions spend their time on CPU doing useful work.

Contention 11g Hash Cluster

Another nice if somewhat obscure solution.

Minimize Records Per Block

Speaking of obscure solutions we come to the “MINMIZE RECORDS_PER_BLOCK” clause, we’ll save that for the next installment as I have quite a lot of material to cover.

Advertisements

Sometimes we don’t need to pack data in, we need to spread it out

I’ve gotten away with doing a presentation called “Contentious Small Tables” (download here) for the UKOUG three times now so I think it’s time to retire it from duty and serialise it here.

The planned instalments are below, I’ll change the items to links as the posts are published. The methods discussed in these posts are not exhaustive but hopefully cover most of the useful or interesting options.

  1. Sometimes we don’t need to pack data in, we need to spread it out (this post)
  2. Spreading out data – old school methods
  3. Spreading out data – with minimize records per block (also old school)
  4. Spreading out data – some partitioning methods
  5. Spreading out data – some modern methods

This post is the first instalment.

Sometimes we don’t need to pack data in, we need to spread it out

As data sets continue to grow there is more and more focus on packing data as tightly as we can. For performance reasons there are cases where we DBAs & developers need to turn this on its head and try to spread data out. An example is a small table with frequently updated/locked rows. There is no TX blocking but instead contention on the buffers containing the rows. This contention can manifest itself as “buffer busy waits”, one of the “gc” variants of “buffer busy waits” such as “gc buffer busy acquire” or “gc buffer busy release” or maybe on “latch: cache buffers chains”.

You’ll probably come at a problem of this nature from a session perspective via OEM, ASH data, v$session_event or SQL*Trace data. Taking ASH data as an example you can see below that for my exaggerated test, global cache busy waits dominate the time taken and, key for this series of posts, the “P1” and “P2” columns contain a small number of values.

column event format a35
select * from (
 select NVL(event,'CPU') event
 , count(*) waits
 , p1, p2
 from gv$active_session_history
 where sample_time between 
       to_date(to_char(sysdate,'DD-MON-YYYY')||' &from_time.','DD-MON-YYYY HH24:MI')
   and to_date(to_char(sysdate,'DD-MON-YYYY')||' &to_time.','DD-MON-YYYY HH24:MI')
 and module = 'NJTEST'
 group by NVL(event,'CPU'), p1, p2
 order by 2 desc
) where rownum <= 5;

EVENT                   WAITS    P1      P2
---------------------- ------ ----- -------
gc buffer busy acquire   1012     5  239004
gc buffer busy release    755     5  239004
gc current block busy     373     5  239004
CPU                        65     0       0

For the wait events above “P1” and “P2” have the following meanings:

select distinct parameter1, parameter2
from v$event_name
where name in ('gc buffer busy acquire'
              ,'gc buffer busy release'
              ,'gc current block busy');

PARAMETER1     PARAMETER2
-------------- --------------
file#          block#

So above we have contention on a single buffer. From ASH data we can also get the “SQL_ID” or “CURRENT_OBJ#” in order to identify the problem table. Below we show that all rows in the test table are in a single block – 239004.

select dbms_rowid.rowid_block_number(rowid) blockno
, count(*)
from procstate
group by dbms_rowid.rowid_block_number(rowid);

   BLOCKNO   COUNT(*)
---------- ----------
    239004         12

After discovering this information the first port of call is to look at the SQL statement, execution plan and supporting code but assuming the application is written as best it can be then perhaps it’s time to look at the segment and data organisation.

In order to help illustrate the point of this series here is a chart showing contention in ASH data from a crude test case. The huge grey stripe being “gc” busy waits and the thin red stripe being standard “buffer busy waits”.

Contention  11g normal heap table

The next post in this series will look at some “Old School” methods of reducing this contention.