21.4 Other Utilities and Tools

---

21.4.1 Import, Export, and SQL*Loader

The Export and SQL*Loader utilities can be executed in DIRECT mode. DIRECT mode implies that the SQL engine, executing INSERT statements, is completely bypassed and data is appended directly to datafiles on disk. DIRECT mode is much faster than passing through the SQL engine. Import and Export have a parameter called BUFFER. The BUFFER parameter limits chunks of rows processed. In very busy environments using the BUFFER parameter can limit the impact on other database services.

Tip 

Do not use the Import and Export Utilities for Backup and Recovery. Generally only use Export to copy individual static tables you are absolutely sure will not change. Additionally using a previously FULL Export backup file to Import and recreate a lost database is incredibly inefficient. I have seen a 200 Gb database reconstruction using a FULL database Export take an entire weekend to complete. This particular database was apparently destroyed by a junior DBA executing a script to drop all users in cascade mode, by mistake of course. Oops!

Note 

 Oracle Database 10 Grid   Data Pump Import and Data Pump Export have much better performance than the Import and Export utilities. Data Pump allows parallel execution, is self-tuning, and does not require compression into a single extent.

SQL*Loader is an excellent tool for loading data into a database. SQL*Loader is very easy to use and can map data files in text format directly into database tables. Use SQL*Loader as an alternative option to that of loading tables using large quantities of INSERT statements, it can give performance increases in the thousands. Use SQL*Loader as it is very easy to learn to use.

Note 

 Oracle Database 10 Grid   SQL*Loader can now cache dates reducing conversions on duplicated date values.

21.4.2 Resource Management and Profiling

Resources can be managed and effectively shared in Oracle Database to create round-robin-type queues. Hardware resources such as CPU time can be evenly distributed across multiple profiles.

Profiles contain groups of users or functionality sets. Different activities can be given priority at different times of day. Perhaps OLTP activity can be favored during the daytime and backup or batch processing activities favored after-hours. This of course assumes that you do not have to maintain a constant level of 24 × 7 database availability. There is a small amount of resource overhead with respect to Oracle Database internal implementation and execution of resource management.

21.4.3 Recovery Manager (RMAN)

RMAN or Recovery Manager is a slightly more power-user-oriented backup utility when compared to using backup mode tablespace datafile copies. However, RMAN simplifies backup and recovery processing and DBA involvement. Additionally RMAN can be executed in parallel.

21.4.4 STATSPACK

People have written and published entire books about STATSPACK. STATSPACK is a comprehensive statistics monitoring and tuning analysis tool, the next generation of the UTLBSTAT.sql and UTLESTAT.sql tuning scripts. In addition STATSPACK can be used to store statistical information in the database in a special repository for later comparison and analysis between different statistics set collections. Therefore, when a performance problem occurs a current snapshot can be compared against a previously obtained baseline snapshot, allowing for easy comparison and thus rapid diagnosis of the problem.

Tip 

STATSPACK is useful for analysis and detection of bottlenecks but using the Oracle Database Wait Event Interface and the Capacity Planner in Oracle Enterprise Manager is better. The Oracle Database Wait Event Interface will be covered in the next chapter, along with some use of STATSPACK.

STATSPACK is very easy to install, configure, and to use. The problem with it is that it produces enormous quantities of what could amount to superfluous information. Wading through all the mounds of information produced by STATSPACK could be somewhat daunting to the tuning novice or someone who is trying to solve a problem in a hurry. Large amounts of information can even sometimes hide small things from an expert. The expression "trying to find a needle in a haystack" comes to mind.

STATSPACK is more useful for general performance tuning and analysis as opposed to attempting to find specific bottlenecks. The Oracle Database Wait Event Interface in Oracle Enterprise Manager is much more capable than STATSPACK in rapidly isolating problems.

To use STATSPACK a specific tablespace must be created:

CREATE TABLESPACE perfstat DATAFILE       'ORACLE_HOME/<SID>/perfstat01.dbf'       SIZE 25M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED       EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT          AUTO;

Do not create a user because the scripts will crash! Additionally you might want to set the ORACLE_SID variable if your database server has multiple databases. The following script will create STATSPACK goodies.

@ORACLE_HOME/rdbms/admin/spcreate.sql;

If the installation completely freaks out, the following script will drop everything created by SPCREATE.SQL so that you can start all over again.

@ORACLE_HOME/rdbms/admin/spdrop.sql;

Once installed take a snapshot of the database by executing these commands in SQL*Plus.

CONNECT perfstat/perfstat[@tnsname]; EXEC STATSPACK.SNAP;

The DBMS_JOBS package can be used to automate STATSPACK SNAP procedure executions on a periodical basis, as in the example script shown overleaf, which executes every 5 min. Different snapshot levels can be used between 0 and 10. 0 is not enough, 10 far too much, 5 is the default and 6 provides query execution plans. Since the large majority of performance problems are caused by poorly written SQL code I would recommend starting at snapshot level 6.

Snapshot levels are as follows:

• 0:   simple performance statistics.

• 5:   include SQL statements, which is the default level.

• 6:   include SQL plans.

• 7:   include segment statistics.

• 10:   include parent and child latches.

  Tip 

  Running STATSPACK will affect performance so do not leave it running constantly.

DECLARE  jobno NUMBER;  i INTEGER DEFAULT 1; BEGIN  DBMS_JOB.SUBMIT(jobno,' STATSPACK.SNAP(I_SNAP_LEVEL=>6);'        ,SYSDATE,'SYSDATE+1/288'); COMMIT; END; /

Remove all jobs using an anonymous procedure such as this.

DECLARE       CURSOR cJobs IS SELECT job FROM user_jobs; BEGIN       FOR rJob IN cJobs LOOP             DBMS_JOB.REMOVE(rJob.job);       END LOOP; END; / COMMIT;

Run a STATSPACK report using the following script. The script will prompt for two snapshots to execute between, comparing sets of statistics with each other.

@ORACLE_HOME/rdbms/admin/spreport.sql;

An SQL report can be executed on one SQL statement, searching for a bottleneck, where the hash value for the SQL code statement is found in the STATSPACK instance report.

@ORACLE_HOME/rdbms/admin/sprepsql.sql; 

There are a number of other "SP*.SQL" STATSPACK scripts used for various functions. Two of those scripts are SPPURGE.SQL and SPTRUNCATE.SQL. Purging allows removal of a range of snapshots. If the database is bounced then snapshots cannot be taken across the database restart. Truncate simply removes all STATSPACK data.

STATSPACK can also have threshold value settings such that characteristics below threshold values will be ignored. Default STATSPACK parameter settings including threshold values are stored in the STATS$STATSPACK_PARAMETER table.

This is enough information about physical tuning tools and use of utilities. The next chapter will look at physical and configuration tuning from the perspective of finding bottlenecks using the Oracle Database Wait Event Interface.

---