| |
 |
|
Oracle Tips by Burleson |
Resource Activity Report
The Resource Activityreport is next. This is another new section for the Oracle10g AWRRPTover the
old STATSPACK type reports. The following listing shows an example of
the Resource report:
Resource Limit
Stats DB/Inst: SSD/ssd2 Snap: 4
-> only rows
with Current or Maximum Utilization > 80% of Limit are shown
-> ordered by
resource name
Current Maximum Initial
Resource
Name Utilization Utilization Allocation Limit
------------------------------ ------------ ------------ ----------
----------
gcs_resources
640 106,592 106592 106592
gcs_shadows
36,610 91,052 106592 106592
ges_procs
24 149 151 151
processes 24 150
150 150
sessions 28 154
170 170
-------------------------------------------------------------
In the report section in the above listing, the
various systems related GCS
and GESlimits are
available. If it appears that the environment is approaching one of
the limits, the DBA may need to perform analysis and tuning to correct
it.
The next section of the report simply shows the
initialization parameters that are different from the default
settings. This is not really important except with regard to tuning
activities, so it will be skipped. After the initialization parameter
section, the AWRRPTreport adds a global enqueuestatistics section.
This section appears earlier in the report lineup in a STATSPACK
report. The following shows a partial listing of this report section:
Global Enqueue
Statistics DB/Inst: SSD/ssd2 Snaps: 3-4
Statistic Total per Second per
Trans
--------------------------------- ---------------- ------------
------------
gcs assume no
cvt 21,317 5.9 2.8
gcs blocked
converts 38,736 10.7 5.1
gcs blocked cr
converts 60,525 16.7 8.0
gcs compatible
basts 1 0.0 0.0
gcs compatible
cr basts (global) 2,481 0.7 0.3
gcs compatible
cr basts (local) 1,551 0.4 0.2
gcs dbwr flush
pi msgs 8,784 2.4 1.2
gcs dbwr write
request msgs 6,942 1.9 0.9
gcs immediate
(compatible) conver 2,008 0.6 0.3
gcs immediate
(null) converts 3,258 0.9 0.4
gcs immediate cr
(compatible) con 1,679 0.5 0.2
gcs immediate cr
(null) converts 1,784 0.5 0.2
gcs indirect ast
5,446 1.5 0.7
gcs lms write
request msgs 1,167 0.3 0.2
gcs msgs process
time(ms) 8,482 2.3 1.1
gcs msgs
received 158,053 43.5 20.9
gcs retry
convert request 5,429 1.5 0.7
gcs side channel
msgs actual 562 0.2 0.1
gcs side channel
msgs logical 98,668 27.2 13.1
gcs write
notification msgs 926 0.3 0.1
gcs writes
refused 16 0.0 0.0
ges msgs process
time(ms) 63 0.0 0.0
ges msgs
received 6,023 1.7 0.8
implicit batch
messages received 1,783 0.5 0.2
implicit batch
messages sent 3,426 0.9 0.5
messages flow
controlled 1,055 0.3 0.1
messages queue
sent actual 28,608 7.9 3.8
messages queue
sent logical 37,033 10.2 4.9
messages
received actual 137,220 37.8 18.2
messages
received logical 164,076 45.2 21.7
messages sent
directly 99,375 27.4 13.2
messages sent
indirectly 36,838 10.1 4.9
messages sent
not implicit batche 25,182 6.9 3.3
messages sent
pbatched 43,929 12.1 5.8
msgs causing lmd
to send msgs 3,009 0.8 0.4
msgs causing
lms(s) to send msgs 13,414 3.7 1.8
msgs received
queue time (ms) 883 0.2 0.1
msgs received
queued 164,076 45.2 21.7
msgs sent queue
time (ms) 1,011 0.3 0.1
msgs sent queue
time on ksxp (ms) 76,689 21.1 10.2
msgs sent
queued 36,744 10.1 4.9
msgs sent queued
on ksxp 131,507 36.2 17.4
process batch
messages received 30,869 8.5 4.1
process batch
messages sent 33,877 9.3 4.5
-------------------------------------------------------------
The report section in the above is more useful for
monitoring and tuning the RAC interconnect environment; however, its
values can help determine if the interconnect is being stressed. The
stress on the interconnect will show itself as high values for the
various time parameters shown in the Global Enqueue Statisticsreport section of the AWRRPT report.
The final section of the report deals with the
Global Cache Statistics (GCS). The
following sample listing shows the GCS Statistics section of the
AWRRPTReport:
Global CR Served Stats DB/Inst: SSD/ssd2
Snaps: 3-4
Statistic
Total
------------------------------
------------------
CR Block Requests
64,210
CURRENT Block Requests
5,353
Data Block Requests
64,210
Undo Block Requests
1,231
TX Block Requests
4,122
Current Results
67,165
Private results
593
Zero Results
1,806
Disk Read Results
0
Fail Results
0
Fairness Down Converts
8,529
Fairness Clears
0
Free GC Elements
0
Flushes
2,907
Flushes Queued
0
Flush Queue Full
0
Flush Max Time (us)
139,047
Light Works
998
Errors
0
---------------------------------------
Global CURRENT Served Stats DB/Inst: SSD/ssd2
Snaps: 3-4
-> Pins = CURRENT Block Pin Operations
-> Flushes = Redo Flush before CURRENT Block
Served Operations
-> Writes = CURRENT Block Fusion Write
Operations
Statistic Total % <1ms % <10ms %
<100ms % <1s % <10s
---------- ------------ -------- --------
-------- -------- --------
Pins 41,766 99.57 0.02
0.41 0.00 0.00
Flushes 216 4.63 27.78
67.59 0.00 0.00
Writes 9,462 72.29 12.63
15.07 0.01 0.00
-------------------------------------------------------------
In the GCS
statistics report, the stress on
the interconnect can be tracked by reviewing the timing histograms
provided for pins, flushes, and writes. If the times shown skew to the
right side of the histogram, as indicated in the listing with the
flushes timings, the interconnect is showing some stress.
The following section will conclude the technical
information in this chapter by reviewing the STATSPACK runs from the
TPC-H SSD runs.
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. |
|
