Database Reference
In-Depth Information
SnapID SnapID Start Time End Time Interval (s) DB time/s
------ ------ -------------- -------------- ------------ ---------
83 84 06.09.07 17:04 06.09.07 17:09 348 .92
49 50 05.09.07 07:45 05.09.07 08:00 850 .02
25 26 25.07.07 19:53 25.07.07 20:00 401 .01
The highest relative DB time occurred in the interval between snapshots 83 and 84.
Importing Statspack Data from Another Database
As stated earlier, it may be desirable to import Statspack data from a database into a different
database for analysis. Let's say that the schema PERFSTAT of a production database is exported
once per month, backed up to tape, and then the Statspack tables are truncated to conserve
space. The deleted snapshots may be imported into another database, should the need arise to
investigate past snapshots, for example to retrieve last month's execution plan of a particular
statement. Obviously, the production database cannot be the target of the import, since this
might interfere with the ongoing snapshot capture process.
The procedure shown next takes into account that the Statspack table
STATS$IDLE_EVENT
might contain additional wait events that were missing in a particular Statspack release. The
brute force approach of dropping all tables owned by PERFSTAT and letting import create
them would remove this customizing. This is why the approach shown next does not drop any
of the Statspack tables. Instead, it disables referential integrity constraints, truncates the tables
with
sptrunc.sql
,
13
and uses the import setting
IGNORE=Y
to import data into existing tables.
As a starting point, a test database where Statspack has been installed with
spcreate.sql
is required. The version of Statspack installed must match the version contained in the export
dump. Any automatic snapshot captures should be disabled. First of all, existing snapshots
need to be removed from the Statspack repository by running the script
sptrunc.sql
.
$ sqlplus perfstat/secret @sptrunc
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you would like to continue, press <return>
Enter value for return:
Entered - starting truncate operation
Table truncated.
…
Truncate operation complete
SQL> EXIT
13. The script
sptrunc.sql
does not truncate the table
STATS$IDLE_EVENT
.
