doubt about parameters (PDQ)
Question: how to turn PDQ on/off from an application. Answer: it's a session-level setting — issue SET PDQPRIORITY HIGH|LOW|0-100 (or the environment variable) within the SQL; it can no longer be defaulted in the ONCONFIG. A follow-up clarified that MAX_PDQPRIORITY in ONCONFIG acts as a multiplicative throttle (e.g. MAX 50 with PDQPRIORITY 100 gives 50%) and has no effect on non-PDQ queries. High PDQPRIORITY helps complex joins, GROUP BY/ORDER BY, UNIONs and complex UPDATE/DELETE, but not simple or non-fragmented single-table queries or inserts. For vetting risky ad-hoc queries, SET EXPLAIN ON AVOID EXECUTE (or the {+EXPLAIN AVOID EXECUTE} directive) was suggested, plus a mention of the I-SPY product.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I need to know how to turn on and off the PDQ via application thank you very much Wanderlei - Intera Consultoria
SET PDQPRIORITY [HIGH|LOW|<value from 0-100>];
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 Tue, May 8, 2012 at 12:39 PM, wanderlei.barboza <
wanderlei.barboza@interaconsultoria.com.br> wrote:
> I need to know how to turn on and off the PDQ via application
>
> thank you very much
>
> Wanderlei - Intera Consultoria
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340c01e8b8cf04bf891b94
Oi Wanderlei, basta setar a cláusula 'SET PDQPRIORITY [valor]', dentro de qualquer conjunto de instruções sql. Att. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional BRIUG website administrator Database Administrator > To: ids@iiug.org > From: wanderlei.barboza@interaconsultoria.com.br > Subject: doubt about parameters (PDQ) [27054] > Date: Tue, 8 May 2012 12:39:55 -0400 > > I need to know how to turn on and off the PDQ via application > > thank you very much > > Wanderlei - Intera Consultoria > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Art, if you set that parameter to 100 in the onconfig file, but only do inserts, updates and deletes, would there be a performance penalty even when no queries are being executed?.. I would think that leaving it at 100 would be more advantageous for any queries, especially complex queries?
First, PDQPRIORITY is a session level environment variable or SET command, so you can't set it in the ONCONFIG file (prior to 7.31/9.21 you could set it as a default for all users, but that was deprecated long ago). That aside, setting PDQPRIORITY 100 in a session has no negative effect on the performance of any query/insert/update/delete within that session and only a minor one on non-PDQ queries run by other sessions with PDQPRIORITY set to zero. Other positive PDQPRIORITY sessions are another story. That said, it does not help simple queries against single non-fragmented tables. But it will help SELECTs that include multiple table complex filter and join conditions, GROUP BY, HAVING, and ORDER BY clauses, UNIONs (and possibly IN and multiple OR condition queries that can be reworked into a UNION/UNION ALL by the optimizer) and even UPDATE and DELETE statements that use complex queries in their WHERE clauses. INSERT statements are mostly unaffected either way. 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 Tue, May 8, 2012 at 5:56 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Art, if you set that parameter to 100 in the onconfig file, but only do > inserts, updates and deletes, would there be a performance penalty even > when > no queries are being executed?.. I would think that leaving it at 100 > would be > more advantageous for any queries, especially complex queries? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba959b3596304bf8df8ef
Sorry people, I meant the MAX_PDQPRIORITY 100 onconfig parameter.
Ahh! That acts as a multiplicative throttle on PDQPRIORITY. If you set
MAX_PDQPRIORITY to 50 then the most PDQ resources anyone can get is 50% if
they set PDQPRIORITY to 100. It's multiplicative so if a user sets
PDQPRIORITY to 50 they get the equivalent of PDQPRIORITY 25 with
MAX_PDQPRIORITY 100.
By itself it has no effect and never affects non-PDQ queries.
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 Tue, May 8, 2012 at 6:38 PM, FRANK J. COMPUTER
<frank_in_pr@hotmail.com>wrote:
> Sorry people, I meant the MAX_PDQPRIORITY 100 onconfig parameter.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340f6ff1eb9b04bf8e3ed1
I can see where PDQ would greatly benefit DSS or OLTP applications in executing many simultaneous or long-running queries, or complex queries with many joins. However, I feel that ad-hoc queries should be submitted to the Information Center, analyzed, classified and scheduled before being allowed to execute. It's very easy to bring a server to its knees by executing, example: a multi-table query which return a Cartesian Product, etc. I would think there'd be some utilities out there that can analyze the query optimizers costs, impact on critical production tables, etc. before the queries are executed and have the ability to prevent and re-direct queries for analysis?
There is, it is a version of the SET EXPLAIN ON command:
SET EXPLAIN ON AVOID EXECUTE;
SELECT ....;
You can do that as an optimizer directive also:
SELECT {+ EXPLAIN AVOID EXECUTE} ....;
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 Tue, May 8, 2012 at 7:38 PM, FRANK J. COMPUTER
<frank_in_pr@hotmail.com>wrote:
> I can see where PDQ would greatly benefit DSS or OLTP applications in
> executing many simultaneous or long-running queries, or complex queries
> with
> many joins. However, I feel that ad-hoc queries should be submitted to the
> Information Center, analyzed, classified and scheduled before being
> allowed to
> execute. It's very easy to bring a server to its knees by executing,
> example:
> a multi-table query which return a Cartesian Product, etc. I would think
> there'd be some utilities out there that can analyze the query optimizers
> costs, impact on critical production tables, etc. before the queries are
> executed and have the ability to prevent and re-direct queries for
> analysis?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3ba9594049da04bf8f5a27
There is a product called I-SPY that had some functionality that could cover those needs. But I'd prefer: 1- A switch to prevent cartesian products (never saw one that was made on purpose). Even if it was a session option. 2- session resource management... Regards On Wed, May 9, 2012 at 12:38 AM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > I can see where PDQ would greatly benefit DSS or OLTP applications in > executing many simultaneous or long-running queries, or complex queries > with > many joins. However, I feel that ad-hoc queries should be submitted to the > Information Center, analyzed, classified and scheduled before being > allowed to > execute. It's very easy to bring a server to its knees by executing, > example: > a multi-table query which return a Cartesian Product, etc. I would think > there'd be some utilities out there that can analyze the query optimizers > costs, impact on critical production tables, etc. before the queries are > executed and have the ability to prevent and re-direct queries for > analysis? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf306f7710214c9504bf8ffca5