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.
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.
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).
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.
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.
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.