Server I/O utilization System I/O utilization V$FILESTAT V$DATAFILE Data files Statspack Performance tools
TABLESPACE FILE_NAME PHYRDS PHYBLKRD READTIM PHYWRTS PHYBLKWRT WRITETIM ------------- ----------------------- ------ --------- -------- ------- --------- -------- UNDO1 /u02/undots01.dbf 26 26 50 257 257 411 USERS /u03/users01.dbf 65012 416752 38420 564 564 8860 SAMPLE /u02/sample01.dbf 8 8 0 8 8 0 SYSTEM /u01/system01.dbf 806 1538 1985 116 116 1721 TEMP /u04/temp01.dbf 168 666 483 675 675 0 QUERY_DATA /u01/query_data01.dbf 8 8 0 8 8 0 6 rows selected.
SID SERIAL# OPNAME START PERCENT_COMPLETE --- ------- ------------ -------- --------------- 8 219 TABLE SCAN 13:00:09 48.98098
LGWR Group 1 Group 2 Group 3 Disk 1 Member Member Member Member Member Member Disk 2
V$ARCHIVE_DEST V$ARCHIVED_LOG V$ARCHIVE_PROCESSES Archived logs LOG_ARCHIVE_DEST_STATE_n
STATSPACK LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOUT FAST_START_IO_TARGET LOG_CHECKPOINTS_TO_ALERT Alert log file
Sorting sort_area_size : Sort run 1 Sort run 2 Server process Temporary segment Sort run 2
Shared pool Stack space User session data PGA UGA Cursor state Sort area User session data UGA Cursor state Sort area PGA Stack space
Temporary tablespace Permanent Objects One single sort segment 2M 2M 2M temp01.dbf temp02.dbf temp03.dbf CREATE TEMPORARY TABLESPACE TEMP TEMPFILE $HOME/ORADATA/u06/temp01.dbf size 200M;
V$SORT_USAGE V$SORT_SEGMENT Server process Sort area (UGA) TEMPORARY tablespace PCTINCREASE INITIAL NEXT Sort in memory SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE Sort on disk V$SYSSTAT STATSPACK
STATISTIC# NAME CLASS VALUE ----------- --------------- ------ ------- 161 sorts (memory) 64 154 162 sorts (disk) 64 4 163 sorts (rows) 64 571768 Statistic Total Per Transact Per Logon Per Second ------------- ------- ------------- ---------- ----------- sorts (disk) 4.02.41.01 sorts (memory) 154.27 5.77.12 sorts (rows) 571768 39.62 862.59 18.19
Disk Mem Ratio --------- --------- ------------ 23 206 11.165049
Transaction rollback Rollback (Undo) segment Read consistency Transaction recovery Data files Control files Redo log files
T1 >update >update >insert >insert >update T2 >update >update >insert >insert >update 1 2 T1 T2 T3 T4 4 3 Inactive extent Active extent
T1 T2 T3 T4 1 2 T5 T6 4 3
STATSPACK output V$SYSTEM_EVENT V$ WAITSTAT T3 T4 T5 Header 1 2 V$ROLLSTAT V$ROLLNAME V$SYSSTAT V$TRANSACTION V$SESSION 4 3 Data SGA
Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- -------- ------------- --------- ------- -------- 0 5.0 0.00 0 0 0 0 1 66.0 0.00 5,636 0 0 0 2 439.0 0.00 358,772 5 0 0 3 50.0 0.00 6,314 0 0 0 4 53.0 0.00 7,004 0 0 0 ------------------------------------------------------------------------
Large rollback Small RBS Small RBS Small RBS Small RBS Small RBS
Rollback segment 1 = Rollback segment 2 INITIAL = NEXT = 2 n, MINEXTENTS = 20 OPTIMAL 0.50 0.40 0.30 0.20 0.10 0.00 Probability of extending 0 10 20 30 40 Number of extents