[
https://issues.apache.org/jira/browse/TRAFODION-2683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16082797#comment-16082797
]
ASF GitHub Bot commented on TRAFODION-2683:
-------------------------------------------
GitHub user anoopsharma00 opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1173
TRAFODION-2683 add a new explain option to mask variant fields in output
--Syntax extension to cleanse and mask:
Full explain:
explain options 'c' select ...
Formatted explain:
explain options 'fc' select...
Full explain on prepared statement:
prepare s from select ...
explain option 'c' s;
--Masked fields will show up as "###" in explain output.
--Both option and options keyword can be used in explain and showplan.
-- explain option/options ...
-- showplan option/options ...
-- showplan can be used on explain statement.
-- Here is an example of what the output will look like:
>>explain option 'c' select * from dual;
----------------------------------------------- PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID ................ ###
ROWS_OUT ............... ###
EST_TOTAL_COST ......... ###
STATEMENT ................ select * from dual;
------------------------------------------- NODE LISTING
ROOT ================================ SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ....... ###
max_max_cardinality ###
total_overflow_size ###
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
select_list ............ %(0)
input_variables ........ %(0), %(0), %(0)
VALUES ======================== SEQ_NO 1 NO CHILDREN
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
tuple_expr ............. %(0)
--- SQL operation complete.
>>
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/anoopsharma00/incubator-trafodion
ansharma_explain_br
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1173.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1173
----
commit 2a520b5e75ebfb87672ddd2b1cf100062fad64c2
Author: Anoop Sharma <[email protected]>
Date: 2017-07-11T19:09:53Z
TRAFODION-2683 add a new explain option to mask variant fields in output
--Syntax extension to cleanse and mask:
explain options 'c' select ...
explain options 'fc' select...
prepare s from select ...
explain option 'c' s;
--Masked fields will show up as "###" in explain output.
--Both option and options keyword can be used in explain and showplan.
-- explain option/options ...
-- showplan option/options ...
-- showplan can be used on explain statement.
-- Here is an example of what the output will look like:
>>explain option 'c' select * from dual;
----------------------------------------------- PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID ................ ###
ROWS_OUT ............... ###
EST_TOTAL_COST ......... ###
STATEMENT ................ select * from dual;
------------------------------------------- NODE LISTING
ROOT ================================ SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ....... ###
max_max_cardinality ###
total_overflow_size ###
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
select_list ............ %(0)
input_variables ........ %(0), %(0), %(0)
VALUES ======================== SEQ_NO 1 NO CHILDREN
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
max_card_est ......... ###
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
tuple_expr ............. %(0)
--- SQL operation complete.
>>
----
> add a new explain option to mask variant fields in output
> ---------------------------------------------------------
>
> Key: TRAFODION-2683
> URL: https://issues.apache.org/jira/browse/TRAFODION-2683
> Project: Apache Trafodion
> Issue Type: Improvement
> Reporter: Anoop Sharma
> Assignee: Anoop Sharma
> Priority: Minor
>
> Full explain returns detailed information about a query plan which is very
> useful in
> determining if the chosen plan is as expected and other details about it.
> But returned output contains information that may vary from run to run, or
> from one
> system to another, or may be data dependent, and so on.
> Some examples are UIDs, or cost/memory estimates, or number of cpus/nodes,
> etc.
> Currently these variations are handled by checking in the new expected files
> (bad idea),
> or creating known diff files, or adding filters, or selecting specific fields
> from explain virtual
> table, or just not using explain.
> This jira proposes a new option which when specified, will filter and mask
> fields that
> are variant and change.
> The option is called 'c' (cleanse) and could be specified as part
> of explain "options '<str>' " clause. It works with both full explain and
> formatted explain.
>
> This option is useful when running regressions where one need to validate
> enhancements
> and other changes that do not involve costing/estimates/dop etc related
> values.
> This option should not be used when those values are important.
> For ex, if a scan is expected to return 100 rows and explain need to show
> that, or
> if a query should use 4 esps, then using the 'c' option will mask it.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
