Hi:
I am reading some statspack reports from our 8173 DB (on Sun Solaris) and
found some of "Dictionary Cache Stats" are pretty high (much higher than
2%). I notice that "Pct Get Miss" for dc_used_extents ,dc_free_extents and
dc_histogram_defs are high (the second column data below). Is this
something I need to pay attention in terms of doing performance
optimization? If yes, what are the things (regarding "Dictionary Cache")
that I should look in order to improve the performance?
TIA.
Guang
ps, here are some "dc_" stats from my reports and a copy of actual report
(partial):
----------
bash-2.03$ grep "dc_used_extents" sp_*
sp_681_682.lst:dc_used_extents 136 83.1 0
136 3,840 100
sp_682_683.lst:dc_used_extents 124 81.5 0
124 3,918 99
sp_683_684.lst:dc_used_extents 34 58.8 0
34 3,924 100
sp_684_685.lst:dc_used_extents 0 0
0 3,924 100
sp_685_686.lst:dc_used_extents 37 64.9 0
37 3,935 99
sp_686_687.lst:dc_used_extents 12 100.0 0
12 3,947 100
sp_687_688.lst:dc_used_extents 18 100.0 0
18 3,965 100
sp_688_689.lst:dc_used_extents 26 100.0 0
26 3,991 100
sp_689_690.lst:dc_used_extents 14 100.0 0
14 4,005 100
sp_690_691.lst:dc_used_extents 16 100.0 0
16 4,021 100
sp_691_692.lst:dc_used_extents 29 100.0 0
29 4,050 100
sp_692_693.lst:dc_used_extents 1 100.0 0
1 4,051 99
sp_693_694.lst:dc_used_extents 1,118 51.2 0
1,118 4,077 100
sp_694_695.lst:dc_used_extents 1,294 99.8 0
1,294 5,365 100
sp_695_696.lst:dc_used_extents 2,031 13.2 0
2,031 3,870 72
sp_696_697.lst:dc_used_extents 1,195 98.5 0
1,195 5,029 94
sp_697_698.lst:dc_used_extents 44 100.0 0
44 5,073 94
sp_698_699.lst:dc_used_extents 0 0
0 5,073 94
sp_699_700.lst:dc_used_extents 1,359 8.5 0
1,359 568 33
bash-2.03$ grep "dc_free_extents" sp_*
sp_681_682.lst:dc_free_extents 395 34.4 166 0.0
362 4,071 82
sp_682_683.lst:dc_free_extents 507 27.2 160 0.0
342 4,100 82
sp_683_684.lst:dc_free_extents 75 32.0 20 0.0
64 4,104 82
sp_684_685.lst:dc_free_extents 12 0.0 0
0 4,104 82
sp_685_686.lst:dc_free_extents 95 35.8 24 0.0
83 4,114 83
sp_686_687.lst:dc_free_extents 47 25.5 12 0.0
36 4,114 83
sp_687_688.lst:dc_free_extents 64 23.4 18 0.0
52 4,111 82
sp_688_689.lst:dc_free_extents 93 24.7 32 0.0
78 4,108 82
sp_689_690.lst:dc_free_extents 54 25.9 14 0.0
42 4,108 82
sp_690_691.lst:dc_free_extents 63 23.8 21 0.0
48 4,107 82
sp_691_692.lst:dc_free_extents 109 25.7 42 0.0
85 4,106 82
sp_692_693.lst:dc_free_extents 15 6.7 1 0.0
3 4,106 82
sp_693_694.lst:dc_free_extents 1,624 24.0 596 0.0
1,574 3,924 79
sp_694_695.lst:dc_free_extents 3,398 31.3 1,307 0.0
3,288 3,696 74
sp_695_696.lst:dc_free_extents 2,043 86.3 268 0.0
2,031 5,191 99
sp_696_697.lst:dc_free_extents 1,209 1.7 1,177 0.0
1,197 4,034 77
sp_697_698.lst:dc_free_extents 55 0.0 44 0.0
44 3,990 76
sp_698_699.lst:dc_free_extents 12 0.0 0
0 3,990 79
sp_699_700.lst:dc_free_extents 1,384 90.4 115 0.0
1,371 1,564 94
bash-2.03$ grep "dc_histogram_defs" sp_*
sp_681_682.lst:dc_histogram_defs 49 79.6 0
25 128 21
sp_682_683.lst:dc_histogram_defs 47 29.8 0
0 142 23
sp_683_684.lst:dc_histogram_defs 186 51.6 0
119 238 39
sp_684_685.lst:dc_histogram_defs 1,756 91.3 0
1,699 1,841 100
sp_685_686.lst:dc_histogram_defs 2,191 1.1 0
59 1,865 100
sp_686_687.lst:dc_histogram_defs 144 0.0 0
0 1,865 100
sp_687_688.lst:dc_histogram_defs 8,322 0.0 0
18 1,865 100
sp_688_689.lst:dc_histogram_defs 5 0.0 0
0 1,865 100
sp_689_690.lst:dc_histogram_defs 13 0.0 0
0 1,865 100
sp_690_691.lst:dc_histogram_defs 36 0.0 0
0 1,865 100
sp_691_692.lst:dc_histogram_defs 212 1.4 0
70 1,868 100
sp_692_693.lst:dc_histogram_defs 559 0.0 0
76 1,868 100
sp_693_694.lst:dc_histogram_defs 127,682 0.3 0
37 1,821 96
sp_694_695.lst:dc_histogram_defs 197,927 0.1 0
27 1,902 100
sp_695_696.lst:dc_histogram_defs 167,818 0.5 0
44 1,826 95
sp_696_697.lst:dc_histogram_defs 137,702 0.0 0
0 1,886 99
sp_697_698.lst:dc_histogram_defs 45,995 0.0 0
0 1,886 99
sp_698_699.lst:dc_histogram_defs 141,411 0.0 0
0 1,886 99
sp_699_700.lst:dc_histogram_defs 74,885 0.8 0
22 362 99
-----------
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg
Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
----------------------------- -------------- ------ ------ ------------ ----
--
Token Manager 474 0.0 0
active checkpoint queue latch 3,697 0.0 0
begin backup scn array 8,433 0.0 0
cache buffer handles 28 0.0 0
cache buffers chains 24,324,839 0.0 0.0 108,645
0.0
cache buffers lru chain 64,637 0.0 0.0 146,648
0.0
channel handle pool latch 58 0.0 0
channel operations parent lat 87 0.0 0
checkpoint queue latch 169,400 0.0 0.0 0
dictionary lookup 63 0.0 0
dml lock allocation 568,738 0.0 0
enqueue hash chains 806,704 0.0 0
enqueues 914,225 0.0 0
event group latch 29 0.0 0
file number translation table 1 0.0 0
job_queue_processes parameter 56 0.0 0
ktm global data 12 0.0 0
library cache 5,035,573 0.0 0.0 0
library cache load lock 780 0.0 0
list of block allocation 210,986 0.0 0
loader state object freelist 118 0.0 0
messages 502,244 0.0 0.0 0
multiblock read objects 3,722 0.0 0
ncodef allocation latch 56 0.0 0
process allocation 29 0.0 29
0.0
process group creation 58 0.0 0
redo allocation 2,064,434 0.0 0.0 0
redo writing 473,415 0.1 0.0 0
row cache objects 136,128 0.0 0
sequence cache 705,719 0.0 0
session allocation 205,912 0.0 0
session idle bit 2,909,059 0.0 0.0 0
session switching 56 0.0 0
session timer 1,175 0.0 0
shared pool 35,414 0.0 0
sort extent pool 30 0.0 0
transaction allocation 316,170 0.0 0
transaction branch allocation 56 0.0 0
undo global data 325,372 0.0 0
user lock 116 0.0 0
virtual circuit queues 175 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: RPT2 Instance: RPT2 Snaps: 681 -682
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps
1->4
-------------------------- -------------- ----------- ----------- ----------
--
redo writing 473,415 274 1
273/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: RPT2 Instance: RPT2 Snaps: 681 -682
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
------------------------ -------------------------- ------- ---------- -----
--
redo writing kcrfsr 0 1
0
-------------------------------------------------------------
Dictionary Cache Stats for DB: RPT2 Instance: RPT2 Snaps: 681 -682
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final
Pct
Cache Requests Miss Requests Miss Req Usage
SGA
---------------------- ------------ ------ -------- ----- -------- ------ --
--
dc_constraints 44 50.0 0 44 1,037
100
dc_database_links 0 0 0 0
0
dc_files 0 0 0 2
10
dc_free_extents 395 34.4 166 0.0 362 4,071
82
dc_global_oids 0 0 0 0
0
dc_histogram_data 0 0 0 0
0
dc_histogram_data_valu 0 0 0 0
0
dc_histogram_defs 49 79.6 0 25 128
21
dc_object_ids 1,206 4.6 0 27 1,570
100
dc_objects 1,065 4.7 0 386 2,311
100
dc_outlines 0 0 0 0
0
dc_profiles 29 0.0 0 0 1
33
dc_rollback_segments 240 0.0 0 0 11
65
dc_segments 1,690 2.8 0 211 1,101
98
dc_sequence_grants 0 0 0 0
0
dc_sequences 15,295 0.0 0 15,285 117
94
dc_synonyms 20 25.0 0 0 45
96
dc_tablespace_quotas 136 0.0 0 136 5
83
dc_tablespaces 583 0.0 0 0 13
87
dc_used_extents 136 83.1 0 136 3,840
100
dc_user_grants 499 0.0 0 0 13
22
dc_usernames 457 0.0 0 0 21
95
dc_users 1,515 0.0 0 0 17
89
ifs_acl_cache_entries 0 0 0 0
0
-------------------------------------------------------------
Library Cache Activity for DB: RPT2 Instance: RPT2 Snaps: 681 -682
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
--------------- ------------ ------ -------------- ------ ---------- -------
-
BODY 22 0.0 22 0.0 0
0
CLUSTER 35 0.0 37 0.0 0
0
INDEX 128 16.4 149 14.1 0
0
OBJECT 0 0 0
0
PIPE 0 0 0
0
SQL AREA 19,377 1.8 1,697,742 0.0 12
53
TABLE/PROCEDURE 1,455 12.3 695,875 0.1 141
0
TRIGGER 0 0 0
0
-------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
