DD-Cache quality
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: DD-Cache quality

  1. #1
    Join Date
    Dec 2002
    Posts
    9

    Question DD-Cache quality

    Hello Gurus,

    I am very new to this community. Advanced thanks for all of your support.
    Please help me out with the DD-Cache quality problem. On my production system the DD-Cache quality is 85.1%. I would like to improve this with all of your expertise.

    SGA parameters -->
    shared_pool_size = 629145600
    db_block_buffers = 128000
    log_buffer = 8388608

    Data Dictionary Quality
    -----------------------
    Sum(Gets) Sum(GetMisses) DD-Cache quality %
    ------------ -------------- ------------------
    5,139,963 763,960 85.1

    --SGA Pool Info.-->
    ----------------

    POOL NAME BYTES PERCENT
    ----------- -------------------------- --------------- ----------
    db_block_buffers 1,048,576,000 60.83193
    shared pool sql area 297,709,096 17.27125
    shared pool library cache 156,632,896 9.08688
    shared pool KQLS heap 76,729,896 4.45140
    shared pool dictionary cache 58,421,192 3.38924
    shared pool free memory 35,710,000 2.07167
    shared pool db_block_buffers 26,624,000 1.54456
    log_buffer 8,388,608 .48666
    shared pool db_block_hash_buckets 4,301,104 .24952
    shared pool miscellaneous 2,112,144 .12253
    shared pool table columns 1,061,328 .06157
    shared pool event statistics per sess 1,041,600 .06043
    shared pool enqueue_resources 832,000 .04827
    shared pool sessions 729,600 .04233
    shared pool State objects 575,648 .03340
    shared pool state objects 551,192 .03198
    shared pool transactions 501,600 .02910
    shared pool PL/SQL DIANA 428,712 .02487
    shared pool DML locks 420,000 .02437
    shared pool Checkpoint queue 342,048 .01984
    shared pool processes 268,000 .01555
    shared pool simulator trace entries 240,000 .01392
    shared pool db_handles 220,000 .01276
    shared pool log_buffer 196,608 .01141
    shared pool message pool freequeue 191,192 .01109
    shared pool ktlbk state objects 176,880 .01026
    shared pool long op statistics array 124,000 .00719
    shared pool table definiti 111,544 .00647
    shared pool SYSTEM PARAMETERS 107,208 .00622
    fixed_sga 104,936 .00609
    shared pool db_files 102,232 .00593
    shared pool PL/SQL MPCODE 60,400 .00350
    shared pool KGFF heap 55,584 .00322
    java pool free memory 32,768 .00190
    shared pool KGK heap 12,448 .00072
    shared pool PLS non-lib hp 2,136 .00012
    shared pool fixed allocation callback 1,904 .00011
    shared pool trigger inform 1,344 .00008
    --------------- ----------
    sum 1,723,697,848 99.99835


    I think I have provided you with all the information. In case if you need more information, please let me know and please help me out with the Data Dictionary Quality problem.

    Thank you,
    Allen

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    I do not really know what for do you need
    shared_pool_size = 600Mb
    Very big shared pool decrease performance.
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    log_buffer around 8M
    The biggest size you can make is 3M, other 5M is useless.
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Dec 2002
    Posts
    9
    Hi,

    1. I was seeing lots of Redo log wait and Allocation retries related the Log_bufer. I read some book and it says by increasing the Log_buffer size I can avoid the Redo log wait and Allocation retries.

    2. If I decrease the shared_pool_size, can I achieve the DD-Cache quality performance?

    Suggestions please...

    Regards,
    Allen

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    as kgb suggested all too big

    1. I was seeing lots of Redo log wait and Allocation retries related the Log_bufer. I read some book and it says by increasing the Log_buffer size I can avoid the Redo log wait and Allocation retries.

    right but to get that part you have to understand how lgwr works which obviously you dont seem to be quite familiar, I suggest you to read Oracle Concepts guide from tahiti.oracle.com

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    You need to open any book by performance tuning.
    How many concurrent connections, which connection type (shared/dedicated) do you use, how many records (SQL_TEXT) in the v$sqlarea and how many distinct records (SQL_TEXT) inb the v$sqlarea ...

    Originally posted by Allen
    2. If I decrease the shared_pool_size, can I achieve the DD-Cache quality performance?
    Best wishes!
    Dmitri

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Allen,

    Pick the script from following location and see whats the hit/miss ratio for dc_* entries and modify parms that control those entries. We had the issue with too many extents/segments which causing too many misses, when we dropped all the unwanted tables dictionary hit ratio got better.

    http://www.dbasupport.com/oracle/scr...ailed/26.shtml
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width