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 [email protected] [firebird-support]"
<[email protected]>
Para: [email protected]
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 [email protected]
[firebird-support] <[email protected]> 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: "[email protected] [firebird-support]"
<[email protected]>
Para: [email protected]
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 #yiv5385826863ads
{margin-bottom:10px;}#yiv5385826863 #yiv5385826863ygrp-mkp .yiv5385826863ad
{padding:0 0;}#yiv5385826863 #yiv5385826863ygrp-mkp .yiv5385826863ad p
{margin:0;}#yiv5385826863 #yiv5385826863ygrp-mkp .yiv5385826863ad a
{color:#0000ff;text-decoration:none;}#yiv5385826863 #yiv5385826863ygrp-sponsor
#yiv5385826863ygrp-lc {font-family:Arial;}#yiv5385826863
#yiv5385826863ygrp-sponsor #yiv5385826863ygrp-lc #yiv5385826863hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5385826863
#yiv5385826863ygrp-sponsor #yiv5385826863ygrp-lc .yiv5385826863ad
{margin-bottom:10px;padding:0 0;}#yiv5385826863 #yiv5385826863actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5385826863
#yiv5385826863activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5385826863
#yiv5385826863activity span {font-weight:700;}#yiv5385826863
#yiv5385826863activity span:first-child
{text-transform:uppercase;}#yiv5385826863 #yiv5385826863activity span a
{color:#5085b6;text-decoration:none;}#yiv5385826863 #yiv5385826863activity span
span {color:#ff7900;}#yiv5385826863 #yiv5385826863activity span
.yiv5385826863underline {text-decoration:underline;}#yiv5385826863
.yiv5385826863attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv5385826863 .yiv5385826863attach div a
{text-decoration:none;}#yiv5385826863 .yiv5385826863attach img
{border:none;padding-right:5px;}#yiv5385826863 .yiv5385826863attach label
{display:block;margin-bottom:5px;}#yiv5385826863 .yiv5385826863attach label a
{text-decoration:none;}#yiv5385826863 blockquote {margin:0 0 0
4px;}#yiv5385826863 .yiv5385826863bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv5385826863
.yiv5385826863bold a {text-decoration:none;}#yiv5385826863 dd.yiv5385826863last
p a {font-family:Verdana;font-weight:700;}#yiv5385826863 dd.yiv5385826863last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5385826863
dd.yiv5385826863last p span.yiv5385826863yshortcuts
{margin-right:0;}#yiv5385826863 div.yiv5385826863attach-table div div a
{text-decoration:none;}#yiv5385826863 div.yiv5385826863attach-table
{width:400px;}#yiv5385826863 div.yiv5385826863file-title a, #yiv5385826863
div.yiv5385826863file-title a:active, #yiv5385826863
div.yiv5385826863file-title a:hover, #yiv5385826863 div.yiv5385826863file-title
a:visited {text-decoration:none;}#yiv5385826863 div.yiv5385826863photo-title a,
#yiv5385826863 div.yiv5385826863photo-title a:active, #yiv5385826863
div.yiv5385826863photo-title a:hover, #yiv5385826863
div.yiv5385826863photo-title a:visited {text-decoration:none;}#yiv5385826863
div#yiv5385826863ygrp-mlmsg #yiv5385826863ygrp-msg p a
span.yiv5385826863yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5385826863
.yiv5385826863green {color:#628c2a;}#yiv5385826863 .yiv5385826863MsoNormal
{margin:0 0 0 0;}#yiv5385826863 o {font-size:0;}#yiv5385826863
#yiv5385826863photos div {float:left;width:72px;}#yiv5385826863
#yiv5385826863photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv5385826863
#yiv5385826863photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5385826863
#yiv5385826863reco-category {font-size:77%;}#yiv5385826863
#yiv5385826863reco-desc {font-size:77%;}#yiv5385826863 .yiv5385826863replbq
{margin:4px;}#yiv5385826863 #yiv5385826863ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv5385826863 #yiv5385826863ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5385826863
#yiv5385826863ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5385826863
#yiv5385826863ygrp-mlmsg select, #yiv5385826863 input, #yiv5385826863 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv5385826863
#yiv5385826863ygrp-mlmsg pre, #yiv5385826863 code {font:115%
monospace;}#yiv5385826863 #yiv5385826863ygrp-mlmsg *
{line-height:1.22em;}#yiv5385826863 #yiv5385826863ygrp-mlmsg #yiv5385826863logo
{padding-bottom:10px;}#yiv5385826863 #yiv5385826863ygrp-msg p a
{font-family:Verdana;}#yiv5385826863 #yiv5385826863ygrp-msg
p#yiv5385826863attach-count span {color:#1E66AE;font-weight:700;}#yiv5385826863
#yiv5385826863ygrp-reco #yiv5385826863reco-head
{color:#ff7900;font-weight:700;}#yiv5385826863 #yiv5385826863ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv5385826863 #yiv5385826863ygrp-sponsor
#yiv5385826863ov li a {font-size:130%;text-decoration:none;}#yiv5385826863
#yiv5385826863ygrp-sponsor #yiv5385826863ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv5385826863
#yiv5385826863ygrp-sponsor #yiv5385826863ov ul {margin:0;padding:0 0 0
8px;}#yiv5385826863 #yiv5385826863ygrp-text
{font-family:Georgia;}#yiv5385826863 #yiv5385826863ygrp-text p {margin:0 0 1em
0;}#yiv5385826863 #yiv5385826863ygrp-text tt {font-size:120%;}#yiv5385826863
#yiv5385826863ygrp-vital ul li:last-child {border-right:none
!important;}#yiv5385826863
