Oracle Tips and Tricks — David Fitzjarrell

July 17, 2008

Over Extended

Filed under: General — dfitzjarrell @ 19:31

Autoextend can be very helpful to busy DBAs by allowing Oracle to automatically increase file sizes to a pre-determined limit when necessary which can make the workday a bit easier by minimizing the effort expended to monitor disk space. In a heavily used system where inserts and updates eclipse the deletes autoextend gives the DBA some “breathing room” so he/she can attend to more pressing issues. For those unfamilar with using autoextend we’ll show how to enable it and how to manage the available space.

Setting a file in a tablespace to autoextend is a fairly easy task as long as the user performing the actions has DBA or SYSDBA privileges:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2000M;

Database altered.

SQL>

Not every datafile or tempfile in a tablespace needs to be set to autoextend–you can select one or more datafiles/tempfiles and leave others unaltered. The size limit, if left unset in the ‘alter database’ command, is one byte shy of 32G, which is the same value set by Oracle by setting MAXSIZE to UNLIMITED for a database using an 8k blocksize. Larger values can be set if the blocksize of the database is 16k or greater.

You have autoextend on for one or more datafiles/tempfiles; how do you monitor the size? The DBA_DATA_FILES/DBA_TEMP_FILES views provide almost all of the necessary information (what’s missing is the db_block_size, and that’s fairly easy to find):

SQL> select file_name, bytes, maxbytes,
 2 increment_by*(bytes/blocks) "INCREMENT",
 3 maxbytes-bytes remaining,
 4 (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
 5 from dba_data_files
 6 where autoextensible = 'YES'
 7 /

FILE_NAME                     BYTES  MAXBYTES INCREMENT REMAINING EXTENSIONS
-------------------------- -------- --------- --------- --------- ----------
/d909/data/users01.dbf     52428800 314572800  10485760 262144000         25

SQL>
SQL> select file_name, bytes, maxbytes,
2 increment_by*(bytes/blocks) "INCREMENT",
3 maxbytes-bytes remaining,
4 (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
5 from dba_data_files
6 where autoextensible = 'YES'
7 /

FILE_NAME                                                     BYTES   MAXBYTES INCREMENT  REMAINING EXTENSIONS
------------------------------------------------------ ------------ ---------- --------- ---------- ----------
+DATA/pspqa09/datafile/system.274.764412479               734003200 3.4360E+10  10485760 3.3626E+10       3206
+DATA/pspqa09/datafile/sysaux.275.764412499               629145600 3.4360E+10  10485760 3.3731E+10       3216
+DATA/pspqa09/datafile/undotbs1.276.764412513             214958080 3.4360E+10   5242880 3.4145E+10       6512
+DATA/pspqa09/datafile/psp_data01.281.764412629          3221225472 3.4360E+10      8192 3.1138E+10    3801086
+DATA/pspqa09/datafile/psp_idx01.283.764412761            536870912 3.4360E+10      8192 3.3823E+10    4128766
+DATA/pspqa09/datafile/users.284.764412775                  5242880 3.4360E+10   1310720 3.4354E+10      26210

6 rows selected.

SQL>

Notice the current size, the maximum size, the incremental value, space remaining and the extensions available are reported, to give you an idea of how much room is left to allocate to your autoextend datafile. You could execute this query on a daily basis to see how quickly or slowly your datafile is growing, then plan for either modifying the maxsize or adding another datafile. And all of this presumes you have sufficient disk space for the remaining extensions to occur; monitoring autoextend tablespaces also means monitoring the disk space at the operating system level or from your ASM instance to ensure that you don’t “overextend” yourself. The queries shown above can be ‘automated’ with cron to report, on a set schedule, how disk space is being used. The report can even be emailed to you:

#!/bin/ksh
#
#
# Variables
#
USER="blorpo"
PASS="bleebo"
DB="mydb"

SQLPLS=`which sqlplus`
MAILLIST="bob@ed.com"
FILE="report/datafile_rpt_`date "+%m%d%Y"`.txt"

if [ ! -d ./report ]
then
 mkdir report
fi

$SQLPLS /nolog < EOF
connect $USER/$PASS@$DB
@report_qry $FILE
EOF

if [ -s $FILE ]
then
 mailx -s "Datafile Space Report" $MAILLIST < $FILE
fi

Simply put the queries in a script named report_qry.sql and spool the output to a file (using the &1 positional parameter) and you can generate a dated report on a schedule, having it emailed right to your desk. The report_qry.sql script would look like this:

set pagesize 0 feedback off linesize 4000 trimspool on termout off set echo off verify off

spool &1

select file_name, bytes, maxbytes, 
increment_by*(bytes/blocks) "INCREMENT",
maxbytes-bytes remaining, 
(maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
from dba_data_files 
where autoextensible = 'YES' 
/

select file_name, bytes, maxbytes,
increment_by*(bytes/blocks) "INCREMENT",
maxbytes-bytes remaining,
(maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
from dba_data_files
where autoextensible = 'YES'
/

spool off

Changing the report is simply changing the query or queries in report_qry.sql.

Speaking of ASM how would you monitor the remaining disk space? It’s a straightforward query against the V$ASM_DISKGROUP view:

SQL> select name, total_mb, free_mb, cold_used_mb, hot_used_mb, usable_file_mb
 2 from v$asm_diskgroup
 3 where name is not null
 4 /

NAME                           TOTAL_MB FREE_MB COLD_USED_MB HOT_USED_MB USABLE_FILE_MB
------------------------------ -------- ------- ------------ ----------- --------------
DATA                             102400   87964        14436           0          87964

SQL>

The above query can also be ‘automated’ using the example above.

What’s the difference between HOT_USED_MB and COLD_USED_MB? It has to do with the disk region where the data lies. ASM allows the DBA to implement Intelligent Data Placement; disk regions can be specified as HOT and ASM will leverage the disk geometry to find the fastest sectors to access and put often accessed data in those sectors to improve performance. By default a diskgroup is considered COLD; adding a HOT template to the diskgroup prepares it for disk reassignment:

SQL> ALTER DISKGROUP data ADD TEMPLATE datafile_hot
 2 ATTRIBUTE ( 
 3 HOT
 4 MIRRORHOT);

Diskgroup altered.

SQL>

This will work if the COMPATIBLE.RDBMS parameter is set to 11.2.0.0.0 or higher; if you happen to have both a 10g and 11g database using your 11.2 ASM instance executing the above command will display:

SQL> ALTER DISKGROUP data ADD TEMPLATE datafile_hot
 2 ATTRIBUTE ( 
 3 HOT
 4 MIRRORHOT);
ALTER DISKGROUP data ADD TEMPLATE datafile_hot
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.2.0.0.0 or higher

SQL>

For illustrative purposes we’ll presume we have the proper COMPATIBLE.RDBMS setting; the diskgroup is now prepared for this:

SQL> ALTER DISKGROUP data MODIFY FILE '+data/orcl/datafile/users.259.679156903'
 2 ATTRIBUTE ( 
 3 HOT
 4 MIRRORHOT);

Diskgroup altered.

SQL>

ASM will now manage your data (presuming you’re not using a storage array using striped volumes) and locate the ‘hot’ data in the HOT region. [Storage arrays with LUNs composed of stripes hide the disk geometry from ASM so it’s harder to determine where the HOT zone should be.]

Can you set a datafile or tempfile, already configured to autoextend, to have a maximum size? Certainly, and it’s done with the same command used to turn autoextend on with a maximum size. To change the setting for the file we set at the beginning of this post we would:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2200M;

Database altered.

SQL>

and, voila!, it will now extend to 2200 M instead of the 2000 M we originally set as its limit. This won’t succeed if you’re trying to set MAXSIZE smaller than the current file size and you have data that would be lost.

Of course one can always turn off autoextend:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend off;

Database altered.

SQL>

Over the years (and through many trenches) I’ve come to look at autoextend as a useful tool in a DBA’s belt, mostly because of improvements in storage technology and improvements in the autoextend mechanism. I don’t set all of the files in a tablespace to autoextend but having at least one configured to automatically expand can keep a busy DBA from receiving 2 AM calls because batch transasctions are failing due to a lack of space. Whether you use the filesystem or ASM to manage your storage autoextend can make DBA life easier (relatively speaking) so you can tackle that performance issue with the report which uses a 17-table, multi-million record join that absolutely, positively has to run in less than 2 minutes although it disables the use of every available index with function calls on indexed columns and a fistful of LIKE conditions which have wildcards on both ends. Did I forget to mention the ORDER BY?

At least, with autoextend, you might not run out of space in TEMP before it returns data.

Advertisements

5 Comments »

  1. Nice post on the controversial subject of autoextend.I normally use one datafile for autoextend as a safety valve and otherwise manually manage my datafiles.I haven't seen actual corruption from failed autoextensions. I'm wondering how common of an event that is.

    Comment by Kevin — September 19, 2009 @ 05:45 | Reply

  2. I have seen this happen once in my 21 years as an Oracle DBA and it was with an earlier release of Oracle (7.2). With the newer restrictions on how large a datafile can extend and with greater sizes available in disk media I doubt that any newer release of Oracle would behave in a similar manner (however there are systems with older file systems or file systems configured to older limits where this could still occur).

    Comment by d_d_f — August 14, 2010 @ 17:15 | Reply

  3. Thanks a lot for sharing!Just curious if you have any suggestions on the optimal size for maxzise of autoextend data files?

    Comment by Anonymous — September 1, 2010 @ 14:15 | Reply

  4. In 10gR2 the limit is just under 32G for the maxsize setting. Of course the value you assign to this setting is dependent upon the available space on the disks and the number of autoextend files you have on the array. As for an 'optimal' size I can't recommend one as each situation is different.

    Comment by d_d_f — September 1, 2010 @ 19:42 | Reply

  5. […] Oracle, since version 8.1.5, has provided a true temporary tablespace, utilizing sparse files (or, as Oracle has you declare them, tempfiles). [8i is the last release where a non-sparse file temporary tablespace can be created; in 9i and later releases attempting to create a temporary tablespace using datafiles will produce an error.] These files are, at the operating system level, markers for the maximum size these sparse files can attain. To prove this simply allocate a new tempfile to an existing temp tablespace and see what happens. The allocation takes almost no time and the file system usage does not increase, indicating that, on a UNIX/Linux system, the inode has been allocated and a marker has been set to restrict the size of the file to that specified in the alter tablespace command. [The df command incorrectly shows this file as the requested size; using du instead reveals the actual size of this tempfile (on Solaris, at least).] This file will not begin to increase in size until Oracle needs the additional temp space for transaction or query processing, at which time Oracle will allocate what it needs, then stop. Unless, of course, the underlying file system fills up before the allocation Oracle requested is fulfilled. Or if Oracle needs more space than you ‘allocated’ in the tempfile addition. This temporary tablespace configuration, which is locally managed, changes the allocation/usage mechanism DBAs were used to in prior versions of the database. Simply because segments are allocated in the temporary tablespace does not create a problem since extents, once allocated, can be reused by other processes and sessions, which prevents the tablespace from growing without bound allocating extents only a specific session can use. [I do not personally recommend using AUTOEXTEND for all files in any tablespace, especially in older releases of Oracle. As of 9.2 (I believe) the default MAXSIZE is 32G per file, which makes using autoextend much better than in releases prior to that. And that's a topic for another blog entry.] […]

    Pingback by TEMPORARY (Tablespace) Insanity? « Oracle Tips and Tricks — July 20, 2012 @ 15:53 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: