Temporary Tablespaces: to BIGFILE or not to BIGFILE
Dec 5, 2012 / By Marc Fielding
I saw a question on the OTN Exadata forum asking whether to use BIGFILE temporary tablespaces in an Exadata system. BIGFILE tablespaces have been around since at least Oracle 10gR2, and extend the 32G datafile size limit, but restrict to a single datafile per tablespace. Effectively, it trades the simplified management of a single datafile at a cost of reduced flexibility.
The default Exadata database “dbm” comes with a single BIGFILE tablespace, and I’ve found this approach to work well in most cases. However, the OTN question reminded me of a situation I encountered last year where we couldn’t use BIGFILE tablespaces for performance reasons, and I thought I’d share some of the details with Pythian blog readers.
The environment was an Exadata environment undergoing pre-production stress testing. We used Real Application Testing to take a highly-concurrent OLTP workload, and replayed the workload with the
synchronization parameter set to
FALSE, effectively increasing concurrency beyond the original test system.
AWR showed a large volume of buffer busy activity:
Top 5 Timed Foreground Events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg wait % DB Event Waits Time(s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ------------- gc buffer busy release 110,700,450 12,544,451 113 58.9 Cluster buffer busy waits 112,565,811 3,950,102 35 18.5 Concurrency latch: cache buffers chains 113,162,321 2,335,055 21 11.0 Concurrency enq: SS - contention 20,026,343 1,208,422 60 5.7 Configuration gc buffer busy acquire 11,244,505 720,003 64 3.4 Cluster
To drill down, I used ASH (active session history) information. Given the raw volume of sessions and data, though, querying dba_hist_active_sess_history is very slow. I therefore took advantage of Exadata’s hybrid columnar compression feature to create a smaller, flattened, and compressed work table containing observations from the RAT replay period. The resulting table is much faster to query.
create table marc_ash nologging compress for query high parallel 128 as select /*+parallel(128)*/ * from dba_hist_active_sess_history where snap_id between 384 and 423;
Identifying the P1, P2, and P3 parameters of the top wait events to see where the waits are happening, as well as how much time is spent in each combination. This is probably a good time for me to mention my standard disclaimer regarding dba_hist_active_session_history: the data is composed of samples of the system state, so it doesn’t have the same granularity as, say, session traces. But with a large sample like I have here, we can still get a good idea of what’s going on.
select event,p1, p2, p3, count(*), sum(tm_delta_time) from marc_ash group by event,p1,p2,p3 order by sum(tm_delta_time) desc; EVENT P1 P2 P3 COUNT(*) SUM(TM_DELTA_TIME) --------------------------- ------------ --- --- -------- ------------------ gc buffer busy release 501 2 13 2336685 57421543417904 buffer busy waits 501 2 13 736241 16902873986356 latch: cache buffers chains 262188190496 155 0 260182 6197021780073 latch: cache buffers chains 230512806688 155 0 217646 5831016580939 enq: SS - contention 1397948422 41 2 183510 2378750008606 gc buffer busy acquire 501 2 13 130649 3265344853224
What’s interesting here is that a common set of parameters comes up in buffer busy waits: P1 = 501, P2 = 2, P3 = 13. P3 lists the block type, and there’s a great summary of values on Charles Hooper’s blog. 13 maps to “file header block”.
P1 is the file number. The number 501 is above my DB_FILES value of 500, indicating that it’s a tempfile, and in this case, the one very large BIGFILE of the TEMP tablespace. P2 is the block number. According to MOS note 109630.1, block 2 is the space management bitmap in a locally-managed tablespace.
My Oracle Support note 465840.1 has some good information about addressing tempfile contention in RAC. It mentions that “buffer busy wait” events on the file header block can be encountered in RAC as each instance allocates space in the same tempfile. It recommends creating at least as many tempfiles as there are instances in the cluster. To accommodate multiple files in a tablespace, we must create a SMALLFILE tablespace, and work around the 32G file limit. In our case, it means creating a new temporary tablespace with 24 32G tempfiles for a total capacity of 768GB, changing the default temporary tablespace, and dropping the old one.
Flashing back the database and re-running the same RAT workload, the waits on tempfile 501 and the SS enqueue disappeared entirely.
What are your experiences, blog readers? Do you normally use BIGFILE temporary tablespaces? Have you encountered similar issues?
13 comments on “Temporary Tablespaces: to BIGFILE or not to BIGFILE”
Pingback: Log Buffer #298, A Carnival of the Vanities for DBAs | The Pythian Blog