You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Gunnar Tapper <ta...@gmail.com> on 2016/02/28 08:14:01 UTC
Documenting CQDs
Hi,
I'm looking at core/sql/sqlcomp/nadefaults.cpp.
Should SDDxxxx CQDs be documented or just XDDxxxx CQDs?
--
Thanks,
Gunnar
*If you think you can you can, if you think you can't you're right.*
RE: Documenting CQDs
Posted by Dave Birdsall <da...@esgyn.com>.
The MV* ones pertain to materialized views which is not production-ready at
this point. So those could be omitted.
-----Original Message-----
From: Gunnar Tapper [mailto:tapper.gunnar@gmail.com]
Sent: Sunday, February 28, 2016 9:21 PM
To: dev@trafodion.incubator.apache.org
Subject: Re: Documenting CQDs
Hi,
This leads me witht the following. WMS_* can be excluded. Any other one?
XDDkwd__(AUTO_QUERY_RETRY_WARNINGS, "OFF"),
XDDkwd__(BLOCK_TO_PREVENT_HALLOWEEN, "ON"),
XDDui___(CACHE_HISTOGRAMS_REFRESH_INTERVAL, "3600"), XDDansi_(CATALOG,
TRAFODION_SYSCAT_LIT),
XDDkwd__(COMPRESSION_TYPE, "NONE"),
XDDui1__(DEFAULT_DEGREE_OF_PARALLELISM, "2"),
XDDui1__(DEF_MAX_HISTORY_ROWS, "1024"),
XDDkwd__(DISPLAY_DIVISION_BY_COLUMNS, "OFF"),
XDDui___(EXE_MEMORY_LIMIT_PER_CPU, "0"),
XDDui___(EXPLAIN_DESCRIPTION_COLUMN_SIZE, "-1"),
// HASH_JOINS ON means do HASH_JOINS
XDDkwd__(HASH_JOINS, "ON"),
XDDkwd__(HIST_AUTO_GENERATION_OF_SAMPLE, "OFF"),
// This CQD is used to control the number of missing stats warnings
// that should be generated.
// 0 ? Display no warnings.
// 1 ? Display only missing single column stats warnings. These include
6008 and 6011
// 2 ? Display all single column missing stats warnings and
// multi-column missing stats warnings for Scans only.
// 3 ? Display all missing single column stats warnings and missing
// multi-column stats warnings for Scans and Join operators only..
// 4 ? Display all missing single column stats and missing multi-column
// stats warnings for all operators including Scans, Joins and
GroupBys.
// The CQD also does not have an impact on the auto update stats behavior.
The stats will
// still be automatically generated even if the warnings have been
suppressed.
// USTAT_AUTO_MISSING_STATS_LEVEL.
// Default behavior is to generate all warnings
XDDui___(HIST_MISSING_STATS_WARNING_LEVEL, "4"),
// This specifies the time interval after which the fake statistics
// should be refreshed. This was done primarirly for users
// which did not want to update statistics on temporary tables.
// If this statistics is cached, then this results in bad plans
// These users can have this default set to 0, in which case histograms
// with fake statistics will never be cached. Note that when ustat
// automation is on, this value divided by 360 is used.
XDDui___(HIST_NO_STATS_REFRESH_INTERVAL, "3600"),
XDDkwd__(HIST_PREFETCH, "ON"),
XDDkwd__(HIST_REMOVE_TRAILING_BLANKS, "ON"), // should remove
after verifying code is solid
XDDflt1_(HIST_ROWCOUNT_REQUIRING_STATS, "500"),
XDDkwd__(HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION , "ON"),
XDDkwd__(INSERT_VSBB, "SYSTEM"),
XDDkwd__(ISOLATION_LEVEL, "READ_COMMITTED"),
XDDkwd__(ISOLATION_LEVEL_FOR_UPDATES, "NONE"),
// Force the join order given by the user XDDkwd__(JOIN_ORDER_BY_USER,
"OFF"), XDDkwd__(LAST0_MODE, "OFF"),
// For old MCSB (that is, we guess MC skews from SC skews),
// apply the MC skew buster when
// SFa,b... * countOfPipeline > MC_SKEW_SENSITIVITY_THRESHOLD
// SFa,b ... is the skew factor for multi column a,b,...
//
XDDflt__(MC_SKEW_SENSITIVITY_THRESHOLD, "0.1"),
// controls the max. number of seek positions under which MDAM will be
// allowed. Set it to 0 turns off the feature.
XDDui___(MDAM_NO_STATS_POSITIONS_THRESHOLD, "10"),
// MDAM_SCAN_METHOD ON means MDAM is enabled,
// OFF means MDAM is disabled. MDAM is enabled by default
// externalized 06/21/01 RV
// mdam off on open source at this point
XDDkwd__(MDAM_SCAN_METHOD, "ON"),
// controls the max. number of probes at which MDAM under NJ plan will be
// generated. Set it to 0 turns off the feature.
XDDui___(MDAM_UNDER_NJ_PROBES_THRESHOLD, "0"),
// MERGE_JOINS ON means do MERGE_JOINS XDDkwd__(MERGE_JOINS, "ON"),
// Allow for the setting of the row count in a long running operation
XDDui1__(MULTI_COMMIT_SIZE, "10000"),
//--------------------------------------------------------------------------
//++ MV
XDDkwd__(MVGROUP_AUTOMATIC_CREATION, "ON"),
XDDkwd__(MVQR_REWRITE_ENABLED_OPTION, "OFF"), // @ZX -- change to ON
later
XDDui0_5(MVQR_REWRITE_LEVEL, "0"),
XDDkwd__(MVQR_REWRITE_SINGLE_TABLE_QUERIES, "ON"),
XDDMVA__(MV_AGE, ""),
XDDkwd__(MV_ALLOW_SELECT_SYSTEM_ADDED_COLUMNS, "OFF"),
// NESTED_JOINS ON means do NESTED_JOINS XDDkwd__(NESTED_JOINS, "ON"),
XDDui30_32000(NOT_ATOMIC_FAILURE_LIMIT, "32000"),
XDDui___(OPI_ERROR73_RETRIES, "10"),
XDDkwd__(OPTIMIZATION_LEVEL, "3"),
// If PARALLEL_NUM_ESPS is "SYSTEM",
// optimizer will compute the number of ESPs.
XDDui1__(PARALLEL_NUM_ESPS, "SYSTEM"),
// Partition OVerlay Support (POS) options
XDDpos__(POS_ABSOLUTE_MAX_TABLE_SIZE, ""),
XDDrlis_(PUBLISHING_ROLES, ""),
// query_cache max should be 200 MB. Set it 0 to turn off query cache
//XDD0_200000(QUERY_CACHE, "0"),
XDD0_200000(QUERY_CACHE, "16384"),
// multiplicative factor used to inflate cost of risky operators.
// = 1.0 means do not demand an insurance premium from risky operators.
// = 1.2 means demand a 20% insurance premium that cost of risky operators
// must overcome before they will be chosen over less-risky operators.
XDDflt0_(RISK_PREMIUM_NJ, "1.0"),
XDDflt0_(RISK_PREMIUM_SERIAL, "1.0"),
XDDui___(RISK_PREMIUM_SERIAL_SCALEBACK_MAXCARD_THRESHOLD, "10000"),
// external master CQD that sets following internal CQDs
// robust_query_optimization
// MINIMUM SYSTEM HIGH MAXIMUM
// risk_premium_NJ 1.0 system 2.5 5.0
// risk_premium_SERIAL 1.0 system 1.5 2.0
// partitioning_scheme_sharing 0 system 2 2
// robust_hj_to_nj_fudge_factor 0.0 system 3.0 1.0
// robust_sortgroupby 0 system 2 2
// risk_premium_MJ 1.0 system 1.5 2.0
// see optimizer/ControlDB.cpp ControlDB::doRobustQueryOptimizationCQDs
// for the actual cqds that set these values
XDDkwd__(ROBUST_QUERY_OPTIMIZATION, "SYSTEM"),
XDDkwd__(SAVE_DROPPED_TABLE_DDL, "OFF"),
XDDansi_(SCHEMA, "SEABASE"),
XDDkwd__(SKEW_EXPLAIN, "ON"),
XDDflt__(SKEW_ROWCOUNT_THRESHOLD, "1000000"), // Column row
count
// threshold
below
// which skew
// buster is
disabled.
XDDflt__(SKEW_SENSITIVITY_THRESHOLD, "0.1"),
// Timeout for a streaming cursor to return to the fetch(), even if no
// rows to return. The cursor is NOT closed, it just gives control to
// the user again.
// "0" means no timeout, just check instead.
// "negative" means never timeout.
// "positive" means the number of centiseconds to wait before timing out.
XDDint__(STREAM_TIMEOUT, "-1"),
XDDkwd__(SUBQUERY_UNNESTING, "ON"),
XDDkwd__(TABLELOCK, "SYSTEM"),
XDDintN2(TIMEOUT, "6000"),
// DTM Transaction Type: MVCC, SSCC
XDDkwd__(TRAF_TRANS_TYPE, "MVCC"),
XDDui___(UDR_DEBUG_FLAGS, "0"), // see
sqludr/sqludr.h for values
XDDui___(UDR_JVM_DEBUG_PORT, "0"),
XDDui___(UDR_JVM_DEBUG_TIMEOUT, "0"),
XDDkwd__(UPD_ORDERED, "ON"),
XDDflt0_(USTAT_AUTO_CV_SAMPLE_SLOPE, "0.5"), // CV multiplier
for sampling %.
XDDui___(USTAT_AUTO_PRIORITY, "150"), // Priority of
ustats under USAS.
XDDui___(USTAT_MAX_READ_AGE_IN_MIN, "5760"),
XDDui1__(USTAT_MIN_ROWCOUNT_FOR_LOW_SAMPLE, "1000000"),
XDDui1__(USTAT_MIN_ROWCOUNT_FOR_SAMPLE, "10000"),
XDDui1__(USTAT_NECESSARY_SAMPLE_MAX, "5000000"), // Maximum
sample size with NECESSARY
XDDpct__(USTAT_OBSOLETE_PERCENT_ROWCOUNT, "15"),
XDDui1__(USTAT_YOULL_LIKELY_BE_SORRY, "100000000"), // guard
against unintentional long-running UPDATE STATS
XDDkwd__(WMS_CHILD_QUERY_MONITORING, "OFF"),
XDDkwd__(WMS_QUERY_MONITORING, "OFF"),
Thanks,
Gunnar
On Sun, Feb 28, 2016 at 6:07 AM, Qifan Chen <qi...@esgyn.com> wrote:
> Hi,
>
> We probably should not expose SDDxxxx ones since they are internal.
> XDDxxx CQDs are external ones.
>
> --Qifan
>
> On Sun, Feb 28, 2016 at 1:14 AM, Gunnar Tapper
> <ta...@gmail.com>
> wrote:
>
> > Hi,
> >
> > I'm looking at core/sql/sqlcomp/nadefaults.cpp.
> >
> > Should SDDxxxx CQDs be documented or just XDDxxxx CQDs?
> >
> >
> > --
> > Thanks,
> >
> > Gunnar
> > *If you think you can you can, if you think you can't you're right.*
> >
>
>
>
> --
> Regards, --Qifan
>
--
Thanks,
Gunnar
*If you think you can you can, if you think you can't you're right.*
Re: Documenting CQDs
Posted by Gunnar Tapper <ta...@gmail.com>.
Hi,
This leads me witht the following. WMS_* can be excluded. Any other one?
XDDkwd__(AUTO_QUERY_RETRY_WARNINGS, "OFF"),
XDDkwd__(BLOCK_TO_PREVENT_HALLOWEEN, "ON"),
XDDui___(CACHE_HISTOGRAMS_REFRESH_INTERVAL, "3600"),
XDDansi_(CATALOG, TRAFODION_SYSCAT_LIT),
XDDkwd__(COMPRESSION_TYPE, "NONE"),
XDDui1__(DEFAULT_DEGREE_OF_PARALLELISM, "2"),
XDDui1__(DEF_MAX_HISTORY_ROWS, "1024"),
XDDkwd__(DISPLAY_DIVISION_BY_COLUMNS, "OFF"),
XDDui___(EXE_MEMORY_LIMIT_PER_CPU, "0"),
XDDui___(EXPLAIN_DESCRIPTION_COLUMN_SIZE, "-1"),
// HASH_JOINS ON means do HASH_JOINS
XDDkwd__(HASH_JOINS, "ON"),
XDDkwd__(HIST_AUTO_GENERATION_OF_SAMPLE, "OFF"),
// This CQD is used to control the number of missing stats warnings
// that should be generated.
// 0 ? Display no warnings.
// 1 ? Display only missing single column stats warnings. These include
6008 and 6011
// 2 ? Display all single column missing stats warnings and
// multi-column missing stats warnings for Scans only.
// 3 ? Display all missing single column stats warnings and missing
// multi-column stats warnings for Scans and Join operators only..
// 4 ? Display all missing single column stats and missing multi-column
// stats warnings for all operators including Scans, Joins and
GroupBys.
// The CQD also does not have an impact on the auto update stats
behavior. The stats will
// still be automatically generated even if the warnings have been
suppressed.
// USTAT_AUTO_MISSING_STATS_LEVEL.
// Default behavior is to generate all warnings
XDDui___(HIST_MISSING_STATS_WARNING_LEVEL, "4"),
// This specifies the time interval after which the fake statistics
// should be refreshed. This was done primarirly for users
// which did not want to update statistics on temporary tables.
// If this statistics is cached, then this results in bad plans
// These users can have this default set to 0, in which case histograms
// with fake statistics will never be cached. Note that when ustat
// automation is on, this value divided by 360 is used.
XDDui___(HIST_NO_STATS_REFRESH_INTERVAL, "3600"),
XDDkwd__(HIST_PREFETCH, "ON"),
XDDkwd__(HIST_REMOVE_TRAILING_BLANKS, "ON"), // should remove
after verifying code is solid
XDDflt1_(HIST_ROWCOUNT_REQUIRING_STATS, "500"),
XDDkwd__(HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION , "ON"),
XDDkwd__(INSERT_VSBB, "SYSTEM"),
XDDkwd__(ISOLATION_LEVEL, "READ_COMMITTED"),
XDDkwd__(ISOLATION_LEVEL_FOR_UPDATES, "NONE"),
// Force the join order given by the user
XDDkwd__(JOIN_ORDER_BY_USER, "OFF"),
XDDkwd__(LAST0_MODE, "OFF"),
// For old MCSB (that is, we guess MC skews from SC skews),
// apply the MC skew buster when
// SFa,b... * countOfPipeline > MC_SKEW_SENSITIVITY_THRESHOLD
// SFa,b ... is the skew factor for multi column a,b,...
//
XDDflt__(MC_SKEW_SENSITIVITY_THRESHOLD, "0.1"),
// controls the max. number of seek positions under which MDAM will be
// allowed. Set it to 0 turns off the feature.
XDDui___(MDAM_NO_STATS_POSITIONS_THRESHOLD, "10"),
// MDAM_SCAN_METHOD ON means MDAM is enabled,
// OFF means MDAM is disabled. MDAM is enabled by default
// externalized 06/21/01 RV
// mdam off on open source at this point
XDDkwd__(MDAM_SCAN_METHOD, "ON"),
// controls the max. number of probes at which MDAM under NJ plan will be
// generated. Set it to 0 turns off the feature.
XDDui___(MDAM_UNDER_NJ_PROBES_THRESHOLD, "0"),
// MERGE_JOINS ON means do MERGE_JOINS
XDDkwd__(MERGE_JOINS, "ON"),
// Allow for the setting of the row count in a long running operation
XDDui1__(MULTI_COMMIT_SIZE, "10000"),
//--------------------------------------------------------------------------
//++ MV
XDDkwd__(MVGROUP_AUTOMATIC_CREATION, "ON"),
XDDkwd__(MVQR_REWRITE_ENABLED_OPTION, "OFF"), // @ZX -- change to ON
later
XDDui0_5(MVQR_REWRITE_LEVEL, "0"),
XDDkwd__(MVQR_REWRITE_SINGLE_TABLE_QUERIES, "ON"),
XDDMVA__(MV_AGE, ""),
XDDkwd__(MV_ALLOW_SELECT_SYSTEM_ADDED_COLUMNS, "OFF"),
// NESTED_JOINS ON means do NESTED_JOINS
XDDkwd__(NESTED_JOINS, "ON"),
XDDui30_32000(NOT_ATOMIC_FAILURE_LIMIT, "32000"),
XDDui___(OPI_ERROR73_RETRIES, "10"),
XDDkwd__(OPTIMIZATION_LEVEL, "3"),
// If PARALLEL_NUM_ESPS is "SYSTEM",
// optimizer will compute the number of ESPs.
XDDui1__(PARALLEL_NUM_ESPS, "SYSTEM"),
// Partition OVerlay Support (POS) options
XDDpos__(POS_ABSOLUTE_MAX_TABLE_SIZE, ""),
XDDrlis_(PUBLISHING_ROLES, ""),
// query_cache max should be 200 MB. Set it 0 to turn off query cache
//XDD0_200000(QUERY_CACHE, "0"),
XDD0_200000(QUERY_CACHE, "16384"),
// multiplicative factor used to inflate cost of risky operators.
// = 1.0 means do not demand an insurance premium from risky operators.
// = 1.2 means demand a 20% insurance premium that cost of risky operators
// must overcome before they will be chosen over less-risky operators.
XDDflt0_(RISK_PREMIUM_NJ, "1.0"),
XDDflt0_(RISK_PREMIUM_SERIAL, "1.0"),
XDDui___(RISK_PREMIUM_SERIAL_SCALEBACK_MAXCARD_THRESHOLD, "10000"),
// external master CQD that sets following internal CQDs
// robust_query_optimization
// MINIMUM SYSTEM HIGH MAXIMUM
// risk_premium_NJ 1.0 system 2.5 5.0
// risk_premium_SERIAL 1.0 system 1.5 2.0
// partitioning_scheme_sharing 0 system 2 2
// robust_hj_to_nj_fudge_factor 0.0 system 3.0 1.0
// robust_sortgroupby 0 system 2 2
// risk_premium_MJ 1.0 system 1.5 2.0
// see optimizer/ControlDB.cpp ControlDB::doRobustQueryOptimizationCQDs
// for the actual cqds that set these values
XDDkwd__(ROBUST_QUERY_OPTIMIZATION, "SYSTEM"),
XDDkwd__(SAVE_DROPPED_TABLE_DDL, "OFF"),
XDDansi_(SCHEMA, "SEABASE"),
XDDkwd__(SKEW_EXPLAIN, "ON"),
XDDflt__(SKEW_ROWCOUNT_THRESHOLD, "1000000"), // Column row
count
// threshold
below
// which skew
// buster is
disabled.
XDDflt__(SKEW_SENSITIVITY_THRESHOLD, "0.1"),
// Timeout for a streaming cursor to return to the fetch(), even if no
// rows to return. The cursor is NOT closed, it just gives control to
// the user again.
// "0" means no timeout, just check instead.
// "negative" means never timeout.
// "positive" means the number of centiseconds to wait before timing out.
XDDint__(STREAM_TIMEOUT, "-1"),
XDDkwd__(SUBQUERY_UNNESTING, "ON"),
XDDkwd__(TABLELOCK, "SYSTEM"),
XDDintN2(TIMEOUT, "6000"),
// DTM Transaction Type: MVCC, SSCC
XDDkwd__(TRAF_TRANS_TYPE, "MVCC"),
XDDui___(UDR_DEBUG_FLAGS, "0"), // see
sqludr/sqludr.h for values
XDDui___(UDR_JVM_DEBUG_PORT, "0"),
XDDui___(UDR_JVM_DEBUG_TIMEOUT, "0"),
XDDkwd__(UPD_ORDERED, "ON"),
XDDflt0_(USTAT_AUTO_CV_SAMPLE_SLOPE, "0.5"), // CV multiplier
for sampling %.
XDDui___(USTAT_AUTO_PRIORITY, "150"), // Priority of
ustats under USAS.
XDDui___(USTAT_MAX_READ_AGE_IN_MIN, "5760"),
XDDui1__(USTAT_MIN_ROWCOUNT_FOR_LOW_SAMPLE, "1000000"),
XDDui1__(USTAT_MIN_ROWCOUNT_FOR_SAMPLE, "10000"),
XDDui1__(USTAT_NECESSARY_SAMPLE_MAX, "5000000"), // Maximum
sample size with NECESSARY
XDDpct__(USTAT_OBSOLETE_PERCENT_ROWCOUNT, "15"),
XDDui1__(USTAT_YOULL_LIKELY_BE_SORRY, "100000000"), // guard
against unintentional long-running UPDATE STATS
XDDkwd__(WMS_CHILD_QUERY_MONITORING, "OFF"),
XDDkwd__(WMS_QUERY_MONITORING, "OFF"),
Thanks,
Gunnar
On Sun, Feb 28, 2016 at 6:07 AM, Qifan Chen <qi...@esgyn.com> wrote:
> Hi,
>
> We probably should not expose SDDxxxx ones since they are internal. XDDxxx
> CQDs are external ones.
>
> --Qifan
>
> On Sun, Feb 28, 2016 at 1:14 AM, Gunnar Tapper <ta...@gmail.com>
> wrote:
>
> > Hi,
> >
> > I'm looking at core/sql/sqlcomp/nadefaults.cpp.
> >
> > Should SDDxxxx CQDs be documented or just XDDxxxx CQDs?
> >
> >
> > --
> > Thanks,
> >
> > Gunnar
> > *If you think you can you can, if you think you can't you're right.*
> >
>
>
>
> --
> Regards, --Qifan
>
--
Thanks,
Gunnar
*If you think you can you can, if you think you can't you're right.*
Re: Documenting CQDs
Posted by Qifan Chen <qi...@esgyn.com>.
Hi,
We probably should not expose SDDxxxx ones since they are internal. XDDxxx
CQDs are external ones.
--Qifan
On Sun, Feb 28, 2016 at 1:14 AM, Gunnar Tapper <ta...@gmail.com>
wrote:
> Hi,
>
> I'm looking at core/sql/sqlcomp/nadefaults.cpp.
>
> Should SDDxxxx CQDs be documented or just XDDxxxx CQDs?
>
>
> --
> Thanks,
>
> Gunnar
> *If you think you can you can, if you think you can't you're right.*
>
--
Regards, --Qifan