Slow read-only


Once a tablespace is made read-only, then it seems reasonable that queries on any tables within that tablespace will be 'optimal', ie, beside ensuring that the SQL is well-formed, then not much can be done to improve performance. However, read-only tablespaces are probably most typically seen in DSS style environments, and a common process involves something along the lines of:

There is a vital step missing here. One the data is massaged, it may well be subject to delayed block cleanout concerns. If the tablespace is made read-only, whilst the cleanout cannot take place (since the blocks cannot be altered), there is still a significant overhead.

Example:

SQL> create table testrw tablespace users nologging
  2  as select * from sys.source$;

Table created.

SQL> update testrw set line = line, source=source;

132609 rows updated.

SQL> commit;

Commit complete.

SQL> alter tablespace users read only;

Tablespace altered.

Elapsed: 00:00:00.05

SQL> select count(*) from testrw;

  COUNT(*)
----------
    132609

Elapsed: 00:00:03.09
SQL> select count(*) from testrw;

  COUNT(*)
----------
    132609

Elapsed: 00:00:03.09
SQL> /

  COUNT(*)
----------
    132609

Elapsed: 00:00:03.09

So it would appear that 3.09 seconds is the best we can do - but this is only because we have not taken the time to clean the blocks before making the tablespace read-only. To fix this problem we need to visit all of the blocks...

SQL> alter tablespace users read write;

Tablespace altered.

Elapsed: 00:00:00.01
SQL> select count(*) from testrw;

  COUNT(*)
----------
    132609

Elapsed: 00:00:07.07   <=== More time taken as we clean the blocks
SQL> /

  COUNT(*)
----------
    132609

Elapsed: 00:00:02.06   <=== And now we get a performance gain
SQL> alter tablespace users read only;

Tablespace altered.

Elapsed: 00:00:00.05

SQL> select count(*) from testrw;

  COUNT(*)
----------
    132609

Elapsed: 00:00:02.06