Upd Stat on SP using PDQ Question
On AIX/IDS 11.50, a site ran UPDATE STATISTICS on tables and then on stored procedures with PDQPRIORITY=20; afterwards every SP ran at PDQ 20, saturating the Memory Grant Manager and killing performance. Respondents confirmed the cause: SPL routines freeze the PDQ priority in effect when they are created or recompiled (documented under "Using SPL routines with PDQ queries"). Fix: use PDQ only for UPDATE STATISTICS on tables, and set PDQPRIORITY=0 before UPDATE STATISTICS FOR PROCEDURE/recompiling SPs so they inherit the calling session's priority; Art Kagel's dostats does this automatically.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good MOrning All, AIX 6.1 IDS 11.50.FC7 Over the weekend, we performed some DB maint in which we ran upd stats on all tables then SPs using PDQPRIORITY of 20. After turning everything loose, all SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance went out the door. I know this was most likely covered in a perf & tuning class somewhere way back but kinda hard to find in the actual doc. My question is did the execution of upd stats on the SPs with PDQPRIORITY set to 20 force that? Thanx, Dan
Yep, PDQ are SPL create/stats time is maintained Cheers Paul > Good MOrning All, > > AIX 6.1 > IDS 11.50.FC7 > > Over the weekend, we performed some DB maint in which we ran upd stats on > all > tables then SPs using PDQPRIORITY of 20. After turning everything loose, > all > SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance > went > out the door. > > I know this was most likely covered in a perf & tuning class somewhere way > back but kinda hard to find in the actual doc. My question is did the > execution of upd stats on the SPs with PDQPRIORITY set to 20 force that? > > Thanx, > Dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
Yes. On Jun 17, 2013 2:45 PM, "DAN MUELLER" <ddmueller@intercall.com> wrote: > Good MOrning All, > > AIX 6.1 > IDS 11.50.FC7 > > Over the weekend, we performed some DB maint in which we ran upd stats on > all > tables then SPs using PDQPRIORITY of 20. After turning everything loose, > all > SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance went > out the door. > > I know this was most likely covered in a perf & tuning class somewhere way > back but kinda hard to find in the actual doc. My question is did the > execution of upd stats on the SPs with PDQPRIORITY set to 20 force that? > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bacbb9a14201904df5a3843
Hello, Dan. You should always take care about two things: 1) starting the engine with PDQ activated (non zero value) -> I´ve faced issues in 4gl programs, because of that. 2) rebuilding database stastics in sp, with PDQ activated That´s why I always recommend the use of Art Kagel´s dostats/drive_dostats utilities. Before the script start dealing with SPs, it zeroes the PDQ, even if you are using PDQ to speed up the statistics rebuild, nothing wrong happens to your SPs. How did you rebuild your stats? Via shell script? If so, make sure you set PDQPRIORITY=0 before dealing with your procedures. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: ddmueller@intercall.com > Subject: Upd Stat on SP using PDQ Question [30547] > Date: Mon, 17 Jun 2013 09:44:29 -0400 > > Good MOrning All, > > AIX 6.1 > IDS 11.50.FC7 > > Over the weekend, we performed some DB maint in which we ran upd stats on all > tables then SPs using PDQPRIORITY of 20. After turning everything loose, all > SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance went > out the door. > > I know this was most likely covered in a perf & tuning class somewhere way > back but kinda hard to find in the actual doc. My question is did the > execution of upd stats on the SPs with PDQPRIORITY set to 20 force that? > > Thanx, > Dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Yes, yes it did. You should ALWAYS compile stored procedures with PDQPRIORITY set to zero in which case they will run with the PDQPRIORITY of the issuing session. If you run update statistics or create a stored procedure with PDQPRIORITY set to a positive value then that procedure will ALWAYS run with the PDQPRIORITY it was compiled with! That is why my dostats utility resets PDQPRIORITY to zero (unless you tell it otherwise with the -P option) before compiling the stored procedures regardless of the PDQPRIORITY set in the user's environment or with the -Q option. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 17, 2013 at 9:44 AM, DAN MUELLER <ddmueller@intercall.com>wrote: > Good MOrning All, > > AIX 6.1 > IDS 11.50.FC7 > > Over the weekend, we performed some DB maint in which we ran upd stats on > all > tables then SPs using PDQPRIORITY of 20. After turning everything loose, > all > SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance went > out the door. > > I know this was most likely covered in a perf & tuning class somewhere way > back but kinda hard to find in the actual doc. My question is did the > execution of upd stats on the SPs with PDQPRIORITY set to 20 force that? > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160baaeec142104df5b3d35
Yes, Stored Procedures take the compile time PDQ value into account when compiling stored procedures. The database server freezes the PDQ priority that is used to optimize SQL statements within SPL routines at the time of procedure creation or the last manual recompilation. The manual explains this under the section called "Using SPL routines with PDQ queries" John F. Miller III ids-bounces@iiug.org wrote on 06/17/2013 06:44:29 AM: > From: "DAN MUELLER" <ddmueller@intercall.com> > To: ids@iiug.org, > Date: 06/17/2013 06:56 AM > Subject: Upd Stat on SP using PDQ Question [30547] > Sent by: ids-bounces@iiug.org > > Good MOrning All, > > AIX 6.1 > IDS 11.50.FC7 > > Over the weekend, we performed some DB maint in which we ran upd stats on all > tables then SPs using PDQPRIORITY of 20. After turning everything loose, all > SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance went > out the door. > > I know this was most likely covered in a perf & tuning class somewhere way > back but kinda hard to find in the actual doc. My question is did the > execution of upd stats on the SPs with PDQPRIORITY set to 20 force that? > > Thanx, > Dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanx to all who responded. I appreciate the info and the pointer to the doc section. Dan
Hi Dan,
When you use UPDATE STATISTICS with PDQPRIORITY set , it will speed up
the creation of your statistics. However, when you do that make sure you
separate your tables from your stored procedures. You can set
PDQPRIORITY when you run UPDATE STATISTICS FOR TABLE for your tables and
set PDQPRIORITY to 0 when you run UPDATE STATISTICS FOR PROCEDURE.
If you set PDQPRIORITY to a positive number, all of the SQL code in the
stored procedures will be compiled with that PDQPRIORITY. Then , when
you run your stored procedures, you will run into blocked situations for
some users that you can see when you run onstat -g mgm. If the
ressources (Max number of queries, etc) managed by the MGM are
saturated, you will see in the READY Query the list all of the sessions
that blocked and waiting for the ressource to get free.
Bottom line, PDQPRIORITY set to a number greater than 0 from UPDATE
STATISTICS FOR TABLE and PDQPRIORITY set to 0 for procedures.
Hope that helps.
Cordialement, Regards,
Khaled Bentebal
Directeur Général - ConsultiX
Président UGIF - User Group Informix France
IIUG - Board of Directors
Tél: 33 (0) 1 39 12 18 00
Fax: 33 (0) 1 39 12 18 18
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 17/06/13 15:44, DAN MUELLER a écrit :
> Good MOrning All,
>
> AIX 6.1
> IDS 11.50.FC7
>
> Over the weekend, we performed some DB maint in which we ran upd stats on all
> tables then SPs using PDQPRIORITY of 20. After turning everything loose, all
> SPs started running at PDQPRIORITY of 20 flooding the MGM. Performance went
> out the door.
>
> I know this was most likely covered in a perf& tuning class somewhere way
> back but kinda hard to find in the actual doc. My question is did the
> execution of upd stats on the SPs with PDQPRIORITY set to 20 force that?
>
> Thanx,
> Dan
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>