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

I do not really know what for do you need
shared_pool_size = 600Mb
Very big shared pool decrease performance.

log_buffer around 8M
The biggest size you can make is 3M, other 5M is useless.

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

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

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 ...

Quote:

---

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

---

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