Neoview Workload Management Services Guide (R2.3)

Displaying the Query Explain Plan
Use the STATUS QUERY <query-id> PLAN command to display the execution plan of a specific
query. For the STATUS QUERY <query-id> PLAN command to succeed, the output option,
PLAN, must be enabled for the service. If the default NO_PLAN is specified for the service, the
STATUS QUERY <query-id> PLAN command does not return a query execution plan.
For example, this STATUS QUERY <query-id> PLAN command displays the query execution
plan for a specified query ID:
NS%status query MXID01002010476212075562920218543000000004004DBA100_234_S1 plan;
QUERY_PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
18 . 19 root 1.00E+000
17 . 18 sort_parti
al_aggr_ro 1.00E+000
16 . 17 esp_exchange 1:4(hash2) 1.00E+000
15 . 16 sort_partial_aggr_le 1.00E+000
14 3 15 hybrid_hash_join 5.92E+008
13 6 14 hybrid_hash_joi
n 3.79E+006
12 9 13 hybrid_hash_join 2.43E+004
11 . 12 esp_exchange 4(hash2):1 1.56E+002
10 . 11 partition_access 1.56E+002
. . 10 file_scan fs fr COLS 1.56E+00
2
8 . 9 esp_exchange 4(rep-b):1 1.56E+002
7 . 8 partition_access 1.56E+002
. . 7 file_scan fs fr COLS 1.56E+002
5 . 6 esp_exchange 4(rep-b):1 1.56E+002
4 . 5 pa
rtition_access 1.56E+002
. . 4 file_scan fs fr COLS 1.56E+002
2 . 3 esp_exchange 4(rep-b):1 1.56E+002
1 . 2 partition_access 1.56E+002
. . 1 file_scan fs fr COL
S 1.56E+002
--- WMS operation complete.
NS%
For the syntax, see the “STATUS Command” (page 66).
To interpret explain plans, see the Neoview Query Guide and the Neoview Query Support Guide.
Displaying the Query SQL Text
Use the STATUS QUERY <query-id> TEXT command to display the SQL text of a specific query.
For the STATUS QUERY <query-id> TEXT command to succeed, the output option, TEXT, must
be enabled for the service. If NO_TEXT, the default, is specified for the service, the STATUS
QUERY <query-id> TEXT command fails to return SQL text for a query.
For example, this STATUS QUERY <query-id> TEXT command displays the SQL text for a
specified query ID:
NS%status query MXID01001000483212066839774858733000000285214SUPER.SERVICES00_13391_S1 text;
QUERY_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
select od.ordernum, sum(qty_ordered * price)
from sales.parts p, sales.odetail od
where od.partnum = p.partnum and od.ordernum in
(select o.ordernum
from sales.orders o, sales.customer c
where o.custnum = c.custnum and state = 'CALIFORNIA')
group by od.ordernum;
--- WMS operation complete.
NS%
For the syntax, see the “STATUS Command” (page 66).
50 Managing Workload in WMS