Monitoring SQL Statement Cache

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