| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> dc_used_extents ,dc_free_extents and dc_histogram_defs
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):
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 01,359 568 33
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.01,371 1,564 94
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 022 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
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
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: gmei_at_incyte.com 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: ListGuru_at_fatcity.com (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).Received on Mon Dec 08 2003 - 11:39:30 CST
 |
 |