Re: How to estimate query cost before execution
LonelyWolf wanted a cheap way to estimate query cost before execution, as a gatekeeper for a multiuser system. LIGHT SCANS suggested onmode -y and the sysmaster sysrstcb and syssqexplain tables. Fernando Nunes described EXPLAIN_SQL in 11.50, or SET EXPLAIN with AVOID_EXECUTE on 11.10. wcottishpoet suggested PDQ limits. No complete solution was found.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello LonelyWolf,
SGBoyUK is right. If your SQL is already running, try "onmode -y
<sid> 1 output.filename" where "sid" is the session ID of the SQL.
And, yes, there is some information in the sysmaster "sysrstcb" and
"syssqexplain" tables (join them with "sysrstcb.sid =
syssqexplain.sqx_sessionid").
-L.S.
On 9 syys, 18:24, LIGHT SCANS <light_sc...@yahoo.com> wrote:
> Hello LonelyWolf,
>
> SGBoyUK is right. If your SQL is already running, try "onmode -y
> <sid> 1 output.filename" where "sid" is the session ID of the SQL.
>
> And, yes, there is some information in the sysmaster "sysrstcb" and
> "syssqexplain" tables (join them with "sysrstcb.sid =
> syssqexplain.sqx_sessionid").
>
> -L.S.
Thank you for your answers!
Yes I'm familiar with SET EXPLAIN ON AVOID_EXECUTE but it is not
possible to build "cost-based" control automatic around it. So far as
I understand it's more research tool and is not suitable controlling
multiuser environment and making decisions if some query is accepted
or not. Theoritically it's possible to get the output file back to
calling process with some filetoclob- or load-command but overhead
doing it is too big. Is there any better options for this kind of
"gate-keeper" problem ?
LonelyWolf wrote:
> On 9 syys, 18:24, LIGHT SCANS <light_sc...@yahoo.com> wrote:
>> Hello LonelyWolf,
>>
>> SGBoyUK is right. If your SQL is already running, try "onmode -y
>> <sid> 1 output.filename" where "sid" is the session ID of the SQL.
>>
>> And, yes, there is some information in the sysmaster "sysrstcb" and
>> "syssqexplain" tables (join them with "sysrstcb.sid =
>> syssqexplain.sqx_sessionid").
>>
>> -L.S.
>
> Thank you for your answers!
> Yes I'm familiar with SET EXPLAIN ON AVOID_EXECUTE but it is not
> possible to build "cost-based" control automatic around it. So far as
> I understand it's more research tool and is not suitable controlling
> multiuser environment and making decisions if some query is accepted
> or not. Theoritically it's possible to get the output file back to
> calling process with some filetoclob- or load-command but overhead
> doing it is too big. Is there any better options for this kind of
> "gate-keeper" problem ?
I'm not sure if I understand you completely. Version 11.50 has a
functionality which could help. It has what IBM calls Visual Explain. To
be clear, the Visual Explain is a functionality that some tools may
provide (data studio does it, and I believe tools like AGS do it, or
will do it in a near future). This functionality is based around a new
procedure called EXPLAIN_SQL. This routine receives some obscure
parameters and returns an xml with the query plan and estimated costs.
If you were using version 11.50 (and I mention this because you may have
plans to do it), you could create some kind of service that receive the
query and uses this to decide if it should be run or not...
With version 11.10, it can be harder... Some ideas:
- Use the set explain file feature and point to a remote filesystem
mounted on the database server. Also use the AVOID_EXECUTE You need to
parse the file...
- I haven't tested if the syssqexplain contains the query if you use
avoid_execute...
Regards,
-
Turn PDQ on You can then limit the resources available to PDQ queries and even I think limit the number of PDQ queries that can run. This will mean that other queries and still run and your slow, complex queries will take even longer. I say bring back SCAFS!