| |
 |
|
Oracle Tips by Burleson |
Instance Activity
The next several sections of the full AWRRPTdeal with
SQL code; however, since this book is not about SQL tuning, these
sections will be skipped. The next section of interest is the Instance
Activitysection, which reports the statistics from the
v$sysstatview. The following listing shows an excerpt from this
section of the report highlighting some statistics of interest.
Instance Activity
Stats DB/Inst: SSD/ssd2 Snaps: 3-4
Statistic
Total per Second per Trans
-------------------------------- ----------------- --------------
------------
CPU used by this session
16,669
4.6 2.2
CPU used when call started
16,672
4.6 2.2
DB time
60,701
16.7 8.0
DBWR checkpoints
6
0.0 0.0
DBWR fusion writes
9,462
2.6 1.3
IPC CPU used by this session
1,584
0.4 0.2
consistent changes
14,651
4.0 1.9
consistent gets
5,937,893 1,635.4
786.8
consistent gets - examination
634,674 174.8
84.1
consistent gets from cache
5,937,893 1,635.4
786.8
data blocks consistent reads - u
13,121
3.6 1.7
db block changes
296,900
81.8 39.3
db block gets
200,058
55.1 26.5
db block gets direct
8
0.0 0.0
db block gets from cache
200,050
55.1 26.5
deferred (CURRENT) block cleanout
40,205
11.1 5.3
execute count
70,345
19.4 9.3
free buffer inspected
96,695
26.6 12.8
free buffer requested
109,016
30.0 14.4
gc CPU used by this session
2,264
0.6 0.3
gc cr block build time
142
0.0 0.0
gc cr block flush time
178
0.1 0.0
gc cr block receive time
9,029
2.5 1.2
gc cr block send time 295
0.1 0.0
gc cr blocks received
55,074
15.2 7.3
gc cr blocks served
69,564
19.2 9.2
gc current block flush time
473
0.1 0.1
gc current block pin time
642
0.2 0.1
gc current block receive time
6,463
1.8 0.9
gc current block send time
179
0.1 0.0
gc current blocks received
37,381
10.3 5.0
gc current blocks served
41,766
11.5 5.5
gcs messages sent
142,933
39.4 18.9
ges messages sent
6,809
1.9 0.9
global
enqueue
CPU used by this
235
0.1 0.0
global
enqueue
get time
21,353
5.9 2.8
global
enqueue
gets async
782
0.2 0.1
global
enqueue
gets sync
38,370
10.6 5.1
global
enqueue
releases
37,478
10.3 5.0
index fetch by key
208,656
57.5 27.7
index scans kdiixs1
17,232
4.8 2.3
logons cumulative
117 0.0
0.0
no work - consistent read gets
5,251,099 1,446.2
695.8
opened cursors cumulative
4,955
1.4 0.7
physical read IO requests
5,519
1.5 0.7
physical reads
5,520
1.5 0.7
physical reads cache
5,495
1.5 0.7
physical reads cache prefetch
1
0.0 0.0
physical reads direct
25
0.0 0.0
physical write IO requests
39,359
10.8 5.2
physical writes
46,215
12.7 6.1
physical writes direct
33
0.0 0.0
physical writes from cache
46,182
12.7 6.1
physical writes non checkpoint
26,290
7.2 3.5
recursive cpu usage
4,004 1.1
0.5
redo log space requests
45
0.0 0.0
redo log space wait time
5
0.0 0.0
rollback changes - undo records
832
0.2 0.1
rollbacks only - consistent read
1,193
0.3 0.2
sorts (memory)
2,148
0.6 0.3
sorts (rows)
20,148
5.6 2.7
table fetch by rowid
250,482
69.0 33.2
table fetch continued row
88
0.0 0.0
table scan blocks gotten
5,100,198 1,404.7
675.8
table scan rows gotten
1,043,284,622 287,332.5
38,238.3
table scans (short tables)
3,869
1.1 0.5
transaction rollbacks
169
0.1 0.0
user I/O wait time
261 0.1
0.0
user calls
29,599
8.2 3.9
user commits
7,390
2.0 1.0
user rollbacks
157
0.0 0.0
workarea executions - optimal
4,160
1.2 0.6
------------------------------------------------------------
An entire chapter could be dedicated to the
meanings of each of the above statistics for tuning purposes; however,
that is not the focus of this book, so only the applicable part will
be highlighted. In the above listing, the detailed sections on Global
Cache Service
(GCS) and Global Enqueue Service
(GES) statistics are of
particular interest. These sections deal with RAC and are used to
calculate latency information. The detailed statistics in the GCS and
GES sections allow the user to not only see what the latency is but
also to find out exactly where the latency is occurring. For example,
the transfer time for a gc cr
block is made up of the gc cr block
build time, the gc cr block flush time
and the
gs cr block send time. In this case, the largest
percentage of the transfer time is the send time component, which
indicates the network was the largest component. For the
gc current block
, The largest component was the pin
time indicating that users had to wait for other users to get access
to the block.
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. |
|
