Previous | Table of Contents | Next

Page 506

Listing 21.1 Performing Load Tests to Obtain Rollback Estimates
REM
REM Create the set up tables
REM
create table stats$undo_begin (writes number);
create table stats$undo_end   (writes number);
REM
REM Capture the initial number of write for rollback seg RO1.
REM Replace R01 with the name of your rollback segment.
REM
insert into stats$undo_begin
     select sum(writes) from v$rollstat
     where usn = ( select usn from v$rollname where
                 name = `RO1');
REM
REM Alter the current session so that it uses the test rollback
REM segment only.  Replace R01 with the name of your rollback segment.
REM
commit;
set transaction use rollback segment RO1;
REM
REM Run the long running batch job which generates largest undo data.
REM Replace the name of your batch job script with `large_batch_job'
REM
@large_batch_job;
REM
REM Capture the writes generated at the end of the batch job.
REM Replace R01 with the name of your rollback segment.
REM
insert into stats$undo_end
     select sum(writes) from v$rollstat
     where usn = ( select usn from v$roll_name where
                 name = `RO1');
REM
REM Get the amount of data that has been generated as redo for the
REM transaction under tesst
REM
select (e.writes - b.writes ) undo_generated
     from stat$undo_begin b, stat$undo_end e;
UNDO_GENERATED
        898998

In the output shown in Listing 21.1, the undo generated by the batch job is 898998 bytes. This script can be used as a starting point to obtain initial estimates, which can be used to size rollback segments.

Identifying Storage Problems

Once a database is up and running, we sometimes need to revisit the issue of how large each major component of the database is. Of primary concern is making sure that tablespaces are large enough to hold the segments they contain. Tables, indexes, and clusters all grow over

Page 507

time, and we need to make sure that an INSERT or UPDATE will not fail during production hours because the tablespace is out of room.

When we discuss the space allocated for a segment, we are referring to the space reserved for a segment. This does not imply that the segment is actually using all the space (although, it is almost always using some portion of it). For example, suppose we say that table ABC has 90MB allocated to it in two extents of 50MB and 40MB, respectively. Table ABC is actually using between 50MB and 90MB. Once the first extent is exhausted, the NEXT parameter in the table definition dictates that another 40MB would be allocated even if only 10KB is actually needed. ABC would not need to look for more space until the remaining 39.99MB were filled. While this design may seem somewhat absurd to some DBAs not familiar with a mainframe data processing environment, it is a key element in Oracle's performance characteristics.

DBAs using Oracle's Enterprise Manager will find the job of assessing their database's current storage situation much easier than their command-line counterparts. Storage Manager is the portion of the Enterprise Manager suite that provides a GUI interface to the most commonly needed storage information. Figure 21.10 shows a sample window of Storage Manager.

FIG.21.10
An example of Oracle's
Storage Manager.

Your database need not reside on a Windows NT machine to use Storage Manager. Oracle's SQL*Net networking component enables a Windows system running Storage Manager to work with databases on remote machines. The remote machines may reside on a local or wide area network and may be any Oracle supported platform.

Like quite a few of Oracle's Windows-based GUI tools at the time of this writing, experienced DBAs will find Storage Manager to be missing several important capabilities needed for effective storage management. Storage Manager is a useful tool simplifying many day-to-day tasks, but it is no substitute for the command-line interface.

Page 508

Exploring Tablespaces

Our exploration of the database tablespaces usually involves deciphering the total size of the tablespace, as well as what datafiles are allocated to the tablespace. The DBA_DATA_FILES table contains all the relevant information about datafiles. Because a tablespace's size is the sum of its datafile parts, we can determine a tablespace's size from the DBA_DATA_FILES table also.

Here's an example:

COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A35
SELECT TABLESPACE_NAME, FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
TABLESPACE FILE_NAME                                BYTES STATUS
---------- ----------------------------------- ---------- --------
RBS        /opt/oracle/dbs/space2/rbs1test.dbf   20971520 AVAILABLE
RBS        /opt/oracle/dbs/space2/rbs2test.dbf   20971520 AVAILABLE
SYSTEM     /opt/oracle/dbs/space1/sys1test.dbf   20971520 AVAILABLE
TEMP       /opt/oracle/dbs/space4/tmp1test.dbf   10485760 AVAILABLE
USERS      /opt/oracle/dbs/space5/usr1test.dbf   20971520 AVAILABLE

We see a row for each datafile in the database, which shows the tablespace it belongs to, its size (in bytes), and its status. The status field should always be AVAILABLE.

We often want to check on our tablespaces to make sure we have plenty of free space available, in case a segment needs to grow. This is not as simple a question as it would at first seem. In order to answer this question, we need to know the following:

You need to start with how large a tablespace is. Totaling the sizes of associated datafiles provides us with that fairly easily. For example:

     SELECT TABLESPACE_NAME,SUM(BYTES) FROM DBA_DATA_FILES GROUP
BY TABLESPACE_NAME

TABLESPACE SUM(BYTES)
---------- ----------
RBS          41943040
SYSTEM       20971520
TEMP         10485760
USERS        20971520

The next problem is to determine how much free space, in aggregate, is in the tablespace. Oracle keeps a list of all free extents in the database in the DBA_FREE_SPACE table. By totaling the sizes of free extents in our tablespace of interest, we see how much free space remains. This example shows the amount of free space in each tablespace in the database.

Previous | Table of Contents | Next