FOR UPDATE... or not FOR UPDATE, that is the question

Have you considered the impact of an unnecesary FOR UPDATE on your queries?

I had this database which suddenly rised the redo log generation rate, running on archive log mode (like every production database must be) it become a nightmare, trying to backup logs every hour or planning a huge space increase for the archive log destination filesystem.

Fortunately we keep track of every development that is promoted to production; our records showed that near the date a recent interface was released, the database increased the redo log activity. Then we proceeded to disect the suspicious code, realizing that the programmer coded 4 nested loops (on 4GL, given we have BaaN) and every loop was coded using, a presumably unnecesary SELECT...FOR UPDATE.

Those findings pointed the way of my research, since on a previous review of the process, every intermediate result table was turned to NOLOGGING, without any reduction on redo log creation, the only place where logging was taking place was the UNDO tablespace, a behavior that cannot be turned off. But I've to verify this theory... and find a solution to the puzzle.

I knew where to look for the information needed: the Oracle Data Dictionary views, then I opened the Oracle Database Reference for 10gR2, which is the 'fortunate' release of our test environment, given that our production is running 9R2, both under HP-Unix and showing exactly the same behavior.

These views helped to find useful information, and finally pinpoint the issue:
V$FILESTAT : if the UNDO tablespace was under heavy IO, it may be reflected here.
DBA_DATA_FILE : I needed to translate the file number to something meaningful.
V$UNDOSTAT : provided a 7 day meassure window that allowed to correlate heavy redo log generation with high UNDO activity.
V$EVENT_HISTOGRAM : histograms for every wait event.
V$SESSTAT : timed statistics by user session.
V$SYSSTAT : timed sytem statistics.
V$STATNAME : name of every statistic.

First I tried the following query and the resulting set confirmed the theory: at the very first place was located the datafiles for the UNDO tablespace.

 SELECT b.file_name, a.phywrts, a.phyblkwrt, a.writetim
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
ORDER BY a.phyblkwrt DESC

Then I built this test case to confirm if there was a difference between SELECT and SELECT..FOR UPDATE shown at redo log write.

SQL> create table test ( col1 varchar2(10) );

Table created

SQL> select sid from v$mystat where rownum = 1;
1609

1 row returned
SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13112
1 row selected

SQL> insert into test values ('World');

SQL> commit

SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13504
1 row selected
SQL> select * from test;

SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13504
1 row selected

SQL> select * from test for update;

redo size 14248
1 row selected

You may see that after issuing a simple SELECT there wasn't an effect in redo size, that effect was present modifying the query with the FOR UPDATE clause.

Explanation

Seems that SELECT...FOR UPDATE creates a copy of the information retrieved, which is stored at the UNDO tablespace. The undo tablespace logging behavior cannot be changed, therefore when the interface program was executed, there was m * n * p * q writes to the undo tablespace and therefore to the redo logs.

Results

After sending our recommendation to development to change their code and use simple SELECTs, the redo generation rate droped from 4 Gb to 500 Mb per hour, which is a considerable reduction.

Subscribe to Oracle Database Disected by Email

Poor's man Capacity Planning

Companies are struggling to follow the pace with IT governance standards; for instance, ITIL has released it's third delivery, doubling the number of 'books' documenting the guidelines.

On the other side, the IT operation is facing an explosive growth of business requirements and information flow that most of the time lends to insufficient resources, even if we planned ahead.

ITIL's Capacity planning intend to cover the broad range of IT operations, from document printing requests to server renovation or upgrade. I'll focus on ideas related to Database Capacity planning and related resources.

The Avalanche is here!
Yes, data growth is a headache and if you don't take actions in advance, you'll be buried by your data. Fortunately for us, this doesn't happen overnight and growth follows a pattern that will help you to forecast purchase of additional storage... or an eventual failure if nothing is done.

First you need to start collecting data for every one of your databases, consolidation of results may depend on your storage architecture, server assignation, business area or the grouping criteria of your choice.

This is very simple, you'll need to query the tablespace free and total space and store it in a table . If you have more than one database, its better to centralize information sending results to a repository. With oracle that is pretty straightforward: you'll need cron, sql*plus and sql*loader, just that.

The repository DB must be added to the local tnsnames.ora file, because sqlldr will access the repository using the username/password@database login form. You will need a table to store DB Name, Tablespace, Free Space, Used Space and the vital, Date of Sample.

You'll get the information from just three views of the Oracle Data Dictionary views: DBA_DATA_FILES, DBA_FREE_SPACE, V$PARAMETER. This is a sample of the query used.

SELECT p.value, 
       to_char(sysdate,'DD-MM-YYYY'),
       d.tablespace_name,
       NVL (a.BYTES / 1024 / 1024, 0),
       NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024)
FROM SYS.dba_tablespaces d,
     (SELECT tablespace_name, SUM (BYTES) BYTES
        FROM dba_data_files
    GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM (BYTES) BYTES
        FROM dba_free_space
    GROUP BY tablespace_name) f,
     v$parameter p
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
  AND p.name like '%instance%name%'

To be Continued... (growth patterns)

Subscribe to Oracle Database Disected by Email