[firebird-support] Stored Procedures Execution Plan
Hi. I would like to know if it is possible to display the plan of stored procedure statements. I'm interested in the plan of the statements inside the stored procedure (SP), not the plan just informing that I'm executing an SP. We are running also selectable SP's. Att. João Paulo
Re: [firebird-support] Stored Procedures Execution Plan
Hi.
Since this was my first post in this list, I'm not sure if it was sent.
If you already get it, or this is not the right place to discuss this kind of
topic, I would be very grateful if you tell me where is.
Att.João Paulo
DE: "joao_paul...@yahoo.com [firebird-support]"
Para: firebird-support@yahoogroups.com
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan
Hi.
I would like to know if it is possible to display the plan of stored procedure
statements.
I'm interested in the plan of the statements inside the stored procedure (SP),
not the plan just informing that I'm executing an SP.
We are running also selectable SP's.
Att.João Paulo
#yiv1927926194 #yiv1927926194 -- #yiv1927926194ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1927926194
#yiv1927926194ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1927926194
#yiv1927926194ygrp-mkp #yiv1927926194hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1927926194 #yiv1927926194ygrp-mkp #yiv1927926194ads
{margin-bottom:10px;}#yiv1927926194 #yiv1927926194ygrp-mkp .yiv1927926194ad
{padding:0 0;}#yiv1927926194 #yiv1927926194ygrp-mkp .yiv1927926194ad p
{margin:0;}#yiv1927926194 #yiv1927926194ygrp-mkp .yiv1927926194ad a
{color:#ff;text-decoration:none;}#yiv1927926194 #yiv1927926194ygrp-sponsor
#yiv1927926194ygrp-lc {font-family:Arial;}#yiv1927926194
#yiv1927926194ygrp-sponsor #yiv1927926194ygrp-lc #yiv1927926194hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1927926194
#yiv1927926194ygrp-sponsor #yiv1927926194ygrp-lc .yiv1927926194ad
{margin-bottom:10px;padding:0 0;}#yiv1927926194 #yiv1927926194actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1927926194
#yiv1927926194activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1927926194
#yiv1927926194activity span {font-weight:700;}#yiv1927926194
#yiv1927926194activity span:first-child
{text-transform:uppercase;}#yiv1927926194 #yiv1927926194activity span a
{color:#5085b6;text-decoration:none;}#yiv1927926194 #yiv1927926194activity span
span {color:#ff7900;}#yiv1927926194 #yiv1927926194activity span
.yiv1927926194underline {text-decoration:underline;}#yiv1927926194
.yiv1927926194attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1927926194 .yiv1927926194attach div a
{text-decoration:none;}#yiv1927926194 .yiv1927926194attach img
{border:none;padding-right:5px;}#yiv1927926194 .yiv1927926194attach label
{display:block;margin-bottom:5px;}#yiv1927926194 .yiv1927926194attach label a
{text-decoration:none;}#yiv1927926194 blockquote {margin:0 0 0
4px;}#yiv1927926194 .yiv1927926194bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1927926194
.yiv1927926194bold a {text-decoration:none;}#yiv1927926194 dd.yiv1927926194last
p a {font-family:Verdana;font-weight:700;}#yiv1927926194 dd.yiv1927926194last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1927926194
dd.yiv1927926194last p span.yiv1927926194yshortcuts
{margin-right:0;}#yiv1927926194 div.yiv1927926194attach-table div div a
{text-decoration:none;}#yiv1927926194 div.yiv1927926194attach-table
{width:400px;}#yiv1927926194 div.yiv1927926194file-title a, #yiv1927926194
div.yiv1927926194file-title a:active, #yiv1927926194
div.yiv1927926194file-title a:hover, #yiv1927926194 div.yiv1927926194file-title
a:visited {text-decoration:none;}#yiv1927926194 div.yiv1927926194photo-title a,
#yiv1927926194 div.yiv1927926194photo-title a:active, #yiv1927926194
div.yiv1927926194photo-title a:hover, #yiv1927926194
div.yiv1927926194photo-title a:visited {text-decoration:none;}#yiv1927926194
div#yiv1927926194ygrp-mlmsg #yiv1927926194ygrp-msg p a
span.yiv1927926194yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1927926194
.yiv1927926194green {color:#628c2a;}#yiv1927926194 .yiv1927926194MsoNormal
{margin:0 0 0 0;}#yiv1927926194 o {font-size:0;}#yiv1927926194
#yiv1927926194photos div {float:left;width:72px;}#yiv1927926194
#yiv1927926194photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv1927926194
#yiv1927926194photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1927926194
#yiv1927926194reco-category {font-size:77%;}#yiv1927926194
#yiv1927926194reco-desc {font-size:77%;}#yiv1927926194 .yiv1927926194replbq
{margin:4px;}#yiv1927926194 #yiv1927926194ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1927926194 #yiv1927926194ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1927926194
#yiv1927926194ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1927926194
#yiv1927926194ygrp-mlmsg select, #yiv1927926194 input, #yiv1927926194 textarea
{fo
Re: [firebird-support] Stored Procedures Execution Plan
It was sent and received... but this is working for me out of the box in FB 2.5.4. I just write the select * from SP and ask for plan and it gives me the plan for the inside statements. Haven't tested on non-selectable SPs. Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com [firebird-support] escreveu: > > > Hi. > > > Since this was my first post in this list, I'm not sure if it was sent. > > If you already get it, or this is not the right place to discuss this kind > of topic, I would be very grateful if you tell me where is. > > Att. > João Paulo > > > -- > *DE:* "joao_paul...@yahoo.com [firebird-support]" < > firebird-support@yahoogroups.com> > *Para:* firebird-support@yahoogroups.com > *Enviadas:* Sexta-feira, 20, Maio 2016 15:54:07 > *Assunto:* [firebird-support] Stored Procedures Execution Plan > > > > Hi. > > > I would like to know if it is possible to display the plan of stored > procedure statements. > > I'm interested in the plan of the statements inside the stored procedure > (SP), not the plan just informing that I'm executing an SP. > > We are running also selectable SP's. > > Att. > João Paulo > > > > >
Re: [firebird-support] Stored Procedures Execution Plan
Hi.
I've been trying to skim over some possibilities to investigate possible
limitations. Here it follow my results and test cases:
- If you call Stored Procedures (SP's) with EXECUTE PROCEDURE statements, plans
are not available;- If you call SP's via SELECT FROM, you got expected
statement plans, if this SP in turn DOESN'T call any other SP;- If you call
SP's via SELECT FROM, you got partially expected statement plans, if this SP in
turn CALL any other SP. In this case you won't get called SP plans, and if you
exec called SP's with EXECUTE PROCEDURE, even the execution of called SP's will
be omitted;
This is the test case script I used (executed with FlameRobin):
--
-- CREATING OBJECTS --RECREATE TABLE test (c1 INT, c2 INT, c3
INT);CREATE INDEX test_ix1 ON test (c1);CREATE INDEX test_ix2 ON test (c2);
SET TERM ^ ;RECREATE PROCEDURE test_01_selectable_sub_sp (c_cons INT)RETURNS
(c_ret INT)ASBEGIN SELECT c1 FROM test WHERE c1 = :c_cons INTO
:c_ret;
SELECT c1 FROM test WHERE c2 = :c_ret INTO :c_ret;
SELECT c1 FROM test WHERE c3 = :c_ret INTO :c_ret;
SUSPEND;END^SET TERM ; ^
SET TERM ^ ;RECREATE PROCEDURE test_03_selectable_sp (c_cons INT)RETURNS (c_ret
INT)ASBEGIN SELECT c_ret FROM test_01_selectable_sub_sp(:c_cons) INTO
:c_ret;
SELECT c_ret FROM test_01_selectable_sub_sp(:c_ret) INTO :c_ret;
SUSPEND;END^SET TERM ; ^
SET TERM ^ ;RECREATE PROCEDURE test_04_selectable_sp (c_cons INT)RETURNS (c_ret
INT)ASBEGIN EXECUTE PROCEDURE test_01_selectable_sub_sp(:c_cons)
RETURNING_VALUES (:c_ret);
EXECUTE PROCEDURE test_01_selectable_sub_sp(:c_ret) RETURNING_VALUES
(:c_ret); SUSPEND;END^SET TERM ; ^
-- -- EXECUTING SP'S --
EXECUTE PROCEDURE test_01_selectable_sub_sp(1);EXECUTE PROCEDURE
test_03_selectable_sp(1);EXECUTE PROCEDURE test_04_selectable_sp(1);-- Got:--
Plan not available.
SELECT * FROM test_01_selectable_sub_sp(1);-- Expected (something like):--
PLAN (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL) Got:--
PLAN (TEST INDEX (TEST_IX1))(TEST INDEX (TEST_IX2))(TEST NATURAL)
Analysis:-- Ok
SELECT * FROM test_03_selectable_sp(1);-- Expected (something like):-- PLAN
-- (test_01_selectable_sub_sp NATURAL)-- (test INDEX
(test_ix1))(test INDEX (test_ix2))(test NATURAL)--
(test_01_selectable_sub_sp NATURAL)-- (test INDEX
(test_ix1))(test INDEX (test_ix2))(test NATURAL) Got:-- PLAN
(TEST_01_SELECTABLE_SUB_SP NATURAL)(TEST_01_SELECTABLE_SUB_SP NATURAL)
Analysis:-- Missing called stored procedure plans
SELECT * FROM test_04_selectable_sp(1);-- Expected (something like):-- PLAN
-- (test_01_selectable_sub_sp NATURAL)-- (test INDEX
(test_ix1))(test INDEX (test_ix2))(test NATURAL)--
(test_01_selectable_sub_sp NATURAL)-- (test INDEX
(test_ix1))(test INDEX (test_ix2))(test NATURAL) Got:-- PLAN
(TEST_04_SELECTABLE_SP NATURAL) Analysis:-- Missing called stored
procedure plans and indication of called stored procedures execution
Att.João Paulo
DE: "Gabriel Frones grfro...@gmail.com [firebird-support]"
Para: firebird-support@yahoogroups.com
Enviadas: Terça-feira, 31, Maio 2016 14:39:56
Assunto: Re: [firebird-support] Stored Procedures Execution Plan
It was sent and received... but this is working for me out of the box in FB
2.5.4. I just write the select * from SP and ask for plan and it gives me the
plan for the inside statements. Haven't tested on non-selectable SPs.
Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com
[firebird-support] escreveu:
Hi.
Since this was my first post in this list, I'm not sure if it was sent.
If you already get it, or this is not the right place to discuss this kind of
topic, I would be very grateful if you tell me where is.
Att.João Paulo
DE: "joao_paul...@yahoo.com [firebird-support]"
Para: firebird-support@yahoogroups.com
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan
Hi.
I would like to know if it is possible to display the plan of stored procedure
statements.
I'm interested in the plan of the statements inside the stored procedure (SP),
not the plan just informing that I'm executing an SP.
We are running also selectable SP's.
Att.João Paulo
#yiv5385826863 #yiv5385826863 -- #yiv5385826863ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5385826863
#yiv5385826863ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5385826863
#yiv5385826863ygrp-mkp #yiv5385826863hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv5385826863 #yiv5385826863ygrp-mkp #yiv
Re: [firebird-support] Stored Procedures Execution Plan
Hi,
you must convert any procedure - for which you need plan - to execute block
format
if you are interested in plan for proc 1 then you need proc 1 execte block
if this proc1 use proc2 you need to make proc2 as execute block..
regards,
Karol Bieniaszewski
From: mailto:firebird-support@yahoogroups.com
Sent: Wednesday, June 1, 2016 2:27 PM
To: firebird-support@yahoogroups.com
Subject: Re: Re: [firebird-support] Stored Procedures Execution Plan
Hi,
One problem with this approach (changing SP's to execute block) is that you
still have the possibility of doesn't getting called SP plans (SP's called by
the SP which you are converting to an execute block).
Att.
João Paulo
--
DE: "liviuslivius liviusliv...@poczta.onet.pl [firebird-support]"
Para: "firebird-support@yahoogroups.com"
Enviadas: Quarta-feira, 1, Junho 2016 7:27:34
Assunto: Re: Re: [firebird-support] Stored Procedures Execution Plan
Hi,
yes but this was "wrong"
Consider this:
inside procedure you have 5 different statements
and you run query and only two of them are reached - do you really need to
see all 5?
Ok - now you got only "natural" word for procedure - i do not know if this is
better now..
But if you need plan for procedure - "change it" to execute block and you got
all statements plan
regards,
Karol Bieniaszewski
W dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones grfro...@gmail.com
[firebird-support] napisał:
It was sent and received... but this is working for me out of the box in FB
2.5.4. I just write the select * from SP and ask for plan and it gives me the
plan for the inside statements. Haven't tested on non-selectable SPs.
Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho
joao_paul...@yahoo.com [firebird-support]
escreveu:
Hi.
Since this was my first post in this list, I'm not sure if it was sent.
If you already get it, or this is not the right place to discuss this
kind of topic, I would be very grateful if you tell me where is.
Att.
João Paulo
DE: "joao_paul...@yahoo.com [firebird-support]"
Para: firebird-support@yahoogroups.com
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan
Hi.
I would like to know if it is possible to display the plan of stored
procedure statements.
I'm interested in the plan of the statements inside the stored
procedure (SP), not the plan just informing that I'm executing an SP.
We are running also selectable SP's.
Att.
João Paulo
Re: Re: [firebird-support] Stored Procedures Execution Plan
Hi, yes but this was "wrong" Consider this: inside procedure you have 5 different statements and you run query and only two of them are reached - do you really need to see all 5? Ok - now you got only "natural" word for procedure - i do not know if this is better now.. But if you need plan for procedure - "change it" to execute block and you got all statements plan regards, Karol Bieniaszewski W dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones grfro...@gmail.com [firebird-support] napisał: It was sent and received... but this is working for me out of the box in FB 2.5.4. I just write the select * from SP and ask for plan and it gives me the plan for the inside statements. Haven't tested on non-selectable SPs. Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com [firebird-support] escreveu: Hi. Since this was my first post in this list, I'm not sure if it was sent. If you already get it, or this is not the right place to discuss this kind of topic, I would be very grateful if you tell me where is. Att. João Paulo DE: "joao_paul...@yahoo.com [firebird-support]" Para: firebird-support@yahoogroups.com Enviadas: Sexta-feira, 20, Maio 2016 15:54:07 Assunto: [firebird-support] Stored Procedures Execution Plan Hi. I would like to know if it is possible to display the plan of stored procedure statements. I'm interested in the plan of the statements inside the stored procedure (SP), not the plan just informing that I'm executing an SP. We are running also selectable SP's. Att. João Paulo
Re: Re: [firebird-support] Stored Procedures Execution Plan
Hi,
One problem with this approach (changing SP's to execute block) is that you
still have the possibility of doesn't getting called SP plans (SP's called by
the SP which you are converting to an execute block).
Att.João Paulo
DE: "liviuslivius liviusliv...@poczta.onet.pl [firebird-support]"
Para: "firebird-support@yahoogroups.com"
Enviadas: Quarta-feira, 1, Junho 2016 7:27:34
Assunto: Re: Re: [firebird-support] Stored Procedures Execution Plan
Hi, yes but this was "wrong"Consider this:inside procedure you have 5
different statementsand you run query and only two of them are reached - do you
really need to see all 5? Ok - now you got only "natural" word for procedure -
i do not know if this is better now..But if you need plan for procedure -
"change it" to execute block and you got all statements plan regards,Karol
BieniaszewskiW dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones
grfro...@gmail.com [firebird-support]
napisał:
It was sent and received... but this is working for me out of the box in FB
2.5.4. I just write the select * from SP and ask for plan and it gives me the
plan for the inside statements. Haven't tested on non-selectable SPs. Em qua,
25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com
[firebird-support] escreveu:
Hi. Since this was my first post in this list, I'm not sure if it was sent.
If you already get it, or this is not the right place to discuss this kind of
topic, I would be very grateful if you tell me where is. Att.João Paulo
DE: "joao_paul...@yahoo.com [firebird-support]"
Para: firebird-support@yahoogroups.com
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan
Hi. I would like to know if it is possible to display the plan of stored
procedure statements. I'm interested in the plan of the statements inside the
stored procedure (SP), not the plan just informing that I'm executing an SP. We
are running also selectable SP's. Att. João Paulo
#yiv7257244709 #yiv7257244709 -- #yiv7257244709ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7257244709
#yiv7257244709ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7257244709
#yiv7257244709ygrp-mkp #yiv7257244709hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv7257244709 #yiv7257244709ygrp-mkp #yiv7257244709ads
{margin-bottom:10px;}#yiv7257244709 #yiv7257244709ygrp-mkp .yiv7257244709ad
{padding:0 0;}#yiv7257244709 #yiv7257244709ygrp-mkp .yiv7257244709ad p
{margin:0;}#yiv7257244709 #yiv7257244709ygrp-mkp .yiv7257244709ad a
{color:#ff;text-decoration:none;}#yiv7257244709 #yiv7257244709ygrp-sponsor
#yiv7257244709ygrp-lc {font-family:Arial;}#yiv7257244709
#yiv7257244709ygrp-sponsor #yiv7257244709ygrp-lc #yiv7257244709hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7257244709
#yiv7257244709ygrp-sponsor #yiv7257244709ygrp-lc .yiv7257244709ad
{margin-bottom:10px;padding:0 0;}#yiv7257244709 #yiv7257244709actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7257244709
#yiv7257244709activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7257244709
#yiv7257244709activity span {font-weight:700;}#yiv7257244709
#yiv7257244709activity span:first-child
{text-transform:uppercase;}#yiv7257244709 #yiv7257244709activity span a
{color:#5085b6;text-decoration:none;}#yiv7257244709 #yiv7257244709activity span
span {color:#ff7900;}#yiv7257244709 #yiv7257244709activity span
.yiv7257244709underline {text-decoration:underline;}#yiv7257244709
.yiv7257244709attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv7257244709 .yiv7257244709attach div a
{text-decoration:none;}#yiv7257244709 .yiv7257244709attach img
{border:none;padding-right:5px;}#yiv7257244709 .yiv7257244709attach label
{display:block;margin-bottom:5px;}#yiv7257244709 .yiv7257244709attach label a
{text-decoration:none;}#yiv7257244709 blockquote {margin:0 0 0
4px;}#yiv7257244709 .yiv7257244709bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7257244709
.yiv7257244709bold a {text-decoration:none;}#yiv7257244709 dd.yiv7257244709last
p a {font-family:Verdana;font-weight:700;}#yiv7257244709 dd.yiv7257244709last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7257244709
dd.yiv7257244709last p span.yiv7257244709yshortcuts
{margin-right:0;}#yiv7257244709 div.yiv7257244709attach-table div div a
{text-decoration:none;}#yiv7257244709 div.yiv7257244709attach-table
{width:400px;}#yiv7257244709 div.yiv7257244709file-title a, #yiv7257244709
div.yiv7257244709file-title a:active, #yiv7257244709
div.yiv7257244709file-title a:hover, #yiv7257244709 div.yiv7257244709file-title
a:visited {text-decoration:none;}
