jump to content

Data Dictionary Info

Here are some scripts related to Data Dictionary Info .

Dictionary Cache

DATA DICTIONARY CACHE NOTES:

  • Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.
  • Gets - Total number of requests for information on the data object.
  • Getmisses - Number of data requests resulting in cache misses.
  • % Cache Misses - Miss Ratio
  • Count - Total number of entries in the cache
  • Usage - Number of cache entries that contain valid data.

  • This report shows the DC cache statistics (a part of the shared pool).
  • Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness. The semantic check requires cross-referencing of the information in the SQL statement and the data dictionary, including the table names, columns-to-date relationships, column names, data types, and security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the data dictionary information from the SYSTEM tablespace. These statements for data dictionary information represent one type of recursive SQL statement. To increase the size available to the dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora parameter.)
  • Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get on a data buffer or waiting for a redo buffer.
  • If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned these in the init.ora file) 

    select	PARAMETER,
	GETS,
	GETMISSES,
	round(GETMISSES/GETS,2)*100 "% Cache Misses",
	COUNT,
	USAGE
from 	v$rowcache
where 	GETS > 0
order 	by (GETMISSES/GETS)*100 desc

    Latch Gets/Misses

    LATCH GET AND MISS NOTES:

  • Latch Name - Name of the latch
  • Gets - Number of times obtained wait
  • Misses - Number of time obtained with wait but failed first try
  • Gets / Misses % - Ratio of misses to gets
  • Immediate Gets - Number of times obtained with no wait
  • Immediate Misses - Number of times failed to get with no wait 

    select	NAME,
	GETS,
	MISSES,
	round(((GETS-MISSES)*100) / GETS , 2) "Gets/Misses %",
	IMMEDIATE_GETS,
	IMMEDIATE_MISSES
from 	v$latch
where 	GETS != 0
or 	IMMEDIATE_MISSES > 0
order 	by ((GETS-MISSES) / GETS) desc