View Query Plans and Statistics

After you have captured one or more query plans, you can view the contents of an individual plan from a peer client connection.

The contents of SQLFire query plans are similar to those found in Apache Derby. See SQLFire Query Execution Plan Codes and Derby Tuning in the Apache Derby documentation for more information about interpreting query plans.

Procedure
  1. Establish a peer client connection to the SQLFire cluster. For example:
    sqlf
    connect peer 'host-data=false;mcast-port=12777';
    Note: This release of SQLFire does not support using the EXPLAIN command from a thin client connection. Use a peer client connection instead, as shown in this example command.
  2. Query the sys.statementplans table to obtain a list of all plans that were captured. For example:
    select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
    STMT_ID                             |STMT_TEXT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1ec14167-012f-b6ad-8c1e-00007dce8640|select user0_.username as username0_, user0_.name as name0_, user0_.password as password0_ from Customer user0_ where user0_.us&
    d884816c-012f-b6ad-8c1e-00007dce8640|update Booking set beds=?, checkinDate=?, checkoutDate=?, creditCard=?, creditCardExpiryMonth=?, creditCardExpiryYear=?, credit&
    15b58164-012f-b6ad-8c1e-00007dce8640|select hotel0_.id as id2_0_, hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country as country2_0_, hotel0_.&
    dd5cc163-012f-b6ad-8c1e-00007dce8640|select hotel0_.id as id2_0_, hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country as country2_0_, hotel0_.&
    87cf416a-012f-b6ad-8c1e-00007dce8640|insert into Booking (beds, checkinDate, checkoutDate, creditCardExpiryMonth, creditCardExpiryYear, hotel_id, smoking, user_user&
    00000000-ffff-ffff-ffff-000000000357|select booking0_.id as id1_, booking0_.beds as beds1_, booking0_.checkinDate as checkinD3_1_, booking0_.checkoutDate as checkou&
    eaa841c2-012f-b6ad-5103-00003dcdc000|select hotel0_.id as id2_0_, hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country as country2_0_, hotel0_.&
    e23801c1-012f-b6ad-5103-00003dcdc000|select user0_.username as username0_0_, user0_.name as name0_0_, user0_.password as password0_0_ from Customer user0_ where use&
    c15841bf-012f-b6ad-5103-00003dcdc000|select hotel0_.id as id2_0_, hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country as country2_0_, hotel0_.&
    00000000-ffff-ffff-ffff-0005000002e2|select hotel0_.id as id2_, hotel0_.address as address2_, hotel0_.city as city2_, hotel0_.country as country2_, hotel0_.name as &
    
    10 rows selected
    
  3. Use the statement text to determine the STMT_ID of the query plan you want to view. Then display the plan contents with the explain stmt_id command. For example:
    explain '00000000-ffff-ffff-ffff-0005000002e2';
    member   vmc-ssrc-rh156(17697)<v1>:31918/53818 begin_execution   end_execution
    QUERY-RECEIVE execute_time 2208000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-SEND execute_time 1980000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 696000
    member   vmc-ssrc-rh156(17696)<v2>:34240/56045 begin_execution   end_execution
    QUERY-RECEIVE execute_time 2570000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-SEND execute_time 1634000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 688000
    member   vmc-ssrc-rh154(21955)<v4>:1227/34442 begin_execution   end_execution
    QUERY-SCATTER execute_time 141898000 member_node
    QUERY-SEND execute_time 1446000 member_node vmc-ssrc-rh156(17696)<v2>:34240/56045
    QUERY-SEND execute_time 979000 member_node vmc-ssrc-rh154(21956)<v3>:38066/55811
    QUERY-SEND execute_time 1306000 member_node vmc-ssrc-rh156(17697)<v1>:31918/53818
    QUERY-SEND execute_time 785000 member_node vmc-ssrc-rh154(21834)<v0>:27784/52902
    QUERY-SEND execute_time 130487000 member_node
    RESULT-RECEIVE execute_time 948000 member_node vmc-ssrc-rh154(21956)<v3>:38066/55811
    RESULT-RECEIVE execute_time 369000 member_node vmc-ssrc-rh156(17696)<v2>:34240/56045
    RESULT-RECEIVE execute_time 328000 member_node vmc-ssrc-rh154(21834)<v0>:27784/52902
    RESULT-RECEIVE execute_time 647000 member_node vmc-ssrc-rh156(17697)<v1>:31918/53818
    RESULT-RECEIVE execute_time 27000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    SEQUENTIAL-ITERATION returned_rows 20 no_opens 1
    DISTRIBUTION-END returned_rows 20
    member   vmc-ssrc-rh154(21834)<v0>:27784/52902 begin_execution   end_execution
    QUERY-RECEIVE execute_time 2525000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-SEND execute_time 1622000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-HOLDER returned_rows 3 no_opens 1 execute_time 830000
    member   vmc-ssrc-rh154(21956)<v3>:38066/55811 begin_execution   end_execution
    QUERY-RECEIVE execute_time 1831000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-SEND execute_time 1334000 member_node vmc-ssrc-rh154(21955)<v4>:1227/34442
    RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 423000