Skip to main content

Search

Welcome to Tibero GTS!

How to Check miss_cnt through ilog

Document Type | Technical Information

Category | Monitoring/Inspection

Applicable Product Version | 6FS07

Document Number | TMOTI010

 

Overview

This guide explains how to check which query increased the miss_cnt using ilog when the DD cache decreases.
  • DD cache is used when referring to information about an object during SQL statement execution; if the information exists, a hit occurs, otherwise a miss occurs.
  • hit_cnt: Increases when a dd cache search is successful.
  • miss_cnt: Increases when a dd cache search fails. miss_cnt can also increase when a recompile occurs.
Note
The more queries executed, the more both hit_cnt and miss_cnt increase, and in practice, the hit ratio may vary depending on the type of queries used.
 

 

Method

1. Check DD Cache Hit Ratio

SELECT TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time", 
ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100,1)
"Dictionary Cache Hit Ratio(%)" 
FROM v$rowcache;

 

2. ilog Configuration

After confirming USE_ILOG=N,
execute: 
alter system ilog enable name 'ALLOC_SC_SEARCH_MISS' level detail;

 

3. ilog Output

C:\tibero_dump\tibero6\log\ilog>tbiv -t
...
sc_search : CACHE_MISS type=18, pin_so=00000000f44d2290
sc_search : CACHE_MISS type=20, pin_so=00000000f44d1450
sc_search : CACHE_MISS type=20, pin_so=00000000f44d0cd0
sc_search : CACHE_MISS type=11, pin_so=00000000f44d2290
sc_search : CACHE_MISS type=11, pin_so=00000000f44d2290
sc_search : CACHE_MISS type=11, pin_so=00000000f44d2290
sc_search : CACHE_MISS type=20, pin_so=00000000f44d2110
sc_search : CACHE_MISS type=18, pin_so=00000000f44d2290
sc_search : CACHE_MISS type=20, pin_so=00000000f44d2110
sc_search : CACHE_MISS type=20, pin_so=00000000f44d0ad0
sc_search : CACHE_MISS type=7, pin_so=00000000f44d1950

โ†’ type=10 indicates failure in OBJAUTH dd cache search process

 

 

 

 

Related to