onstat -g prc

Posted in 2008

Summary

A user on IDS 7.31 asked how to read 'onstat -g prc' output and how to tune PC_POOLSIZE/PC_HASHSIZE. Art Kagel explained the output: the defaults (PC_POOLSIZE 127, PC_HASHSIZE 31) allow ~3937 cached procedures; the figures shown were 214 procedures currently in cache, 277 max entries, and ref_cnt showing how often each procedure was called since caching. Tuning simply means sizing the cache to hold the commonly used procedures plus headroom; with the posted numbers the defaults were fine even with 1000 procedures total. John Miller added that IDS 11 and the OpenAdmin Tool show hit ratios for the procedure and other SQL caches.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Stored Procedures & SPL, Server Administration

Kern Doe — Tue, 26 Feb 2008 16:49:20 — source: IIUG Forums & Mailing Lists

How do you interpret the output of "onstat -g prc"? and/or how do you tune the PC_POOLSIZE (PC_HASHSIZE?) ? Any feedback is appreciated. onstat -g prc | more IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 2 days 08:36:59 -- 3711120 Kbytes Stored Procedure Cache: Number of lists : 31 PC_POOLSIZE : 127 Number of entries : 277 Number of entries in use : 214 Stored Procedure Cache Entries: list# id ref_cnt dropped? heap_ptr procedure name -------------------------------------------------------------- 7 11685 302 0 b8be6420 customer@nyweb_prdct:informix.sp_cart_get_all 24 10054 44 0 5b86bb60 customer@nyweb_prdct:informix.sp_clr_chk_answer 27 10556 8 0 b6376820 customer@nyweb_prdct:informix.sp_set_email_sub 5 11534 52 0 5c1ef3a0 customer@nyweb_prdct:informix.sp_get_email_sub 18 11758 279 0 b8b8a020 customer@nyweb_prdct:informix.sp_get_promo_pull2 . . . ________________________________________________________________________________ ____ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs

↪ replying to Kern Doe

Art S. Kagel (Oninit) — Tue, 26 Feb 2008 19:07:23 — source: IIUG Forums & Mailing Lists

Kern Doe wrote: This says that you have the default SP cache configuration of PC_POOLSIZE = 127 and PC_HASHSIZE = 31 permitting the caching of up to 3937 procedures. There are 214 procedures currently in cache and the max since stats were zero'd was 277. The procedure sp_cart_get_all has been referenced 302 times since it was added to the cache and at the point of the report it was still active. As to tuning, you just have to make sure that your cache is large enough to hold all of the stored procedures you commonly access with some headroom for the occasional routine that's access repeatedly for a short period of time. What else do you want to know? Art S. Kagel Oninit > How do you interpret the output of "onstat -g prc"? > and/or how do you tune the PC_POOLSIZE (PC_HASHSIZE?) ? > Any feedback is appreciated. > > onstat -g prc | more > IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 2 days 08:36:59 > -- 3711120 Kbytes> > Stored Procedure Cache: > > Number of lists : 31 > > PC_POOLSIZE : 127> > Number of entries : 277 > > Number of entries in use : 214 > > Stored Procedure Cache Entries: > list# id ref_cnt dropped? heap_ptr procedure name > -------------------------------------------------------------- > 7 11685 302 0 b8be6420 customer@nyweb_prdct:informix.sp_cart_get_all > 24 10054 44 0 5b86bb60 customer@nyweb_prdct:informix.sp_clr_chk_answer > 27 10556 8 0 b6376820 customer@nyweb_prdct:informix.sp_set_email_sub > 5 11534 52 0 5c1ef3a0 customer@nyweb_prdct:informix.sp_get_email_sub > 18 11758 279 0 b8b8a020 customer@nyweb_prdct:informix.sp_get_promo_pull2 > .. . . > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

↪ replying to Art S. Kagel (Oninit)

Kern Doe — Tue, 26 Feb 2008 19:20:01 — source: IIUG Forums & Mailing Lists

Thanks for your reply and inputs Art. OK, so we have about 1000 stored procedures totally -- very unlike all will be used at once. Even though, these defaults seem OK (127, 31)? Regards ----- Original Message ---- From: Art S. Kagel (Oninit) <art@oninit.com> To: ids@iiug.org Sent: Tuesday, February 26, 2008 2:07:23 PM Subject: Re: onstat -g prc [11435] Kern Doe wrote: This says that you have the default SP cache configuration of PC_POOLSIZE = 127 and PC_HASHSIZE = 31 permitting the caching of up to 3937 procedures. There are 214 procedures currently in cache and the max since stats were zero'd was 277. The procedure sp_cart_get_all has been referenced 302 times since it was added to the cache and at the point of the report it was still active. As to tuning, you just have to make sure that your cache is large enough to hold all of the stored procedures you commonly access with some headroom for the occasional routine that's access repeatedly for a short period of time. What else do you want to know? Art S. Kagel Oninit > How do you interpret the output of "onstat -g prc"? > and/or how do you tune the PC_POOLSIZE (PC_HASHSIZE?) ? > Any feedback is appreciated. > > onstat -g prc | more > IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 2 days 08:36:59 > -- 3711120 Kbytes> > Stored Procedure Cache: > > Number of lists : 31 > > PC_POOLSIZE : 127> > Number of entries : 277 > > Number of entries in use : 214 > > Stored Procedure Cache Entries: > list# id ref_cnt dropped? heap_ptr procedure name > -------------------------------------------------------------- > 7 11685 302 0 b8be6420 customer@nyweb_prdct:informix.sp_cart_get_all > 24 10054 44 0 5b86bb60 customer@nyweb_prdct:informix.sp_clr_chk_answer > 27 10556 8 0 b6376820 customer@nyweb_prdct:informix.sp_set_email_sub > 5 11534 52 0 5c1ef3a0 customer@nyweb_prdct:informix.sp_get_email_sub > 18 11758 279 0 b8b8a020 customer@nyweb_prdct:informix.sp_get_promo_pull2 > .. . . > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ =========== ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ________________________________________________________________________________ ____ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

↪ replying to Art S. Kagel (Oninit)

John Miller iii — Tue, 26 Feb 2008 19:23:40 — source: IIUG Forums & Mailing Lists

In version 11 and/or by using the OpenAdmin Tool you can see the hit ratio of 14 different SQL caches, including the procedure cache. John F. Miller III = "Art S. Kagel = (Oninit)" = <art@oninit.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: onstat -g prc [11435] = 02/26/2008 11:07 = AM = = = Please respond to = ids@iiug.org = = = Kern Doe wrote: This says that you have the default SP cache configuration of PC_POOLSIZE =3D 127 and PC_HASHSIZE =3D 31 permitting the caching of up= to 3937 procedures. There are 214 procedures currently in cache and the max since stats were zero'd was 277. The procedure sp_cart_get_all has been referenced 302 times since it was added to the cache and at the point of the report it was still active. As to tuning, you just have to= make sure that your cache is large enough to hold all of the stored procedures you commonly access with some headroom for the occasional routine that's access repeatedly for a short period of time. What else do you want to know? Art S. Kagel Oninit > How do you interpret the output of "onstat -g prc"? > and/or how do you tune the PC_POOLSIZE (PC_HASHSIZE?) ? > Any feedback is appreciated. > > onstat -g prc | more > IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 2 days 08:36:59 > -- 3711120 Kbytes> > Stored Procedure Cache: > > Number of lists : 31 > > PC_POOLSIZE : 127> > Number of entries : 277 > > Number of entries in use : 214 > > Stored Procedure Cache Entries: > list# id ref_cnt dropped? heap_ptr procedure name > -------------------------------------------------------------- > 7 11685 302 0 b8be6420 customer@nyweb_prdct:informix.sp_cart_get_all > 24 10054 44 0 5b86bb60 customer@nyweb_prdct:informix.sp_clr_chk_answe= r > 27 10556 8 0 b6376820 customer@nyweb_prdct:informix.sp_set_email_sub > 5 11534 52 0 5c1ef3a0 customer@nyweb_prdct:informix.sp_get_email_sub > 18 11758 279 0 b8b8a020 customer@nyweb_prdct:informix.sp_get_promo_pu= ll2 > .. . . > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! =

↪ replying to Kern Doe

Art S. Kagel (Oninit) — Tue, 26 Feb 2008 20:27:31 — source: IIUG Forums & Mailing Lists

Kern Doe wrote: > Thanks for your reply and inputs Art. > OK, so we have about 1000 stored procedures totally -- very unlike all will be > used at once. Even though, these defaults seem OK (127, 31)? > If the output you posted is typical, you're OK. If it's not, and normally more procs are run, then you'll have to do some analysis yourself to determine that. Art S. Kagel Oninit > Regards > > ----- Original Message ---- > From: Art S. Kagel (Oninit) <art@oninit.com> > To: ids@iiug.org > Sent: Tuesday, February 26, 2008 2:07:23 PM > Subject: Re: onstat -g prc [11435] > > Kern Doe wrote: > > This says that you have the default SP cache configuration of > PC_POOLSIZE = 127 and PC_HASHSIZE = 31 permitting the caching of up to > 3937 procedures. There are 214 procedures currently in cache and the > max since stats were zero'd was 277. The procedure sp_cart_get_all has > been referenced 302 times since it was added to the cache and at the > point of the report it was still active. As to tuning, you just have to > make sure that your cache is large enough to hold all of the stored > procedures you commonly access with some headroom for the occasional > routine that's access repeatedly for a short period of time. > > What else do you want to know? > > Art S. Kagel > Oninit > >> How do you interpret the output of "onstat -g prc"? >> and/or how do you tune the PC_POOLSIZE (PC_HASHSIZE?) ? >> Any feedback is appreciated. >> >> onstat -g prc | more >> IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 2 days>> > 08:36:59 > >> -- 3711120 Kbytes >> >> Stored Procedure Cache: >> >> Number of lists : 31 >> >> PC_POOLSIZE : 127>> >> Number of entries : 277 >> >> Number of entries in use : 214 >> >> Stored Procedure Cache Entries: >> list# id ref_cnt dropped? heap_ptr procedure name >> -------------------------------------------------------------- >> 7 11685 302 0 b8be6420 customer@nyweb_prdct:informix.sp_cart_get_all >> 24 10054 44 0 5b86bb60 customer@nyweb_prdct:informix.sp_clr_chk_answer >> 27 10556 8 0 b6376820 customer@nyweb_prdct:informix.sp_set_email_sub >> 5 11534 52 0 5c1ef3a0 customer@nyweb_prdct:informix.sp_get_email_sub >> 18 11758 279 0 b8b8a020 customer@nyweb_prdct:informix.sp_get_promo_pull2 >> .. . . >> >> >> > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

↪ replying to John Miller iii

Kern Doe — Tue, 26 Feb 2008 20:50:40 — source: IIUG Forums & Mailing Lists

Thanks John. We haven't got to those good stuff of version 11 yet, the customer resists the change -- guess we're a little behind. Now you are openning to another discussion: OpenAdmin Tool, how hard is it to use it? any quick document to look at? Kern -- ----- Original Message ---- From: John Miller iii <miller3@us.ibm.com> To: ids@iiug.org Sent: Tuesday, February 26, 2008 2:23:41 PM Subject: Re: onstat -g prc [11437] In version 11 and/or by using the OpenAdmin Tool you can see the hit ratio of 14 different SQL caches, including the procedure cache. John F. Miller III = "Art S. Kagel = (Oninit)" = <art@oninit.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: onstat -g prc [11435] = 02/26/2008 11:07 = AM = = = Please respond to = ids@iiug.org = = = Kern Doe wrote: This says that you have the default SP cache configuration of PC_POOLSIZE =3D 127 and PC_HASHSIZE =3D 31 permitting the caching of up= to 3937 procedures. There are 214 procedures currently in cache and the max since stats were zero'd was 277. The procedure sp_cart_get_all has been referenced 302 times since it was added to the cache and at the point of the report it was still active. As to tuning, you just have to= make sure that your cache is large enough to hold all of the stored procedures you commonly access with some headroom for the occasional routine that's access repeatedly for a short period of time. What else do you want to know? Art S. Kagel Oninit > How do you interpret the output of "onstat -g prc"? > and/or how do you tune the PC_POOLSIZE (PC_HASHSIZE?) ? > Any feedback is appreciated. > > onstat -g prc | more > IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 2 days 08:36:59 > -- 3711120 Kbytes> > Stored Procedure Cache: > > Number of lists : 31 > > PC_POOLSIZE : 127> > Number of entries : 277 > > Number of entries in use : 214 > > Stored Procedure Cache Entries: > list# id ref_cnt dropped? heap_ptr procedure name > -------------------------------------------------------------- > 7 11685 302 0 b8be6420 customer@nyweb_prdct:informix.sp_cart_get_all > 24 10054 44 0 5b86bb60 customer@nyweb_prdct:informix.sp_clr_chk_answe= r > 27 10556 8 0 b6376820 customer@nyweb_prdct:informix.sp_set_email_sub > 5 11534 52 0 5c1ef3a0 customer@nyweb_prdct:informix.sp_get_email_sub > 18 11758 279 0 b8b8a020 customer@nyweb_prdct:informix.sp_get_promo_pu= ll2 > .. . . > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! = ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ________________________________________________________________________________ ____ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Related threads

Glossary

onstat