0398-0400

Previous Table of Contents Next

Page 398

 . exporting table                    EXECUTABLES        0 rows exported . exporting table                          FALL5       30 rows exported . exporting table                           ITEM       64 rows exported . exporting table                            ORD       21 rows exported . exporting table                          PRICE       17 rows exported Export terminated successfully without warnings. 

Export Parameters That Affect Performance

Export has several parameters that can affect performance during the export and also during the import. Understanding these parameters and how they impact the system resources, a user 's ability to use the database, and your ability to successfully and quickly complete the export and import is important. Some of the parameters with the most impact on performance are discussed in this section.

COMPRESS

One of the most widely used parameters is the COMPRESS=Y option. Many people misunderstand what this does for them. Oracle writes out the export file, which contains table and index creation scripts. If COMPRESS=N, the current table storage clause remains intact, but if COMPRESS=Y, the storage clause of the table is altered to reflect one extent, the sum of all of the tables' extents, whatever its size . One extent is usually desirable.

Consider the following scenario. A 90MB table has three equal extents of 30MB each in a tablespace. The tablespace has three data files of 33MB each for a total of 100MB. Remember that an extent is a contiguous group of Oracle blocks. Because the extent must be contiguous, one extent can fit in each data file. If the table is exported using the parameter COMPRESS=Y and then imported, the import fails. The resulting import fails because an extent cannot span data files, and the tablespace has three data files. The export rewrote the storage clause of the table to be one initial extent. The size of the initial extent is calculated as the sum of all existing extents at the time of the export, whether or not they contain data.

At this point, you have two options available: Re-create the tablespace with one data file of 100MB or more, or create the table before loading the data using the original storage clause utilizing the three extents. If you create the table before importing data, specify the Import parameter IGNORE=Y; otherwise , Import will fail.

DIRECT

The direct option bypasses the SQL Command Processing layer. Direct reading of the data blocks can increase performance dramatically. Additional performance can be gained if the database is in direct read mode. Unfortunately, Direct Path Export cannot be used with new Oracle8 objects (object types, REFs, LOBs, files, VARRAYs, and nested tables). When exporting tables using the Oracle8 object types, only the table's definition is exported, not the data.

Page 399

BUFFER

The buffer is the amount of memory claimed by Oracle to perform the export. Typically, the larger the better (at least as far as Export is concerned ). Export does not complain if you set BUFFER greater than the amount of memory available. As always, Oracle takes what it can get.

INDEXES

If you export with INDEXES=Y, you have the capability of creating the required indexes without much trouble during Importbut you pay for it with additional import time. Instead of allowing Import to create the indexes for you, have Import write the index creation scripts so that you can run the script after the import completes. This enables you to focus on the data as it is importing. You might also find that during day-to-day operation, indexes are created in the most unlikely places. Creating the indexes after the import gives you time to reconsider their locations.

CONSISTENT

The CONSISTENT parameter can be a killer if you are not prepared. The idea is to export the consistent data together. The consistent data refers to tables that have references to other tables. If the tables are in use, you must export them together using the CONSISTENT parameter. It's best if this is only a small number of tables. The remainder of the tables are exported in a second export. During an export, Oracle reads tables one at time. Any changes that are applied to dependent tables might not be reflected in the export file. The CONSISTENT parameter is the only way to alleviate this. During the consistent export, Oracle accumulates all of the transactions on the tables and saves them to a rollback segment. Export then saves the transactions to the export file.

Using the CONSISTENT parameter can cause the rollback segment to grow very large if there are many transactions on the consistent tables. If you must use the CONSISTENT parameter, use it when the tables to be exported have minimal inserts and updates. Your export can fail because of a rollback segment that is too small or the dreaded "snapshot too old" message.

Probably the safest move is to test the export of your consistent tables to see what happens. This might seem crude, but you will discover what your rollback requirements are for the future.

STATISTICS

Using the STATISTICS option does not slow the export, but it dramatically slows the import while it computes or estimates statistics. You shouldn't use the STATISTICS parameter on any sizable tables. It is almost always better to estimate or compute the statistics after the import is complete. Your time is better spent concentrating on the import rather than gathering the statistics.

Page 400

LOG

The log file creates a record of what happened during the export. When you view the log file, you notice that data in the file looks just like what was echoed to the screen during the export. If you have problems, the log file logs exactly what object failed to export and provides an error message describing why. The log file can be of great help in finding exactly what the problem was and directing you toward a possible fix. If everything goes well during the export, you don't need the log file.

Exporting Tablespaces

As you probably noticed, you can export by owner and you can export individual tables, but there is no option to export entire tablespaces in a database. This is one of the most needed maintenance functions of the DBA as users or applications increase their space needs. A DBA should monitor tablespaces in the database and react proactively to prevent any problems related to sizing. Unfortunately, to export a tablespace, the DBA must do quite a bit of database research to find all the tables or table owners in a tablespace.

For UNIX users, we provided a nice shell script to do just what you need. It's a big help in maintenance efforts. It is easy enough to use that you have our night operators perform the required exports. The shell script has a few requirements to work properly. In short, you must be able to access DBA_TABLES, and you must have the role EXP_FULL_DATABASE. Using the script is very easy.

Enter the following from the command line of UNIX:

 export_ts tablespace name, tablespace name, tablespace name ... 

How It Works

The script queries the database for all the tables in the tablespace to be exported and then proceeds to build a parameter file for Export to use. The script calls exp using the parameter file as one of the export parameter options (PARFILE=export_ts.par). The parameter file script created is named export_ts.par, and the export file is named export_ts.dmp. The script also creates a file it uses internally, export.spl. You usually do not need to be concerned with this file.

Currently, you must run the export_ts script only once for a single tablespace. You could easily modify the script to ask for the name of the export file, thus enabling you to make multiple exports before importing any data:

 EXPORT_TS #! /bin/sh ########## ##### #          export_ts # # UNIX Shell script to export all Oracle tables and indexes from a tablespace. 

Previous Table of Contents Next