Re: dc_used_extents ,dc_free_extents and dc_histogram_defs

Larry Ellison has publicly stated that his goal is to produce a database with less then 100 tunable parameters. Allegedly, he came rather close with 10g. As far as 10g is concerned, I'm rather disappointed with the marketing hype being created with oracle not making an early version available. I don't plan on migrating to 10g until I learn it well and if some oracle sales guy tries to exert pressure on me to migrate, he will get a very stable sign used by English archers after the battle at Agincourt to signify that they still have all the fingers needed to operate a longbow. I've had my fill of white papers and articles and now I want to see the software.

On 12/08/2003 02:24:33 PM, Rajesh.Rao_at_jpmchase.com wrote:
>
> .. 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
> > ----------------------------- -------------- ------ ------ ------------
> ----
> > --
> > 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: 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).
>

Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).