1. Create a seperate tablespace for the STATSPACK user:
It is a recommended best practice to create a seperate tablespace for the STATSPACK user. The reason is that; in the case of a maintenance problem or a negligence, the PERFSTAT procedures are capable of filling their tablespace by the nature of their work logic. If a seperate tablespace is assigned to PERFSTAT, then at least it will fill only its own tablespace effecting the statspack funtionaluty only… So it is also important to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space.
SQL> CREATE TABLESPACE TBS_PERFSTAT DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 2G;
2. Run the script necessary to create the PERFSTAT user and the objects:
$> cd $ORACLE_HOME/rdbms/admin/ $> sqlplus "/ as sysdba" @spcreate.sql #Choose the PERFSTAT user's password #----------------------------------- #Not specifying a password will result in the installation FAILING # #Enter value for perfstat_password: perfstat # #Choose the Default tablespace for the PERFSTAT user #--------------------------------------------------- #Below is the list of online tablespaces in this database which can #store user data. Specifying the SYSTEM tablespace for the user's #default tablespace will result in the installation FAILING, as #using SYSTEM for performance data is not supported. # #Choose the PERFSTAT users's default tablespace. This is the tablespace #in which the STATSPACK tables and indexes will be created. # #TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE #------------------------------ --------- ---------------------------- #TBS_PERFSTAT PERMANENT #SYSAUX PERMANENT * #USERS PERMANENT # #Pressing will result in STATSPACK's recommended default #tablespace (identified by *) being used. # #Enter value for default_tablespace: TBS_PERFSTAT <--------- # #Choose the Temporary tablespace for the PERFSTAT user #----------------------------------------------------- #Below is the list of online tablespaces in this database which can #store temporary data (e.g. for sort workareas). Specifying the SYSTEM #tablespace for the user's temporary tablespace will result in the #installation FAILING, as using SYSTEM for workareas is not supported. # #Choose the PERFSTAT user's Temporary tablespace. # #TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE #------------------------------ --------- -------------------------- #TEMP TEMPORARY * # #Pressing will result in the database's default Temporary #tablespace (identified by *) being used. # #Enter value for temporary_tablespace: TEMP <--------- # #..... #..... #Creating Package STATSPACK... # #Package created. # #No errors. #Creating Package Body STATSPACK... # #Package body created. # #No errors. # #NOTE: #SPCPKG complete.
3. After the installation is successful, we can take a snapshot of the system using the following procedure:
SQL> execute statspack.snap;
Taking a snapshot means, saving the current statistics and some metrics to predefined repository tables for further analysis. A single snapshot, therefore, is usually meaningless. It is like saying; “A car travelled 120 kilometers.” But it lacks the information of time. How long did it take the car to travel that distance? Were there any bumps on the road? So, we need another snapshot maybe after 30 minutes, to be able to compare the change in the statistics values in a well-defined time span.
Long story, short, we need at least two snapshots to be able to generate a statspack report. This is also the same pronciple for AWR reports.
Since it won’t be feasible to run some snapshots manually in a designated period, we should automate this procedure using some Oracle scheduling mechanism. (see the next step)
4. Schedule a job for snapshots: We should schedule the snapshots so that, we can create reports later. It is usually convenient to create snapshots in every 30 minutes.
# First, create a schedule:
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'SNAP_30min',
repeat_interval => 'FREQ=MINUTELY;BYMINUTE=00,30');
END;
/
# Then, create the job itself:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'sp_snapshot',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
schedule_name => 'SNAP_30min',
auto_drop => FALSE,
comments => 'Statspack collection');
END;
/
# We should enable the job as follows:
BEGIN DBMS_SCHEDULER.ENABLE('sp_snapshot');
END;
/
5. Modify the default statspack level: We can change the detail of statistics gathered by the Statspack by specifying a snapshot level.
SQL> execute statspack.modify_statspack_parameter(i_snap_level => 7, i_modify_parameter => 'true');
SQL> select * from stats$level_description order by snap_level;
| 0 | This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information |
| 5 (default) | This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels |
| 6 | This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels |
| 7 | This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels |
| 10 | This level includes capturing Child Latch statistics, along with all data captured by lower levels |
6. To view the snapshots taken so far:
SQL> select snap_id,to_char(snap_time,'DD.MM.YYYY HH24:MI:SS') "DATE" from stats$snapshot order by snap_time desc; -- SNAP_ID DATE -- 641 28.11.2018 13:30:55 -- 640 28.11.2018 13:00:55 -- 639 28.11.2018 12:30:55 -- .. -- .. -- .. -- 2 01.11.2018 09:00:55 -- 1 01.11.2018 08:30:55
7. And Finally… Generate a report:
%> sqlplus perfstat/perfstat SQL> @?/rdbms/admin/spreport.sql #Current Instance #~~~~~~~~~~~~~~~~ # # DB Id DB Name Inst Num Instance #----------- ------------ -------- ------------ # 245679034 TESTDB 1 TESTDB # # # #Instances i n this Statspack schema #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ # # DB Id Inst Num DB Name Instance Host #----------- -------- ------------ ------------ ------------ # 245679034 1 TESTDB TESTDB H-001234-XXY # #Using 245679034 f0r database Id #Using 1 f0r instance number # # #Specify the number of days of snapshots to choose from #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #Entering the number of days (n) will result i n the most recent #(n) days of snapshots being listed. Pressing without #specifying a number lists all completed snapshots. # # # #Listing all Completed Snapshots # # Snap #Instance DB Name Snap Id Snap Started Level Comment #------------ ------------ --------- ----------------- ----- -------------------- #TESTDB TESTDB 1 12 Nov 2018 15:00 7 # # 2 12 Nov 2018 15:30 7 # 3 12 Nov 2018 16:00 7 # 4 12 Nov 2018 16:30 7 # # #Specify the Begin and End Snapshot Ids #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #Enter value f0r begin_snap: 1 <--------- #Begin Snapshot Id specified: 1 # #Enter value f0r end_snap: 2 <--------- #End Snapshot Id specified: 2 # #Specify the Report Name #~~~~~~~~~~~~~~~~~~~~~~~ #The default report file name is sp_1_2 To use this name, press to #c0ntinue, otherwise enter an alternative. Enter value f0r report_name: |-HIT ENTER-| <--------- # #Using the report name sp_1_2
8. Important Notice: The SQL Texts in the Statspack reports are truncated if they are longer than a specific length. If you want to be able to read the whole SQL statement, apply the below fix.
CAUSE:
The number of rows displayed for an sql statement is determined by the num_rows_per_hash parameter defined in $ORACLE_HOME\rdbms\admin\sprepins.sql
FIX:
Modify the num_rows_per_hash parameter in $ORACLE_HOME\rdbms\admin\sprepins.sql
| 1. Open sprepins.sql 2. Search for the row define num_rows_per_hash=5; 3. Modify the value to a larger number define num_rows_per_hash=15; 4. Save the modifications in sprepins.sql |