6 Replies Latest reply: Mar 21, 2013 9:08 AM by Mr.D. RSS

    Dictionary cache stats question

    Mr.D.
      Hi guys,
      i've a db 9.2.0.8 in RBO (don't ask me why).
      Looking spreport i see high latches on a database that do nothing.
      In Dictionary cache stats i see:
                                         Get    Pct    Scan   Pct      Mod      Final
      Cache                         Requests   Miss    Reqs  Miss     Reqs      Usage
      ------------------------- ------------ ------ ------- ----- -------- ----------
      dc_files                           466   18.2       0              0          0
      dc_global_oids                  31,683    2.1       0              0         61
      dc_histogram_defs            1,665,961   93.8       0       ########    361,443
      dc_object_ids                1,006,604   70.9       0              0     53,491
      dc_objects                   2,038,678    8.1       0              0     56,984
      dc_profiles                      2,453    0.2       0              0          1
      dc_rollback_segments            40,917    0.0       0              0         12
      dc_segments                  2,241,382   25.7       0             27    128,705
      dc_sequences                       199   35.7       0            199          2
      dc_tablespace_quotas               108    5.6       0             27          2
      dc_tablespaces                  33,024    0.1       0              0         10
      dc_user_grants                 648,413    0.0       0              0         29
      dc_usernames                 1,790,451    0.0       0              0        221
      dc_users                       736,660    0.0       0              0         63
      dc_histogram_defs and dc_object_ids misses are high.
      Latch free is about 30%
      Top 5 Timed Events
      ~~~~~~~~~~~~~~~~~~                                                     % Total
      Event                                               Waits    Time (s) Ela Time
      -------------------------------------------- ------------ ----------- --------
      db file sequential read                         1,230,978      13,710    44.72
      latch free                                        220,372       9,556    31.17
      Top queries are on dictionary tables.

      Library cache advisor report:
                               Get  Pct        Pin        Pct               Invali-
      Namespace           Requests  Miss     Requests     Miss     Reloads  dations
      --------------- ------------ ------ -------------- ------ ---------- --------
      BODY                   6,452    1.9          6,523    3.8        128        0
      CLUSTER               14,928    0.2         13,608    0.3          0        0
      INDEX                 56,258    0.0         56,258    0.0          0        0
      JAVA DATA                 14   21.4        107,226    0.0          7        0
      JAVA RESOURCE             14  100.0             28  100.0          0        0
      JAVA SOURCE               28  100.0             56  100.0          0        0
      SQL AREA          18,961,997    3.0     72,130,081    1.6      4,395  172,398
      TABLE/PROCEDURE    2,755,979   18.0      6,547,592   31.8     70,443        0
      TRIGGER                7,317    0.4          7,317    0.4          0        0
      Shared pool advisor report:
                                                                Estd
      Shared Pool    SP       Estd         Estd     Estd Lib LC Time
         Size for  Size  Lib Cache    Lib Cache   Cache Time   Saved  Estd Lib Cache
        Estim (M) Factr   Size (M)      Mem Obj    Saved (s)   Factr    Mem Obj Hits
      ----------- ----- ---------- ------------ ------------ ------- ---------------
              752    .8        679      177,824    2,477,130     1.0      20,893,760
              848    .9        772      190,608    2,477,227     1.0      20,967,434
              944   1.0        870      203,816    2,477,310     1.0      21,025,677
            1,040   1.1        965      216,439    2,477,377     1.0      21,090,173
            1,136   1.2      1,061      229,729    2,477,432     1.0      21,159,297
            1,232   1.3      1,157      243,361    2,477,699     1.0      21,227,748
      Any ideas?

      Thank you.

      Edited by: Mr.D. on 18-mar-2013 7.41
        • 1. Re: Dictionary cache stats question
          sb92075
          It appears that you suffer from Compulsive Tuning Disorder.

          In EVERY Top 5 list you will see 5 items.
          Just because an item is listed does NOT mean it is a problem that needs to be solved.
          • 2. Re: Dictionary cache stats question
            Mr.D.
            And for dictionary cache stats and library cache stats?
            • 3. Re: Dictionary cache stats question
              jgarry
              You are perhaps hitting a bug or not-a-bug. Shared pool "features" seem to come and go with versions and patch levels. Are dblinks involved?

              Some features use CBO even if you have RBO explicit.

              Poke around on MOS for your problem dc tables.

              And I echo sb: why are you looking at this?
              • 4. Re: Dictionary cache stats question
                Mr.D.
                jgarry wrote:
                And I echo sb: why are you looking at this?
                The connection to the database is very low, there is no network problem.
                Other database do not suffers of this problem.
                I think there is some concurrency at dictionary level.
                There is high Pct Miss and reloads on a database that has low workload.

                Ok, i will search on mos.

                Thank, you
                • 5. Re: Dictionary cache stats question
                  Jonathan Lewis
                  Mr.D. wrote:
                  Hi guys,
                  i've a db 9.2.0.8 in RBO (don't ask me why).
                  Looking spreport i see high latches on a database that do nothing.
                  19 million gets and 72 million pins is a lot of nothing.
                  How long is the snapshot interval you've used.
                  Top queries are on dictionary tables.
                  Top by what ? Executions, disk reads, version count ?
                  Please supply a couple of examples.


                  >
                  Library cache advisor report:
                                           Get  Pct        Pin        Pct               Invali-
                  Namespace           Requests  Miss     Requests     Miss     Reloads  dations
                  --------------- ------------ ------ -------------- ------ ---------- --------
                  SQL AREA          18,961,997    3.0     72,130,081    1.6      4,395  172,398
                  TABLE/PROCEDURE    2,755,979   18.0      6,547,592   31.8     70,443        0
                  Invalidations are generally caused by DDL on the underlying objects. Check that you're not running the stats collection code (despite being on RBO), look for create/drop statements, check particularly for truncate statements.

                  As far as slow logon is concerned. check for audit options and logon triggers, and code that does things like "set role".

                  While you're at it, you might also consider supplying us with the top section of the report - including the Load Profile.

                  Regards
                  Jonathan Lewis
                  • 6. Re: Dictionary cache stats question
                    Mr.D.
                    Hi Jonathan,
                    i found a problem in mount option of datafile filesystem.
                    CIO was not enabled.
                    After restart the situation is, for a snap on 9 am to 18pm:
                                             Get  Pct        Pin        Pct               Invali-
                    Namespace           Requests  Miss     Requests     Miss     Reloads  dations
                    --------------- ------------ ------ -------------- ------ ---------- --------
                    BODY                     735    0.0            735    0.0          0        0
                    INDEX                  3,234    0.0          3,234    0.0          0        0
                    SQL AREA              26,004   12.5         98,403    8.2        935        0
                    TABLE/PROCEDURE       14,215    0.4         26,897    1.1          0        0
                    TRIGGER                1,936    0.0          1,936    0.0          0        0
                              -------------------------------------------------------------
                    
                                                       Get    Pct    Scan   Pct      Mod      Final
                    Cache                         Requests   Miss    Reqs  Miss     Reqs      Usage
                    ------------------------- ------------ ------ ------- ----- -------- ----------
                    dc_histogram_defs                    1  100.0       0              0         79
                    dc_object_ids                   10,494    0.9       0              0     17,203
                    dc_objects                      12,084    0.1       0              0     49,945
                    dc_profiles                        681    0.0       0              0          1
                    dc_rollback_segments             2,368    0.0       0              0         12
                    dc_segments                     14,642    0.0       0              2    119,931
                    dc_sequences                        43    4.7       0             43          5
                    dc_tablespace_quotas                 8    0.0       0              2          2
                    dc_tablespaces                      28    3.6       0              0          3
                    dc_user_grants                   6,713    0.1       0              0         20
                    dc_usernames                     1,443    0.0       0              0        200
                    dc_users                        13,867    0.1       0              0         53
                              -------------------------------------------------------------
                    
                    
                    Top 5 Timed Events
                    ~~~~~~~~~~~~~~~~~~                                                     % Total
                    Event                                               Waits    Time (s) Ela Time
                    -------------------------------------------- ------------ ----------- --------
                    CPU time                                                           16    38.93
                    control file parallel write                        10,795           9    23.16
                    db file sequential read                               611           6    15.58
                    db file scattered read                                157           3     8.55
                    control file sequential read                        4,923           2     4.50
                              -------------------------------------------------------------
                    
                    
                    Load Profile
                    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                                       ---------------       ---------------
                                      Redo size:                315.30              9,638.74
                                  Logical reads:                  9.15                279.73
                                  Block changes:                  1.70                 51.92
                                 Physical reads:                  0.04                  1.22
                                Physical writes:                  0.08                  2.53
                                     User calls:                  2.29                 69.93
                                         Parses:                  0.83                 25.43
                                    Hard parses:                  0.12                  3.68
                                          Sorts:                  0.42                 12.74
                                         Logons:                  0.02                  0.67
                                       Executes:                  1.36                 41.60
                                   Transactions:                  0.03
                    Jonathan Lewis wrote:Top by what ? Executions, disk reads, version count ?
                    Please supply a couple of examples.
                    Buffer gets, physical read, elapsed....

                    Yes stats are collected and the database is RBO, please don't ask me why!

                    Edited by: Mr.D. on 21-mar-2013 7.08