| |
 |
|
Oracle Tips by Burleson |
Dictionary and Library Cache
Statistics
The next AWRRPT report section deals
with dictionary and library cache statistics. An item to watch for in
a RAC environment is excessive sequence activity as this can indicate
that the sequences are not properly configured for a RAC environment.
For example, to be RAC Safe, a sequence should be cached,
nocycle and noorder.
The following listing shows the dictionary and library reports:
Dictionary Cache
Stats DB/Inst: SSD/ssd2 Snaps: 3-4
-> "Pct Misses"
should be very low (< 2% in most cases)
-> "Final Usage"
is the number of cache entries being used
Get Pct Scan Pct
Mod Final
Cache Requests Miss Reqs Miss Reqs
Usage
------------------------- ------------ ------ ------- ----- --------
---------
dc_awr_control
62 3.2 0 0 1
dc_global_oids
1,137 0.0 0 0 22
dc_histogram_defs 110 0.0 0
0 2,285
dc_object_ids
1,507 0.3 0 0 558
dc_objects
436 5.0 0 0 621
dc_profiles
85 0.0 0 0 1
dc_rollback_segments 17,403 0.1 0
30 98
dc_segments 175 8.6 0
41 369
dc_sequences 14 35.7 0
14 6
dc_tablespace_quotas 2 0.0 0
2 1
dc_tablespaces
5,004 0.0 0 0 7
dc_usernames
298 0.0 0 0 11
dc_users
8,822 0.0 0 0 38
outstanding_alerts 36 66.7 0
0 5
-------------------------------------------------------------
Dictionary Cache
Stats (RAC) DB/Inst: SSD/ssd2 Snaps: 3-4
GES GES GES
Cache Requests Conflicts Releases
------------------------- ------------ ------------ ------------
dc_awr_control
2 2 0
dc_object_ids
5 0 0
dc_objects
22 0 0
dc_rollback_segments 57 1 0
dc_segments 84 4 0
dc_sequences 24 4 0
dc_tablespace_quotas 4 0 0
outstanding_alerts 72 24 0
-------------------------------------------------------------
Library Cache
Activity DB/Inst: SSD/ssd2 Snaps: 3-4
-> "Pct Misses"
should be very low
Get Pct Pin
Pct Invali-
Namespace Requests Miss Requests Miss Reloads
dations
---------------
------------ ------ -------------- ------ ---------- --------
BODY 12 0.0 30 0.0
0 0
INDEX 1 0.0 1 0.0
0 0
SQL
AREA 3,111 0.1 77,182 0.0
2 0
TABLE/PROCEDURE 51 0.0 8,823 0.0
0 0
-------------------------------------------------------------
Library Cache
Activity (RAC) DB/Inst: SSD/ssd2 Snaps: 3-4
GES Lock GES Pin GES Pin GES Inval GES
Invali-
Namespace Requests Requests Releases Requests
dations
---------------
------------ ------------ ------------ ----------- -----------
INDEX 1 0 0
0 0
TABLE/PROCEDURE 309 0 0
0 0
-------------------------------------------------------------
There is a section for the above referenced
section of the AWRRPT that is only present if the database is RAC
enabled. This section shows the dictionary statistics for the
dictionary cache for the global enqueueservice as well as a
library cache activity section for GES. As long as reloads
and invalidations are kept to near zero, things are probably ok. The
miss percent of 35% for dc_sequences
probably indicates some of the instance sequences are not RAC safe.
The next AWRRPTreport section deals
with the breakdown of memory into the various SGA sections and pools.
The first report in the following listing shows the various memory
sections and their sizes. The next section in the listing shows how
the memory profile may have changed during the period of time between
the first snapshot and the second. This section of the report does not
yield much helpful information on the use or non-use of SSD:
SGA Memory
Summary DB/Inst: SSD/ssd2 Snap: 4
SGA
regions Size in Bytes
------------------------------ ----------------
Database
Buffers 805,306,368
Fixed
Size 782,544
Redo
Buffers 524,288
Variable
Size 267,128,624
SGA breakdown
difference DB/Inst: SSD/ssd2 Snaps: 3-4
Pool
Name Begin value End value %
Diff
------
------------------------------ ---------------- ----------------
------
java free
memory 4,194,304 4,194,304 0.00
large PX msg
pool 902,160 902,160 0.00
large free
memory 3,292,144 3,292,144 0.00
shared ASH
buffers 4,194,304 4,194,304 0.00
shared KGLS
heap 2,463,820 2,463,820 0.00
shared KQR L
SO 45,056 45,056 0.00
shared KQR M
PO 1,779,792 1,793,616 0.78
shared KQR M
SO 50,688 50,688 0.00
shared KQR S
PO 124,224 130,624 5.15
shared KQR S
SO 4,096 4,096 0.00
shared KSXR
receive buffers 1,032,500 1,032,500 0.00
shared PL/SQL
DIANA 1,424,592 1,424,592 0.00
shared PL/SQL
MPCODE 1,371,980 1,371,980 0.00
shared PLS
non-lib hp 11,168 11,168 0.00
shared PX
subheap 131,068 131,068 0.00
shared dbwriter
coalesce buffer 1,049,088 1,049,088 0.00
shared event
statistics per sess 3,860,360 3,860,360 0.00
shared fixed
allocation callback 428 428 0.00
shared free
memory 158,959,480 158,216,716 -0.47
shared gcs
resources 16,900,480 16,900,480 0.00
shared gcs
shadows 6,893,064 6,893,064 0.00
shared ges big
msg buffers 3,013,444 3,013,444 0.00
shared ges
enqueues 2,224,400 2,247,808 1.05
shared ges
reserved msg buffers 1,238,404 1,238,404 0.00
shared ges
resources 1,475,496 1,492,872 1.18
shared joxs
heap 4,220 4,220 0.00
shared library
cache 8,487,496 8,874,140 4.56
shared
miscellaneous 25,591,776 25,647,460
0.22
shared
parameters 51,908 51,908
0.00
shared
partitioning d 133,692 133,692
0.00
shared pl/sql
source 17,732 17,732 0.00
shared
repository 57,636 57,636
0.00
shared row
cache 3,707,272 3,707,272 0.00
shared sql
area 9,220,432 9,459,860 2.60
shared table
definiti 952 952 0.00
shared trigger
defini 3,248 3,248 0.00
shared trigger
inform 1,944 1,944 0.00
shared trigger
source 7,616 7,616 0.00
shared type
object de 318,688 318,688 0.00
buffer_cache 805,306,368 805,306,368
0.00
fixed_sga
782,544 782,544 0.00
log_buffer 524,288 524,288
0.00
-------------------------------------------------------------
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. |
|
