Oracle Statspack
In this post we are going to install and use Oracle Statspack which is a free alternative to AWR reports. It is mainly used for performance tuning.
Table of Contents
In this example we use a Oracle 12.2 SE2 database running on a Windows Server 2008 R2 VM. But the process is the same for other setups. The installation files have already been provided during the database installation.
Creating a dedicated tablespace
We will create a separate tablespace for the statspack data (snapshots). This is important so we don’t affect other data when the statspack cleanup should fail.
rem run in a windows command prompt
(echo create tablespace statspack_data
echo datafile 'C:\app\user1\oradata\orcl\sp01.dbf' size 50M
echo autoextend on next 50m maxsize 2G
echo extent management local uniform size 1M
echo segment space management auto;)|sqlplus / as sysdba
Installing Oracle Statspack
This will create the schema perfstat (password: changeme) and install Oracle Statspack:
(echo define perfstat_password=changeme
echo define default_tablespace=statspack_data
echo define temporary_tablespace=temp
echo @?/rdbms/admin/spcreate.sql)|sqlplus / as sysdba
Creating snapshots
Before we are able to create a Statspack Report we need to create at least 2 snapshots that cover the timespan we want to analyze.
Statspack Levels
Snapshots can be taken with different levels. The level describes the detail of data that is gathered. More detailed snapshots consume more space. You can get a description of the statspack snapshot levels by running:
select * from stats$level_description;
For a 12.2 SE2 database the output looks like this:
| 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 information |
| 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 |
The default level is 5. You can change that with:
exec statspack.modify_statspack_parameter(i_snap_level=>7, i_modify_parameter=>’true’);
Manual snapshot creation
# this creates a snapshot with the default snapshot level
exec statspack.snap()
# this creates a snapshot with the specified snapshot level
exec statspack.snap(i_snap_level => 7);Automatic snapshot creation
We can use the Oracle scheduler to create snapshots every 30 minutes:
sqlplus system/changeme
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'perfstat.statspack_every30',
repeat_interval => 'FREQ=MINUTELY;BYMINUTE=55,25');
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'perfstat.sp_snapshot',
job_type => 'STORED_PROCEDURE',
job_action => 'perfstat.statspack.snap',
schedule_name => 'perfstat.statspack_every30',
comments => 'Statspack collection');
DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot');
END;
/
Creating a Statspack Report
At least two snapshots need to exist before we can create an Statspack Report. The selected snapshots should cover the timespan we want to analyze.
Creating a Instance Report
This report covers the instance as a whole.
@?/rdbms/admin/spreport.sql
You need to enter the start snap id and the end snap id and the name of the report file.
Sample Output (report creation)
SQL> @?/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1723333555 ORCL 1 orcl
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1723333555 1 ORCL orcl WIN102
Using 1723333555 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
------------ ------------ --------- ----------------- ----- --------------------
orcl ORCL 1 12 Mar 2025 14:10 5
2 12 Mar 2025 14:40 5
3 12 Mar 2025 15:10 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 3
End Snapshot Id specified: 3
PAR VERSN
--- -----------------
HOST_NAME
----------------------------------------------------------------
PLATFORM_NAME
--------------------------------------------------------------------------------
DB Name Instance BTIME SUTIME
------------ ------------ ----------------- ------------------------
NO 12.2.0.1.0
WIN102
Microsoft Windows x86 64-bit
ORCL orcl 20250312 14:10:33 12-Mar-25 13:43
ETIME
-----------------
20250312 15:10:32
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_3. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name sp_1_3Sample Statspack Report (Instance)
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1723333555 orcl 1 12-Mar-25 13:43 12.2.0.1.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
WIN102 Microsoft Windows x86 4 4 1 8.0
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 12-Mar-25 14:10:33 40 .8
End Snap: 3 12-Mar-25 15:10:32 39 .8
Elapsed: 59.98 (mins) Av Act Sess: 0.0
DB time: 0.05 (mins) DB CPU: 0.05 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,792M Std Block Size: 8K
Shared Pool: 480M Log Buffer: 7,496K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 0.3 0.00 0.00
DB CPU(s): 0.0 0.3 0.00 0.00
Redo size: 1,539.1 553,938.0
Logical reads: 75.3 27,115.8
Block changes: 4.7 1,701.8
Physical reads: 0.0 10.9
Physical writes: 1.0 370.5
User calls: 0.8 283.0
Parses: 1.1 391.9
Hard parses: 0.2 72.9
W/A MB processed: 0.1 18.2
Logons: 0.1 26.7
Executes: 2.5 880.3
Rollbacks: 0.0 0.0
Transactions: 0.0
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.96 Optimal W/A Exec %: 100.00
Library Hit %: 90.82 Soft Parse %: 81.40
Execute to Parse %: 55.48 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 88.89 % Non-Parse CPU: 94.97
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 61.65 68.10
% SQL with executions>1: 43.43 56.54
% Memory for SQL w/exec>1: 46.50 66.55
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
Data Guard: Gap Manager 60 3,601 60010 33.3
Data Guard: Timer 6 3,600 ###### 33.3
pman timer 1,200 3,600 3000 33.3
CPU time 6 .1
control file sequential read 8,058 6 1 .1
-------------------------------------------------------------
Host CPU (CPUs: 4 Cores: 4 Sockets: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.09 0.29 99.62
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 14,400.5
Host: Busy CPU time (s): 54.6
% of time Host is Busy: 0.4
Instance: Total CPU time (s): 6.0
% of Busy CPU used for Instance: 11.1
Instance: Total Database time (s): 234.5
%DB time waiting for CPU (Resource Mgr): 0.0
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 8,191.6 8,191.6
SGA use (MB): 2,464.0 2,464.0
PGA use (MB): 122.9 121.9
% Host Mem used for SGA+PGA: 31.6 31.6
-------------------------------------------------------------
Time Model System Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 2.8 90.0
sql execute elapsed time 2.6 84.3
parse time elapsed 1.4 46.6
hard parse elapsed time 1.4 44.3
PL/SQL compilation elapsed time 0.1 3.0
PL/SQL execution elapsed time 0.1 2.5
connection management call elapsed 0.0 .6
hard parse (sharing criteria) elaps 0.0 .4
repeated bind elapsed time 0.0 .3
failed parse elapsed time 0.0 .1
DB time 3.1
background elapsed time 231.5
background cpu time 3.3
-------------------------------------------------------------
Foreground Wait Events DB/Inst: ORCL/orcl Snaps: 1-3
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
control file sequential read 300 0 0 0 30.0 .0
db file sequential read 32 0 0 1 3.2 .0
PGA memory operation 1,101 0 0 0 110.1 .0
log file sync 31 0 0 0 3.1 .0
Disk file operations I/O 37 0 0 0 3.7 .0
SQL*Net more data to client 8 0 0 0 0.8 .0
SQL*Net message from client 1,736 0 2,399 1382 173.6
jobq slave wait 522 100 260 498 52.2
SQL*Net message to client 1,735 0 0 0 173.5
-------------------------------------------------------------
Background Wait Events DB/Inst: ORCL/orcl Snaps: 1-3
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
Data Guard: Gap Manager 60 0 3,601 60010 6.0 33.3
Data Guard: Timer 6 0 3,600 ###### 0.6 33.3
pman timer 1,200 100 3,600 3000 120.0 33.3
control file sequential read 7,758 0 5 1 775.8 .1
oracle thread bootstrap 259 0 2 6 25.9 .0
control file parallel write 1,440 0 1 0 144.0 .0
db file parallel write 71 0 0 7 7.1 .0
log file parallel write 847 0 0 0 84.7 .0
latch free 293 0 0 0 29.3 .0
db file async I/O submit 23 0 0 3 2.3 .0
db file sequential read 72 0 0 1 7.2 .0
os thread creation 259 0 0 0 25.9 .0
ADR block file read 15 0 0 1 1.5 .0
PGA memory operation 1,788 0 0 0 178.8 .0
reliable message 36 0 0 0 3.6 .0
Disk file operations I/O 39 0 0 0 3.9 .0
ADR block file write 9 0 0 0 0.9 .0
LGWR wait for redo copy 34 0 0 0 3.4 .0
log file sync 4 0 0 0 0.4 .0
rdbms ipc message 30,185 97 53,764 1781 3,018.5
Space Manager: slave idle wa 1,866 0 8,756 4692 186.6
pmon timer 2,400 100 7,200 3000 240.0
DIAG idle wait 7,198 100 7,196 1000 719.8
smon timer 13 92 3,628 ###### 1.3
Streams AQ: qmn slave idle w 132 0 3,612 27365 13.2
Streams AQ: qmn coordinator 258 0 3,612 14000 25.8
dispatcher timer 60 100 3,600 60007 6.0
AQPC idle 120 100 3,600 30000 12.0
heartbeat redo informer 3,600 0 3,600 1000 360.0
wait for unread message on b 3,599 100 3,599 1000 359.9
lreg timer 1,199 100 3,597 3000 119.9
SQL*Net message from client 13 0 0 2 1.3
class slave wait 251 0 0 0 25.1
-------------------------------------------------------------
Wait Events (fg and bg) DB/Inst: ORCL/orcl Snaps: 1-3
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
Data Guard: Gap Manager 60 0 3,601 60010 6.0 33.3
Data Guard: Timer 6 0 3,600 ###### 0.6 33.3
pman timer 1,200 100 3,600 3000 120.0 33.3
control file sequential read 8,058 0 6 1 805.8 .1
oracle thread bootstrap 259 0 2 6 25.9 .0
control file parallel write 1,440 0 1 0 144.0 .0
db file parallel write 71 0 0 7 7.1 .0
log file parallel write 847 0 0 0 84.7 .0
latch free 293 0 0 0 29.3 .0
db file sequential read 104 0 0 1 10.4 .0
db file async I/O submit 23 0 0 3 2.3 .0
os thread creation 259 0 0 0 25.9 .0
PGA memory operation 2,889 0 0 0 288.9 .0
Disk file operations I/O 76 0 0 0 7.6 .0
log file sync 35 0 0 0 3.5 .0
ADR block file read 15 0 0 1 1.5 .0
reliable message 36 0 0 0 3.6 .0
ADR block file write 9 0 0 0 0.9 .0
LGWR wait for redo copy 34 0 0 0 3.4 .0
SQL*Net more data to client 8 0 0 0 0.8 .0
rdbms ipc message 30,185 97 53,764 1781 3,018.5
Space Manager: slave idle wa 1,866 0 8,756 4692 186.6
pmon timer 2,400 100 7,200 3000 240.0
DIAG idle wait 7,198 100 7,196 1000 719.8
smon timer 13 92 3,628 ###### 1.3
Streams AQ: qmn slave idle w 132 0 3,612 27365 13.2
Streams AQ: qmn coordinator 258 0 3,612 14000 25.8
dispatcher timer 60 100 3,600 60007 6.0
AQPC idle 120 100 3,600 30000 12.0
heartbeat redo informer 3,600 0 3,600 1000 360.0
wait for unread message on b 3,599 100 3,599 1000 359.9
lreg timer 1,199 100 3,597 3000 119.9
SQL*Net message from client 1,749 0 2,399 1372 174.9
jobq slave wait 522 100 260 498 52.2
class slave wait 251 0 0 0 25.1
SQL*Net message to client 1,745 0 0 0 174.5
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 1-3
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read 15 100.0
ADR block file write 9 100.0
ADR file lock 6 100.0
Data Guard: Gap Manager 60 100.0
Data Guard: Timer 6 100.0
Disk file operations I/O 76 100.0
LGWR wait for redo copy 34 100.0
PGA memory operation 2889 100.0
SQL*Net break/reset to cli 12 100.0
SQL*Net more data to clien 8 100.0
asynch descriptor resize 45 100.0
control file parallel writ 1440 98.9 .8 .1 .2
control file sequential re 8058 93.5 .2 .2 6.0 .0 .0
db file async I/O submit 23 60.9 13.0 8.7 8.7 4.3 4.3
db file parallel write 71 69.0 2.8 14.1 7.0 7.0
db file scattered read 1 100.0
db file sequential read 104 96.2 1.0 1.9 1.0
latch free 293 97.3 1.4 1.4
log file parallel write 847 99.4 .5 .1
log file sync 35 100.0
oracle thread bootstrap 259 11.2 80.3 6.6 1.2 .8
os thread creation 259 100.0
pman timer 1200 100.0
reliable message 36 100.0
AQPC idle 120 100.0
DIAG idle wait 7198 100.0
SQL*Net message from clien 1749 52.7 8.1 2.7 2.7 5.1 19.4 7.7 1.4
SQL*Net message to client 1745 100.0
SQL*Net more data from cli 8 100.0
Space Manager: slave idle 1866 3.8 .1 .1 .6 95.6
Streams AQ: qmn coordinato 258 50.0 50.0
Streams AQ: qmn slave idle 132 2.3 97.7
class slave wait 251 100.0
dispatcher timer 60 100.0
heartbeat redo informer 3600 100.0
jobq slave wait 522 100.0
lreg timer 1199 100.0
pmon timer 2400 100.0
rdbms ipc message 30K 1.2 .0 .1 .5 .1 .1 57.5 40.4
smon timer 13 100.0
wait for unread message on 3599 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 1-3
-> Total DB CPU (s): 3
-> Captured SQL accounts for 34.1% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.34 4 0.09 12.5 0.32 202,220 1580262155
Module: SQL*Plus
with event_histogram as ( select /*+ inline ordered index(h) i
ndex(se) */ h.snap_id , se.event , sum(h.
wait_count) total_waits , sum(case when (h.wait_time_mill
i = 1) then (nvl(h.wait_count,0)) else 0 end)
0.19 168 0.00 6.8 0.59 1,866 1566118407
declare vsn varchar2(20); begin
vsn := dbms_rcvman.getPackageVersion; :p
kg_vsn:pkg_vsn_i := vsn; if vsn is not null then
:pkg_vsnub4 := to_number(subs
0.14 519 0.00 5.1 0.10 40 1307714173
Module: SQL*Plus
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
0.06 290 0.00 2.3 0.06 1,140 476435637
insert /* KSXM:FLUSH COL */ into sys.col_usage$ (obj#, intcol#,
equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
like_preds, null_preds, flags, timestamp) values ( :objn, :col
n, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0
0.06 741 0.00 2.3 0.05 3,382 559882357
insert /* QOSD */ into exp_head$ (exp_id, objn, sub_id, fixed_co
st, text, col_list, flags, ctime) values (:1, :2, :3, :4, :5, :6
, :7, :8)
0.03 219 0.00 1.1 0.07 1,827 3544535890
merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m
using dual
on (m.obj# = :objn and
(:pobjn = 0 or
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: ORCL Instance: orcl Snaps: 1 -3
-> Total DB Time (s): 3
-> Captured SQL accounts for 90.8% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.84 2 0.42 27.6 0.00 7 3197283829
Module: DBMS_SCHEDULER
call perfstat.statspack.snap ( )
0.59 168 0.00 19.3 0.19 199 1566118407
declare vsn varchar2(20); begin
vsn := dbms_rcvman.getPackageVersion; :p
kg_vsn:pkg_vsn_i := vsn; if vsn is not null then
:pkg_vsnub4 := to_number(subs
0.43 170 0.00 14.0 0.02 0 3879834072
select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Sha
red IO Pool Memory'
0.32 4 0.08 10.6 0.34 0 1580262155
Module: SQL*Plus
with event_histogram as ( select /*+ inline ordered index(h) i
ndex(se) */ h.snap_id , se.event , sum(h.
wait_count) total_waits , sum(case when (h.wait_time_mill
i = 1) then (nvl(h.wait_count,0)) else 0 end)
0.10 519 0.00 3.3 0.14 0 1307714173
Module: SQL*Plus
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
0.07 219 0.00 2.2 0.03 2 3544535890
merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m
using dual
on (m.obj# = :objn and
(:pobjn = 0 or
0.06 170 0.00 2.1 0.02 0 1301067080
select shared_pool_size_for_estimate s, shared_pool_siz
e_factor * 100 f, estd_lc_load_time l, 0 fr
om v$shared_pool_advice
0.06 290 0.00 1.8 0.06 1 476435637
insert /* KSXM:FLUSH COL */ into sys.col_usage$ (obj#, intcol#,
equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
like_preds, null_preds, flags, timestamp) values ( :objn, :col
n, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0
0.05 170 0.00 1.8 0.00 0 87488304
select size_for_estimate, size_factor * 100
f, estd_physical_read_time, e
std_physical_reads from v$db_cache_advice where id
= '3'
0.05 741 0.00 1.5 0.06 5 559882357
insert /* QOSD */ into exp_head$ (exp_id, objn, sub_id, fixed_co
st, text, col_list, flags, ctime) values (:1, :2, :3, :4, :5, :6
, :7, :8)
SQL ordered by Elapsed time for DB: ORCL Instance: orcl Snaps: 1 -3
-> Total DB Time (s): 3
-> Captured SQL accounts for 90.8% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: ORCL/orcl Snaps: 1-3
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 271,158
-> Captured SQL accounts for 86.4% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
202,220 4 50,555.0 74.6 0.34 0.32 1580262155
Module: SQL*Plus
with event_histogram as ( select /*+ inline ordered index(h) i
ndex(se) */ h.snap_id , se.event , sum(h.
wait_count) total_waits , sum(case when (h.wait_time_mill
i = 1) then (nvl(h.wait_count,0)) else 0 end)
17,125 2 8,562.5 6.3 0.00 0.84 3197283829
Module: DBMS_SCHEDULER
call perfstat.statspack.snap ( )
3,382 741 4.6 1.2 0.06 0.05 559882357
insert /* QOSD */ into exp_head$ (exp_id, objn, sub_id, fixed_co
st, text, col_list, flags, ctime) values (:1, :2, :3, :4, :5, :6
, :7, :8)
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 1-3
-> End Disk Reads Threshold: 1000 Total Disk Reads: 109
-> Captured SQL accounts for 72.5% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
199 168 1.2 182.6 0.19 0.59 1566118407
declare vsn varchar2(20); begin
vsn := dbms_rcvman.getPackageVersion; :p
kg_vsn:pkg_vsn_i := vsn; if vsn is not null then
:pkg_vsnub4 := to_number(subs
19 152 0.1 17.4 0.02 0.02 3378994511
select node,owner,name from syn$ where obj#=:1
17 5 3.4 15.6 0.00 0.01 379381160
select /*+ index(idl_ub2$ i_idl_ub21) */ piece#,length,piece fro
m idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pie
ce#
17 107 0.2 15.6 0.00 0.01 1456987554
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)),max(bit
and(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16),
max(bitand(nvl(option$,0), 64) /64), max(bitand(nvl(option$,0),
128) /128) from objauth$ where obj#=:1 and grantee#=:2 group by
7 2 3.5 6.4 0.00 0.84 3197283829
Module: DBMS_SCHEDULER
call perfstat.statspack.snap ( )
5 741 0.0 4.6 0.06 0.05 559882357
insert /* QOSD */ into exp_head$ (exp_id, objn, sub_id, fixed_co
st, text, col_list, flags, ctime) values (:1, :2, :3, :4, :5, :6
, :7, :8)
3 174 0.0 2.8 0.00 0.02 966877366
select count(*) from ilmobj$ where rownum = 1
2 5 0.4 1.8 0.00 0.00 688709712
select /*+ index(idl_char$ i_idl_char1) */ piece#,length,piece f
rom idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#
2 742 0.0 1.8 0.02 0.01 2842394183
select /* QOSD */ /*+ index(eh)*/ objn, sub_id, fixed_cost, text
, col_list, flags, ctime from exp_head$ eh where exp_id = :1
2 120 0.0 1.8 0.02 0.02 3040921633
select /* KSXM:LOAD_DML_INF *//*+ leading(o) index(m) use_nl(m)
*/ nvl(m.inserts, 0) ins, nvl(m.updates, 0) upd, nvl(
m.deletes, 0) del, nvl(m.drop_segments, 0) dropseg, nvl
(m.flags, 0) flags, nvl(rowcnt, 0) rowcnt,
2 219 0.0 1.8 0.03 0.07 3544535890
merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m
using dual
on (m.obj# = :objn and
(:pobjn = 0 or
SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 1-3
-> End Disk Reads Threshold: 1000 Total Disk Reads: 109
-> Captured SQL accounts for 72.5% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 8,803
-> Captured SQL accounts for 71.6% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
742 1 0.0 0.00 0.00 2842394183
select /* QOSD */ /*+ index(eh)*/ objn, sub_id, fixed_cost, text
, col_list, flags, ctime from exp_head$ eh where exp_id = :1
741 741 1.0 0.00 0.00 559882357
insert /* QOSD */ into exp_head$ (exp_id, objn, sub_id, fixed_co
st, text, col_list, flags, ctime) values (:1, :2, :3, :4, :5, :6
, :7, :8)
519 519 1.0 0.00 0.00 1307714173
Module: SQL*Plus
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
492 1 0.0 0.00 0.00 214372507
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname from obj$ o where o.obj#=:1
309 80 0.3 0.00 0.00 377251343
update /* KSXM:FLUSH COL */ sys.col_usage$ set
equality_preds = equality_preds + decode(bitand(:flag,1),0
,0,1), equijoin_preds = equijoin_preds + decode(bitand(:
flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode
290 290 1.0 0.00 0.00 476435637
insert /* KSXM:FLUSH COL */ into sys.col_usage$ (obj#, intcol#,
equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
like_preds, null_preds, flags, timestamp) values ( :objn, :col
n, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0
252 6 0.0 0.00 0.00 2566028731
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln, minimum_enc, maximum_enc
from hist_head$ where obj#=:1 and intcol#=:2
219 219 1.0 0.00 0.00 3544535890
merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m
using dual
on (m.obj# = :objn and
(:pobjn = 0 or
210 210 1.0 0.00 0.00 3026130686
begin dbms_output.get_line(:line, :status);
end;
184 184 1.0 0.00 0.00 1864097893
Module: SQL*Plus
SELECT VALUE FROM STATS$SYSSTAT WHERE SNAP_ID = :B4 AND DBID = :
B3 AND INSTANCE_NUMBER = :B2 AND NAME = :B1
174 174 1.0 0.00 0.00 966877366
select count(*) from ilmobj$ where rownum = 1
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 8,803
-> Captured SQL accounts for 71.6% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
172 0 0.0 0.00 0.00 1178168971
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date <= :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5
)) and (this_date is null) and ((dbms_logstdby.db_is_logstdby =
170 3,570 21.0 0.00 0.00 87488304
select size_for_estimate, size_factor * 100
f, estd_physical_read_time, e
std_physical_reads from v$db_cache_advice where id
= '3'
170 1,700 10.0 0.00 0.00 303149640
select java_pool_size_for_estimate s, java_pool_size_f
actor * 100 f, estd_lc_load_time l, 0 from
v$java_pool_advice
170 4,930 29.0 0.00 0.00 1301067080
select shared_pool_size_for_estimate s, shared_pool_siz
e_factor * 100 f, estd_lc_load_time l, 0 fr
om v$shared_pool_advice
170 0 0.0 0.00 0.00 3879834072
select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Sha
red IO Pool Memory'
168 168 1.0 0.00 0.00 1566118407
declare vsn varchar2(20); begin
vsn := dbms_rcvman.getPackageVersion; :p
kg_vsn:pkg_vsn_i := vsn; if vsn is not null then
:pkg_vsnub4 := to_number(subs
152 152 1.0 0.00 0.00 3378994511
select node,owner,name from syn$ where obj#=:1
120 120 1.0 0.00 0.00 3040921633
select /* KSXM:LOAD_DML_INF *//*+ leading(o) index(m) use_nl(m)
*/ nvl(m.inserts, 0) ins, nvl(m.updates, 0) upd, nvl(
m.deletes, 0) del, nvl(m.drop_segments, 0) dropseg, nvl
(m.flags, 0) flags, nvl(rowcnt, 0) rowcnt,
119 119 1.0 0.00 0.00 2463490013
SELECT SUBSTR(DECODE(:B2 , NULL, 'NULL', 0, 'NEXT OBJECT', 1, 'I
NDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SE
QUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKA
GE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE
107 8 0.1 0.00 0.00 1456987554
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)),max(bit
and(nvl(option$,0), 8) /8), max(bitand(nvl(option$,0), 16) /16),
max(bitand(nvl(option$,0), 64) /64), max(bitand(nvl(option$,0),
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 8,803
-> Captured SQL accounts for 71.6% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
128) /128) from objauth$ where obj#=:1 and grantee#=:2 group by
104 104 1.0 0.00 0.00 3715989406
Module: SQL*Plus
SELECT VALUE FROM STATS$PARAMETER WHERE SNAP_ID = :B4 AND DBID =
:B3 AND INSTANCE_NUMBER = :B2 AND ( NAME = '__' || :B1 OR NAME
= :B1 ) ORDER BY NAME
89 5 0.1 0.00 0.00 1495667577
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oi
d$, spare1, spare2, spare3, signature, spare7, spare8, spare9, n
vl(dflcollid, 16382), creappid, creverid, modappid, modverid, cr
epatchid, modpatchid from obj$ where owner#=:1 and name=:2 and n
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 1-3
-> End Parse Calls Threshold: 1000 Total Parse Calls: 3,919
-> Captured SQL accounts for 66.4% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
519 519 13.24 1307714173
Module: SQL*Plus
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
482 290 12.30 476435637
insert /* KSXM:FLUSH COL */ into sys.col_usage$ (obj#, intcol#,
equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
like_preds, null_preds, flags, timestamp) values ( :objn, :col
n, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0
219 219 5.59 3544535890
merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m
using dual
on (m.obj# = :objn and
(:pobjn = 0 or
210 210 5.36 3026130686
begin dbms_output.get_line(:line, :status);
end;
174 174 4.44 966877366
select count(*) from ilmobj$ where rownum = 1
172 172 4.39 1178168971
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date <= :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5
)) and (this_date is null) and ((dbms_logstdby.db_is_logstdby =
168 168 4.29 1566118407
declare vsn varchar2(20); begin
vsn := dbms_rcvman.getPackageVersion; :p
kg_vsn:pkg_vsn_i := vsn; if vsn is not null then
:pkg_vsnub4 := to_number(subs
122 309 3.11 377251343
update /* KSXM:FLUSH COL */ sys.col_usage$ set
equality_preds = equality_preds + decode(bitand(:flag,1),0
,0,1), equijoin_preds = equijoin_preds + decode(bitand(:
flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode
120 120 3.06 3040921633
select /* KSXM:LOAD_DML_INF *//*+ leading(o) index(m) use_nl(m)
*/ nvl(m.inserts, 0) ins, nvl(m.updates, 0) upd, nvl(
m.deletes, 0) del, nvl(m.drop_segments, 0) dropseg, nvl
(m.flags, 0) flags, nvl(rowcnt, 0) rowcnt,
77 77 1.96 3718493685
select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ pr
ivilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysa
uth$ connect by grantee#=prior privilege# and privilege#>0 start
with grantee#=:1 and privilege#>0
SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 1-3
-> End Parse Calls Threshold: 1000 Total Parse Calls: 3,919
-> Captured SQL accounts for 66.4% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
75 75 1.91 1198893840
select order#,columns,types from access$ where d_obj#=:1
75 75 1.91 2824870641
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, nvl(property,0),subname,type#,flags,d_attrs from dependenc
y$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
-------------------------------------------------------------
truncate table STATS$TEMP_SQLSTATS
*
ERROR at line 1:
ORA-00942: table or view does not exist
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
ASSM bg: segment fix monitor 6 0.0 0.6
ASSM cbk:blocks examined 464 0.1 46.4
ASSM cbk:blocks marked full 154 0.0 15.4
ASSM gsp:L1 bitmaps examined 480 0.1 48.0
ASSM gsp:L2 bitmap full 6 0.0 0.6
ASSM gsp:L2 bitmaps examined 250 0.1 25.0
ASSM gsp:Search all 11 0.0 1.1
ASSM gsp:Search hint 6 0.0 0.6
ASSM gsp:Search steal 11 0.0 1.1
ASSM gsp:bump HWM 11 0.0 1.1
ASSM gsp:get free block 314 0.1 31.4
ASSM gsp:get free data block 199 0.1 19.9
ASSM gsp:get free index block 115 0.0 11.5
ASSM gsp:good hint 181 0.1 18.1
ASSM rsv:fill reserve 17 0.0 1.7
ASSM wasted db state change 0 0.0 0.0
Batched IO block miss count 0 0.0 0.0
Batched IO double miss count 0 0.0 0.0
Batched IO single block count 0 0.0 0.0
Batched IO vector block count 0 0.0 0.0
Batched IO vector read count 0 0.0 0.0
Block Cleanout Optim referenced 5 0.0 0.5
CCursor + sql area evicted 11 0.0 1.1
CPU used by this session 636 0.2 63.6
CPU used when call started 192 0.1 19.2
CR blocks created 22 0.0 2.2
Cached Commit SCN referenced 0 0.0 0.0
DB time 26,295 7.3 2,629.5
DBWR checkpoint buffers written 3,705 1.0 370.5
DBWR thread checkpoint buffers wr 3,705 1.0 370.5
DBWR transaction table writes 21 0.0 2.1
DBWR undo block writes 874 0.2 87.4
Effective IO time 0 0.0 0.0
HSC Heap Segment Block Changes 1,651 0.5 165.1
Heap Segment Array Inserts 281 0.1 28.1
Heap Segment Array Updates 7 0.0 0.7
IMU CR rollbacks 0 0.0 0.0
IMU Flushes 7 0.0 0.7
IMU Redo allocation size 83,556 23.2 8,355.6
IMU commits 4 0.0 0.4
IMU ktichg flush 2 0.0 0.2
IMU undo allocation size 754,136 209.5 75,413.6
KTFB alloc req 5 0.0 0.5
KTFB alloc space (block) 327,680 91.1 32,768.0
KTFB alloc time (ms) 249 0.1 24.9
KTFB apply req 36 0.0 3.6
KTFB apply time (ms) 1,485 0.4 148.5
KTFB free req 36 0.0 3.6
KTFB free space (block) 4,128 1.2 412.8
KTFB free time (ms) 20 0.0 2.0
Requests to/from client 1,735 0.5 173.5
RowCR attempts 2 0.0 0.2
RowCR hits 2 0.0 0.2
SMON posted for undo segment shri 1 0.0 0.1
SQL*Net roundtrips to/from client 1,735 0.5 173.5
TBS Extension: tasks created 0 0.0 0.0
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
TBS Extension: tasks executed 0 0.0 0.0
active txn count during cleanout 229 0.1 22.9
background timeouts 29,355 8.2 2,935.5
blocks cleaned out using minact 153 0.0 15.3
buffer is not pinned count 415,911 115.6 41,591.1
buffer is pinned count 477,466 132.7 47,746.6
bytes received via SQL*Net from c 1,471,671 408.9 147,167.1
bytes sent via SQL*Net to client 1,104,556 306.9 110,455.6
calls to get snapshot scn: kcmgss 9,675 2.7 967.5
calls to kcmgas 3,325 0.9 332.5
calls to kcmgcs 8,565 2.4 856.5
cell physical IO interconnect byt 216,534,016 60,165.1 ############
change write time 11 0.0 1.1
cleanout - number of ktugct calls 308 0.1 30.8
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 78 0.0 7.8
cluster key scan block gets 138 0.0 13.8
cluster key scans 128 0.0 12.8
commit batch/immediate performed 5 0.0 0.5
commit batch/immediate requested 5 0.0 0.5
commit cleanout failures: block l 6 0.0 0.6
commit cleanout failures: callbac 13 0.0 1.3
commit cleanouts 2,581 0.7 258.1
commit cleanouts successfully com 2,562 0.7 256.2
commit immediate performed 5 0.0 0.5
commit immediate requested 5 0.0 0.5
commit txn count during cleanout 171 0.1 17.1
concurrency wait time 0 0.0 0.0
consistent changes 67 0.0 6.7
consistent gets 254,196 70.6 25,419.6
consistent gets examination 192,410 53.5 19,241.0
consistent gets examination (fast 192,408 53.5 19,240.8
consistent gets from cache 254,196 70.6 25,419.6
consistent gets pin 61,786 17.2 6,178.6
consistent gets pin (fastpath) 61,680 17.1 6,168.0
cursor authentications 168 0.1 16.8
data blocks consistent reads - un 4 0.0 0.4
db block changes 17,018 4.7 1,701.8
db block gets 16,962 4.7 1,696.2
db block gets from cache 16,962 4.7 1,696.2
db block gets from cache (fastpat 12,063 3.4 1,206.3
deferred (CURRENT) block cleanout 1,547 0.4 154.7
enqueue conversions 12,892 3.6 1,289.2
enqueue releases 448,872 124.7 44,887.2
enqueue requests 448,871 124.7 44,887.1
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 8,803 2.5 880.3
file io wait time 14,618,979 4,062.0 1,461,897.9
free buffer inspected 0 0.0 0.0
free buffer requested 2,503 0.7 250.3
heap block compress 6 0.0 0.6
immediate (CR) block cleanout app 78 0.0 7.8
immediate (CURRENT) block cleanou 281 0.1 28.1
in call idle wait time 12,042,680 3,346.1 1,204,268.0
index crx upgrade (positioned) 0 0.0 0.0
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index fast full scans (full) 42 0.0 4.2
index fetch by key 198,421 55.1 19,842.1
index scans kdiixs1 11,649 3.2 1,164.9
leaf node 90-10 splits 5 0.0 0.5
leaf node splits 94 0.0 9.4
lob reads 2 0.0 0.2
lob writes 2 0.0 0.2
lob writes unaligned 0 0.0 0.0
logical read bytes from cache 2,221,326,336 617,206.5 ############
logons cumulative 267 0.1 26.7
max cf enq hold time 15 0.0 1.5
messages received 1,159 0.3 115.9
messages sent 1,159 0.3 115.9
min active SCN optimization appli 33 0.0 3.3
no work - consistent read gets 54,189 15.1 5,418.9
non-idle wait count 15,827 4.4 1,582.7
non-idle wait time 879 0.2 87.9
opened cursors cumulative 8,049 2.2 804.9
parse count (describe) 0 0.0 0.0
parse count (failures) 9 0.0 0.9
parse count (hard) 729 0.2 72.9
parse count (total) 3,919 1.1 391.9
parse time cpu 32 0.0 3.2
parse time elapsed 36 0.0 3.6
physical read IO requests 105 0.0 10.5
physical read bytes 892,928 248.1 89,292.8
physical read total IO requests 8,163 2.3 816.3
physical read total bytes 132,915,200 36,931.2 ############
physical read total multi block r 0 0.0 0.0
physical reads 109 0.0 10.9
physical reads cache 109 0.0 10.9
physical reads cache prefetch 4 0.0 0.4
physical reads direct 0 0.0 0.0
physical reads direct (lob) 0 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical write IO requests 1,527 0.4 152.7
physical write bytes 30,351,360 8,433.3 3,035,136.0
physical write total IO requests 6,020 1.7 602.0
physical write total bytes 83,618,816 23,233.9 8,361,881.6
physical write total multi block 96 0.0 9.6
physical writes 3,705 1.0 370.5
physical writes direct 0 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes from cache 3,705 1.0 370.5
physical writes non checkpoint 3,663 1.0 366.3
pinned cursors current 0 0.0 0.0
process last non-idle time 3,599 1.0 359.9
recursive calls 51,632 14.4 5,163.2
recursive cpu usage 275 0.1 27.5
redo blocks checksummed by FG (ex 5,529 1.5 552.9
redo blocks written 11,878 3.3 1,187.8
redo blocks written (group 0) 0 0.0 0.0
redo blocks written (group 1) 0 0.0 0.0
redo entries 9,878 2.7 987.8
redo ordering marks 2 0.0 0.2
redo size 5,539,380 1,539.1 553,938.0
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo subscn max counts 8 0.0 0.8
redo synch time 1 0.0 0.1
redo synch time (usec) 8,982 2.5 898.2
redo synch time overhead (usec) 1,682,115,048 467,384.0 ############
redo synch time overhead count ( 77 0.0 7.7
redo synch time overhead count ( 2 0.0 0.2
redo synch time overhead count ( 0 0.0 0.0
redo synch time overhead count (1 1 0.0 0.1
redo synch time overhead count (i 1 0.0 0.1
redo synch writes 115 0.0 11.5
redo wastage 423,728 117.7 42,372.8
redo write active strands 1,611 0.5 161.1
redo write finish time 245,507 68.2 24,550.7
redo write gather time 4,652 1.3 465.2
redo write info find 81 0.0 8.1
redo write schedule time 5,792 1.6 579.2
redo write size count ( 4KB) 805 0.2 80.5
redo write size count ( 8KB) 27 0.0 2.7
redo write size count ( 16KB) 3 0.0 0.3
redo write size count ( 32KB) 6 0.0 0.6
redo write size count ( 128KB) 3 0.0 0.3
redo write size count ( 512KB) 0 0.0 0.0
redo write size count (1024KB) 1 0.0 0.1
redo write size count (inf) 2 0.0 0.2
redo write time 25 0.0 2.5
redo write time (usec) 246,136 68.4 24,613.6
redo write total time 267,294 74.3 26,729.4
redo write worker delay (usec) 0 0.0 0.0
redo write worker delay count 0 0.0 0.0
redo writes 847 0.2 84.7
redo writes (group 0) 0 0.0 0.0
redo writes (group 1) 0 0.0 0.0
redo writes adaptive all 847 0.2 84.7
redo writes adaptive worker 0 0.0 0.0
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 3 0.0 0.3
rows fetched via callback 185,328 51.5 18,532.8
securefile allocation bytes 0 0.0 0.0
securefile allocation chunks 0 0.0 0.0
securefile bytes non-transformed 1,334 0.4 133.4
securefile direct read bytes 0 0.0 0.0
securefile direct read ops 0 0.0 0.0
securefile direct write bytes 0 0.0 0.0
securefile direct write ops 0 0.0 0.0
securefile number of non-transfor 2 0.0 0.2
segment chunks allocation from di 0 0.0 0.0
segment dispenser allocations 0 0.0 0.0
segment dispenser load tasks 0 0.0 0.0
segment prealloc ops 1 0.0 0.1
segment prealloc tasks 0 0.0 0.0
segment prealloc time (ms) 32 0.0 3.2
segment prealloc ufs2cfs bytes 0 0.0 0.0
session connect time 3,599 1.0 359.9
session cursor cache hits 8,618 2.4 861.8
session logical reads 271,158 75.3 27,115.8
session pga memory max 198,760 55.2 19,876.0
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session uga memory 33,493,424 9,306.3 3,349,342.4
session uga memory max 120,503,368 33,482.5 ############
shared hash latch upgrades - no w 83 0.0 8.3
shared hash latch upgrades - wait 0 0.0 0.0
shared io pool buffer get success 0 0.0 0.0
sorts (memory) 1,995 0.6 199.5
sorts (rows) 83,800 23.3 8,380.0
sql area evicted 11 0.0 1.1
sql area purged 11 0.0 1.1
switch current caused by our pin 27 0.0 2.7
switch current to new buffer 27 0.0 2.7
table fetch by rowid 441,454 122.7 44,145.4
table fetch continued row 11 0.0 1.1
table scan blocks gotten 12,318 3.4 1,231.8
table scan disk non-IMC rows gott 170,443 47.4 17,044.3
table scan rows gotten 170,443 47.4 17,044.3
table scans (short tables) 903 0.3 90.3
temp space allocated (bytes) 0 0.0 0.0
total cf enq hold time 15 0.0 1.5
total number of cf enq holders 76 0.0 7.6
total number of times SMON posted 1 0.0 0.1
transaction rollbacks 5 0.0 0.5
undo change vector size 1,725,528 479.5 172,552.8
user I/O wait time 8 0.0 0.8
user calls 2,830 0.8 283.0
user commits 10 0.0 1.0
user logons cumulative 3 0.0 0.3
user logouts cumulative 4 0.0 0.4
workarea executions - optimal 1,602 0.5 160.2
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 40 39
opened cursors current 30 31
session cursor cache count 1,041 1,565
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: ORCL/orcl Snaps: 1-3
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 5,456
IDLE_TIME 1,434,597
SYS_TIME 4,199
USER_TIME 1,273
PHYSICAL_MEMORY_BYTES 8,589,467,648
NUM_CPUS 4
NUM_CPU_CORES 4
NUM_CPU_SOCKETS 1
TCP_RECEIVE_SIZE_MAX 64,240
-------------------------------------------------------------
OS Statistics - detail DB/Inst: ORCL/orcl Snaps: 1-3
Snap Snapshot
Id Day Time Load %Busy %User %System %WIO %WCPU
------ --------------- ------ ------ ------ ------- ------ ------
1 Wed 12 14:10:33
2 Wed 12 14:40:33 .2 .1 .2
3 Wed 12 15:10:32 .5 .1 .4
-------------------------------------------------------------
IO Stat by Function - summary DB/Inst: ORCL/orcl Snaps: 1-3
->Data Volume values suffixed with M,G,T,P are in multiples of 1024,
other values suffixed with K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc
---------- Read --------- --------- Write -------- --- Wait ----
Data Requests Data Data Requests Data Avg
Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
Others 126M 2.2 .0M 45M .8 .0M 9498 0.0
Buffer Cache Re 1M .0 .0M 106 0.0
-------------------------------------------------------------
IO Stat by Function - detail DB/Inst: ORCL/orcl Snaps: 1-3
->ordered by Data Volume (Read+Write) desc
----------- Read ---------- ----------- Write ---------
Small Large Small Large Small Large Small Large
Read Read Data Data Write Write Data Data
Function Reqs Reqs Read Read Reqs Reqs Writn Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
Others 8058 126M 2880 45M
Buffer Cache Reads 106 1M
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 1-3
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
STATSPACK_DATA
0 0 0.0 805 0 0 0.0
SYSTEM
27 0 0.7 1.0 437 0 0 0.0
UNDOTBS1
1 0 0.0 1.0 221 0 0 0.0
SYSAUX
78 0 0.8 1.1 64 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: ORCL/orcl Snaps: 1-3
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
STATSPACK_DATA C:\APP\USER1\ORADATA\ORCL\SP01.DBF
0 0 805 0 0
SYSAUX C:\APP\USER1\ORADATA\ORCL\SYSAUX01.DBF
78 0 0.8 1 1.1 64 0 0
SYSTEM C:\APP\USER1\ORADATA\ORCL\SYSTEM01.DBF
27 0 0.7 1 1.0 437 0 0
UNDOTBS1 C:\APP\USER1\ORADATA\ORCL\UNDOTBS01.DBF
1 0 0.0 1 1.0 221 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: ORCL/orcl Snaps: 1-3
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSTEM C:\APP\USER1\ORADATA\ORCL\SYSTEM01.DBF
26 0 0 0 0 0
SYSAUX C:\APP\USER1\ORADATA\ORCL\SYSAUX01.DBF
74 0 0 0 0 0
UNDOTBS1 C:\APP\USER1\ORADATA\ORCL\UNDOTBS01.DBF
1 0 0 0 0 0
-------------------------------------------------------------
IO Stats Function Detail DB/Inst: ORCL/orcl Snaps: 1-3
Small Small Large Large
Function FileType Read Write Read Write Wait Time
Name Name (MB) (MB) (MB) (MB) Waits (ms)
------------ ------------ ------- ------- ------- ------- --------- -----------
DBWR Control File 0 0 0 0 0 0
DBWR Data File 0 18 0 11 0 0
DBWR Temp File 0 0 0 0 0 0
LGWR Control File 0 0 0 0 0 0
LGWR Data File 0 0 0 0 0 0
LGWR Log File 0 2 0 4 0 0
LGWR Other(8) 0 0 0 0 847 211
Streams AQ Data File 0 0 0 0 0 0
Buffer Cache Data File 1 0 0 0 106 78
Buffer Cache Temp File 0 0 0 0 0 0
Inmemory Pop Data File 0 0 0 0 0 0
Inmemory Pop Temp File 0 0 0 0 0 0
Archive Mana Control File 126 45 0 0 8058 405
Archive Mana Data File 0 0 0 0 0 0
Archive Mana Other(8) 0 0 0 0 1440 556
Archive Mana Parameter Fi 0 0 0 0 0 0
Archive Mana Password Fil 0 0 0 0 0 0
Archive Mana External Tab 0 0 0 0 0 0
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 0 3323 21492 663552 663552
E 0 0 2268 10259 7396 663552 7396
-------------------------------------------------------------
Memory Dynamic Components DB/Inst: ORCL/orcl Snaps: 1-3
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode
Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 1,792 0 SHRINK/IMM 12-Mar 13:44:18
PGA Target 832 0 STATIC
SGA Target 2,464 0 STATIC
Shared IO Pool 128 0 GROW/IMM 12-Mar 13:44:18
java pool 16 0 STATIC
large pool 32 0 SHRINK/DEF 12-Mar 13:44:09
shared pool 480 0 STATIC
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: ORCL/orcl End Snap: 3
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 176 .1 22 1.0 8 3 8.3
D 352 .2 43 1.0 8 3 8.3
D 528 .3 65 1.0 8 3 8.3
D 704 .4 86 1.0 8 3 8.3
D 880 .5 108 1.0 8 3 8.3
D 1,056 .6 129 1.0 8 3 8.3
D 1,232 .7 151 1.0 8 3 8.3
D 1,408 .8 172 1.0 8 3 8.3
D 1,584 .9 194 1.0 8 3 8.3
D 1,760 1.0 215 1.0 8 3 8.3
D 1,792 1.0 219 1.0 8 3 8.3
D 1,936 1.1 237 1.0 8 3 8.3
D 2,112 1.2 259 1.0 8 3 8.3
D 2,288 1.3 280 1.0 8 3 8.3
D 2,464 1.4 302 1.0 8 3 8.3
D 2,640 1.5 323 1.0 8 3 8.3
D 2,816 1.6 345 1.0 8 3 8.3
D 2,992 1.7 366 1.0 8 3 8.3
D 3,168 1.8 388 1.0 8 3 8.3
D 3,344 1.9 409 1.0 8 3 8.3
D 3,520 2.0 431 1.0 8 3 8.3
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: ORCL/orcl Snaps: 1-3
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 219K 100 272,351 110 3,705 0 0 0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas
-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of WorkArea memory under Manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 182 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 820 668 122.9 0.0 .0 .0 .0 102,400
E 820 669 121.9 0.0 .0 .0 .0 102,400
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: ORCL/orcl Snaps: 1-3
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 1,385 1,385 0 0
64K 128K 16 16 0 0
128K 256K 6 6 0 0
512K 1024K 150 150 0 0
1M 2M 43 43 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: ORCL/orcl End Snap: 3
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
---------- ------ -------------- -------------- ---------- ------ ----------
103 0.1 365 0 0.2 100.0 0
205 0.3 365 0 0.2 100.0 0
410 0.5 365 0 0.2 100.0 0
615 0.8 365 0 0.2 100.0 0
820 1.0 365 0 0.2 100.0 0
984 1.2 365 0 0.2 100.0 0
1,148 1.4 365 0 0.2 100.0 0
1,312 1.6 365 0 0.2 100.0 0
1,476 1.8 365 0 0.2 100.0 0
1,640 2.0 365 0 0.2 100.0 0
2,460 3.0 365 0 0.2 100.0 0
3,280 4.0 365 0 0.2 100.0 0
4,920 6.0 365 0 0.2 100.0 0
6,560 8.0 365 0 0.2 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 122.9 81.9 15.1 2.9 4.5 23 23 42
Other 105.7 2.5 4.1 21 21 42
Freeable 15.2 .0 1.5 1.6 5 10
PL/SQL 1.1 1.0 .0 .1 0 0 40
SQL 1.0 .7 .1 .2 1 3 9
E -------- 121.9 80.4 16.4 3.0 4.6 23 23 41
Other 103.4 2.5 4.1 21 21 41
Freeable 16.4 .0 1.5 1.5 5 11
SQL 1.1 1.0 .1 .2 1 2 8
PL/SQL 1.0 .9 .0 .1 0 0 39
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-3
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 28 MMON -------- 23.5 5.0 2.1 23.5 23.5
Other 20.9 20.9 20.9
Freeable 2.1 .0 2.1
PL/SQL .4 .4 .4 .5
SQL .0 .0 .0 1.2
33 TT00 -------- 18.9 17.4 .0 18.9 18.9
Other 18.9 18.9 18.9
PL/SQL .0 .0 .0 .0
10 DBW0 -------- 8.3 6.8 1.3 8.3 8.3
Other 7.0 7.0 7.0
Freeable 1.3 .0 1.3
PL/SQL .0 .0 .0 .0
37 CJQ0 -------- 8.2 2.5 5.2 8.2 8.2
Freeable 5.2 .0 5.2
Other 3.0 3.0 3.0
SQL .0 .0 .0 1.1
PL/SQL .0 .0 .0 .0
39 J000 -------- 7.0 3.7 3.0 7.0 7.0
Other 3.6 3.6 3.6
Freeable 3.1 .0 3.1
PL/SQL .2 .2 .2 .2
SQL .2 .0 .2 3.1
16 DIA0 -------- 4.0 3.9 .0 4.0 4.0
Other 4.0 4.0 4.0
PL/SQL .0 .0 .0 .0
45 Q002 -------- 3.7 1.9 1.6 3.7 3.7
Other 1.8 1.8 1.8
Freeable 1.6 .0 1.6
PL/SQL .3 .3 .3 .3
SQL .0 .0 .0 1.1
23 W000 -------- 3.1 2.2 .8 3.1 3.1
Other 1.7 1.7 1.7
Freeable .8 .0 .8
SQL .6 .6 .6 1.4
PL/SQL .0 .0 .0 .0
17 LGWR -------- 2.4 1.3 .7 2.4 2.4
Other 1.7 1.7 1.7
Freeable .7 .0 .7
PL/SQL .0 .0 .0 .0
12 SMON -------- 2.3 1.8 .3 2.3 2.3
Other 2.0 2.0 2.0
Freeable .3 .0 .3
SQL .0 .0 .0 .5
PL/SQL .0 .0 .0 .0
24 LREG -------- 2.2 1.6 .1 2.2 2.2
Other 2.0 2.0 2.0
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .4
22 RECO -------- 2.0 1.6 .1 2.0 2.0
Other 1.8 1.8 1.8
Freeable .1 .0 .1
SQL .1 .0 .1 .4
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-3
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 22 PL/SQL .0 .0 .0 .0
38 SHAD -------- 1.7 1.2 .0 1.7 1.7
Other 1.6 1.6 1.6
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 .1
11 DBRM -------- 1.6 1.3 .0 1.6 1.6
Other 1.6 1.6 1.6
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .0
18 CKPT -------- 1.6 1.1 .0 1.6 1.6
Other 1.6 1.6 1.6
PL/SQL .0 .0 .0 .0
30 D000 -------- 1.5 1.4 .0 1.5 1.5
Other 1.5 1.5 1.5
21 SMCO -------- 1.4 1.2 .0 1.4 1.4
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .0
36 AQPC -------- 1.3 1.1 .0 1.3 1.3
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .0
25 W001 -------- 1.3 1.1 .0 1.3 1.3
Other 1.3 1.3 1.3
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
14 SVCB -------- 1.3 1.2 .0 1.3 1.3
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
E 28 MMON -------- 23.5 5.0 2.1 23.5 23.5
Other 20.9 20.9 20.9
Freeable 2.1 .0 2.1
PL/SQL .4 .4 .4 .5
SQL .0 .0 .0 1.2
33 TT00 -------- 18.9 17.4 .0 18.9 18.9
Other 18.9 18.9 18.9
PL/SQL .0 .0 .0 .0
10 DBW0 -------- 9.8 7.0 2.6 9.8 9.8
Other 7.1 7.1 7.1
Freeable 2.6 .0 2.6
PL/SQL .0 .0 .0 .0
37 CJQ0 -------- 8.2 2.5 5.2 8.2 8.2
Freeable 5.2 .0 5.2
Other 3.0 3.0 3.0
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.1
25 J000 -------- 4.5 2.7 1.5 4.5 4.5
Other 2.6 2.6 2.6
Freeable 1.5 .0 1.5
PL/SQL .2 .2 .2 .2
SQL .2 .1 .2 2.4
16 DIA0 -------- 4.0 3.9 .0 4.0 4.0
Other 4.0 4.0 4.0
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-3
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 16 PL/SQL .0 .0 .0 .0
45 Q002 -------- 3.7 1.9 1.6 3.7 3.7
Other 1.8 1.8 1.8
Freeable 1.6 .0 1.6
PL/SQL .3 .3 .3 .3
SQL .0 .0 .0 1.1
23 W000 -------- 3.1 2.2 .8 3.1 3.1
Other 1.7 1.7 1.7
Freeable .8 .0 .8
SQL .6 .6 .6 1.4
PL/SQL .0 .0 .0 .0
27 W002 -------- 3.0 1.7 1.3 3.0 3.0
Other 1.5 1.5 1.5
Freeable 1.3 .0 1.3
SQL .3 .3 .3 1.6
PL/SQL .0 .0 .0 .0
17 LGWR -------- 2.4 1.3 .7 2.4 2.4
Other 1.7 1.7 1.7
Freeable .7 .0 .7
PL/SQL .0 .0 .0 .0
12 SMON -------- 2.3 1.8 .3 2.3 2.3
Other 2.0 2.0 2.0
Freeable .3 .0 .3
SQL .0 .0 .0 .5
PL/SQL .0 .0 .0 .0
24 LREG -------- 2.2 1.6 .1 2.2 2.2
Other 2.0 2.0 2.0
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .4
22 RECO -------- 2.0 1.6 .3 2.0 2.0
Other 1.8 1.8 1.8
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .4
11 DBRM -------- 1.6 1.3 .0 1.6 1.6
Other 1.6 1.6 1.6
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .0
18 CKPT -------- 1.6 1.1 .0 1.6 1.6
Other 1.6 1.6 1.6
PL/SQL .0 .0 .0 .0
30 D000 -------- 1.5 1.4 .0 1.5 1.5
Other 1.5 1.5 1.5
21 SMCO -------- 1.4 1.2 .0 1.4 1.4
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .0
36 AQPC -------- 1.3 1.1 .0 1.3 1.3
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .0
14 SVCB -------- 1.3 1.2 .0 1.3 1.3
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-3
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 14 Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
2 PMON -------- 1.2 1.1 .0 1.2 1.2
Other 1.2 1.2 1.2
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Undo Segment Summary DB/Inst: ORCL/orcl Snaps: 1-3
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
2 .3 577 426 3 15/20.3 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: ORCL/orcl Snaps: 1-3
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
12-Mar 15:03 51 54 0 3 15 0/0 0/0/0/0/0/0
12-Mar 14:53 0 0 0 0 15 0/0 0/0/0/0/0/0
12-Mar 14:43 109 144 0 3 15 0/0 0/0/0/0/0/0
12-Mar 14:33 0 34 0 0 15 0/0 0/0/0/0/0/0
12-Mar 14:23 0 16 426 0 20 0/0 0/0/0/0/0/0
12-Mar 14:13 112 329 397 3 20 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ Background: interrupt 2 0.0 0 2 0.0
AQ Coord jobx_kwsbgsgn l 120 0.0 0 0
AQ Sharded master pool l 120 0.0 0 0
AQ deq hash table latch 2 0.0 0 0
ASM db client latch 2,462 0.0 0 0
ASM map operation hash t 2 0.0 0 0
ASM network state latch 57 0.0 0 0
ASM remote client latch 57 0.0 0 0
AWR Alerted Metric Eleme 27,246 0.0 0 0
Change Notification Hash 1,198 0.0 0 0
Column stats entry latch 2 0.0 0 0
Consistent RBA 848 0.0 0 0
DML lock allocation 2,788 0.0 0 0
Event Group Locks 531 0.0 0 0
File State Object Pool P 2 0.0 0 0
GCS logfile block 2 0.0 0 0
GCS logfile write queue 2 0.0 0 0
Hang Manager wait event 240 0.0 0 0
I/O Staticstics latch 2 0.0 0 0
ILM Stats Stripe Latch 2 0.0 0 0
ILM Stats main anchor la 607 0.0 0 0
ILM access tracking exte 2 0.0 0 0
ILM activity tracking la 2 0.0 0 0
IM Global dictionary lat 2 0.0 0 0
IM Global dictionary que 2 0.0 0 0
IM Global dictionary rw 2 0.0 0 0
IM area sb latch 2 0.0 0 0
IM area scb latch 2 0.0 0 0
IM emb latch 2 0.0 0 0
IM seg hdr latch 2 0.0 0 0
IMFS defer write list 2 0.0 0 0
IPC stats buffer allocat 2 0.0 0 0
In memory undo latch 20,713 0.0 0 10,269 0.0
JS Sh mem access 10,251 0.0 0 0
JS mem alloc latch 11 0.0 0 0
JS queue access latch 13 0.0 0 0
JS queue state obj latch 430,508 0.0 0 0
JS slv state obj latch 18 0.0 0 0
KCNIBR - invalid block r 2 0.0 0 0
KDCStatHash latch 2 0.0 0 0
KDMADO action list latch 2 0.0 0 0
KDMADO bll latch 2 0.0 0 0
KDMADO latch 2 0.0 0 0
KFC FX Hash Latch 2 0.0 0 0
KFC Hash Latch 2 0.0 0 0
KFCL LE Freelist 2 0.0 0 0
KFIAS client latch 2 0.0 0 0
KFIAS cluster latch 2 0.0 0 0
KFIAS endpoint latch 2 0.0 0 0
KFIAS node latch 2 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
KFIAS process latch 2 0.0 0 0
KFIAS queue latch 2 0.0 0 0
KGNFS-NFS:SHM structure 2 0.0 0 0
KGNFS-NFS:SVR LIST 2 0.0 0 0
KJC message pool free li 2 0.0 0 0
KJC message pool pending 2 0.0 0 0
KJCT flow control latch 2 0.0 0 0
KMG MMAN ready and start 1,200 0.0 0 0
KSFS OFS ctx level paren 2 0.0 0 0
KSFS OFS req layer paren 2 0.0 0 0
KSFS OFS sess layer pare 2 0.0 0 0
KSFS Receive and Send Qu 2 0.0 0 0
KSFS id table parent lat 2 0.0 0 0
KSFS ksfs_node latch 2 0.0 0 0
KSFSD name cache parent 2 0.0 0 0
KSGL LS Segment latch 2 0.0 0 0
KSGL SGA anchor latch 2 0.0 0 0
KSIPC SGA allocation par 59 0.0 0 0
KSK PDB IO STAT 2 0.0 0 0
KSRMA SGA anchor latch 2 0.0 0 0
KSRMF SGA anchor latch 2 0.0 0 0
KSXR hang analysis phase 2 0.0 0 0
KSXR hang analysis phase 2 0.0 0 0
KTF sga latch 7 0.0 0 1,106 0.0
KTILM latch 2 0.0 0 0
KTU in-memory txn table 2 0.0 0 0
KWQP Prop Status 2 0.0 0 0
Latch for NonceHistory 2 0.0 0 0
Locator state objects po 2 0.0 0 0
Lsod array latch 2 0.0 0 0
MQL Tracking Latch 0 0 69 0.0
Memory Management Latch 0 0 1,200 0.0
Memory Queue 2 0.0 0 0
Memory Queue Message Sub 2 0.0 0 0
Memory Queue Message Sub 2 0.0 0 0
Memory Queue Message Sub 2 0.0 0 0
Memory Queue Message Sub 2 0.0 0 0
Memory Queue Subscriber 2 0.0 0 0
Message cache latch 2 0.0 0 0
MinActiveScn Latch 120 0.0 0 0
Mutex 2 0.0 0 0
Mutex Stats 2 0.0 0 0
Nologging Standby SCN Ti 1,099 0.0 0 0
OS process 2,348 0.0 0 0
OS process allocation 8,335 0.0 0 0
OS process: request allo 525 0.0 0 0
PDB Hash Table Latch 2 0.0 0 0
PDB LRU structure 70 0.0 0 0
PL/SQL warning settings 267 0.0 0 0
PX hash array latch 2 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Parent latch for depende 2 0.0 0 0
Parent latch for query h 2 0.0 0 0
Parent latch for segment 2 0.0 0 0
QMT 2 0.0 0 0
Report Request stats lat 240 0.0 0 0
Request holder compeltio 520 0.0 0 0
Retry Ht elm latch 2 0.0 0 0
Retry bkt latch 2 0.0 0 0
SGA IO buffer pool latch 133 0.0 0 153 0.0
SGA Logging Bkt Latch 2 0.0 0 0
SGA Logging Log Latch 9,698 0.0 0 0
SGA blob parent 2 0.0 0 0
SGA bucket locks 2 0.0 0 0
SGA heap locks 2 0.0 0 0
SGA pool locks 2 0.0 0 0
SQL memory manager latch 2 0.0 0 1,200 0.0
SQL memory manager worka 81,228 0.0 0 0
SR Stats Stripe Latch 2 0.0 0 0
SR Stats main anchor lat 26 0.0 0 0
Sched IM Job latch 59 0.0 0 0
Sched InMem Job Cache 4 0.0 0 0
Shared B-Tree 137 0.0 0 0
Shared context latch 2 0.0 0 0
Streams Generic 2 0.0 0 0
Subscriber Ht elm latch 2 0.0 0 0
Testing 2 0.0 0 0
Token Manager 2 0.0 0 0
Txn Ht elm latch 2 0.0 0 0
Txn bkt latch 2 0.0 0 0
WCR: sync 2 0.0 0 0
Write State Object Pool 2 0.0 0 0
XDB NFS Security Latch 2 0.0 0 0
XDB unused session pool 2 0.0 0 0
XDB used session pool 2 0.0 0 0
active checkpoint queue 1,271 0.0 0 0
active service list 167,092 0.0 0 4,147 0.0
archive destination 120 0.0 0 0
buffer pool 2 0.0 0 0
business card 2 0.0 0 0
cache buffer handles 1,469 0.0 0 0
cache buffers chains 402,565 0.0 0.0 0 2,450 0.0
cache buffers lru chain 3,800 0.0 0 6,227 0.0
cache table scan latch 1 0.0 0 1 0.0
call allocation 2,365 0.0 0 0
change notification clie 2 0.0 0 0
channel handle pool latc 1,053 0.0 0 0
channel operations paren 2,314 0.0 0 0
checkpoint queue latch 22,926 0.0 0 2,692 0.0
client/application info 1,764 0.0 0 0
compile environment latc 267 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
corrupted undo seg latch 5 0.0 0 0
cp handoff latch 2 0.0 0 0
cp pool latch 2 0.0 0 0
cp server hash latch 2 0.0 0 0
cp sga latch 57 0.0 0 0
cp srv type state latch 3,602 0.0 0 0
cp srv type wait latch 2 0.0 0 0
cr slave free list 2 0.0 0 0
cvmap freelist lock 2 0.0 0 0
deferred cleanup latch 57 0.0 0 0
dispatcher info 26 0.0 0 0
dml lock allocation 57 0.0 0 0
done queue latch 2 0.0 0 0
dtp latch 263 0.0 0 0
dummy allocation 537 0.0 0 0
eighth spare latch - X p 2 0.0 0 0
eleventh spare latch - c 2 0.0 0 0
enqueue freelist latch 2 0.0 0 891,983 0.0
enqueue hash chains 910,767 0.0 0 0
enqueues 66 0.0 0 0
fifteenth spare latch - 2 0.0 0 0
file cache latch 247 0.0 0 0
flash file access latch 2 0.0 0 0
flashback copy 2 0.0 0 0
fourteenth spare latch - 2 0.0 0 0
fourth Audit Vault latch 2 0.0 0 0
gc element 2 0.0 0 0
gcs commit scn state 2 0.0 0 0
gcs nodemap pool 2 0.0 0 0
gcs opaque info freelist 2 0.0 0 0
gcs partitioned table ha 2 0.0 0 0
gcs pcm hashed value buc 2 0.0 0 0
gcs resource freelist 2 0.0 0 0
gcs resource hash 2 0.0 0 0
gcs resource scan list 2 0.0 0 0
gcs shadows freelist 2 0.0 0 0
ges cached resource list 2 0.0 0 0
ges domain table 2 0.0 0 0
ges enqueue table freeli 2 0.0 0 0
ges group table 2 0.0 0 0
ges process hash list 2 0.0 0 0
ges process parent latch 2 0.0 0 0
ges resource hash list 2 0.0 0 0
ges resource scan list 2 0.0 0 0
ges resource table freel 2 0.0 0 0
ges timeout list 2 0.0 0 0
ges value block free lis 2 0.0 0 0
global KZLD latch for au 2 0.0 0 0
global tx hash mapping 2 0.0 0 0
granule from data transf 2 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
gws:Sharding global desc 240 0.0 0 0
hash table Sql Plan Find 40 0.0 0 187 0.0
hash table column usage 325 0.0 0 15,291 0.0
hash table expression us 468 0.0 0 0
hash table modification 27 0.0 0 0
heartbeat check 2 0.0 0 0
imc buf hdl lat 2 0.0 0 0
imc cr clone buf 2 0.0 0 0
imc hash lat 2 0.0 0 0
imc sj lat 2 0.0 0 0
imc srchsp lat 2 0.0 0 0
imc-txn-wrk-lat 2 0.0 0 0
in-memory area latch 2 0.0 0 0
in-memory columnar segme 2 0.0 0 0
in-memory columnar ts ex 2 0.0 0 0
in-memory global pool la 2 0.0 0 0
internal temp table obje 5 0.0 0 0
intra txn parallel recov 2 0.0 0 0
io pool granule metadata 2 0.0 0 0
job workq parent latch 7 0.0 0 5 0.0
job_queue_processes free 21 0.0 0 0
job_queue_processes para 10 0.0 0 0
jslv pdb context latch 10 0.0 0 0
k2q lock allocation 2 0.0 0 0
kcb DW scan objtemp hash 2 0.0 0 0
kcn buffer chains 2 0.0 0 0
kdlx hb parent latch 2 0.0 0 0
keiut hash table modific 17 0.0 0 0
kgb parent 2 0.0 0 0
kgnfs mount latch 2 0.0 0 0
kjci objects freelist la 2 0.0 0 0
kjci process context lat 2 0.0 0 4,908 0.0
kjoedcso state object fr 57 0.0 0 0
kjoeq omni enqueue hash 2 0.0 0 0
kjoer owner hash bucket 2 0.0 0 0
kjsca protect pkey hash 2 0.0 0 0
kjsca protect service ha 2 0.0 0 0
krso process 126 0.0 0 0
ksevn object root latch 1,200 0.0 0 0
ksfv messages 2 0.0 0 0
ksi resource reuse count 2 0.0 0 0
ksim group membership ca 2 0.0 0 0
kss move lock 504 0.0 0 0
ksuosstats global area 246 0.0 0 1,378 0.0
ksv allocation latch 1,061 0.0 0 0
ksv class latch 1,006 0.1 0.0 0 0
ksv msg queue latch 2 0.0 0 0
ksz_so allocation latch 525 0.0 0 0
ktfbn latch 770 0.0 0 0
ktm adg jrnls 2 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ktm adg lookuptable 2 0.0 0 0
ktm adg space 2 0.0 0 0
ktm adg stages 2 0.0 0 0
ktm global data 74 0.0 0 0
ktm prv jrnls 2 0.0 0 0
ktmpj HT ls 2 0.0 0 0
kwqbsn:qsga 135 0.0 0 0
kwslbmdl: metadata latch 120 0.0 0 0
kwslbql: queue latch 2 0.0 0 0
kwsptQcachLt: queue cach 2 0.0 0 0
kwsptTrncTsksLt: trunc t 2 0.0 0 0
kwsptjobAdPtLt: AdPt lis 86 0.0 0 0
kwsslLat: skiplist latch 2 0.0 0 0
kxfxscanrate_latch 2 0.0 0 0
lgwr LWN SCN 1,938 0.1 0.0 0 0
list of block allocation 70 0.0 0 0
loader state object free 2 0.0 0 0
lob segment dispenser la 2 0.0 0 0
lob segment hash table l 28 0.0 0 0
lob segment query latch 2 0.0 0 0
lock DBA buffer during m 2 0.0 0 0
log write info 0 0 847 0.0
log write worker phase 2 0.0 0 0
logical standby cache 2 0.0 0 0
logminer context allocat 2 0.0 0 0
logminer local 2 0.0 0 0
logminer work area 2 0.0 0 0
longop free list parent 2 0.0 0 0
managed standby 120 0.0 0 0
message bitmap latch 2 0.0 0 0
message pool operations 110 0.0 0 0
messages 62,546 0.0 0.0 0 0
msg queue latch 2 0.0 0 0
multiblock read objects 2 0.0 0 0
name-service namespace b 2 0.0 0 0
ncodef allocation latch 57 0.0 0 0
ninth spare latch - X pa 2 0.0 0 0
object queue header free 100 0.0 0 0
object queue header oper 12,689 0.0 0 0
object queue memory 24 0.0 0 0
object stats modificatio 656 0.0 0 0
parallel query alloc buf 2 0.0 0 436 0.0
parallel query stats 2 0.0 0 0
parameter table manageme 666 0.0 0 0
pdb enqueue hash chains 2 0.0 0 0
peshm 2 0.0 0 0
pesom_free_list 2 0.0 0 0
pesom_hash_node 2 0.0 0 0
pkey global statistics 2 0.0 0 0
pmon dead latch 57 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
post/wait queue 3,732 0.0 0 3,680 0.0
process allocation 784 0.0 0 0
process group creation 525 0.0 0 0
process pkey statistics 2 0.0 0 0
process queue 2 0.0 0 0
process queue reference 2 0.0 0 0
qmn task queue latch 528 24.6 1.0 0 0
query server freelists 2 0.0 0 0
query server process 2,397 0.0 0 0
queued dump request 12 0.0 0 0
queuing load statistics 2 0.0 0 0
recovery domain hash buc 2 0.0 0 0
redo allocation 35,915 0.0 0.0 0 0
redo copy 2 0.0 0 9,890 0.4
redo transport task 6 0.0 0 0
redo writing 6,978 0.0 0 0
remote tool request latc 268 0.0 0 0
resmgr group change latc 966 0.0 0 0
resmgr:active threads 1,673 0.0 0 32,955 0.0
resmgr:actses change gro 985 0.0 0 0
resmgr:actses change sta 245 0.0 0 0
resmgr:free threads list 1,011 0.0 0 0
resmgr:plan CPU method 2 0.0 0 0
resmgr:plan change alloc 2 0.0 0 0
resmgr:plan change latch 2 0.0 0 0
resmgr:resource group CP 10 0.0 0 0
resmgr:schema config 455 0.0 0 60 0.0
resmgr:session queuing 2 0.0 0 0
row cache objects 2 0.0 0 0
rules engine rule set st 200 0.0 0 0
second Audit Vault latch 2 0.0 0 0
sequence cache 54 0.0 0 0
service drain list 3,599 0.0 0 0
session allocation 752 0.0 0 715 0.0
session idle bit 6,828 0.0 0 0
session queue latch 2 0.0 0 0
session state list latch 570 0.0 0 0
session statistics 537 0.0 0 0
session switching 322 0.0 0 0
session timer 1,200 0.0 0 0
seventh spare latch - X 2 0.0 0 0
sga hash table parent la 2 0.0 0 0
shard latch 2 0.0 0 0
shared pool 98,800 0.0 0 0
shared pool sim alloc 43 0.0 0 0
shared pool simulator 849 0.0 0 0
shared server configurat 1,205 0.0 0 0
sim partition latch 2 0.0 0 0
simulator hash latch 9,260 0.0 0 0
simulator lru latch 2 0.0 0 8,944 0.0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
sixth spare latch - X pa 2 0.0 0 0
sort extent pool 125 0.0 0 0
space background state o 2 0.0 0 0
space background task la 1,459 11.2 1.0 0 2,409 0.0
subscriber Ht bkt 2 0.0 0 0
tablespace key chain 2 0.0 0 0
temp lob duration state 8 0.0 0 0
temporary table state ob 4 0.0 0 0
tenth spare latch - X pa 2 0.0 0 0
test excl. parent l0 2 0.0 0 0
test excl. parent2 l0 2 0.0 0 0
test excl. parent2 lmid 2 0.0 0 0
test mode exclusive 2 0.0 0 0
test open exclusive 2 0.0 0 0
test pdb exclusive 2 0.0 0 0
test shared parent2 lmid 2 0.0 0 0
thirteenth spare latch - 2 0.0 0 0
threshold alerts latch 109 0.0 0 0
transaction allocation 134 0.0 0 0
twelfth spare latch - ch 2 0.0 0 0
twenty-fifth spare latch 2 0.0 0 0
twenty-first spare latch 2 0.0 0 0
twenty-fourth spare latc 2 0.0 0 0
twenty-second spare latc 2 0.0 0 0
twenty-third spare latch 2 0.0 0 0
undo global data 5,340 0.0 0 0
virtual circuit buffers 2 0.0 0 0
virtual circuit holder 2 0.0 0 0
virtual circuit queues 2 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown DB/Inst: ORCL/orcl Snaps: 1-3
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
space background task latc 1,459 163 163 0
qmn task queue latch 528 130 130 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: ORCL/orcl Snaps: 1-3
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
qmn task queue latch kwqmnmvtsks: delay to read 0 127 0
qmn task queue latch kwqmnaddtsk: add task 0 3 0
space background task la ktsj_detach_task 0 65 0
space background task la ktsj_grab_task 0 31 163
unknown latch No latch 0 67 0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: ORCL/orcl Snaps: 1-3
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 78 0.0 0 2 1
dc_global_oids 22 9.1 0 0 167
dc_histogram_defs 5,402 21.1 0 11 3,951
dc_object_grants 1,449 1.7 0 0 132
dc_objects 7,314 1.2 0 14 2,480
dc_profiles 6 0.0 0 0 1
dc_props 2,917 0.0 0 0 70
dc_rollback_segments 939 0.0 0 0 12
dc_segments 75 2.7 0 8 633
dc_tablespaces 319 0.0 0 0 20
dc_users 12,137 0.0 20 0.0 0 313
outstanding_alerts 1 0.0 0 0 6
sch_lj_objs 1 100.0 0 0 3
sch_lj_oids 11 0.0 0 0 5
-------------------------------------------------------------
Library Cache Activity DB/Inst: ORCL/orcl Snaps: 1-3
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
ACCOUNT_STATUS 8 0.0 0 0 0
AUDIT POLICY 10 0.0 10 0.0 0 0
BODY 604 0.0 1,463 0.0 0 0
CLUSTER 2 0.0 2 0.0 0 0
DBLINK 13 0.0 0 0 0
EDITION 258 0.0 516 0.0 0 0
KGLNEDSYNC 1 0.0 0 0 0
OBJECT ID 2 100.0 0 0 0
SCHEMA 136 0.7 0 0 0
SQL AREA 1,774 40.1 12,648 10.9 6 11
SQL AREA BUILD 406 97.3 0 0 0
SQL AREA STATS 411 98.5 411 98.5 0 0
TABLE/PROCEDURE 1,523 6.0 8,036 4.1 128 0
TRIGGER 7 0.0 7 0.0 0 0
-------------------------------------------------------------
Rule Sets DB/Inst: ORCL/orcl Snaps: 1-3
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: ORCL/orcl End Snap: 3
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
240 .5 4 1,055 103 .9 12 3.0 46,362
288 .6 50 3,131 111 1.0 4 1.0 49,001
320 .7 82 4,399 111 1.0 4 1.0 49,035
336 .7 89 4,852 111 1.0 4 1.0 49,035
352 .7 89 4,852 111 1.0 4 1.0 49,035
368 .8 89 4,852 111 1.0 4 1.0 49,035
384 .8 89 4,852 111 1.0 4 1.0 49,035
400 .8 89 4,852 111 1.0 4 1.0 49,036
416 .9 89 4,852 111 1.0 4 1.0 49,048
432 .9 89 4,852 111 1.0 4 1.0 49,220
448 .9 89 4,852 111 1.0 4 1.0 49,517
464 1.0 89 4,852 111 1.0 4 1.0 49,815
480 1.0 89 4,852 111 1.0 4 1.0 50,129
496 1.0 89 4,852 111 1.0 4 1.0 50,129
512 1.1 89 4,852 111 1.0 4 1.0 50,129
528 1.1 89 4,852 111 1.0 4 1.0 50,129
544 1.1 89 4,852 111 1.0 4 1.0 50,129
560 1.2 89 4,852 111 1.0 4 1.0 50,129
576 1.2 89 4,852 111 1.0 4 1.0 50,129
592 1.2 89 4,852 111 1.0 4 1.0 50,129
608 1.3 89 4,852 111 1.0 4 1.0 50,129
624 1.3 89 4,852 111 1.0 4 1.0 50,129
672 1.4 89 4,852 111 1.0 4 1.0 50,129
720 1.5 89 4,852 111 1.0 4 1.0 50,129
768 1.6 89 4,852 111 1.0 4 1.0 50,129
816 1.7 89 4,852 111 1.0 4 1.0 50,129
864 1.8 89 4,852 111 1.0 4 1.0 50,129
912 1.9 89 4,852 111 1.0 4 1.0 50,129
960 2.0 89 4,852 111 1.0 4 1.0 50,129
-------------------------------------------------------------
SGA Target Advisory DB/Inst: ORCL/orcl End Snap: 3
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
616 .3 44 1.2 7,613
1,232 .5 36 1.0 7,613
1,848 .8 36 1.0 7,613
2,464 1.0 36 1.0 7,613
3,080 1.3 36 1.0 7,613
3,696 1.5 36 1.0 7,613
4,312 1.8 36 1.0 7,613
4,928 2.0 36 1.0 7,613
-------------------------------------------------------------
SGA Memory Summary DB/Inst: ORCL/orcl Snaps: 1-3
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 2,013,265,920
Fixed Size 8,750,160
Redo Buffers 8,024,064
Variable Size 553,651,120
-------------------- --------------------
sum 2,583,691,264
-------------------------------------------------------------
SGA breakdown difference DB/Inst: ORCL/orcl Snaps: 1-3
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 16.0 16.0 0.00
large PX msg pool 15.0 15.0 0.00
large free memory 17.0 17.0 0.00
shared ASH buffers 8.0 8.0 0.00
shared KGLH0 23.6 35.4 50.34
shared KGLHD 7.3 9.0 22.62
shared KGLS 14.5 14.9 2.75
shared KGLSG 5.0 5.0 0.00
shared KQR X PO 6.3 7.2 14.43
shared PLDIA 7.8 7.9 2.45
shared PLMCD 5.3 5.3 0.00
shared SQLA 36.9 50.9 38.03
shared db_block_hash_buckets 11.1 11.1 0.00
shared dbktb: trace buffer 5.0 5.0 0.00
shared event statistics per sess 7.7 7.7 0.00
shared file state object 4.9 4.9 0.00
shared free memory 184.1 153.1 -16.81
shared object queue hash buckets 8.0 8.0 0.00
shared row cache mutex 6.0 6.0 0.00
shared write state object 8.7 8.7 0.00
buffer_cache 1,792.0 1,792.0 0.00
fixed_sga 8.3 8.3 0.00
log_buffer 7.7 7.7 0.00
shared_io_pool 128.0 128.0 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: ORCL/orcl Snaps: 1-3
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 32.54 35.52 8.41
Cursor to Parent ratio: 1.03 1.03 -.08
Total Cursors: 1,210 1,569 22.88
Total Parents: 1,179 1,530 22.94
-------------------------------------------------------------
init.ora Parameters DB/Inst: ORCL/orcl Snaps: 1-3
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest C:\APP\USER1\ADMIN\ORCL\ADUMP
audit_trail DB
compatible 12.2.0
control_files C:\APP\USER1\ORADATA\ORCL\CONTROL
01.CTL, C:\APP\USER1\RECOVERY_ARE
A\ORCL\CONTROL02.CTL
db_block_size 8192
db_domain fritz.box
db_name orcl
db_recovery_file_dest C:\app\user1\recovery_area
db_recovery_file_dest_size 10009706496
diagnostic_dest C:\APP\USER1
dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB)
local_listener LISTENER_ORCL
nls_language AMERICAN
nls_territory AMERICA
open_cursors 300
pga_aggregate_target 859832320
processes 320
remote_login_passwordfile EXCLUSIVE
sga_target 2583691264
undo_tablespace UNDOTBS1
-------------------------------------------------------------
End of Report ( sp_1_3.lst )
Statspack Report contains error: ORA-00942: table or view does not exist
If the Statspack report contains this error:
truncate table STATS$TEMP_SQLSTATS
*
ERROR at line 1:
ORA-00942: table or view does not existThen the report was probably created with a user other than perfstat (sys in my case). To fix this error we need to change the following line (file C:\app\user1\product\12.2.0\dbhome_1\rdbms\admin\sprepins.sql)
truncate table STATS$TEMP_SQLSTATS;to
truncate table perfstat.STATS$TEMP_SQLSTATS;Statspack Report contains idle events
If the Statspack Reports contains the following idle events in the Top 5 Timed Events section:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
Data Guard: Gap Manager 60 3,601 60010 33.3
Data Guard: Timer 6 3,600 ###### 33.3
pman timer 1,200 3,600 3000 33.3We hit a Oracle Bug (see Oracle Doc ID 2437142.1). To fix it we can run the following statement (beginning with Oracle 12.2):
rem run in a windows command prompt
(echo insert into perfstat.stats$idle_event
echo select name from v$event_name where wait_class='Idle'
echo minus
echo select event from perfstat.stats$idle_event;
echo commit;)|sqlplus / as sysdba
Creating a SQL Report
This report show information about a specific SQL statement during the selected snapshot period. The SQL statements needs to be identified by the SQL Hash Value. The hash value of a statement can be found in a Statspack Report (instance) in the column “Old Hash Value” or in the column old_hash_value from the view v$sql. To show SQL execution plans at least statspack snapshots at level 6 are needed.
@?/rdbms/admin/sprepsql.sql
Sample Output (report generation)
SQL> @?/rdbms/admin/sprepsql.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1723333555 ORCL 1 orcl
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1723333555 1 ORCL orcl WIN102
Using 1723333555 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 Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl ORCL 1 12 Mar 2025 14:10 5
2 12 Mar 2025 14:40 5
3 12 Mar 2025 15:10 5
4 12 Mar 2025 15:40 6
5 12 Mar 2025 16:10 6
6 12 Mar 2025 16:40 6
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 4
Begin Snapshot Id specified: 4
Enter value for end_snap: 6
End Snapshot Id specified: 6
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 2783330933
Hash Value specified is: 2783330933
PAR VERSN
--- -----------------
HOST_NAME DB Name
---------------------------------------------------------------- ------------
Instance
------------
NO 12.2.0.1.0
WIN102 ORCL
orcl
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_4_6_2783330933. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name sp_4_6_2783330933Sample Statspack Report (SQL)
STATSPACK SQL report for Old Hash Value: 2783330933 Module: DBMS_SCHEDULER
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORCL 1723333555 orcl 1 12.2.0.1.0 NO WIN102
Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
4 12-Mar-25 15:40:32 6 12-Mar-25 16:40:32 60.00
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 318,263 159,131.5 93.91
Disk Reads: 0 0.0
Rows processed: 505 252.5
CPU Time(s/ms): 0 187.5
Elapsed Time(s/ms): 0 198.8
Sorts: 4 2.0
Parse Calls: 2 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 132
Executions: 2
SQL Text
~~~~~~~~
INSERT INTO STATS$SQL_PLAN ( PLAN_HASH_VALUE , ID , OPERATION ,
OPTIONS , OBJECT_NODE , OBJECT# , OBJECT_OWNER , OBJECT_NAME , O
BJECT_ALIAS , OBJECT_TYPE , OPTIMIZER , PARENT_ID , DEPTH , POSI
TION , SEARCH_COLUMNS , COST , CARDINALITY , BYTES , OTHER_TAG ,
PARTITION_START , PARTITION_STOP , PARTITION_ID , OTHER , DISTR
IBUTION , CPU_COST , IO_COST , TEMP_SPACE , ACCESS_PREDICATES ,
FILTER_PREDICATES , PROJECTION , TIME , QBLOCK_NAME , REMARKS ,
SNAP_ID ) SELECT /*+ ordered use_nl(s) use_nl(sp.p) */ NEW_PLAN.
PLAN_HASH_VALUE , SP.ID , MAX(SP.OPERATION) , MAX(SP.OPTIONS) ,
MAX(SP.OBJECT_NODE) , MAX(SP.OBJECT#) , MAX(SP.OBJECT_OWNER) , M
AX(SP.OBJECT_NAME) , MAX(SP.OBJECT_ALIAS) , MAX(SP.OBJECT_TYPE)
, MAX(SP.OPTIMIZER) , MAX(SP.PARENT_ID) , MAX(SP.DEPTH) , MAX(SP
.POSITION) , MAX(SP.SEARCH_COLUMNS) , MAX(SP.COST) , MAX(SP.CARD
INALITY) , MAX(SP.BYTES) , MAX(SP.OTHER_TAG) , MAX(SP.PARTITION_
START) , MAX(SP.PARTITION_STOP) , MAX(SP.PARTITION_ID) , MAX(SP.
OTHER) , MAX(SP.DISTRIBUTION) , MAX(SP.CPU_COST) , MAX(SP.IO_COS
T) , MAX(SP.TEMP_SPACE) , 0 , 0 , MAX(SP.PROJECTION) , MAX(SP.TI
ME) , MAX(SP.QBLOCK_NAME) , MAX(SP.REMARKS) , MAX(NEW_PLAN.SNAP_
ID) FROM (SELECT /*+ index(spu) */ SPU.PLAN_HASH_VALUE , SPU.HAS
H_VALUE HASH_VALUE , SPU.ADDRESS ADDRESS , SPU.TEXT_SUBSET TEXT_
SUBSET , SPU.SNAP_ID SNAP_ID FROM STATS$SQL_PLAN_USAGE SPU WHERE
SPU.SNAP_ID = :B3 AND SPU.DBID = :B2 AND SPU.INSTANCE_NUMBER =
:B1 AND NOT EXISTS (SELECT /*+ nl_aj */ * FROM STATS$SQL_PLAN SS
P WHERE SSP.PLAN_HASH_VALUE = SPU.PLAN_HASH_VALUE ) ) NEW_PLAN ,
V$SQL S , V$SQL_PLAN SP WHERE S.ADDRESS = NEW_PLAN.ADDRESS AND
S.PLAN_HASH_VALUE = NEW_PLAN.PLAN_HASH_VALUE AND S.HASH_VALUE =
NEW_PLAN.HASH_VALUE AND SP.HASH_VALUE = NEW_PLAN.HASH_VALUE AND
SP.ADDRESS = NEW_PLAN.ADDRESS AND SP.HASH_VALUE = S.HASH_VALUE A
ND SP.ADDRESS = S.ADDRESS AND SP.CHILD_NUMBER = S.CHILD_NUMBER G
ROUP BY NEW_PLAN.PLAN_HASH_VALUE, SP.ID ORDER BY NEW_PLAN.PLAN_H
ASH_VALUE, SP.ID
Known Optimizer Plan(s) for this Old Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id
First First Last Plan
Snap Id Snap Time Active Time Hash Value Cost
--------- --------------- --------------- ------------ ----------
5 12-Mar-25 16:10 12-Mar-25 16:10 3951187275 4
Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|INSERT STATEMENT |----- 3951187275 ----| | | 4 |
|LOAD TABLE CONVENTIONAL |STATS$SQL_PLAN | | | |
| SORT GROUP BY | | 1 | 6K| 4 |
| NESTED LOOPS | | 1 | 6K| 3 |
| NESTED LOOPS ANTI | | 1 | 129 | 3 |
| NESTED LOOPS | | 1 | 116 | 2 |
| VIEW |VW_GBF_6 | 1 | 45 | 1 |
| SORT GROUP BY | | 1 | 58 | 1 |
| FIXED TABLE FULL |X$KGLCURSOR_CHILD | 1 | 58 | 0 |
| TABLE ACCESS BY INDEX ROWID|STATS$SQL_PLAN_USAGE | 1 | 71 | 1 |
| INDEX FULL SCAN |STATS$SQL_PLAN_USAGE | 134 | | 1 |
| INDEX RANGE SCAN |STATS$SQL_PLAN_PK | 1 | 13 | 1 |
| FIXED TABLE FIXED INDEX |X$KQLFXPL (ind:3) | 1 | 6K| 0 |
--------------------------------------------------------------------------------
End of ReportRemoving Snapshots
Manual Snapshot removal
# remove specific snapshots (including the specified ones)
@?/rdbms/admin/sppurge.sql
# remove all snapshots (will truncate the tables)
@?/rdbms/admin/sptrunc.sql
# remove snapshots older than 31 days
exec statspack.purge(31);Automatic snapshot removal
The next statement creates a job that deletes snapshots older than 31 days from the database. The job runs every day at 1am.
sqlplus system/changeme
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'perfstat.sp_purge',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN perfstat.statspack.purge(31); END;',
repeat_interval => 'freq=daily; byhour=1; byminute=0;',
comments => 'Statspack Purge Snapshots older than 31 days');
DBMS_SCHEDULER.ENABLE('perfstat.sp_purge');
END;
/
The job can be dropped with:
exec dbms_scheduler.drop_job('perfstat.sp_purge');
Removing Oracle Statspack
Statspack can be removed by running the following commands:
echo @?/rdbms/admin/spdrop.sql|sqlplus / as sysdba
echo drop tablespace statspack_data including contents and datafiles;|sqlplus / as sysdba
Useful commands
# show details of the automatic snapshot creation job
select status,actual_start_date,run_duration from dba_scheduler_job_run_details where job_name='SP_SNAPSHOT' order by actual_start_date;
# show details of the automatic snapshot purge job
select status,actual_start_date,run_duration from dba_scheduler_job_run_details where job_name='SP_PURGE' order by actual_start_date;
# show all available snapshots including snap level
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
select snap_id,snap_time,snap_level from perfstat.stats$snapshot order by snap_id;
Sample Output
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select snap_id,snap_time,snap_level from perfstat.stats$snapshot orde
nap_id;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
53 14.03.2025 13:23:03 6
54 14.03.2025 15:30:09 6
55 14.03.2025 15:40:32 6
64 14.03.2025 16:10:33 6
65 14.03.2025 16:40:33 6
66 14.03.2025 17:10:32 6
67 14.03.2025 17:40:32 6
68 14.03.2025 18:10:32 6
69 14.03.2025 18:40:32 6
70 14.03.2025 19:10:32 6
71 14.03.2025 19:40:32 6
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
72 15.03.2025 07:36:05 6
73 15.03.2025 07:40:32 6
74 15.03.2025 08:10:32 6
75 15.03.2025 08:40:32 6
76 15.03.2025 09:10:32 6
77 15.03.2025 09:40:32 6
78 15.03.2025 10:10:32 6
79 15.03.2025 10:40:32 6
80 15.03.2025 11:10:32 6
81 15.03.2025 11:40:33 6
82 15.03.2025 12:10:33 6
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
83 15.03.2025 12:40:32 6
84 15.03.2025 13:10:32 6
24 rows selected.
SQL>Further information
- s.a. Oracle Doc ID 394937.1 (Statistics Package (STATSPACK) Guide). Also available at: $ORACLE_HOME\rdbms\admin\spdoc.txt
Leave a Reply