Monitoring SQL Statement Cache
The original poster wanted to monitor the SQL statement cache hit ratio and average statement execution time through SMI. Obnoxio The Clown asked the version and said 11.5 can report some of this through OAT and thus sys tables. Another poster asked the version. Zachi suggested the undocumented onstat -g cac, which works in 10 and 11 and shows the statement cache. No confirmation.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I need to define a set of monitors to monitor key DB metrics. I can see that using the SMI tables, I can get a lot of what I want. However, I can't seem to find any way of monitoring the efficiency of the SQL Statement Cache. Something like a cache hit ratio (in %) would be useful. Nor can I seem to find out, on average, how long SQL statements are taking to execute. Is there any way of finding out stuff like this on an Informix DB?
dave.clarke@reflective.com wrote: > I need to define a set of monitors to monitor key DB metrics. > > I can see that using the SMI tables, I can get a lot of what I want. > > However, I can't seem to find any way of monitoring the efficiency of > the SQL Statement Cache. Something like a cache hit ratio (in %) would > be useful. > > Nor can I seem to find out, on average, how long SQL statements are > taking to execute. > > Is there any way of finding out stuff like this on an Informix DB? What version? I'm pretty sure 11.5 can report at least some of this out of the box with OAT, and hence you can probably find it all in sys* -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
On 30 Mar, 13:48, dave.cla...@reflective.com wrote:
> I need to define a set of monitors to monitor key DB metrics.
>
> I can see that using the SMI tables, I can get a lot of what I want.
>
> However, I can't seem to find any way of monitoring the efficiency of
> the SQL Statement Cache. Something like a cache hit ratio (in %) would
> be useful.
>
> Nor can I seem to find out, on average, how long SQL statements are
> taking to execute.
>
> Is there any way of finding out stuff like this on an Informix DB?
Which version?
RIn onstat to get the stats?
On Mar 30, 8:48 am, dave.cla...@reflective.com wrote:
> I need to define a set of monitors to monitor key DB metrics.
>
> I can see that using the SMI tables, I can get a lot of what I want.
>
> However, I can't seem to find any way of monitoring the efficiency of
> the SQL Statement Cache. Something like a cache hit ratio (in %) would
> be useful.
>
> Nor can I seem to find out, on average, how long SQL statements are
> taking to execute.
>
> Is there any way of finding out stuff like this on an Informix DB?
try:
onstat -g cac
this is an undocumented feature, works in 10 and 11. it shows various
caches, including the statement cache you are looking for.
Zachi