[ 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 <anoop.sha...@esgyn.com> 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)