 |
|
Oracle Tips by Burleson |
Instance Activity Values
The next section of the AWRRPTreport
shows begin/end type Instance activity values that should not be diffed.
Instance
Activity Stats - Absolute Values DB/Inst: SSD/ssd2 Snaps: 3-4
-> Statistics
with absolute values (should not be diffed)
-> Statistics
identified by '(derived)' come from sources other than SYSSTAT
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
logons
current 124 106
open threads
(derived) 4 4
opened cursors
current 1,787 78
-------------------------------------------------------------
Instance
Activity Stats - Thread Activity DB/Inst: SSD/ssd2 Snaps: 3-4
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches
(derived) 6 5.95
-------------------------------------------------------------
The next sections of the report deal with the
tablespace and file I/O statistics. In a non-fully cached environment,
this section of the report should be reviewed to see what datafiles
are undergoing the most I/O. These are prime candidates for a move to
SSD resource. The following is an example of this part of the report:
Tablespace I/O
Stats DB/Inst: SSD/ssd2 Snaps: 3-4
-> 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)
--------------
------- ------ ------- ------------ -------- ---------- ------
SSD_DATA
5,469 2 0.4 1.0 38,133 11 1,306 21.6
UNDOTBS2
5 0 0.0 1.0 942 0 12 6.7
SYSAUX
21 0 0.0 1.0 197 0 0
0.0
SYSTEM
21 0 0.5 1.0 82 0 3 3.3
UNDOTBS1
5 0 0.0 1.0 5 0 539 4.3
-------------------------------------------------------------
File I/O Stats
DB/Inst: SSD/ssd2 Snaps: 3-4
-> ordered by
Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer Av
Buf
Reads
Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
SYSAUX /oracle2/oradata/ssd/sysaux01.dbf
21
0 0.0 1.0 197 0 0 0.0
SYSTEM /oracle2/oradata/ssd/system01.dbf
21 0
0.5 1.0 82 0 3 3.3
UNDOTBS1
/oracle2/oradata/ssd/undotbs01.dbf
5 0 0.0 1.0 5 0 539 4.3
UNDOTBS2 /oracle2/oradata/ssd/undotbs02.dbf
5 0 0.0 1.0 942 0 12 6.7
SSD_DATA
/oracle2/oradata/ssd/ssd_data01.dbf
5,469 2 0.4 1.0 38,133 11 1,306 21.6
-------------------------------------------------------------
In the above report, I/O is observed even in a
fully cached environment due to INSERT, UPDATE and DELETE activity
forcing cache replacement to occur.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
