Replaced logo, added drafts of new manuals, updated SQL Reference, corrected issues in other manuals.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/33d796d6 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/33d796d6 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/33d796d6 Branch: refs/heads/master Commit: 33d796d617e896ea02ece5858ee0e69c6b642120 Parents: 39d3a0c Author: Gunnar Tapper <[email protected]> Authored: Wed Mar 2 00:28:58 2016 -0700 Committer: Gunnar Tapper <[email protected]> Committed: Wed Mar 2 00:28:58 2016 -0700 ---------------------------------------------------------------------- .../src/asciidoc/_chapters/about.adoc | 1 + .../src/asciidoc/_chapters/odb.adoc | 14 +- .../src/asciidoc/_chapters/about.adoc | 1 + docs/cqd_reference/pom.xml | 291 +++++++++++ .../src/asciidoc/_chapters/about.adoc | 179 +++++++ .../src/asciidoc/_chapters/histograms.adoc | 215 ++++++++ .../src/asciidoc/_chapters/introduction.adoc | 111 ++++ .../_chapters/operational_controls.adoc | 110 ++++ .../src/asciidoc/_chapters/query_plans.adoc | 515 +++++++++++++++++++ .../asciidoc/_chapters/runtime_controls.adoc | 83 +++ .../src/asciidoc/_chapters/schema_controls.adoc | 49 ++ .../src/asciidoc/_chapters/transactions.adoc | 94 ++++ .../src/asciidoc/_chapters/update_stats.adoc | 92 ++++ docs/cqd_reference/src/asciidoc/index.adoc | 69 +++ docs/load_transform/pom.xml | 289 +++++++++++ .../src/asciidoc/_chapters/about.adoc | 186 +++++++ .../src/asciidoc/_chapters/bulk_load.adoc | 291 +++++++++++ .../src/asciidoc/_chapters/bulk_unload.adoc | 73 +++ .../src/asciidoc/_chapters/introduction.adoc | 117 +++++ .../src/asciidoc/_chapters/monitor.adoc | 77 +++ .../src/asciidoc/_chapters/tables_indexes.adoc | 208 ++++++++ .../src/asciidoc/_chapters/trickle_load.adoc | 496 ++++++++++++++++++ .../src/asciidoc/_chapters/troubleshoot.adoc | 159 ++++++ docs/load_transform/src/asciidoc/index.adoc | 68 +++ .../src/asciidoc/_chapters/about.adoc | 3 + docs/odb_user/src/asciidoc/_chapters/about.adoc | 3 + docs/odb_user/src/asciidoc/_chapters/load.adoc | 48 +- .../src/asciidoc/_chapters/about.adoc | 2 + .../src/asciidoc/_chapters/about.adoc | 2 + .../src/asciidoc/_chapters/cqds.adoc | 20 - .../src/asciidoc/_chapters/sql_statements.adoc | 8 +- .../src/asciidoc/_chapters/sql_utilities.adoc | 117 ++++- docs/src/site/markdown/download.md | 18 +- docs/src/site/markdown/index.md | 12 +- docs/src/site/markdown/logo.md | 56 ++ docs/src/site/resources/css/site.css | 88 +++- .../site/resources/images/carousel/scale.png | Bin 93783 -> 127571 bytes .../site/resources/images/carousel/stack.png | Bin 102472 -> 170554 bytes .../resources/images/logos/pb-Trafodion.png | Bin 0 -> 49089 bytes .../resources/images/logos/pb-trafodion.jpg | Bin 0 -> 80904 bytes .../images/logos/trafodion-dragon-large.jpg | Bin 0 -> 50000 bytes .../images/logos/trafodion-dragon-large.png | Bin 0 -> 20595 bytes .../resources/images/logos/trafodion-dragon.png | Bin 0 -> 12786 bytes .../resources/images/logos/trafodion-logo.jpg | Bin 18953 -> 58953 bytes docs/src/site/site.xml | 20 +- 45 files changed, 4087 insertions(+), 98 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/client_install/src/asciidoc/_chapters/about.adoc ---------------------------------------------------------------------- diff --git a/docs/client_install/src/asciidoc/_chapters/about.adoc b/docs/client_install/src/asciidoc/_chapters/about.adoc index bbc0d9f..e29d926 100644 --- a/docs/client_install/src/asciidoc/_chapters/about.adoc +++ b/docs/client_install/src/asciidoc/_chapters/about.adoc @@ -111,6 +111,7 @@ ATTRIBUTE[S] attribute [, attribute] ... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. + For example: ++ ``` expression-n ... ``` http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/client_install/src/asciidoc/_chapters/odb.adoc ---------------------------------------------------------------------- diff --git a/docs/client_install/src/asciidoc/_chapters/odb.adoc b/docs/client_install/src/asciidoc/_chapters/odb.adoc index 4d84388..af97723 100644 --- a/docs/client_install/src/asciidoc/_chapters/odb.adoc +++ b/docs/client_install/src/asciidoc/_chapters/odb.adoc @@ -20,9 +20,11 @@ */ //// +[[install-odb]] = Install odb These installation instructions apply to Trafodion Release 1.3.*_n_* and subsequent updates of the product until otherwise indicated. +[[installation-package]] == Installation Package The distribution file for odb is packaged within the Trafodion clients tar file, `clients-1.3.*_n_*.tar.gz`, @@ -36,12 +38,14 @@ Use this distribution file to install odb: /bin/odb64luo | 64-bit Linux |=== +[[installation-requirements]] == Installation Requirements The odb tool runs on 64-bit Linux. On a Linux workstation, odb requires `pthread` libraries, which are usually installed by default. It also requires the unixODBC Driver Manager to be installed and configured on the client workstation. For more information, see the http://trafodion.apache.org/docs/odb_user/index.html[_Trafodion odb User Guide_]. +[[installation-instructions]] == Installation Instructions NOTE: Before following these installation instructions, please make sure to install and configure unixODBC on the client workstation. For more @@ -49,13 +53,13 @@ information, see the http://trafodion.apache.org/docs/odb_user/index.html[_Trafo Follow these instructions to download and install odb on the client workstation. -* <<odb_download, Downloading odb>> -* <<odb_install, Installing odb>> +* <<odb-download, Downloading odb>> +* <<odb-install, Installing odb>> To uninstall odb, please refer to <<odb_uninstall, Uninstalling odb>>. <<< -[[odb_download]] +[[odb-download]] === Download odb 1. Create a download folder on the client workstation. @@ -72,7 +76,7 @@ The package file contains the `odb64_linux.tar.gz` distribution file, which is e 6. Proceed with <<odb_install, Installing odb>>. -[[odb_install]] +[[odb-install]] === Install odb 1. Change the directory to the clients subdirectory. @@ -89,7 +93,7 @@ The command extracts these files: 3. You are now ready to run the odb executable. For more information, see the http://trafodion.apache.org/docs/odb_user/index.html[_Trafodion odb User Guide_]. -[[odb_uninstall]] +[[odb-uninstall]] === Uninstall odb To uninstall odb, delete the `README` and `/bin/odb64luo` files from their installed location. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/command_interface/src/asciidoc/_chapters/about.adoc ---------------------------------------------------------------------- diff --git a/docs/command_interface/src/asciidoc/_chapters/about.adoc b/docs/command_interface/src/asciidoc/_chapters/about.adoc index 6a7c672..6b4f1c6 100644 --- a/docs/command_interface/src/asciidoc/_chapters/about.adoc +++ b/docs/command_interface/src/asciidoc/_chapters/about.adoc @@ -111,6 +111,7 @@ ATTRIBUTE[S] attribute [, attribute] ... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. + For example: ++ ``` expression-n ... ``` http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/pom.xml ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/pom.xml b/docs/cqd_reference/pom.xml new file mode 100644 index 0000000..9fbb88c --- /dev/null +++ b/docs/cqd_reference/pom.xml @@ -0,0 +1,291 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd";> + <!-- +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ +--> + <modelVersion>4.0.0</modelVersion> + <groupId>org.apache.trafodion</groupId> + <artifactId>cqd-reference-guide</artifactId> + <version>${env.TRAFODION_VER}</version> + <packaging>pom</packaging> + <name>Trafodion CQD Reference Guide</name> + <description> + This guide describes Trafodion Control Query Defaults (CQDs) that are used to override system-level default settings. + </description> + <url>http://trafodion.incubator.apache.org</url> + <inceptionYear>2015</inceptionYear> + + <parent> + <groupId>org.apache.trafodion</groupId> + <artifactId>trafodion</artifactId> + <relativePath>../../pom.xml</relativePath> + <version>1.3.0</version> + </parent> + + + <licenses> + <license> + <name>The Apache Software License, Version 2.0</name> + <url>http://www.apache.org/licenses/LICENSE-2.0.txt</url> + <distribution>repo</distribution> + <comments>A business-friendly OSS license</comments> + </license> + </licenses> + + <organization> + <name>Apache Software Foundation</name> + <url>http://www.apache.org</url> + </organization> + + <issueManagement> + <system>JIRA</system> + <url>http://issues.apache.org/jira/browse/TRAFODION</url> + </issueManagement> + + <scm> + <connection>scm:git:http://git-wip-us.apache.org/repos/asf/incubator-trafodion.git</connection> + <developerConnection>scm:git:https://git-wip-us.apache.org/repos/asf/incubator-trafodion.git</developerConnection> + <url>https://git-wip-us.apache.org/repos/asf?p=incubator-trafodion.git</url> + <tag>HEAD</tag> + </scm> + + <ciManagement> + <system>Jenkins</system> + <url>https://jenkins.esgyn.com</url> + </ciManagement> + + <properties> + <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> + <asciidoctor.maven.plugin.version>1.5.2.1</asciidoctor.maven.plugin.version> + <asciidoctorj.pdf.version>1.5.0-alpha.11</asciidoctorj.pdf.version> + <asciidoctorj.version>1.5.4</asciidoctorj.version> + <rubygems.prawn.version>2.0.2</rubygems.prawn.version> + <jruby.version>9.0.4.0</jruby.version> + </properties> + + <repositories> + <repository> + <id>rubygems-proxy-releases</id> + <name>RubyGems.org Proxy (Releases)</name> + <url>http://rubygems-proxy.torquebox.org/releases</url> + <releases> + <enabled>true</enabled> + </releases> + <snapshots> + <enabled>false</enabled> + </snapshots> + </repository> + </repositories> + + <dependencies> + <dependency> + <groupId>rubygems</groupId> + <artifactId>prawn</artifactId> + <version>${rubygems.prawn.version}</version> + <type>gem</type> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.jruby</groupId> + <artifactId>jruby-complete</artifactId> + <version>${jruby.version}</version> + </dependency> + <dependency> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctorj</artifactId> + <version>${asciidoctorj.version}</version> + </dependency> + </dependencies> + + <build> + <plugins> + <plugin> + <groupId>de.saumya.mojo</groupId> + <artifactId>gem-maven-plugin</artifactId> + <version>1.0.10</version> + <configuration> + <!-- align JRuby version with AsciidoctorJ to avoid redundant downloading --> + <jrubyVersion>${jruby.version}</jrubyVersion> + <gemHome>${project.build.directory}/gems</gemHome> + <gemPath>${project.build.directory}/gems</gemPath> + </configuration> + <executions> + <execution> + <goals> + <goal>initialize</goal> + </goals> + </execution> + </executions> + </plugin> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-resources-plugin</artifactId> + <version>2.7</version> + <configuration> + <encoding>UTF-8</encoding> + <attributes> + <generateReports>false</generateReports> + </attributes> + </configuration> + </plugin> + <plugin> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctor-maven-plugin</artifactId> + <version>${asciidoctor.maven.plugin.version}</version> + <dependencies> + <dependency> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctorj-pdf</artifactId> + <version>${asciidoctorj.pdf.version}</version> + </dependency> + <dependency> + <groupId>org.asciidoctor</groupId> + <artifactId>asciidoctorj</artifactId> + <version>${asciidoctorj.version}</version> + </dependency> + </dependencies> + <configuration> + <sourceDirectory>${basedir}/src</sourceDirectory> + </configuration> + <executions> + <execution> + <id>generate-html-doc</id> + <goals> + <goal>process-asciidoc</goal> + </goals> + <phase>site</phase> + <configuration> + <doctype>book</doctype> + <backend>html5</backend> + <sourceHighlighter>coderay</sourceHighlighter> + <outputDirectory>${basedir}/target/site</outputDirectory> + <requires> + <require>${basedir}/../shared/google-analytics-postprocessor.rb</require> + </requires> + <attributes> + <!-- Location of centralized stylesheet --> + <stylesheet>${basedir}/../shared/trafodion-manuals.css</stylesheet> + <project-version>${env.TRAFODION_VER}</project-version> + <build-date>${maven.build.timestamp}</build-date> + <google-analytics-account>UA-72491210-1</google-analytics-account> + </attributes> + </configuration> + </execution> + <execution> + <id>generate-pdf-doc</id> + <phase>site</phase> + <goals> + <goal>process-asciidoc</goal> + </goals> + <configuration> + <doctype>book</doctype> + <backend>pdf</backend> + <sourceHighlighter>coderay</sourceHighlighter> + <outputDirectory>${basedir}/target</outputDirectory> + <attributes> + <project-version>${env.TRAFODION_VER}</project-version> + <build-date>${maven.build.timestamp}</build-date> + <pdf-stylesdir>${basedir}/../shared</pdf-stylesdir> + <pdf-style>trafodion</pdf-style> + <icons>font</icons> + <pagenums/> + <toc/> + <idprefix/> + <idseparator>-</idseparator> + </attributes> + </configuration> + </execution> + </executions> + </plugin> + <!-- Rename target/site/index.pdf to client-install-guide.pdf --> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-antrun-plugin</artifactId> + <version>1.8</version> + <inherited>false</inherited> + <executions> + <execution> + <id>populate-release-directories</id> + <phase>post-site</phase> + <configuration> + <target name="Populate Release Directories"> + <!-- The website uses the following organization for the docs/target/docs directory: + - To ensure a known location, the base directory contains the LATEST version of the web book and the PDF files. + - The know location is docs/target/docs/<document> + - target/docs/<version>/<document> contains version-specific renderings of the documents. + - target/docs/<version>/<document> contains the PDF version and the web book. The web book is named index.html + --> + <!-- Copy the PDF file to its target directories --> + <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/cqd_reference_guide/Trafodion_CQD_Reference_Guide.pdf" /> + <copy file="${basedir}/target/index.pdf" tofile="${basedir}/../target/docs/${project.version}/cqd_reference_guide/Trafodion_CQD_Reference_Guide.pdf" /> + <!-- Copy the Web Book files to their target directories --> + <copy todir="${basedir}/../target/docs/cqd_reference_guide"> + <fileset dir="${basedir}/target/site"> + <include name="**/*.*"/> <!--All sub-directories, too--> + </fileset> + </copy> + <copy todir="${basedir}/../target/docs/${project.version}/cqd_reference_guide"> + <fileset dir="${basedir}/target/site"> + <include name="**/*.*"/> <!--All sub-directories, too--> + </fileset> + </copy> + </target> + </configuration> + <goals> + <goal>run</goal> + </goals> + </execution> + </executions> + </plugin> + </plugins> + </build> + + <!-- Included because this is required. No reports are generated. --> + <reporting> + <excludeDefaults>true</excludeDefaults> + <plugins> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-project-info-reports-plugin</artifactId> + <version>2.8</version> + <reportSets> + <reportSet> + <reports> + </reports> + </reportSet> + </reportSets> + </plugin> + </plugins> + </reporting> + + <distributionManagement> + <site> + <id>trafodion.incubator.apache.org</id> + <name>Trafodion Website at incubator.apache.org</name> + <!-- On why this is the tmp dir and not trafodion.incubator.apache.org, see + https://issues.apache.org/jira/browse/HBASE-7593?focusedCommentId=13555866&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13555866 + --> + <url>file:///tmp</url> + </site> + </distributionManagement> +</project> http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/src/asciidoc/_chapters/about.adoc ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/src/asciidoc/_chapters/about.adoc b/docs/cqd_reference/src/asciidoc/_chapters/about.adoc new file mode 100644 index 0000000..732a6ce --- /dev/null +++ b/docs/cqd_reference/src/asciidoc/_chapters/about.adoc @@ -0,0 +1,179 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + += About This Document + +This guide describes Trafodion Control Query Defaults (CQDs) that are used to override +system-level default settings. + +== Intended Audience + +This guide is intended for database administrators and application programmers who want to +use CQDs to override system-default settings when querying a Trafodion database. + + +== New and Changed Information +This is a new guide. + +== Notation Conventions +This list summarizes the notation conventions for syntax presentation in this manual. + +* UPPERCASE LETTERS ++ +Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. ++ +``` +SELECT +``` + +* lowercase letters ++ +Lowercase letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. ++ +``` +file-name +``` + +* [ ] Brackets ++ +Brackets enclose optional syntax items. ++ +``` +DATETIME [start-field TO] end-field +``` ++ +A group of items enclosed in brackets is a list from which you can choose one item or none. ++ +The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. ++ +For example: ++ +``` +DROP SCHEMA schema [CASCADE] +DROP SCHEMA schema [ CASCADE | RESTRICT ] +``` + +* { } Braces ++ +Braces enclose required syntax items. ++ +``` +FROM { grantee [, grantee ] ... } +``` ++ +A group of items enclosed in braces is a list from which you are required to choose one item. ++ +The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. ++ +For example: ++ +``` +INTERVAL { start-field TO end-field } +{ single-field } +INTERVAL { start-field TO end-field | single-field } +``` +* | Vertical Line ++ +A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. ++ +``` +{expression | NULL} +``` + +* … Ellipsis ++ +An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. ++ +``` +ATTRIBUTE[S] attribute [, attribute] ... +{, sql-expression } ... +``` ++ +An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. ++ +For example: ++ +``` +expression-n ... +``` + +* Punctuation ++ +Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. ++ +``` +DAY (datetime-expression) +@script-file +``` ++ +Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. ++ +For example: ++ +``` +"{" module-name [, module-name] ... "}" +``` + +* Item Spacing ++ +Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. ++ +``` +DAY (datetime-expression) DAY(datetime-expression) +``` ++ +If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: ++ +``` +myfile.sh +``` + +* Line Spacing ++ +If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. ++ +This spacing distinguishes items in a continuation line from items in a vertical list of selections. ++ +``` +match-value [NOT] LIKE _pattern + [ESCAPE esc-char-expression] +``` + +== Publishing History +[cols="2*",options="header"] +|=== +| Product Version | Publication Date +| Trafodion Release 1.3.0 | January 2016. +|=== + +== Comments Encouraged +The Trafodion community encourages your comments concerning this document. We are committed to providing documentation that meets your +needs. Send any errors found, suggestions for improvement, or compliments to: + [email protected] + +Include the document title and any comment, error found, or suggestion for improvement you have concerning this document. Or, even +better, join our community and help us improve our documentation. Please refer to +http://trafodion.incubator.apache.org/contributing_redirect.html[Trafodion Contributor Guide] for details. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc b/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc new file mode 100644 index 0000000..53d1539 --- /dev/null +++ b/docs/cqd_reference/src/asciidoc/_chapters/histograms.adoc @@ -0,0 +1,215 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + +[[manage-histograms]] += Manage Histograms + +This section describes CQDs that are used to manage histograms. + +[[cache-histograms-refresh-interval]] +== CACHE_HISTOGRAMS_REFRESH_INTERVAL + +[cols="25%h,75%"] +|=== +| *Category* | Manage Histograms +| *Description* | Defines the time interval after which timestamps for cached histograms are checked to be refreshed. +| *Values* | Unsigned integer Unit is seconds. + + + +The default value is *'3600'* (1 hour). +| *Usage* | Histogram statistics are cached so that the compiler can avoid access to the metadata tables, thereby reducing compile times. +The timestamp of the tables are checked against those of the cached histograms at an interval specified by this CQD, in order to see if the cached histograms +need to be refreshed. + + + +You can increase the interval to reduce the impact on compile times as long as you do not need to obtain fresh statistics more frequently in order to improve +query performance. It may be that the default interval is too long and you would rather refresh the statistics more frequently than the default one hour, +in order to improve query performance at the cost of increased compile times. + + + +This setting depends on how frequently you are updating statistics on tables. There is no point in refreshing statistics frequently when statistics are not +being updated during that time. On the other hand, if you are updating statistics, or generating them for the first time on freshly loaded tables frequently +enough, and you want these to be picked up immediately by the compiler because you have seen this to have a dramatic impact on plan quality, then you can +make the refresh more frequent. +| *Production Usage* | Not applicable. +| *Impact* | Longer histogram refresh intervals can improve compile times. However, the longer the refresh interval the more obsolete +the histograms. That could result in poor performance for queries that could leverage recently updated statistics. +| *Level* | System or Service. +| *Conflicts/Synergies* | Frequency of update statistics run either using MAINTAIN or using Update Statistics Automation Server. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[hist-missing-stats-warning-level]] +== HIST_MISSING_STATS_WARNING_LEVEL + +[cols="25%h,75%"] +|=== +| *Category* | Manage Histograms +| *Description* | Controls the level of missing statistics warnings that should be displayed. The warnings impacted are 6007, 6008, 6010 and 6011. +| *Values* | +*'0'*: Display no warnings. Update Statistics Automation Server still continues to record missing column statistics. + + + +*'1'*: Display only missing single column statistics warnings. These include 6008 and 6011. + + + +*'2'*: Display all missing single and multi-column statistics warnings for scans only. + + + +*'3'*: Display all missing single and multi-column statistics warnings for scans and join operators only. +Update Statistics Automation Server continues to record missing column statistics. + + + +*'4'*: Display all missing single and multi-column statistics warnings. + + + +The default value is *'4'*. +| *Usage* | Update Statistics Automation Server captures these warnings and gathers appropriate statistics. +If you do not want to see these warnings, then change the setting to '0'. If you want to track the warnings, then you have a +choice of which warnings you want to track. Each setting gives you the ability to filter the warnings seen for missing single +or multi-column statistics for join or scan operations. This controls the resulting number of warning messages. + + + +If poor query plans are being caused by cardinality estimations that seem to be off, then you can check the histogram statistics +to see if statistics are being collected for those columns and how accurate they are. If you don't find statistics being collected, +then you could look for the warnings by setting this CQD to the appropriate setting. Based on that you could take appropriate action: +either find out why USAS is not collecting appropriate statistics, or if USAS is not being used then ensure that update statistics +is being run to generate those statistics. +| *Production Usage* | Many tools divide a query into several steps. During the first phases volatile tables are created +and populated, the last phase usually joins all the volatile tables created in the previous steps. Usually statistics are not needed +for those volatile tables because the final join is straight forward and the optimizer has no big choices. Nevertheless the log is +flooded with useless warnings if you don't set the warning level to 0. If possible, try to direct queries from those tools to a +dedicated service where you set the warning level to 0. +| *Impact* | Though the warnings give information about all statistics that are missing, it can be overwhelming +to get several warnings. Not all warnings may contribute to plan improvements. The optimizer issues multi-column statistics warnings +based on the search path, some of which may not even impact the plan quality. Also, the cost of gathering statistics on those columns +may not bring commensurate benefit to a large number of queries. +| *Level* | System. +| *Conflicts/Synergies* | Update Statistics Automation Server does capture these warnings and gathers statistics felt appropriate +for query performance. If you are using USAS then you may set the warning level to low or 0. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[hist-no-stats-refresh-interval]] +== HIST_NO_STATS_REFRESH_INTERVAL + +[cols="25%h,75%"] +|=== +| *Category* | Manage Histograms +| *Description* | Defines the time interval after which the fake histograms in the cache should be refreshed unconditionally. +| *Values* | Unsigned integer Unit is seconds. + + + +The default value is *'3600'* (1 hour). +| *Usage* | Histogram statistics are "fake" when update statistics is not being run, but instead the customer is +updating the histogram tables directly with statistics to guide the optimizer. This may be done if the data in the table is very +volatile (such as for temporary tables), update statistics is not possible because of constant flush and fill of the table occurring, +and statistics are manually set to provide some guidance to the optimizer to generate a good plan. + + + +If these fake statistics are updated constantly to reflect the data churn, then this default can be set to 0. This would ensure that +the histograms with fake statistics are not cached and are always refreshed. If these fake statistics are set and not touched again, +then this interval could be set very high. +| *Production Usage* | Not applicable. +| *Impact* | Setting a high interval improves compilation time. However, if statistics are being updated, +then the compiler may be working with obsolete histogram statistics, potentially resulting in poorer plans. +| *Level* | Service. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[hist-prefetch]] +== HIST_PREFETCH + +[cols="25%h,75%"] +|=== +| *Category* | Manage Histograms +| *Description* | Influences the compiler to pre-fetch the histograms and save them in cache. +| *Values* | +*'ON'*: Pre-fetches the histograms. + +*'OFF'*: Does not pre-fetch the histograms. + + + +The default value is *'ON'*. +| *Usage* | You may want to turn this off if you don't want to pre-fetch a large number of histograms, many of which may not be used. +| *Production Usage* | Not applicable. +| *Impact* | Though it makes compilation time faster, it may result in the histogram cache to be filled with histograms that may never be used. +| *Level* | System or Service. +| *Conflicts/Synergies* | Use this CQD with CACHE_HISTOGRAMS. If CACHE_HISTOGRAMS is OFF, then this CQD has no effect. $$$ +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[hist-rowcount-requiring-stats]] +== HIST_ROWCOUNT_REQUIRING_STATS + +[cols="25%h,75%"] +|=== +| *Category* | Manage Histograms +| *Description* | Specifies the minimum row count for which the optimizer needs histograms, in order to compute better cardinality estimates. +The optimizer does not issue any missing statistics warnings for tables whose size is smaller than the value of this CQD. +| *Values* | Integer. + + + +The default value is *'50000'*. +| *Usage* | Use this CQD to reduce the number of statistics warnings. +| *Production Usage* | Not applicable. +| *Impact* | Missing statistics warnings are not displayed for smaller tables, which in most cases don't impact plan quality much. +However, there may be some exceptions where missing statistics on small tables could result in less than optimal plans. +| *Level* | System. +| *Conflicts/Synergies* | Use this CQD with <<hist-missing-stats-warning-level,HIST_MISSING_STATS_WARNING_LEVEL>>. If the warning level CQD is 0, +then this CQD does not have any effect. Also, for tables having fewer rows than set in this CQD, no warnings are displayed irrespective of the warning level. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[hist-use-sample-for-cardinality-estimation]] +== HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION + +[cols="25%h,75%"] +|=== +| *Category* | Manage Histograms +| *Description* | Enables the Compile Time Stats feature. Compile Time Stats are produced during query plan generation by executing a +subset of the query on a subset of data to gather more accurate cardinality estimations. +| *Values* | +*'ON'*: Compile Time Statistics is enabled. + +*'OFF'*: Compile Time Statistics is disabled. + + + +The default value is *'ON'*. +| *Usage* | The feature is very helpful for cases when the query contains complex predicates on a table. These predicates include +LIKE, CASE, any other expressions or more than one range predicates and equality on large character columns. + + + +It can be disabled if most of the queries are single table or at most two-way joins. + + + +It can also be disabled if the extra collection of statistics seems to be adversely affecting the total query compile and execution time. +| *Production Usage* | Not applicable. +| *Impact* | The feature improves cardinality estimates for Scan operators thus improving the plan quality. +However, it can also increase the compile time. +| *Level* | Any. +| *Conflicts/Synergies* | In order to use the feature in its default form, sample tables should exist in `public_access_schema`. $$$ +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc b/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc new file mode 100644 index 0000000..5ba441e --- /dev/null +++ b/docs/cqd_reference/src/asciidoc/_chapters/introduction.adoc @@ -0,0 +1,111 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + +[[introduction]] += Introduction + +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html#control_query_cancel_statement[Trafofion SQL Reference Manual] +full documentation of the CQD (CONTROL QUERY DEFAULT) statement. + +The CONTROL QUERY DEFAULT statement changes the default settings for the current process. You can execute +the CONTROL QUERY DEFAULT statement in a client-based tool like TrafCI or through any ODBC or JDBC application. + +*Syntax* + +``` +{ CONTROL QUERY DEFAULT | CQD } control-default-option + +control-default-option is: + attribute {'attr-value' | RESET} +``` + +The result of the execution of a CONTROL QUERY DEFAULT statement stays in effect until the current process +terminates or until the execution of another statement for the same attribute overrides it. +CQDs are applied at compile time, so CQDs do not affect any statements that are already prepared. For example: + +``` +PREPARE x FROM SELECT * FROM t; +CONTROL QUERY DEFAULT SCHEMA 'myschema'; +EXECUTE x; -- uses the default schema SEABASE +SELECT * FROM t2; -- uses MYSCHEMA; +PREPARE y FROM SELECT * FROM t3; +CONTROL QUERY DEFAULT SCHEMA 'seabase'; +EXECUTE y; -- uses MYSCHEMA; +``` + +*Examples* + +* Change the maximum supported length of the column names to 200 for the current process: ++ +``` +CONTROL QUERY DEFAULT HBASE_MAX_COLUMN_NAME_LENGTH '200'; +``` + +* Reset the HBASE_MAX_COLUMN_NAME_LENGTH attribute to its initial value in the current process: ++ +``` +CONTROL QUERY DEFAULT HBASE_MAX_COLUMN_NAME_LENGTH RESET; +``` + +<<< +[[introduction-cqd-descriptin]] +== CQD Descriptions + +The following information is provided for each CQD: + +[cols="25%h,75%"] +|=== +| *Category* | Identifies one of these categories with which the CQD is associated: + + + +• Influence Query Plans + +• Manage Histograms + +• Transaction control and locking + +• Run time controls + +• Schema controls + +• Update Statistics and Reorg + +• Operational controls +| *Description* | Describes the purpose of the CQD. +| *Values* | Identifies this information: + + + +• Values, in the form of a character string, that specify the applicable attribute values for the CQD. + +• The default attribute value. + +• If applicable, the Trafodion release in which the attribute values or default changed. +| *Usage* | Describes the conditions when the CQD is helpful, and how to detect the conditions. +| *Production Usage* | Identifies when the CQD is not safe to be used as a permanent setting in production. +| *Impact* | Describes any positive and negative implications of using the CQD. +| *Level* | Indicates one of these levels at which the CQD should be used: + + + +• Query + +• Session + +• Service + +• Any + + + +NOTE: This level indicates that the CQD can be used at the Query, Session or Service level as long as you fully understands the scope of the impact of the CQD. +| *Conflicts/Synergies* | Describes CQDs that are in conflict with or can be used in conjunction with the CQD. +| *Real Problem Addressed* | Describes any design or solution that the CQD may be a workaround for and how you can directly address the real problem. +| *Introduced In Release* | Indicates the Trafodion release when the CQD was introduced. +| *Deprecated In Release* | Indicates in what release the CQD was deprecated. +|=== http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc b/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc new file mode 100644 index 0000000..aad0e7f --- /dev/null +++ b/docs/cqd_reference/src/asciidoc/_chapters/operational_controls.adoc @@ -0,0 +1,110 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + +[[operational-controls]] += Operational Controls + +This section describes CQDs that are used for operational controls. + +[[auto-query-retry-warnings]] +== AUTO_QUERY_RETRY_WARNINGS + +[cols="25%h,75%"] +|=== +| *Category* | Operational Controls +| *Description* | Indicates whether a warning should be issued when a query is retried, in case a failed query is automatically retried. +| *Values* | *'ON'* or *'OFF'* + + + +The default value is *'OFF'*. +| *Usage* | There are certain cases, such as when a node failure occurs, where a query may fail midstream. In many of such failure scenarios, +if the query has not returned any data, then it is retried automatically. + + + +When such retries happen, you may want to see a warning that an automatic retry took place. That would be a reason to turn this on. +| *Production Usage* | Not applicable. +| *Impact* | You get a warning message every time a query is automatically retried due to a failure. When there is a node failure, +then a large number of queries may be impacted. Therefore, you need to assess if you want to see a flood of warnings. The warning is returned after the query completes. +| *Level* | System. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[query-cache]] +== QUERY_CACHE + +[cols="25%h,75%"] +|=== +| *Category* | Operational Controls $$$ +| *Description* | Attempts to reduce compilation times by storing and reusing previously compiled query plans. +It maximizes the chances of plan reuse by parameterizing literals in equality predicates. Two equality predicates, "col = val1" and "col = val2", +are considered to match if their selectivities match. + + + +A query cache setting of *'16384'* means a maximum of 16,384 KB of compiler memory can be used for keeping previously compiled plans +before evicting the oldest unused plan(s) to make room for the latest cacheable plan. +| *Values* | +*Up through 4294967295*: Kilobytes of memory allocated to query cache. + +*'0'*: Turns off query plan caching. + + + +The default value is *'16384'* (16 MB). +| *Usage* | To choose the appropriate size for the query cache, examine your applications. Applications that use a PREPARE +statement to pre-compile queries once and then EXECUTE the prepared plan, should turn off plan caching. + + + +Ad-hoc query applications can specify a size that can hold most of the frequently processed queries. For example, if an application processes +40 classes of queries frequently with an average plan size of 100 KB per query, a cache size of 4000 KB might be optimal. +(Plan size is not the same as the size of the SQL statement and is not easy to assess.) + + + +There may be applications that are operational in nature, with many small queries, and others that are analytical in nature with large complex queries. +Cache size can be set differently for different service levels handling such workloads based on the classes and types of queries, size of the queries, +and propensity to get cache hits. + + + +Another consideration is how frequently the cache is getting flushed due to the compiler being shutdown and a new one started by an MXOSRVR +(ODBC/Connect server), in order to run queries on behalf of a different role than the role that was using the compiler before. +If this happens often and not enough static servers can be started to reduce this from happening, then creating a large cache may not be useful, +because it has to be flushed and filled too often. + + + +After taking the above into account the best way to really assess whether caching is effective, and tune it for your specific applications, +is to understand the cache hit statistics, how many queries are forced to be removed from cache (on a least recently used basis), and a number +of other statistics about the efficiency of query plan caching for your applications. +| *Production Usage* | Not applicable. +| *Impact* | A larger cache size allows more query plans to be cached. This increases the probability of finding a plan in +cache that can be reused for a query, thereby reducing compile time. It does mean that the compiler uses more memory, but because there are +usually not that many compilers running in a node, the negative effects may be minimal. + + + +However, you do need to know the amount of physical memory available on each node and the number of compilers that run on a node +(influenced by the number of concurrent connections configured to run on the cluster). If the cache size is disproportionately large, +it is likely to result in reduced performance as the operating system may repeatedly swap the compiler (bloated by a huge cache) in and out of physical memory. +| *Level* | Service. +| *Conflicts/Synergies* | You should be aware that the cache allocated is divided into text caching and template caching. +Text caching gets approximately 25% of the cache memory. Query plan caching occurs prior to parsing (text-based caching) and after parsing (template-based caching). +The compiler caches same-text queries as text cache hits. Same-text queries are queries whose SQL texts are identical in everything, +including case and white space. By caching text-based queries, the compiler avoids redundant re-computation of previously compiled +queries and improves performance by reducing compile times and increasing compiler throughput. The text cache is always searched first for a query. +If the plan object is not produced due to a text cache miss, then the plan is stored in the template cache if it meets the criteria for template caching. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc b/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc new file mode 100644 index 0000000..33bc8d6 --- /dev/null +++ b/docs/cqd_reference/src/asciidoc/_chapters/query_plans.adoc @@ -0,0 +1,515 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + +[[influence-query-plans]] += Influence Query Plans + +This section describes CQDs that are used to influence query plans. + +[[default-degree-of-parallelism]] +== DEFAULT_DEGREE_OF_PARALLELISM + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Defines the minimum size for query operator parallelism. +The optimizer may choose an adaptive segment size that is equal to, or the multiple of, +the value of this CQD, depending on the maximum estimated resource consumed by any single +operator in the query. The optimizer may also decide to run the query with no parallelism +if the resource consumption estimate is very low. +| *Values* | Unsigned Integer. + +The default value is *'16'*. +| *Usage* | For clusters running at higher levels of concurrency with +workloads that include a large number of small queries, reducing the default degree of +parallelism may help achieve higher throughput. +| *Production Usage* | Not applicable. +| *Impact* | Lowering the value of this CQD can increase the throughput of +high-concurrency small-query workloads, but has the potential disadvantage of increasing the +elapsed time for some of the longer running queries. +| *Level* | System. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Sddressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[hash-joins]] +== HASH_JOINS + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Determines if Hash Join is considered by the optimizer to generate an execution plan. +| *Values* | *'ON'* Hash Join is considered. + +'OFF Hash Join is disabled. + + + +The default value is *'ON'*. +| *Usage* | Use this CQD when you want to force a query plan not to use any Hash Joins. +| *Production Usage* | Hash Join is an important join implementation strategy for most complex queries. +It is highly recommended not to turn Hash Join OFF. It should only be used to force a query plan for a particular query on an exception basis. +| *Impact* | Turning Hash Join OFF may result in very inefficient query plans with expensive nested joins or sorts for merge joins. +| *Level* | Query. +| *Conflicts/Synergies* | Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins). +This may result in the compiler failing to generate query plans. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[join-order-by-user]] +== JOIN_ORDER_BY_USER + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Enables or disables the join order in which the optimizer joins the tables to be the sequence of the +tables in the FROM clause of the query. +| *Values* | *'ON'* Join order is forced. + +*'OFF'* Join order is decided by the optimizer. + + + +The default value is *'OFF'*. +| *Usage* | When set to ON, the optimizer considers only execution plans that have the join order matching +the sequence of the tables in the FROM clause. +| *Production Usage* | This setting is to be used for forcing a desired join order that was not generated by default by +the optimizer only. It can be used as a workaround for query plans with inefficient join order. +| *Impact* | Because you are in effect forcing the optimizer to use a plan that joins the table in the order +specified in the FROM clause, the plan generated may not be the optimal one. +| *Level* | Query. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + + +<<< +[[mdam-scan-method]] +== MDAM_SCAN_METHOD + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Enables or disables the Multi-Dimensional Access Method. +| *Values* | *'ON'* MDAM is considered. + +*'OFF'* MDAM is disabled. + + + +The default value is *'ON'*. +| *Usage* | In certain situations, the optimizer might choose MDAM inappropriately, causing poor performance. +In such situations you may want to turn MDAM OFF for the query it is effecting. +| *Production Usage* | Not applicable. +| *Impact* | Table scans with predicates on non-leading clustering key column(s) could benefit from MDAM access +method if the leading column(s) has a small number of distinct values. Turning MDAM off results in a longer scan time for such queries. +| *Level* | Set this CQD at the query level when MDAM is not working efficiently for a specific query. However, +there may be cases (usually a defect) where a larger set of queries is being negatively impacted by MDAM. In those cases you may want +to set it at the service or system level. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[merge-joins]] +== MERGE_JOINS + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Determines if Merge Join is considered by the optimizer to generate an execution plan. +| *Values* | *'ON'* Merge Join is considered. + +*'OFF'* Merge Join is disabled. + + + +The default value is *'ON'*. +| *Usage* | Use this CQD when you want to force a query plan not to use Merge Joins. This is useful as a workaround +for query plans with very expensive sorts for Merge Joins. Turning Merge Join OFF also has the advantage of reducing the query compile time. +| *Production Usage* | Merge Join is an efficient join implementation strategy if the physical schema was designed to take advantage +of it. For example, large tables are physically ordered based on the most frequently joined column(s). +| *Impact* | Turning Merge Join OFF may result in the optimizer not considering potentially efficient query plans, +for queries with large joins on tables that are physically ordered by the join column(s). Turning Merge Join ON causes an increase in compile +time because the optimizer now has to consider many more join options. +| *Level* | Set this CQD at the query level when a Merge Join is not working efficiently for a specific query. However, +there may be cases (usually a defect) where a larger set of queries is being negatively impacted by Merge Joins. In those cases you may want to +set it at the service or system level. +| *Conflicts/Synergies* | Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins). +This may result in the compiler failing to generate query plans. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[nested-joins]] +== NESTED_JOINS + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Determines if Nested Join is considered by the optimizer to generate an execution plan. +| *Values* | *'ON'* Nested Join is considered. + +*'OFF'* Nested Join is disabled. + + + +The default value is *'ON'*. +| *Usage* | Use this CQD when you want to force a query plan not to use Nested Joins. This is useful +as a workaround for query plans with very expensive Nested Joins, which may occur if the optimizer fails to estimate the +cost of a Nested Join correctly. +| *Production Usage* | Nested Join is an important join implementation strategy for many complex queries. It is +recommended not to turn Nested Join OFF. It should only be used to force a query plan for a particular query on an exception basis. +| *Impact* | Turning Nested Join OFF may result in inefficient query plans for certain type of queries, +such as light workloads and star join queries. +| *Level* | Query. +| *Conflicts/Synergies* | Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge Joins). +This may result in the compiler failing to generate query plans. +| *Real Problem Addressed* | The problem of inefficient Nested Joins can be better handled using a higher degree of query plan +robustness as set by the ROBUST_QUERY_OPTIMIZATION CQD. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[optimization-level]] +== OPTIMIZATION_LEVEL + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Controls the optimizer resources and time spent for optimizing a query plan, with level 0 indicating +the least amount of optimization effort and level 5 indicating the most. Lower optimization levels produce lower plan quality with +minimal compile time, while higher optimization levels cause the compiler to spend more compilation time to produce better plan quality. +| *Values* | '0', '2', '3', '5' +The default value is *'3'*. +| *Usage* | Reduce the optimization level when compile time is longer than desired and queries have relatively small +execution cost and are simple in structure. +| *Production Usage* | Use this CQD only as a workaround for queries with unacceptable compile time or plan quality. +| *Impact* | Lowering the optimization level below the system default may result in inefficient query execution plans. +Increasing the optimization level over the system default may result in very high compile time for complex queries. +| *Level* | Query. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[parallel-num-esps]] +== PARALLEL_NUM_ESPS + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Controls the maximum number of parallel ESPs that work on a particular operation, like a join. +| *Values* | Unsigned Integer: The maximum number of ESPs that should be used for a particular operation. +The value must be less than the number of nodes in the cluster. + + + +*'SYSTEM'*: The compiler calculates the number of ESPs to be used. + + + +The default value is *'SYSTEM'*. +| *Usage* | Used to control the maximum degree of parallelism for a query. This could be useful to limit the +number of resources (node and memory) any single query can use. +| *Production Usage* | Not applicable. +| *Impact* | Lowering the value of this CQD can increase the throughput of high concurrency small and medium +query workloads, but has the potential disadvantage of increasing the elapsed time of some of the long-running queries. +| *Level* | Service. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[risk-premium-nj]] +== RISK_PREMIUM_NJ + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Influences the optimizer to choose other types of joins over nested joins, by making a nested join plan relatively more expensive. +| *Values* | Any positive fractional value. + + + +The default value is *'1.2'*. +| *Usage* | Review <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> before considering the use of this CQD. + + + +The default setting indicates that a nested join plan must be 20% cheaper before it is allowed to win over competing safer (hash) join plans. +A setting of 1.0 means no handicap for nested joins. A setting of 5.0 means a nested join must be 400% cheaper before it is allowed to win over +competing hash join plans. + + + +If it is determined that the optimizer is using nested joins often enough where these plans are resulting in poor performance, then this CQD +may be used to influence the optimizer to consider another join instead, such as a hash join, in some of those cases. + + + +NESTED_JOINS OFF could turn nested joins off completely. However, there are many cases where nested joins do provide better performance than hash +joins, and turning them off completely may negatively impact the performance of queries that can do a lot better with nested joins. +| *Production Usage* | Not applicable. +| *Impact* | Specifying a risk premium insures against nested joins being chosen when they should not have been. However, +this can also result in nested joins not being chosen where the cardinality estimation was in fact accurate and a nested join could have +performed better. Therefore, this setting should be used with care in order to get robustness with a net gain in performance. +| *Level* | Any. There may be cases where there are different applications or workloads that might benefit from this CQD more +than other workloads. In such cases this could be used at the Service level. +| *Conflicts/Synergies* | <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> is a CQD that provides a robust query setting across the board, +influencing the nested join risk premium as well. It is advisable that you use that setting instead to influence plans, unless they are specifically +addressing nested join issues and need to use this setting independent of that CQD. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[risk-premium-serial]] +== RISK_PREMIUM_SERIAL + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Influences the optimizer to choose a parallel plan over a serial plan, by making a serial plan relatively more expensive. +| *Values* | Any positive fractional value. + + + +The default value is *'1.2'*. +| *Usage* | Review <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> before considering the use of this CQD. + + + +The default setting means that a serial plan must be 20% cheaper before it is allowed to win over competing parallel plans. +A setting of 1.0 means no handicap for serial plans. A setting of 2.0 means a serial plan must be 100% cheaper before it is +allowed to win over competing parallel plans. + + + +If it is determined that the optimizer is using serial plans often enough where these plans are resulting in poor performance, then this CQD +may be used to influence the optimizer to consider parallel plans instead in some of those cases. + +| *Production Usage* | Not applicable. +| *Impact* | Specifying a risk premium insures against serial plans being chosen when they should not have been. +However, this can also result in serial plans not being chosen where the cardinality estimation was in fact accurate and a serial plan +could have performed better. Therefore, this setting should be used with care in order to get robustness with a net gain in performance. +| *Level* | Any. There may be cases where there are different applications or workloads that might benefit from this +CQD more than other workloads. In such cases this could be used at the Service level. +| *Conflicts/Synergies* | <<robust-query-optimization,ROBUST_QUERY_OPTIMIZATION>> is a CQD that provides a robust query setting across the board, +influencing the serial plan risk premium as well. It is advised that you use that setting instead to influence plans, unless they are specifically +addressing serial plan issues and need to use this setting independent of that CQD. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[robust-query-optimization]] +== ROBUST_QUERY_OPTIMIZATION + +[cols="25%h,75%a"] +|=== +| *Category* | Influence Query Plans +| *Description* | Provides a simpler way to influence the optimizer's choice of query plans. The optimizer chooses query plans +based on cardinality estimates (the number of result rows estimated at each step of a query execution plan). Actual cardinalities encountered +at query execution often differ from estimates. The optimizer considers risky choices and exacts risk premiums before it chooses a plan that +is inherently sensitive to cardinality estimation errors. Risky choices include: + + + +*Nested joins*: Can be excellent data reducers but they can also result in extremely long-running queries when their outer table +cardinality is grossly underestimated. + + + +*Serial plans*: Are an excellent choice because they use the least resources when processing low data volumes. But they can also result +in very long-running queries when actual cardinalities greatly exceed estimates. + + + +*Complete sharing of ESP partitioning schemes*: Correct parallel processing requires partitioning the data across ESP instances using a +partitioning scheme usually based on the largest table's partitioning keys, join columns, and group by columns. Complete sharing of ESP +partitioning schemes minimizes the overhead of runtime repartitioning. But, it can also result in very long-running queries if the +"least common denominator" partitioning scheme results in a few active ESPs doing most of the work. This can happen when repartitioning +is being done on a very low unique entry count attribute. For example, gender. + + + +ROBUST_QUERY_OPTIMIZATION can be used to influence the premiums associated with these risky plans and thereby overall plan quality and +performance for your specific workloads. +| *Values* | +*'MIN'*: No risk premium. + +*'HIGH'* and *'MAXIMUM'*: Higher risk premium. + +*'SYSTEM'*: Safe risk premium. + + + +The default value is *'SYSTEM'*. +| *Usage* | +MAXIMUM tells the optimizer to make the safest choice of query plans. This means: + + + +• RISK_PREMIUM_NJ is set to 5.0: nested join must be 400% cheaper before it can win over competing (hash) join plans. + +• RISK_PREMIUM_SERIAL is set to 2.0: serial plan must be 100% cheaper before it can win over competing parallel plans. + +• PARTITIONING_SCHEME_SHARING is set to 2: no partition scheme sharing between adjacent ESP fragments. + + + +HIGH tells the optimizer to make a safer choice of query plans. This means: + + + +• RISK_PREMIUM_NJ is set to 2.5: nested join must be 150% cheaper before it can win over competing (hash) join plans. + +• RISK_PREMIUM_SERIAL is set to 1.5: serial plan must be 50% cheaper before it can win over completing parallel plans. + +• PARTITIONING_SCHEME_SHARING is set to 1: subset sharing of partition schemes between adjacent ESP fragments. + + + +SYSTEM tells the optimizer to make a safe choice of query plans. This means: + + + +• RISK_PREMIUM_NJ is set to 1.2: nested join must be 20% cheaper before it can win over competing (hash) join plans. + +• RISK_PREMIUM_SERIAL is set to 1.2: serial plan must be 20% cheaper before it can win over completing parallel plans. + +• PARTITIONING_SCHEME_SHARING is set to 1: subset sharing of partition schemes between adjacent ESP fragments. + + + +MIN tells the optimizer to believe its cardinality estimates are always correct when choosing query plans. For example, don't apply any risk premium for risky operations. This means: + + + +• RISK_PREMIUM_NJ is set to 1.0: nested join can win over competing (hash) join plans purely based on cost & cardinality estimates. + +• RISK_PREMIUM_SERIAL is set to 1.0: serial plan can win over completing parallel plans purely based on cost & cardinality estimates. + +• PARTITIONING_SCHEME_SHARING is set to 0: complete sharing of partition schemes between adjacent ESP fragments. + + + +If histograms are accurate and the queries are relatively simple, then you could choose a lower robustness setting. +In complex query environments where queries could end up processing large amounts of data, you should consider higher settings. + + + +If you notice that when queries are not performing well it is due to either nested join plans, serial plans, or reduced parallelism, +then you could consider increasing risk premiums to see if you can get overall better performance. +| *Production Usage* | It is best to try out different options to achieve best overall performance in a test environment before +implementing the changed settings in a production environment. +| *Impact* | Specifying a risk premium insures against nested joins or serial plans being chosen when they should not +have been. However, this can also result in such plans not being chosen where the cardinality estimation was in fact accurate and such +plans could have performed better. So this setting should be used with care in order to get robustness with a net gain in performance. +| *Level* | Any. There may be cases where there are different applications or workloads that might benefit from this +CQD more than other workloads. In such cases this could be used at the Service level. +| *Conflicts/Synergies* | This conflicts with the <<risk-premium-nj,RISK_PREMIUM_NJ>>, <<risk-premium-serial,RISK_PREMIUM_SERIAL>>, +and <<partitioning-scheme-sharing,PARTITIONING_SCHEME_SHARING>> settings. Use this CQD when possible. Use the risk premium settings rarely, +when specific premiums need to be set differently to address specific issues. + + + +If overall this CQD is working well but you have outliers, such as poor nested join plans or inappropriate serial plans, then you could +use the individual CQDs at a finer granularity, such as at a query level, to get better plans. +| *Real Problem Addressed* | Sometimes the cardinality underestimation, compared to the actual row counts, resulting in a nested join +or serial plan being chosen when it shouldn't have been, may be due to not enough, or inaccurate, histogram statistics information available +to the optimizer. So, first and foremost, histogram statistics should be kept up to date along with the multi-column statistics that the +optimizer may warn about. However, cardinality underestimations may still happen at higher levels of an execution plan. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[skew-explain]] +== SKEW_EXPLAIN + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Turns on the reporting of anti-skew join plan details in EXPLAIN or EXPLAIN OPTIONS 'f'. +| *Values* | +*'OFF'*: Disables the use of SKEW_EXPLAIN. + +*'ON'*: Enables skew information in EXPLAIN. + + + +The default value is *'OFF'*. +| *Usage* | Not applicable. +| *Production Usage* | Not applicable. +| *Impact* | Query plans are not changed by this CQD. +| *Level* | Any. +| *Conflicts/Synergies* | Allows additional information to be displayed in explain plans. It has no impact on query plans. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[skew-rowcount-threshold]] +== SKEW_ROWCOUNT_THRESHOLD + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | The optimizer looks for skewed values and address that skew if the number of rows in the table exceeds this threshold. +| *Values* | +*'n'*: where n is the number of rows + + + +Default: *'1000000'* +| *Usage* | Skew can occur either in a nested join or a hash join. Currently, such skew is typically handled for the outer +table of a join. The outer table is identified by the Explain plan. + + + +The first indication is that there is a performance problem caused by skew that is not addressed by the compiler. A skew can be detected by +observing the imbalanced use of node cycles during query execution or by observed table skew. + + + +The default setting has been chosen to handle most skew values that are worth worrying about. That is, in other cases there may be skew but +the impact on total query execution may be minimal. However, there could be cases where this is not true. + + + +If you detect or suspect that a performance issue is caused by skew issues, then you need to look at the cardinality of the table with skew +(typically, the outer table in a join) to determine whether the table has fewer rows than defined by this CQD (default: 1 million rows.) +If the table has fewer rows than the CQD setting, then set this CQD to a value smaller than the number of rows in that table. + + + +If changing the setting addresses the performance problem, then the skew has been addressed. You can also examine whether the optimizer +has addressed the skew issue by turning on SKEW_EXPLAIN EXPLAIN of the plan and then run the EXPLAIN statement. + + + +If you find that you need to change the setting of this CQD to a value other than the default value, then please file a case providing +information about the table skew, the query, and the value that worked. Filing a case for this situation helps us tune the default value further. +| *Production Usage* | See usage discussion above. Use this CQD with care. +| *Impact* | A lower setting allows more skews to be detected and addressed. However, compile time is increased. +| *Level* | Any. +| *Conflicts/Synergies* | SKEW_EXPLAIN can be used as described in above. Also, <<skew-sensitivity-threshold,SKEW_SENSITIVITY_THRESHOLD>> +is relevant if this threshold allows a skew to be detected only. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[skew-sensitivity-threshold]] +== SKEW_SENSITIVITY_THRESHOLD + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Used to specify a threshold that determines whether a value in the join column is considered skewed. +| *Values* | +*'-1'*: Disables the use of skew buster. + +*'n'*: 'n' is a floating-point value greater than or equal to 0. + + + +The default value is *'-1'*. +| *Usage* | A value is considered skewed if its occurrence frequency is greater than the threshold value multiplied +by the average number of rows per processing node (CPU). That is, if the average row count equals the row count divided by the number +of processing nodes, then the frequency of the value is greater than the threshold multiplied by the average row count. + + + +A setting of _n_, where _n_ >= 0, indicates that the value should be considered as skewed if its occurrence frequency is greater +than _n_ times the average number of rows per processing node. + + + +If some small skew is suspected for hash joins during query execution (detected by observing spiked CPU busy usage), then try to lower +this setting. A default setting of 0.1 should eliminate most skews. Setting the CQD to a very large value, such as 10, is not recommended, +as it effectively turns off skew buster. +| *Production Usage* | Not applicable. +| *Impact* | Not applicable. +| *Level* | Any. +| *Conflicts/Synergies* | This CQD is only relevant if the <<skew-rowcount-threshold,SKEW_ROWCOUNT_THRESHOLD>> has been met. +The <<skew-rowcount-threshold,SKEW_ROWCOUNT_THRESHOLD CQD>> controls the row count of the table at which the optimizer looks for a skew. +| *Real Problem Addressed* | Skew is quite common in a real big-data application, and is effectively addressed by skew buster. +However, there may be design opportunities that could help address the problem as well. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[upd-ordered]] +== UPD_ORDERED + +[cols="25%h,75%"] +|=== +| *Category* | Influence Query Plans +| *Description* | Controls whether rows should be inserted, updated, or deleted in clustering key order. +| *Values* | +*'ON'*: The optimizer generates and considers plans where the rows are inserted, updated, or deleted in clustering key order. + +*'OFF'*: The optimizer does not generate plans where the rows must be inserted, updated, or deleted in clustering key order. + + + +The default value is *'ON'*. +| *Usage* | Inserting, updating or deleting rows in the clustering key order is most efficient and highly recommended. +Turning this CQD OFF may result in saving the data sorting cost but at the expense of having less efficient random I/O Insert/Update/Delete operations. +If you know that he data is already sorted in clustering key order, or is mostly in clustering key order, so that it would not result in random I/O, +you could set this CQD to OFF. +| *Production Usage* | Not applicable. +| *Impact* | If turned OFF, the system may perform large number of inefficient Random I/Os when performing Insert/Update/Delete operations. +| *Level* | Query. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/33d796d6/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc ---------------------------------------------------------------------- diff --git a/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc b/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc new file mode 100644 index 0000000..6f1c7c7 --- /dev/null +++ b/docs/cqd_reference/src/asciidoc/_chapters/runtime_controls.adoc @@ -0,0 +1,83 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + +[[runtime-controls]] += Runtime Controls + +This section describes CQDs that are used for runtime controls. + +[[last0-mode]] +== LAST0_MODE + +[cols="25%h,75%"] +|=== +| *Category* | Runtime Controls +| *Description* | Ensures that all parts of the query plan are executed but no rows are returned by the query. +| *Values* | *'ON'* or *'OFF'*. + + + +The default value is *'OFF'*. +| *Usage* | This setting provides a realistic measure of the query's performance, minus the cost/time of +returning the rows to the client. It is especially useful for testing the plans and performance of queries that return large result sets. +| *Production Usage* | Only use this CQD to assess the performance of a query. +| *Impact* | The query runs completely but no rows are returned. +| *Level* | Query. +| *Conflicts/Synergies* | Not to be confused with SELECT [LAST 0] which behaves the same way but does not guarantee that the plan +is the same as when you do not use the [LAST 0] clause in the query. +| *Real Problem Addressed* | Not applicable. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|=== + +<<< +[[query-limit-sql-process-cpu]] +== QUERY_LIMIT_SQL_PROCESS_CPU + +[cols="25%h,75%"] +|=== +| *Category* | Runtime Controls +| *Description* | Use to limit the amount of CPU time that a query is allowed to use in any one server process (MXESP) +including the NDCS server (MXOSRVR). Also includes the CPU time the query spends in the disk process (ESAM). $$$ + + + +If a query exceeds the limit, then an error is raised and the query is terminated. This is a way to limit the impact on the +system of a poorly written or badly optimized query. +| *Values* | +*'0'*: There is no limit. + +*Greater than *'0'* and up through *'2,147,483,583'*: The limit, in seconds, to how much CPU time a query is allowed. + + + +The default value is *'0'*. +| *Usage* | This setting helps you with queries that are poorly written or are badly optimized. A poorly written +query does not use predicates to limit the number of rows processed. A query that joins large tables without a predicate can have a +severe impact on the system. A badly optimized query can result from failure to maintain histograms. Typically, these are ad-hoc queries. + + + +These types of queries seldom run to completion, and are instead stopped after the problems that they cause to other users of the system are noticed. +| *Production Usage* | Not applicable. +| *Impact* | Use of this default can prevent any one query from using an unlimited amount of CPU time. +However, if the default is set too low, then even well behaved, useful queries fail to complete. +| *Level* | Service. +| *Conflicts/Synergies* | Not applicable. +| *Real Problem Addressed* | Use standard processed for creating high-quality queries, and procedures that ensure that table histograms are always current. +| *Introduced In Release* | Trafodion 1.3.0. +| *Deprecated In Release* | Not applicable. +|===
