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:[email protected]] Sent: Sunday, February 28, 2016 9:21 PM To: [email protected] 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 <[email protected]> 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 > <[email protected]> > 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.*
