Inhalt
Problem
In der Oracle Standard Edition ist das Automatic Workload Repository (AWR) nicht verfuegbar. In der Enterprise Edition ist es als lizenzpflichte Option erhaeltlich.
Analyse
Mit Oracle Statspack gibt es auch für die Standard Edition eine gute Moeglickeit Performance Probleme aufzudecken.
Loesung
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u00/oradata/ORCL2/datafile/o1_mf_system_b8373qwq_.dbf /u00/oradata/ORCL2/datafile/o1_mf_sysaux_b8373qxc_.dbf /u00/oradata/ORCL2/datafile/o1_mf_undotbs1_b8373qxg_.dbf /u00/oradata/ORCL2/datafile/o1_mf_users_bgchw6xl_.dbf SQL> CREATE SMALLFILE TABLESPACE STATSPACK DATAFILE SIZE 64M AUTOEXTEND ON NEXT 64M MAXSIZE 2048M LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u00/oradata/ORCL2/datafile/o1_mf_system_b8373qwq_.dbf /u00/oradata/ORCL2/datafile/o1_mf_sysaux_b8373qxc_.dbf /u00/oradata/ORCL2/datafile/o1_mf_undotbs1_b8373qxg_.dbf /u00/oradata/ORCL2/datafile/o1_mf_users_bgchw6xl_.dbf /u00/oradata/ORCL2/datafile/o1_mf_statspac_bh032bwd_.dbf SQL>
Nachdem der Tablspace angelegt ist, kann nun mit spcreate das Statspack Package installiert werden.
SQL> @?/rdbms/admin/spcreate 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 ------------------------------ --------- ---------------------------- STATSPACK PERMANENT SYSAUX PERMANENT * USERS PERMANENT Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used. Enter value for default_tablespace: STATSPACK Using tablespace STATSPACK as PERFSTAT default tablespace. 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 <return> will result in the database's default Temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user ... Installing required packages ... ... ...
Der Level fuer die Snapshots wird auf sieben gesetzt, um detailierte Angaben zu erhalten.
SQL> select * from stats$level_description; SNAP_LEVEL ---------- DESCRIPTION ------------------------------------------------------------------------------------------------- 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 inf ormation 5 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 5 rows selected. SQL> -- um loecher zu vermeiden, wird der sequence Cache disabled SQL> ALTER SEQUENCE perfstat.stats$snapshot_id NOCACHE; Sequence altered. SQL> exec statspack.snap; PL/SQL procedure successfully completed. SQL> exec STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>7,i_modify_parameter=>'true', i_instance_number=>1); PL/SQL procedure successfully completed. SQL> Den aktuellen Default fuer den snap level findet man im STATS$STATSPACK_PARAMETER .
Das reglemaessige Anlegen neuer Snapshots und das Loeschen alter Snapshots wird ueber den Oracle Scheduler gesteuert.
SQL> begin
dbms_scheduler.create_job('PERFSTAT.SP_PURGE'
, job_type => 'PLSQL_BLOCK'
, job_action => 'begin statspack.purge(i_num_days=>30,i_extended_purge=>true); end;'
, schedule_name => 'SYS.SATURDAY_WINDOW'
, enabled => true
, auto_drop => false
, comments => 'Purge Stats from Statspack'
);
end;
/
PL/SQL procedure successfully completed.
SQL> begin
dbms_scheduler.create_job('PERFSTAT.SP_SNAP'
, job_type => 'PLSQL_BLOCK'
, job_action => 'begin statspack.snap; end;'
, start_date => trunc(sysdate+1/24,'HH')
, repeat_interval => 'FREQ=HOURLY;BYMINUTE=30'
, enabled => true
, auto_drop => false
, comments => 'Gather Stats for Statspack'
);
end;
/
PL/SQL procedure successfully completed.
SQL> set pages 9999 lines 200
col owner form a15
col job_name form a15
col schedule_name form a15
col start_date form a35
col last_start_date form a35
col repeat_interval form a40
select OWNER,JOB_NAME,SCHEDULE_NAME,NEXT_RUN_DATE,REPEAT_INTERVAL,LAST_START_DATE from dba_scheduler_jobs where owner = 'PERFSTAT';
OWNER JOB_NAME SCHEDULE_NAME NEXT_RUN_DATE REPEAT_INTERVAL LAST_START_DATE
--------------- --------------- --------------- ----------------------------------- ---------------------------------------- -----------------------------------
PERFSTAT SP_PURGE SATURDAY_WINDOW
PERFSTAT SP_SNAP 27-FEB-15 08.00.00.000000 AM +01:00 FREQ=HOURLY;BYMINUTE=30
2 rows selected.
SQL>
Den Schedule anpassen
Wenn erforderlich, koennen kuerzere Intervalle fuer die Snapshot Erstellung gesetzt werden.
SQL> BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SP_SNAP', attribute => 'repeat_interval', value => 'FREQ=HOURLY;BYMINUTE=0,15,30,45'); END; / PL/SQL procedure successfully completed. SQL>
Statspack Reports erzeugen
SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY HH24:MI:SS') "Date/Time" from stats$snapshot,v$database order by 2;
NAME SNAP_ID Date/Time
--------- ---------- --------------------
ORCL2 1 27.02.2015 07:34:07
ORCL2 2 27.02.2015 08:30:03
ORCL2 3 27.02.2015 08:33:15
ORCL2 4 27.02.2015 08:41:51
ORCL2 5 27.02.2015 08:45:03
ORCL2 6 27.02.2015 09:00:03
ORCL2 7 27.02.2015 09:15:03
7 rows selected.
SQL>
SQL> @?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
846337890 ORCL2 1 ORCL2
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
846337890 1 ORCL2 ORCL2 oel21.dammey
er.local
Using 846337890 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL2 ORCL2 1 27 Feb 2015 07:34 5
2 27 Feb 2015 08:30 7
3 27 Feb 2015 08:33 7
4 27 Feb 2015 08:41 7
5 27 Feb 2015 08:45 7
6 27 Feb 2015 09:00 7
7 27 Feb 2015 09:15 7
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5
Begin Snapshot Id specified: 5
Enter value for end_snap: 6
End Snapshot Id specified: 6
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_5_6. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name sp_5_6
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
846337890 ORCL2 1 18-Feb-15 14:44 12.1.0.1.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
oel21.dummy.l Linux x86 64-bit 1 0 0 3.9
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 5 27-Feb-15 08:45:03 40 1.4
End Snap: 6 27-Feb-15 09:00:03 39 1.2
Elapsed: 15.00 (mins) Av Act Sess: 0.0
DB time: 0.37 (mins) DB CPU: 0.09 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 276M Std Block Size: 8K
Shared Pool: 208M Log Buffer: 2,112K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 0.4 0.00 0.14
DB CPU(s): 0.0 0.1 0.00 0.03
Redo size: 756,301.0 13,089,825.5
Logical reads: 2,146.0 37,141.7
Block changes: 3,014.4 52,172.4
Physical reads: 34.2 591.9
Physical writes: 92.4 1,599.1
User calls: 0.2 3.0
Parses: 6.0 104.6
Hard parses: 1.3 21.9
W/A MB processed: 0.2 3.9
Logons: 0.1 0.9
Executes: 17.0 294.2
Rollbacks: 0.0 0.0
Transactions: 0.1
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.99
Buffer Hit %: 98.53 Optimal W/A Exec %: 100.00
Library Hit %: 86.51 Soft Parse %: 79.06
Execute to Parse %: 64.44 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 41.61 % Non-Parse CPU: 92.38
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.92 94.85
% SQL with executions>1: 82.01 81.70
% Memory for SQL w/exec>1: 66.38 79.74
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
lreg timer 300 900 3002 32.7
heartbeat redo informer 900 900 1000 32.6
AQPC idle 30 900 30003 32.6
log file parallel write 841 17 21 .6
db file async I/O submit 801 10 13 .4
-------------------------------------------------------------
...
...
...
Fazit
Ist das lizenzpflichte Diagnostic and Tuning Pack nicht verfuegbar, sollte auf jeden Fall das Statspack Package installiert und konfiguriert werden. Das Package wird zwar in der offizielle Dokumentation nicht beschrieben, dennoch wird es noch weiterentwickelt. Eine ausfuehrliche Doku ist in $ORACLE_HOME/rdbms/admin/spdoc.txt enthalten.
Diverse Beispiele zeigt auch Jonathan Lewis auf seiner Website.