Oracle read-only tablespaces and Performance Robert G. Freeman & Mike Ault

I have seen 5% to 10% improvements in overall performance of read-only tablespaces, but some say that this difference is too small to make it worthwhile.

Everything is relative, and there is a debate about he performance benefits of access via a read-only tablespace, and some have accused Oracle of perpetuating a myth about read-only tablespace performance. Historically, many Oracle articles have suggested that there is a performance improvement to read-only tablespaces:

Oracle10g documentation on read-only tablespace performance

The Oracle 10g Database Administrator's Guide also suggests that read-only transactions will have faster performance:  

For better performance while accessing data in a read-only tablespace, you might want to issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only.

A simple query, such as SELECT COUNT (*), executed against each table will ensure that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for Oracle to check the status of the transactions that most recently modified the blocks.

Robert Freeman - This suggests that "better performance" is related to delayed block clean-out. It has nothing to do with how the blocks are read in the future, except to insure that no delayed block clean-out has to occur.

Oracle Press on read-only tablespace performance

In the Oracle Press book, "Oracle Data Warehousing", the authors Corey and Abbey claim that Oracle read-only tablespaces use less system resources:

When a tablespace is in read-only mode, the resources required to manage concurrent access to its data are minimal. 

Robert Freeman - I think the first sentence is correct in the sense that the blocks are not changing. But this would be true for any non -changing table block, in any tablespace type. So, the distinguishing factor is not the tablespace type, it's the nature of the block in the tablespace.

The resources required to manage a read-only tablespace are less than those resources to manage a read-write tablespace, but the degree of difference is difficult to measure.

Robert Freeman - To me this implies a different code tree, and this is not correct. Also, as I've already pointed out, given table A with characteristics that make it eligible for placement in a read-only tablespace (that is, the records have no DML activity), given all other things being equal (IO distribution, having sole placement in the tablespace, etc) then the performance will be exactly the same. In other words, if Abbey is correct, a table that has sole occupancy in a read-only tablespace should perform faster selects than the same table that has sole occupancy of a read-write tablespace. This is not the case based on all the information, and the experimentation, that I have done.

One difference between the read-only and read-write is that the datafile headers are not updated in a read-only. This might have some *very small* performance impact, but I bet one could not even measure it.
 

Oracle Magazine on read-only tablespace performance

In this Oracle Magazine article by Erik Peterson we see that read-only tablespaces have less locking overhead:

Can the tablespaces where the partitions reside be made read-only? Keeping each partition on a separate tablespace allows you to make the tablespace read-only when no more changes will occur to that partition.

This reduces backups (see Tip 2 in "Backup-and-Recovery Strategy," below) and lock overhead, resulting in a 5- to 10-percent performance increase over writable tablespaces.

Robert Freeman - I agree read-only tablespaces reduce backups. The key to Erik's statement is the partitioning. It's the partitioning that does the trick with the locking, not the read-only tablespace. That's because, by virtue of partitioning, we group all the "no-change" records into one partition. Thus, all blocks in that partition have the same access pattern (read-only), so this reduces locking as a result of DML operations.

Other articles on read-only tablespace performance

In the article ?Riddles of read-only tablespaces?, Venkat Chandrasekar notes that read-only tablespaces have less overhead: 

A read-only tablespace has a lesser over head than an updateable tablespace.

By carefully segregating the static data on to a read-only tablespace, the DBA can minimize the maintenance problems of static data repository and improve the performance of the database.

Robert Freeman - Note how Mr. Chandrasekar prefaces what he says. If he segregated the static data in a separate read-write tablespace the results might be the same.

Perhaps we have a new myth here, but it is far from clear whether read-only tablespaces have faster performance.  As I find time, I'll do more research and share my finding on read-only tablespaces.

Mike Ault Benchmark's Read-only Tablespace Performance

I decided rather than do a knee jerk reaction as these folks have done I would do something radical, I would test the premise using an actual database.

For my test I selected a 2,545,000 row table in a tablespace that has been placed on a 7X1 disk RAID-5 array attached to a 3 Ghtz CPU Linux Redhat 3.4 based server running Oracle Enterprise Version 10.1.0.3. The query was chosen to force a full table scan against the table by calculating an average of a non-indexed numeric field.

The table structure was:

SQL> desc merged_patient_counts_ru

Name--------------------------------------Null?----Type
----------------------------------------- -------- ------------
SOURCE---------------------------------------------VARCHAR2(10)
PATIENT_NAME---------------------------------------VARCHAR2(128)
DMIS-----------------------------------------------VARCHAR2(4)
MEPRS----------------------------------------------VARCHAR2(32)
DIVISION_NAME--------------------------------------VARCHAR2(30)
MONTH----------------------------------------------VARCHAR2(6)
ICD9-----------------------------------------------VARCHAR2(9)
ICD9_CODE_DIAGNOSIS--------------------------------VARCHAR2(30)
ENCOUNTER_ADMISSION_DATETIME-----------------------DATE
ENCOUNTER_DISCHARGE_DATETIME-----------------------DATE
APP_COUNT------------------------------------------NUMBER
ADMIT_COUNT----------------------------------------NUMBER
ER_COUNT-------------------------------------------NUMBER

The SELECT statement used for the test was:

SQL> SELECT avg(app_count) FROM MERGED_PATIENT_COUNTS_RU;

AVG(APP_COUNT)
--------------
86.4054172

The explain plan and statistics (identical for both READ WRITE and READ ONLY executions) was:

Elapsed: 00:00:01.52

Execution Plan
----------------------------------------------------------
0---SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1102 Card=1 Bytes=3)
1 0--SORT (AGGREGATE)
2 1--PX COORDINATOR
3 2--PX SEND* (QC (RANDOM)) OF ':TQ10000' :Q1000
4 3--SORT* (AGGREGATE) :Q1000
5 4--PX BLOCK* (ITERATOR) (Cost=1102 Card=2545000 Bytes :Q1000 =7635000)
6 5--TABLE ACCESS* (FULL) OF 'MERGED_PATIENT_COUNTS_R :Q1000
U' (TABLE) (Cost=1102 Card=2545000 Bytes=7635000)


3 PARALLEL_TO_SERIAL
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
12----recursive calls
0-----db block gets
21024-consistent gets
19312-physical reads
0-----redo size
418---bytes sent via SQL*Net to client
512---bytes received via SQL*Net from client
2-----SQL*Net roundtrips to/from client
53----sorts (memory)
0-----sorts (disk)
1-----rows processed

The process used for testing was:

1. Set timing on
2. Set autotrace on
3. Gather explain plans and statistics for both READ WRITE and READ ONLY runs, allowing statistics to stabilize.
4. Set autotrace off
5. Set tablespace status to READ WRITE
6. Execute SELECT 15 times
7. Set tablespace status to READ ONLY
8. Execute SELECT 15 times

The results are shown in table 1.

Reading Read Only Read Write
1-------1.49------1.56
2-------1.45------1.49
3-------1.39------1.37
4-------1.45------1.5
5-------1.5-------1.55
6-------1.3-------1.46
7-------1.43------1.5
8-------1.42------1.46
9-------1.35------1.38
10------1.4-------1.43
11------1.47------1.5
12------1.49------1.46
13------1.44------1.33
14------1.43------1.47
15------1.45------1.56
Avg-----1.430667--1.468

Table 1: Results from Performance Test of READ-ONLY Tablespace

As can be seen from the table, the performance of the READ-ONLY tablespace was marginally better (1.4307 verses 1.468 seconds on the average) than the READ-WRITE tablespace.

Based on these results, the article was correct in the assertion that the performance of READ-ONLY tablespaces is better than that of READ-WRITE tablespaces for SELECT transactions. While the performance difference is only a couple of percent it is still a measurable affect.

Of course, in the real-world even a small percentage improvement could result in significant overall performance improvements.

The benchmark plan

We are planning a TPC-C benchmark test of the value of read-only tablespaces in an active OLTP RAC environment.  The test will have n-way table joins, with some tables in read-only tablespaces and others in updateable tablespaces (cross RS boundaries) with full tracing to tease-out the actual area where read-only tablespaces are faster.

Marginal differences matter

While the net reduction in overhead with read-only tablespaces is small (1.43 vs. 1.46), this can make a huge difference in busy databases.

In the sample below, we see over 1,000 disk writes per second and over 50,000 logical reads per second.  It is this type of system where a small reduction in system overhead can multiply into a significant performance gain.

 

Load Profile ~~~~~~~~~~~~ Per Second      Per Transaction
                          --------------- ---------------
Redo size:                     139,878.97          371.50
Logical reads:                  50,364.16          133.76
Block changes:                     907.73            2.41
Physical reads:                  6,034.92           16.03
Physical writes:                 1,070.95            2.84