| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: dc_used_extents ,dc_free_extents and dc_histogram_defs
.. And there used to be all these dc_ parameters that one could set, giving the dba control over the dictionary cache, which was not a part of the shared pool. And then came Oracle V7, with the shared_pool_size, wresting that control.
Regards
Mladen Gogala
<mladen_at_wangtr To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
ading.com> cc:
Sent by: Subject: Re: dc_used_extents ,dc_free_extents and dc_histogram_defs
ml-errors_at_fatc
ity.com
12/08/2003
01:29 PM
Please respond
to ORACLE-L
Well, once upon a time, in a land far, far away, there was Oracle V6 with
something called "TPO", which was essentially row locking + PL/SQL V1 (no
stored procedures). My guess is that Cary Millsap, Anjo Kolk, Steve
Feuerstein
and Howard Rogers know a thing or two about the Jurassic period in the
database
development. BTW, that was also when buffer hit ratio was invented. The
entries that you see are remnants from oracle v6, together with the table
called "V$ROWCACHE" and are both religiously maintained for the
compatibility
reasons, because Oracle Corp. doesn't want to disappoint all those who are
still running V6. Even compatibility with V5 is still maintained. In Oracle
5.1.22,
dictionary views weren't called "user_tables" and "user_objects", they were
called
"tab" (user_tables) and cat (from "CATALOG", replaced with "USER_OBJECTS").
The term
"CATALOG" was directory command on Apple IIe (6502, later Z80) with 100k
floppies,
computer immensly popular at the time, and I believe that is why the first
implementation of "user_objects" was called "catalog". Now, let's fast
forward to the
present time and Oracle 9.2.0.4. Do "Select * from tab" and "select * from
cat".
You'll be surprised. For all those still running V5.1.22 with forms 2.0 and
2.3,
the world is not over yet.
On 12/08/2003 12:39:30 PM, Guang Mei wrote:
> 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
> ----------------------------- -------------- ------ ------ ------------
--Received on Mon Dec 08 2003 - 13:24:33 CST
> --
> 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).
>
Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Rajesh.Rao_at_jpmchase.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).
 |
 |