Get the App
SLTechnology News&Howtos  ›  Database  › 

Oracle 11G uses statspack to view the performance of the database

Shulou Source: shulou.com Published: 2022-06-01 18:52:37 11月16日 Update

1. Install the Statspack tool (the scripts for the statspack tool are in the $ORACLE_HOME/rdbms/admin directory, all files that start with sp)

[oracle@oracle ~] $sqlplus / as sysdba

SQL > select file_name,tablespace_name from dba_data_files

FILE_NAMETABLESPACE_NAME

-

/ home/oracle/app/oradata/orcl/users01.dbf USERS

/ home/oracle/app/oradata/orcl/undotbs01.dbf UNDOTBS1

/ home/oracle/app/oradata/orcl/sysaux01.dbf SYSAUX

/ home/oracle/app/oradata/orcl/system01.dbf SYSTEM

/ home/oracle/app/oradata/orcl/tong.dbf TONG1

6 rows selected.

SQL > create tablespace perfstat datafile'/ home/oracle/app/oradata/orcl/perfstat.dbf' size 200m;-- create a separate tablespace to store statspack data

Tablespace created.

SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spcreate.sql-when you execute the installation statspack script file, you will be prompted for the perfstat user's password, default tablespace, and temporary tablespace

SQL >

two。 Log in using statspack users to generate data

SQL > conn perfstat/system

Connected.

SQL > execute statspack.snap

PL/SQL procedure successfully completed. -- perform snapshots to obtain data (perform several more times)

SQL > execute statspack.snap

PL/SQL procedure successfully completed.

SQL > execute statspack.snap

PL/SQL procedure successfully completed.

SQL > execute statspack.snap

PL/SQL procedure successfully completed.

SQL > select max (snap_id) from stats$snapshot;-see how many reports there are

MAX (SNAP_ID)

-

four

SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spreport.sql-- generate a report

SQL > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracle admin] $vim sp_1_4.lst-- View information in statspack

3.statspack management

SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/sppurge.sql-- Delete historical data

SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/sptrunc.sql-- clear the data in the table

SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spdrop.sql-- Uninstall the statspack tool

Tags: Data tools space reports files users scripts generation information history historical data passwords beginnings snapshots directories storage prompts independence login management Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno OPPO Reno Huawei macOS Shulou Technology Redmi