db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r773294 [1/2] - in /db/derby/docs/trunk/src: ref/ tuning/
Date Sun, 10 May 2009 02:07:08 GMT
Author: bpendleton
Date: Sun May 10 02:07:07 2009
New Revision: 773294

URL: http://svn.apache.org/viewvc?rev=773294&view=rev
Log:
DERBY-4065: Provide documentation for XPLAIN style statistics processing

This change introduces an initial documentation set for the new XPLAIN
statistics feature (DERBY-2487). The documentation consists of changes
to the tuning and reference manuals. The tuning guide contains conceptual
background material and examples; the reference guide contains detailed
syntax and reference information for the new system procedures and the
XPLAIN database tables.


Added:
    db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_mode.dita   (with props)
    db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_schema.dita   (with props)
    db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_mode.dita   (with props)
    db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_schema.dita   (with props)
    db/derby/docs/trunk/src/ref/rref_xplain_tables.dita   (with props)
    db/derby/docs/trunk/src/ref/rrefsysxplain_resultset_timings.dita   (with props)
    db/derby/docs/trunk/src/ref/rrefsysxplain_resultsets.dita   (with props)
    db/derby/docs/trunk/src/ref/rrefsysxplain_scan_props.dita   (with props)
    db/derby/docs/trunk/src/ref/rrefsysxplain_sort_props.dita   (with props)
    db/derby/docs/trunk/src/ref/rrefsysxplain_statement_timings.dita   (with props)
    db/derby/docs/trunk/src/ref/rrefsysxplain_statements.dita   (with props)
    db/derby/docs/trunk/src/tuning/ctun_xplain_style.dita   (with props)
    db/derby/docs/trunk/src/tuning/ctun_xplain_tables.dita   (with props)
Modified:
    db/derby/docs/trunk/src/ref/refderby.ditamap
    db/derby/docs/trunk/src/tuning/ctundepth26674.dita
    db/derby/docs/trunk/src/tuning/tuningderby.ditamap

Modified: db/derby/docs/trunk/src/ref/refderby.ditamap
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/refderby.ditamap?rev=773294&r1=773293&r2=773294&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/refderby.ditamap (original)
+++ db/derby/docs/trunk/src/ref/refderby.ditamap Sun May 10 02:07:07 2009
@@ -353,6 +353,8 @@
 </topicref>
 <topicref href="rrefgetuseraccess.dita" navtitle="SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function">
 </topicref>
+<topicref href="rref_syscs_get_xplain_mode.dita" navtitle="SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function"></topicref>
+<topicref href="rref_syscs_get_xplain_schema.dita" navtitle="SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function"></topicref>
 </topicref>
 <topicref href="crefsqlbuiltinsystemprocedures.dita" navtitle="Built-in system procedures">
 <!--(Laura - March 2007) The topics in this section are intentionally not listed alphabetical order. They are listed by group of information. For example, the COMPRESS and INPLACE_COMPRESS topics are listed together; the IMPORT and EXPORT topics are listed together.-->
@@ -402,6 +404,8 @@
 </topicref>
 <topicref href="rrefsetstatstimingproc.dita" navtitle="SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure">
 </topicref>
+<topicref href="rref_syscs_set_xplain_schema.dita" navtitle="SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure"> </topicref>
+<topicref href="rref_syscs_set_xplain_mode.dita" navtitle="SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure"> </topicref>
 <topicref href="rrefsetuseraccess.dita" navtitle="SYSCS_UTIL.SYSCS_SET_USER_ACCESS stored procedure">
 </topicref>
 <topicref href="rrefupdatestatsproc.dita" navtitle="SYSCS_UTIL.SYSCS_UPDATE_STATISTICS stored procedure">
@@ -489,6 +493,14 @@
 </topicref>
 <topicref href="rrefsistabs24045.dita" navtitle="SYSVIEWS system table"></topicref>
 </topicref>
+<topicref href="rref_xplain_tables.dita" navtitle="XPLAIN style tables">
+<topicref href="rrefsysxplain_statements.dita" navtitle="SYSXPLAIN_STATEMENTS table"></topicref>
+<topicref href="rrefsysxplain_statement_timings.dita" navtitle="SYSXPLAIN_STATEMENT_TIMINGS table"></topicref>
+<topicref href="rrefsysxplain_resultsets.dita" navtitle="SYSXPLAIN_RESULTSETS table"></topicref>
+<topicref href="rrefsysxplain_resultset_timings.dita" navtitle="SYSXPLAIN_RESULTSET_TIMINGS table"></topicref>
+<topicref href="rrefsysxplain_scan_props.dita" navtitle="SYSXPLAIN_SCAN_PROPS table"></topicref>
+<topicref href="rrefsysxplain_sort_props.dita" navtitle="SYSXPLAIN_SORT_PROPS table"></topicref>
+</topicref>
 <topicref collection-type="family" href="rrefexcept16677.dita" navtitle="Derby exception messages and SQL states">
 <topicref href="rrefexcept71493.dita" navtitle="SQLState and error message reference">
 </topicref>

Added: db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_mode.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_mode.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_mode.dita (added)
+++ db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_mode.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,46 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rref_syscs_get_xplain_mode" xml:lang="en-us">
+<title>SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function</title>
+<refbody>
+<section><p>The <codeph>SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE</codeph> function
+returns the current xplain mode.</p><p>If
+the xplain mode is non-zero, then statements are not actually executed, but
+are just compiled, and their statistics recorded in the SYSXPLAIN_* database
+tables. If the xplain mode is zero (the default), then statements are
+executed normally.
+</p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+</section>
+<section><title>Syntax</title> <codeblock>SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE() RETURNS INTEGER</codeblock>
+</section>
+<section><title>Example</title>
+    <p>To determine the current value of the XPLAIN mode:</p>
+    <codeblock>
+        values syscs_util.syscs_get_xplain_mode();
+</codeblock></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_mode.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_schema.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_schema.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_schema.dita (added)
+++ db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_schema.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,50 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rref_syscs_get_xplain_schema" xml:lang="en-us">
+<title>SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function</title>
+<refbody>
+<section><p>The <codeph>SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA</codeph> function
+        returns the xplain schema for the connection.</p>
+    <p>The default xplain schema is empty,
+        so if the xplain style hasn't been set, the function returns the
+        empty string.
+        If the xplain schema has been set using
+        <codeph>SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA</codeph>, the function
+        returns the xplain schema that was set. If xplain schema is set to
+        a non-empty value, and runtime statistics are being captured, then
+        the runtime statistics will be stored into the SYSXPLAIN_* database
+        tables in that schema for later analysis.</p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+</section>
+<section><title>Syntax</title> <codeblock>SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA () RETURNS VARCHAR</codeblock>
+</section>
+<section><title>Example</title>
+    <p>To determine the current value of the XPLAIN schema:</p>
+    <codeblock>
+        values syscs_util.syscs_get_xplain_schema();
+</codeblock></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rref_syscs_get_xplain_schema.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_mode.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_mode.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_mode.dita (added)
+++ db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_mode.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,58 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!-- 
+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.
+-->
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rref_syscs_set_xplain_mode" xml:lang="en-us">
+<title>SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure</title>
+<refbody>
+    <section><p>When runtime statistics are being captured, you can control
+            the mode of processing using this procedure. When the xplain mode
+            is set to 1, statements are compiled and optimized, but not
+            executed; when the xplain mode is set to 0 (the default), statements
+            are compiled, optimized, and executed normally.</p>
+        <p>Note that xplain_mode only matters when xplain style has been
+            enabled, see the
+<xref href="rref_syscs_set_xplain_schema.dita#rref_syscs_set_xplain_schema">SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA</xref> system
+procedure for more information.</p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+</section>
+<section><title>Syntax</title> <codeblock>SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE(IN SMALLINT NOEXECUTE)</codeblock></section>
+<section><title>Example</title>
+    <p>To let 
+        <ph conref="../conrefs.dita#prod/productshortname"></ph>
+        explain a statement without executing it:</p>
+    <codeblock>
+        call syscs_util.syscs_set_runtimestatistics(1);
+        call syscs_util.syscs_set_xplain_schema('STATS');
+        call syscs_util.syscs_set_xplain_mode(1);
+
+        select country from countries;
+
+        call syscs_util.syscs_set_runtimestatistics(0);
+        call syscs_util.syscs_set_xplain_schema('');
+        call syscs_util.syscs_set_xplain_mode(0);
+</codeblock></section>
+</refbody>
+</reference>
+

Propchange: db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_mode.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_schema.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_schema.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_schema.dita (added)
+++ db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_schema.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,64 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!-- 
+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.
+-->
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rref_syscs_set_xplain_schema" xml:lang="en-us">
+<title>SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure</title>
+<refbody>
+    <section><p>This procedure allows you to request XPLAIN style processing
+            of runtime statistics. When XPLAIN style is used, the runtime
+            statistics are written to the SYSXPLAIN_* database tables, so
+            that you can analyze the statistics by running queries against
+            the tables.
+           <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+          <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+        </p></section>
+<section><p>Turn xplain style on by calling this procedure with a non-empty
+argument. Turn xplain style off by calling the procedure with an empty
+argument.</p></section>
+<section><p>The argument that you provide must be a legal schema name, and
+        you should use this argument to indicate the schema in which
+        runtime statistics should be captured. If the schema that you
+        specify does not already exist, it will be automatically created.
+        If the XPLAIN tables do not already exist in this schema, they
+        will be automatically created. Runtime statistics information about
+        statements executed in this session will then be captured into
+        these tables, until runtime statistics capturing is halted by either
+        calling <codeph>SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA</codeph> with
+        an empty argument or by calling
+        <codeph>SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)</codeph></p></section>
+<section><title>Syntax</title> <codeblock>SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA(IN VARCHAR(128) SCHEMA_NAME)</codeblock></section>
+<section><title>Example</title>
+    <p>To cause <ph conref="../conrefs.dita#prod/productshortname"></ph>
+        to record statistics about statement execution in
+        the SYSXPLAIN_* database tables in the schema named 'MY_STATS':</p>
+    <codeblock>
+        call syscs_util.syscs_set_runtimestatistics(1);
+        call syscs_util.syscs_set_xplain_schema('MY_STATS');
+
+        select country from countries;
+
+        call syscs_util.syscs_set_runtimestatistics(0);
+        call syscs_util.syscs_set_xplain_schema('');
+</codeblock></section>
+</refbody>
+</reference>
+

Propchange: db/derby/docs/trunk/src/ref/rref_syscs_set_xplain_schema.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rref_xplain_tables.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rref_xplain_tables.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rref_xplain_tables.dita (added)
+++ db/derby/docs/trunk/src/ref/rref_xplain_tables.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,53 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!-- 
+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.
+-->
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rref_xplain_tables" xml:lang="en-us">
+<title>XPLAIN style tables</title>
+<refbody>
+<section><p><ph conref="../conrefs.dita#prod/productshortname"></ph> 
+optionally creates database tables to hold statistics
+information captured using XPLAIN style. You can have zero, one, or many
+sets of these tables; each set of tables is stored in a separate
+schema. The schema which is used for capturing statement execution
+information is specified using the
+<xref href="rref_syscs_set_xplain_schema.dita#rref_syscs_set_xplain_schema">SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA</xref>
+system procedure</p></section>
+<section><p>You can query these tables to analyze the behavior of
+statement execution.</p></section>
+<section><p>All of the above system tables reside in the schema which
+you specified. Because this
+is not the default schema, qualify all queries accessing the system tables
+with the schema name.</p></section>
+<section><p>You can create the schema and tables ahead of time if you wish,
+        but usually it is easier to let 
+        <ph conref="../conrefs.dita#prod/productshortname"></ph>
+        automatically create the schema and
+the tables for you. You can capture multiple sets of data into the same
+tables, or you can specify a different schema each time.</p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+</section>
+</refbody>
+</reference>
+

Propchange: db/derby/docs/trunk/src/ref/rref_xplain_tables.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rrefsysxplain_resultset_timings.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsysxplain_resultset_timings.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsysxplain_resultset_timings.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsysxplain_resultset_timings.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,181 @@
+<?xml version="1.0" encoding="utf-8"?>
+ 
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rrefsysxplain_resultset_timings" xml:lang="en-us">
+<title>SYSXPLAIN_RESULTSET_TIMINGS system table</title>
+<refbody>
+    <section><p>This table captures timing information about result set accesses
+            which occurred during statements that were
+            executed using RUNTIMESTATISTICS with XPLAIN style (see the
+            RUNTIMESTATISTICS section in the
+            <ph conref="../conrefs.dita#pub/citref"></ph> for more information
+            on how to configure this).
+            Note that statistics timing must be configured by calling
+            SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING().
+            Each row in this table describes various timing information
+            for this particular result set in this particular statement.
+            Rows in this table are typically joined with rows in
+            SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis.
+        For example:</p>
+    <codeblock>
+        select rs.op_identifier, rst.execute_time
+        from my_stats.sysxplain_resultsets rs,
+             my_stats.sysxplain_resultset_timings rst
+        where rs.stmt_id = ? and
+              rs.timing_id = rst.timing_id
+        order by rst.execute_time desc
+    </codeblock>
+            <p>Rows in this table are added
+            automatically when 
+            <ph conref="../conrefs.dita#prod/productshortname"></ph>
+            has been configured appropriately. The
+            rows remain in the table until you delete them or drop the table.
+                </p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+                <p>   <table
+pgwide="1" frame="all">
+<tgroup cols="5" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="24*"/>
+<colspec colname="2" colnum="2" colwidth="23*"/><colspec colname="3" colnum="3"
+colwidth="11*"/><colspec colname="4" colnum="4" colwidth="14*"/><colspec colname="5"
+colnum="5" colwidth="29*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Column Name</entry>
+<entry align="left" colname="2" valign="bottom">Type</entry>
+<entry align="left" colname="3" valign="bottom">Length</entry>
+<entry align="left" colname="4" valign="bottom">Nullability</entry>
+<entry align="left" colname="5" valign="bottom">Contents</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">TIMING_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A unique ID for this particular row. This column can
+    be used to join against the TIMING_ID column in the
+SYSXPLAIN_RESULTSETS table.</entry>
+</row>
+<row>
+<entry colname="1">CONSTRUCTOR_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The time it took to construct this instance of this
+result set, in milliseconds.</entry>
+</row>
+<row>
+<entry colname="1">OPEN_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The time it took to open this instance of this
+    result set, in milliseconds. Note that if this result set was opened
+    multiple times, this column is the sum of all the individual open times.
+</entry>
+</row>
+<row>
+<entry colname="1">NEXT_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The accumulated time for all the calls to fetch the
+    next row from this result set, in milliseconds,
+    for all the opens of this result set.</entry>
+</row>
+<row>
+<entry colname="1">CLOSE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The time it took to close this instance of the result
+set, in milliseconds.</entry>
+</row>
+<row>
+<entry colname="1">EXECUTE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The time for all operations performed by this
+    result set, excluding the time taken by all the children result sets of
+    this result set, in milliseconds.
+</entry>
+</row>
+<row>
+<entry colname="1">AVG_NEXT_TIME_PER_ROW</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">If there was at least one row returned from this result
+    set, then this value is the NEXT_TIME value divided by the number of rows
+    returned from this result set, which thus is the average time, in
+milliseconds, that it took to retrieve a row from this result set.</entry>
+</row>
+<row>
+<entry colname="1">PROJECTION_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This value is NULL unless this result set is a
+    PROJECTION result set, in which case this column contains the time,
+    in milliseconds, that it took to perform projection of columns from the
+rows in this result set.</entry>
+</row>
+<row>
+<entry colname="1">RESTRICTION_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This value is NULL unless this result set is a
+PROJECTION result set, in which case this column contains the time,
+in milliseconds, that it took to perform restriction of rows from the
+rows in this result set.</entry>
+</row>
+<row>
+<entry colname="1">TEMP_CONG_CREATE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">For result sets which involve a materialization of
+    a temporary intermediate result set, this value is the time it took to
+    create the materialized result set, in milliseconds. I think this
+    may occur with hash joins where the number of rows in the intermediate
+result is too large to hold in memory?</entry>
+</row>
+<row>
+<entry colname="1">TEMP_CONG_FETCH_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Similar to TEMP_CONG_CREATE_TIME, this value is
+    the time it took to retrieve rows from the materialized result set, in
+milliseconds.</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rrefsysxplain_resultset_timings.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rrefsysxplain_resultsets.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsysxplain_resultsets.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsysxplain_resultsets.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsysxplain_resultsets.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,322 @@
+<?xml version="1.0" encoding="utf-8"?>
+ 
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rrefsysxplain_resultsets" xml:lang="en-us">
+<title>SYSXPLAIN_RESULTSETS system table</title>
+<refbody>
+    <section><p>This table captures information about each result set which
+            is part of a statement that has been
+            executed using RUNTIMESTATISTICS with XPLAIN style (see the
+            RUNTIMESTATISTICS section in the
+            <ph conref="../conrefs.dita#pub/citref"></ph> for more information
+            on how to configure this).
+        </p>
+        <p>
+            Most statements have at least one result set associated with
+            them, and some complex statements may have many result sets
+            associated with them. Some statements, for example DDL statements
+            such as CREATE TABLE, have no result sets associated with them.
+        </p>
+        <p>
+            Each row in this table describes a particular result set used
+            by a particular statement. Rows
+            in this table are typically joined with rows in
+            SYSXPLAIN_STATEMENTS during analysis:</p>
+        <codeblock>
+            select st.stmt_text, rs.op_identifier 
+              from my_stats.sysxplain_statements st
+              join my_stats.sysxplain_resultsets rs
+                on st.stmt_id = rs.stmt_id
+        </codeblock>
+            <p>Rows in this table are added
+                automatically when 
+                <ph conref="../conrefs.dita#prod/productshortname"></ph>
+                has been configured appropriately. The
+            rows remain in the table until you delete them or drop the table.
+                </p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+                <p>   <table
+pgwide="1" frame="all">
+<tgroup cols="5" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="24*"/>
+<colspec colname="2" colnum="2" colwidth="23*"/><colspec colname="3" colnum="3"
+colwidth="11*"/><colspec colname="4" colnum="4" colwidth="14*"/><colspec colname="5"
+colnum="5" colwidth="29*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Column Name</entry>
+<entry align="left" colname="2" valign="bottom">Type</entry>
+<entry align="left" colname="3" valign="bottom">Length</entry>
+<entry align="left" colname="4" valign="bottom">Nullability</entry>
+<entry align="left" colname="5" valign="bottom">Contents</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">RS_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A unique identifier for this particular row.</entry>
+</row>
+<row>
+<entry colname="1">OP_IDENTIFIER</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">128</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A code indicating what type of result set these statistics
+    are for.
+    Common result set types include: TABLESCAN, INDEXSCAN, PROJECTION, etc.
+Should I try to list all the result set types here?</entry>
+</row>
+<row>
+<entry colname="1">OP_DETAILS</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">256</entry>
+<entry colname="4">true</entry>
+<entry colname="5">Additional string information which varies for each
+    different type of result set. Interpreting this information currently
+    requires reading the
+    <ph conref="../conrefs.dita#prod/productshortname"></ph>
+    source code to know what values are being displayed here.</entry>
+</row>
+<row>
+<entry colname="1">NO_OPENS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of times this result set was opened during
+execution of the containing statement.</entry>
+</row>
+<row>
+<entry colname="1">NO_INDEX_UPDATES</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The number of index updates performed by this
+    result set. This value is NULL for result sets used by queries, but may have
+    a non-zero value for modification statements such as INSERT, UPDATE, or
+    DELETE.
+</entry>
+</row>
+<row>
+<entry colname="1">LOCK_MODE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">2</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating the locking level that was used
+    for this result set, as follows: 'EX'=Exclusive table-level locking,
+    'SH'=Share table-level locking, 'IX'=Exclusive row-level locking,
+    'IS'=Share row-level locking.
+</entry>
+</row>
+<row>
+<entry colname="1">LOCK_GRANULARITY</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating the locking granularity that
+    was used for this result set, as follows: 'T'=Table-level locking,
+'R'=Row-level locking.</entry>
+</row>
+<row>
+<entry colname="1">PARENT_RS_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">true</entry>
+<entry colname="5">The result sets for a particular statement are
+    arranged in a parent-child tree structure. The output rows from one
+    result set are delivered as the input rows to its parent. This column
+    stores the identifier of the parent result set. For the outermost
+    result set in a particular statement, this column is NULL. Note that sometimes
+    there are multiple result sets with the same parent result set (that is,
+    some nodes have multiple children): for example, a UNION result set will have
+    two child result sets, representing the two sets of rows which are UNIONed
+together.</entry>
+</row>
+<row>
+<entry colname="1">EST_ROW_COUNT</entry>
+<entry colname="2">DOUBLE</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The optimizer's estimate of the total number of rows
+    for this result set.
+</entry>
+</row>
+<row>
+<entry colname="1">EST_COST</entry>
+<entry colname="2">DOUBLE</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">The optimizer's estimated cost for this result set.
+    The value indicates the number of milliseconds that the optimizer
+    estimates it will take to process this result set.
+</entry>
+</row>
+<row>
+<entry colname="1">AFFECTED_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This column is only non-null for INSERT, UPDATE, and
+    DELETE result sets. For those result sets, this column holds the number of
+rows which were inserted, updated, or deleted, respectively.</entry>
+</row>
+<row>
+<entry colname="1">DEFERRED_ROWS</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">The column is only non-null for INSERT, UPDATE, and
+    DELETE result sets. For those result sets, this column holds 'Y' if the
+    INSERT/UPDATE/DELETE is being performed using deferred change semantics,
+    and holds 'N' otherwise. I think that deferred change semantics are used
+    when there is self-referencing going on, and we must avoid the
+    "Halloween" problem of processing the rows multiple times.
+</entry>
+</row>
+<row>
+<entry colname="1">INPUT_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This column is used for SORT, AGGREGATE, and GROUPBY
+    result sets, and indicates the number of rows that were input to the
+result set, and thus were sorted by the sorter.</entry>
+</row>
+<row>
+<entry colname="1">SEEN_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">For join and set nodes, this is the number of rows
+    seen by the "left" side of the processing. For aggregate, group, sort,
+    normalize, materialize, and certain other nodes, this is the number of
+    rows seen.
+</entry>
+</row>
+<row>
+<entry colname="1">SEEN_ROWS_RIGHT</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">For join and set nodes, this is the number of rows
+    seen by the "right" side of the processing. For example, in the statement
+    <codeblock>
+        select country from countries where region = 'Central America' 
+        union 
+        select country from countries where region = 'Africa'
+</codeblock>, the UNION result set has SEEN_ROWS = 6 and SEEN_ROWS_RIGHT=19.
+</entry>
+</row>
+<row>
+<entry colname="1">FILTERED_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This column holds the number of rows which were
+eliminated from the result set during processing.</entry>
+</row>
+<row>
+<entry colname="1">RETURNED_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This column holds the number of rows which were
+    returned by the result set to its caller. Generally speaking, the number
+    of returned rows is the number of rows INPUT or SEEN, minus the number
+of rows FILTERED.</entry>
+</row>
+<row>
+<entry colname="1">EMPTY_RIGHT_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">This column is used for left outer joins, and, if not null,
+    holds the number of empty rows which had to be constructed because no
+existing rows met the join criteria.</entry>
+</row>
+<row>
+<entry colname="1">INDEX_KEY_OPT</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">This column records when the Index Key Optimization is
+    used. The Index Key Optimization is a special optimization which occurs when
+    a query references the MAX or MIN value of a column which happens to have an
+    index, and so the MIN or MAX computation can be performed by fetching the
+    first or last, respectively, entry in the index, as in:
+    <codeblock>
+        select max(country_iso_code) from countries
+</codeblock></entry>
+</row>
+<row>
+<entry colname="1">SCAN_RS_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">true</entry>
+<entry colname="5">If this resultset is one of the resultset types which
+    performs a scan of a table or index, this column contains the id value
+    which identifies the particular row in SYSXPLAIN_SCAN_PROPS that
+    describes the statistics related to the scan behavior.</entry>
+</row>
+<row>
+<entry colname="1">SORT_RS_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">true</entry>
+<entry colname="5">If this resultset is one of the resultset types which
+    performs a sort of a table or index, this column contains the id value
+    which identifies the particular row in SYSXPLAIN_SORT_PROPS that
+    describes the statistics related to the sort behavior. The most common
+    situations which involve sorting of the data are when processing the
+ORDER BY and GROUP BY clauses.</entry>
+</row>
+<row>
+<entry colname="1">STMT_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">This column will contain the id value which identifies
+    the particular statement for which this result set was executed. Note that
+    there may be multiple result sets executed for a single statement, so a join
+    between the SYSXPLAIN_STATEMENTS table and the SYSXPLAIN_RESULTSETS table
+may retrieve multiple rows.</entry>
+</row>
+<row>
+<entry colname="1">TIMING_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">true</entry>
+<entry colname="5">If statistics timings were not being captured, this column
+    will have a NULL value. If statistics timings were being captured, this
+    column will contain the id value which can be used as a foreign key to join
+    with the SYSXPLAIN_RESULTSET_TIMINGS row which has the timing information
+for this resultset.</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rrefsysxplain_resultsets.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rrefsysxplain_scan_props.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsysxplain_scan_props.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsysxplain_scan_props.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsysxplain_scan_props.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,251 @@
+<?xml version="1.0" encoding="utf-8"?>
+ 
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rrefsysxplain_scan_props" xml:lang="en-us">
+<title>SYSXPLAIN_SCAN_PROPS system table</title>
+<refbody>
+    <section><p>This table captures information about table/index access
+            which occurred during statements that were
+            executed using RUNTIMESTATISTICS with XPLAIN style (see the
+            RUNTIMESTATISTICS section in the
+            <ph conref="../conrefs.dita#pub/citref"></ph> for more information
+            on how to configure this).
+            Each row in this table describes a single table/index scan for
+            a particular result set used by a particular statement. Rows
+            in this table are typically joined with rows in
+            SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis:</p>
+        <codeblock>
+             select st.stmt_text, sp.no_visited_rows 
+               from my_stats.sysxplain_scan_props sp, 
+                    my_stats.sysxplain_resultsets rs, 
+                    my_stats.sysxplain_statements st 
+               where st.stmt_id = rs.stmt_id and 
+                    rs.scan_rs_id = sp.scan_rs_id and 
+                    rs.op_identifier = 'TABLESCAN' and 
+                    sp.scan_object_name = 'COUNTRIES'
+         </codeblock>
+            <p>Rows in this table are added
+                automatically when 
+                <ph conref="../conrefs.dita#prod/productshortname"></ph>
+                has been configured appropriately. The
+            rows remain in the table until you delete them or drop the table.
+                </p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+                <p>   <table
+pgwide="1" frame="all">
+<tgroup cols="5" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="24*"/>
+<colspec colname="2" colnum="2" colwidth="23*"/><colspec colname="3" colnum="3"
+colwidth="11*"/><colspec colname="4" colnum="4" colwidth="14*"/><colspec colname="5"
+colnum="5" colwidth="29*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Column Name</entry>
+<entry align="left" colname="2" valign="bottom">Type</entry>
+<entry align="left" colname="3" valign="bottom">Length</entry>
+<entry align="left" colname="4" valign="bottom">Nullability</entry>
+<entry align="left" colname="5" valign="bottom">Contents</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">SCAN_RS_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A unique identifer for this particular row. Referenced
+by the foreign key SCAN_RS_ID in SYSXPLAIN_RESULTSETS</entry>
+</row>
+<row>
+<entry colname="1">SCAN_OBJECT_NAME</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">128</entry>
+<entry colname="4">true</entry>
+<entry colname="5">The name of the object being scanned. If this is a
+    scan of a table or index, the table name or index name appears here.
+    If this is a scan of the internal index created for a constraint, the
+    constraint name appears here.
+    For complex join queries, the object being scanned may be an intermediate
+    result, in which case a description such as 'Temporary HashTable' appears.
+</entry>
+</row>
+<row>
+<entry colname="1">SCAN_OBJECT_TYPE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A code indicating the type of object being scanned.
+Codes include 'T' for Table, 'I' for Index, and 'C' for Constraint.</entry>
+</row>
+<row>
+<entry colname="1">SCAN_TYPE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">8</entry>
+<entry colname="4">false</entry>
+<entry colname="5">The type of scan being performed. Scan types include
+'HEAP', 'BTREE', and 'SORT'.</entry>
+</row>
+<row>
+<entry colname="1">ISOLATION_LEVEL</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">6</entry>
+<entry colname="4">true</entry>
+<entry colname="5">The isolation level being used for this scan.
+    Isolation levels are identified by a code: 'RU' for Read Uncommitted,
+    'RC' for Read Committed, 'RR' for Repeatable Read,
+    and 'SE' for Serializable.</entry>
+</row>
+<row>
+<entry colname="1">NO_VISITED_PAGES</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of database pages that this scan touched.
+For btree scans this number only includes the leaf pages visited.</entry>
+</row>
+<row>
+<entry colname="1">NO_VISITED_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of database rows that were examined by this scan.
+    This number includes all rows, including those rows marked deleted,
+    those rows that don't meet qualification, and those rows which were
+returned by the scan.</entry>
+</row>
+<row>
+<entry colname="1">NO_QUALIFIED_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of rows that satisfied the qualifiers for this scan.</entry>
+</row>
+<row>
+<entry colname="1">NO_VISITED_DELETED_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of the database rows that were examined by this
+scan which were found to be rows that were marked deleted.</entry>
+</row>
+<row>
+<entry colname="1">NO_FETCHED_COLUMNS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of columns that were fetched from each qualifying row.</entry>
+</row>
+<row>
+<entry colname="1">BITSET_OF_FETCHED_COLUMNS</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">512</entry>
+<entry colname="4">true</entry>
+<entry colname="5">Description of the columns which were fetched from each qualifying row.</entry>
+</row>
+<row>
+<entry colname="1">BTREE_HEIGHT</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">For a scan of type BTREE, this column holds the height
+    of the BTREE index. The typical height of a BTREE is 2-4; BTREE heights
+    larger than this should only be seen with very large indexes. A tree
+    with one page has a height of 1. Total number of pages visited in a
+    scan of a BTREE should be (BTREE_HEIGHT - 1 + NO_VISITED_PAGES). For an
+    extremely small BTREE, the btree height may be negative (-1).
+    For other types of scans, this column is NULL.
+</entry>
+</row>
+<row>
+<entry colname="1">FETCH_SIZE</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">I think this is the number of pages fetched at a time
+    when the scan is retrieving pages from disk? I expected this to be 16
+    when doing a TABLESCAN, and 1 when doing an INDEXSCAN, but I've also seen
+it be 16 for INDEXSCAN?</entry>
+</row>
+<row>
+<entry colname="1">START_POSITION</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">1024</entry>
+<entry colname="4">true</entry>
+<entry colname="5">For index and constraint scans, this column holds a
+    textual representation of the operator, if any, which was used to position
+    the beginning of the index/constraint scan.</entry>
+</row>
+<row>
+<entry colname="1">STOP_POSITION</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">1024</entry>
+<entry colname="4">true</entry>
+<entry colname="5">For index and constraint scans, this column holds a
+    textual representation of the operator, if any, which was used to position
+    the end of the index/constraint scan.</entry>
+</row>
+<row>
+<entry colname="1">SCAN_QUALIFIERS</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">1024</entry>
+<entry colname="4">true</entry>
+<entry colname="5">If the query specified values which are to be used to
+    limit the rows that are scanned, information about those values is
+captured in this column.</entry>
+</row>
+<row>
+<entry colname="1">NEXT_QUALIFIERS</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">1024</entry>
+<entry colname="4">true</entry>
+<entry colname="5">If the query specified values which are to be used to
+limit the rows that are scanned, information about those values is
+captured in this column.</entry>
+</row>
+<row>
+<entry colname="1">HASH_KEY_COLUMN_NUMBER</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">1024</entry>
+<entry colname="4">true</entry>
+<entry colname="5">For hash joins, this column contains information about which column is being used to hash the rows that are joined.</entry>
+</row>
+<row>
+<entry colname="1">HASH_TABLE_SIZE</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">For hash joins, this column contains information about
+    the size of the hash table that will be used to hold the rows being
+    joined. This hash table is an intermediate result, and will be
+    discarded at the end of the query. If the hash table cannot fit in
+    memory, it will automatically spill over to disk. Since the
+    spillover to disk can have significant performance implications,
+    this value can provide a clue that the hash table was
+    unexpectedly too large to fit in memory.</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rrefsysxplain_scan_props.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rrefsysxplain_sort_props.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsysxplain_sort_props.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsysxplain_sort_props.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsysxplain_sort_props.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,159 @@
+<?xml version="1.0" encoding="utf-8"?>
+ 
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rrefsysxplain_sort_props" xml:lang="en-us">
+<title>SYSXPLAIN_SORT_PROPS system table</title>
+<refbody>
+    <section><p>This table captures information about row sorting actions
+            which occurred during statements that were
+            executed using RUNTIMESTATISTICS with XPLAIN style (see the
+            RUNTIMESTATISTICS section in the
+            <ph conref="../conrefs.dita#pub/citref"></ph> for more information
+            on how to configure this).
+            Rows in this table are typically joined with rows in
+            SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis.</p>
+        <codeblock>
+            select s.stmt_text, rs.op_identifier, 
+                   srt.no_input_rows, srt.no_output_rows
+            from my_stats.sysxplain_sort_props srt,
+                 my_stats.sysxplain_resultsets rs,
+                 my_stats.sysxplain_statements s
+            where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_id
+        </codeblock>
+            <p>Rows in this table are added
+                automatically when
+                <ph conref="../conrefs.dita#prod/productshortname"></ph>
+                has been configured appropriately. The
+            rows remain in the table until you delete them or drop the table.
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p><p>   <table
+pgwide="1" frame="all">
+<tgroup cols="5" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="24*"/>
+<colspec colname="2" colnum="2" colwidth="23*"/><colspec colname="3" colnum="3"
+colwidth="11*"/><colspec colname="4" colnum="4" colwidth="14*"/><colspec colname="5"
+colnum="5" colwidth="29*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Column Name</entry>
+<entry align="left" colname="2" valign="bottom">Type</entry>
+<entry align="left" colname="3" valign="bottom">Length</entry>
+<entry align="left" colname="4" valign="bottom">Nullability</entry>
+<entry align="left" colname="5" valign="bottom">Contents</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">SORT_RS_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A unique identifier for this row. Matches the corresponding
+    value of SORT_RS_ID in the my_stats.SYSXPLAIN_RESULTSETS row for the result set
+which required this sort to be performed.</entry>
+</row>
+<row>
+<entry colname="1">SORT_TYPE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">2</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating the type of sort that was performed.
+    The code values include 'IN' for an internal sort, and 'EX' for an
+    external sort. I think that an internal sort is one which was entirely
+    performed in-memory and did not overflow to any temporary files, while an
+external sort used one or more external files.</entry>
+</row>
+<row>
+<entry colname="1">NO_INPUT_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of rows which were provided to the sorter.</entry>
+</row>
+<row>
+<entry colname="1">NO_OUTPUT_ROWS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of rows which were returned by the sorter.
+    Note that this may be fewer rows than were input, for example when
+    the sorter is performing GROUP BY processing or is eliminating duplicates.
+</entry>
+</row>
+<row>
+<entry colname="1">NO_MERGE_RUNS</entry>
+<entry colname="2">INTEGER</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Number of merge runs which were provided. This value
+    will be NULL for an internal sort, but for an external sort it
+    indicates how many times the intermedate sort files were merged together.
+    External sorts are far more expensive than internal sorts, and each
+    additional merge run that an external sort must perform adds
+    considerably more to the overhead of the sort.
+</entry>
+</row>
+<row>
+<entry colname="1">MERGE_RUN_DETAILS</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">256</entry>
+<entry colname="4">true</entry>
+<entry colname="5">Additional information about the size of the merge runs.
+This value will be NULL for an internal sort.</entry>
+</row>
+<row>
+<entry colname="1">ELIMINATE_DUPLICATES</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating whether or not this sort
+    eliminated duplicates from the input: (Y)es or (N)o.
+    This column only applies for a sort which was NOT performing GROUP BY
+    aggregation; for GROUP BY sorts this column is always NULL. See the
+    DISTINCT_AGGREGATE column for the corresponding information for
+    aggregating sorts.
+</entry>
+</row>
+<row>
+<entry colname="1">IN_SORT_ORDER</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating whether or not the rows which were
+    input to the sorter were already in sort order, which can happen if
+    the rows were retrieved by using an index, or if an earlier phase of
+    processing had already sorted the data. The code is 'Y' for Yes if the
+rows are already in sorted order, and 'N' for No otherwise.</entry>
+</row>
+<row>
+<entry colname="1">DISTINCT_AGGREGATE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating whether the aggregation process
+was computing distinct aggregates or not.</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rrefsysxplain_sort_props.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rrefsysxplain_statement_timings.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsysxplain_statement_timings.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsysxplain_statement_timings.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsysxplain_statement_timings.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,182 @@
+<?xml version="1.0" encoding="utf-8"?>
+ 
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rrefsysxplain_statement_timings" xml:lang="en-us">
+<title>SYSXPLAIN_STATEMENT_TIMINGS system table</title>
+<refbody>
+    <section><p>This table captures information about statement timings
+            which occurred during statements that were
+            executed using RUNTIMESTATISTICS with XPLAIN style (see the
+            RUNTIMESTATISTICS section in the
+            <ph conref="../conrefs.dita#pub/citref"></ph> for more information
+            on how to configure this).
+            Note in particular that you must
+            call syscs_util.syscs_set_statistics_timing(1) to enable
+            timing information to be captured.
+            Rows in this table are typically joined with rows in
+            SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis.
+        For example:</p>
+    <codeblock>
+        select s.stmt_text, st.execute_time
+        from my_stats.sysxplain_statements s,
+             my_stats.sysxplain_statement_timings st
+        where s.timing_id = st.timing_id
+        order by st.execute_time desc
+    </codeblock>
+            <p>Rows in this table are added
+                automatically when 
+                <ph conref="../conrefs.dita#prod/productshortname"></ph>
+                has been configured appropriately. The
+            rows remain in the table until you delete them or drop the table.
+                </p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+                <p>   <table
+pgwide="1" frame="all">
+<tgroup cols="5" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="24*"/>
+<colspec colname="2" colnum="2" colwidth="23*"/><colspec colname="3" colnum="3"
+colwidth="11*"/><colspec colname="4" colnum="4" colwidth="14*"/><colspec colname="5"
+colnum="5" colwidth="29*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Column Name</entry>
+<entry align="left" colname="2" valign="bottom">Type</entry>
+<entry align="left" colname="3" valign="bottom">Length</entry>
+<entry align="left" colname="4" valign="bottom">Nullability</entry>
+<entry align="left" colname="5" valign="bottom">Contents</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">TIMING_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A unique identifier for this particular row. This column
+    can be used to join with the TIMING_ID column in SYSXPLAIN_STATEMENTS
+to match statement timings with their corresponding statements.</entry>
+</row>
+<row>
+<entry colname="1">PARSE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time in milliseconds that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> took to parse
+this statement.</entry>
+</row>
+<row>
+<entry colname="1">BIND_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time in milliseconds that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> took to bind
+    this statement. Binding a statement is the process of resolving table
+    and column references in the statement against the table and column
+definitions in the system catalogs.</entry>
+</row>
+<row>
+<entry colname="1">OPTIMIZE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time in milliseconds that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> took to optimize
+    this statement. During optimization, 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph>
+    considers the various possible
+    execution plans that could be used for the statement, and chooses the
+one it thinks will be best.</entry>
+</row>
+<row>
+<entry colname="1">GENERATE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time in milliseconds that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> took to generate
+    code for this statement.</entry>
+</row>
+<row>
+<entry colname="1">COMPILE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time in milliseconds that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> took to compile
+    this statement. Overall statement time is divided into compile time and
+    execute time, and the compile time is further sub-divided into parse, bind,
+optimize, and generate time.</entry>
+</row>
+<row>
+<entry colname="1">EXECUTE_TIME</entry>
+<entry colname="2">BIGINT</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time in milliseconds that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> took to execute
+this statement.</entry>
+</row>
+<row>
+<entry colname="1">BEGIN_COMP_TIME</entry>
+<entry colname="2">TIMESTAMP</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time at which 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph>
+    began to compile this statement.</entry>
+</row>
+<row>
+<entry colname="1">END_COMP_TIME</entry>
+<entry colname="2">TIMESTAMP</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time at which 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> finished 
+compiling this statement.</entry>
+</row>
+<row>
+<entry colname="1">BEGIN_EXE_TIME</entry>
+<entry colname="2">TIMESTAMP</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time at which 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> began to execute
+    this statement.</entry>
+</row>
+<row>
+<entry colname="1">END_EXE_TIME</entry>
+<entry colname="2">TIMESTAMP</entry>
+<entry colname="3"></entry>
+<entry colname="4">false</entry>
+<entry colname="5">The time at which 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph> finished 
+executing this statement.</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rrefsysxplain_statement_timings.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/ref/rrefsysxplain_statements.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsysxplain_statements.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsysxplain_statements.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsysxplain_statements.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,191 @@
+<?xml version="1.0" encoding="utf-8"?>
+ 
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+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.
+-->
+<reference id="rrefsysxplain_statements" xml:lang="en-us">
+<title>SYSXPLAIN_STATEMENTS system table</title>
+<refbody>
+    <section><p>This table captures information about statements which have been
+            executed using RUNTIMESTATISTICS with XPLAIN style (see the
+            RUNTIMESTATISTICS section in the
+            <ph conref="../conrefs.dita#pub/citref"></ph> for more information
+            on how to configure this).
+        </p>
+        <p>
+            Each row in this table describes a single statement which has
+            been captured. Depending on the precise configuration of the
+            RUNTIMESTATISTICS and XPLAIN features, there may be additional
+            rows in the other XPLAIN system tables with additional information;
+            the STMT_ID and TIMING_ID columns in this table are used to join
+            against those tables.</p><p>Rows in this table are added
+            automatically when 
+            <ph conref="../conrefs.dita#prod/productshortname"></ph>
+            has been configured appropriately. The
+            rows remain in the table until you delete them or drop the table.
+                </p>
+    <p>
+               <ph conref="../conrefs.dita#pub/cittuning"></ph> contains
+              <xref href="../tuning/ctun_xplain_style.dita#ctun_xplain_style">
+                 general information</xref> about how to use XPLAIN style
+                and the XPLAIN database tables.
+            </p>
+                <p>   <table
+pgwide="1" frame="all">
+<tgroup cols="5" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="24*"/>
+<colspec colname="2" colnum="2" colwidth="23*"/><colspec colname="3" colnum="3"
+colwidth="11*"/><colspec colname="4" colnum="4" colwidth="14*"/><colspec colname="5"
+colnum="5" colwidth="29*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Column Name</entry>
+<entry align="left" colname="2" valign="bottom">Type</entry>
+<entry align="left" colname="3" valign="bottom">Length</entry>
+<entry align="left" colname="4" valign="bottom">Nullability</entry>
+<entry align="left" colname="5" valign="bottom">Contents</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">STMT_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A unique identifier for this particular captured statement.</entry>
+</row>
+<row>
+<entry colname="1">STMT_NAME</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">128</entry>
+<entry colname="4">true</entry>
+<entry colname="5">The name of the associated query or statement. This value
+    is NULL if the user did not assign a name. I'm not sure how the
+    user assigns a name to a statement, perhaps by
+    calling Statement.setCursorName()?</entry>
+</row>
+<row>
+<entry colname="1">STMT_TYPE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">6</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A code indicating what type of statement this is:
+    '<i>S</i>'=SELECT, '<i>I</i>'=INSERT, '<i>U</i>'=UPDATE,
+    '<i>D</i>'=DELETE, '<i>C</i>'=CALL, '<i>DDL</i>'=Data Definition, such
+    as CREATE TABLE, '<i>SA</i>'=SELECT (Approximate), or blank,
+indicating the statement was a comment.</entry>
+</row>
+<row>
+<entry colname="1">STMT_TEXT</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">32672</entry>
+<entry colname="4">false</entry>
+<entry colname="5">The text of the statement.</entry>
+</row>
+<row>
+<entry colname="1">JVM_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">30</entry>
+<entry colname="4">false</entry>
+<entry colname="5">A code indicating what version of the JVM was running
+    when this statement was captured: '4'=J2SE_14 - JDK 1.4.0 or 1.4.1,
+    '5'= J2SE_142 - JDK 1.4.2, '6'=J2SE_15 - JDK 1.5,
+    '7'=J2SE_16 - JDK 1.6
+</entry>
+</row>
+<row>
+<entry colname="1">OS_IDENTIFIER</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">30</entry>
+<entry colname="4">false</entry>
+<entry colname="5">Contains information about the operating system 
+which was being used when this statement was captured.</entry>
+</row>
+<row>
+<entry colname="1">XPLAIN_MODE</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">1</entry>
+<entry colname="4">true</entry>
+<entry colname="5">A code indicating the XPLAIN mode which was in use
+    when this statement was captured: '<i>F</i>'=FULL, '<i>O</i>'=ONLY.</entry>
+</row>
+<row>
+<entry colname="1">XPLAIN_TIME</entry>
+<entry colname="2">TIMESTAMP</entry>
+<entry colname="3"></entry>
+<entry colname="4">true</entry>
+<entry colname="5">Contains the date and time when this statement
+was captured.</entry>
+</row>
+<row>
+<entry colname="1">XPLAIN_THREAD_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">32</entry>
+<entry colname="4">false</entry>
+<entry colname="5">The JVM thread which was running when this statement
+was captured.</entry>
+</row>
+<row>
+<entry colname="1">TRANSACTION_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">32</entry>
+<entry colname="4">false</entry>
+<entry colname="5">An internal identifier for the transaction which was
+active when this statement was captured.</entry>
+</row>
+<row>
+<entry colname="1">SESSION_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">32</entry>
+<entry colname="4">false</entry>
+<entry colname="5">An internal identifier for the session which was
+active when this statement was captured.</entry>
+</row>
+<row>
+<entry colname="1">DATABASE_NAME</entry>
+<entry colname="2">VARCHAR</entry>
+<entry colname="3">128</entry>
+<entry colname="4">false</entry>
+<entry colname="5">Contains the name of the database which was being
+used when this statement was captured.</entry>
+</row>
+<row>
+<entry colname="1">DRDA_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">32</entry>
+<entry colname="4">true</entry>
+<entry colname="5">In a network environment, this column contains
+    an internal identifier for the network connection which was
+    active when this statement was captured. In an embedded environment,
+this column is null.</entry>
+</row>
+<row>
+<entry colname="1">TIMING_ID</entry>
+<entry colname="2">CHAR</entry>
+<entry colname="3">36</entry>
+<entry colname="4">true</entry>
+<entry colname="5">This field will be NULL unless
+    SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING has been called to enable
+    statistics timing. If statistics timings are being captured, then this
+    column will contain the ID of the row in SYSXPLAIN_STATEMENT_TIMINGS
+which record the statement timing for this statement.</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p></section>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/ref/rrefsysxplain_statements.dita
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/docs/trunk/src/tuning/ctun_xplain_style.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctun_xplain_style.dita?rev=773294&view=auto
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctun_xplain_style.dita (added)
+++ db/derby/docs/trunk/src/tuning/ctun_xplain_style.dita Sun May 10 02:07:07 2009
@@ -0,0 +1,149 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!-- 
+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.
+-->
+
+<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "../dtd/concept.dtd">
+<concept xml:lang="en-us" id="ctun_xplain_style">
+<title>How you use the XPLAIN style</title>
+<prolog><metadata>
+<keywords>
+    <indexterm>XPLAIN style<indexterm>using</indexterm></indexterm>
+</keywords>
+</metadata>
+</prolog>
+<conbody>
+<section><title>Overview</title>
+    <p>
+        XPLAIN style is an enhanced form of RUNTIMESTATISTICS processing
+        which preserves captured statistics information in 
+        <xref href="ctun_xplain_tables.dita#ctun_xplain_tables">database 
+            tables</xref>.
+        Once the statistics have been collected and saved in the 
+        tables, they can be queried for analysis purposes.
+    </p>
+    <p>
+        Note that <ph conref="../conrefs.dita#prod/productshortname"></ph>
+        spells XPLAIN without the initial 'E'. This is done
+        to help distinguish the
+        <ph conref="../conrefs.dita#prod/productshortname"></ph>
+        functionality from the explain
+        functionality that you might be familiar with from commercial DBMS
+        products. The current XPLAIN implementation is optimized for
+        ad-hoc queries and tool support. Furthermore, the explain data
+        is quite extensive to analyze. 
+        <ph conref="../conrefs.dita#prod/productshortname"></ph>
+        tries to implement a
+        compromise between detailed explain information which is
+        almost unreadable by human users and which has to be evaluated
+        with the help of a tool, versus a compact version of explain
+        data which is only applicable for rough investigations but
+        is still browseable by human users. We feel that the information
+        in the XPLAIN system tables is sufficiently detailed to be powerful,
+        but still simple enough to provide useful information to
+        ad-hoc querying during interactive use.
+    </p>
+    <p>
+    To use XPLAIN style, first turn on RUNTIMESTATISTICS using the
+            <codeph>SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS()</codeph> system
+            procedure.</p>
+        <p>Optionally, turn on statistics timing using the
+            <codeph>SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING</codeph>
+            system procedure.  If you do not turn on statistics timing,
+            you will see the statement execution
+            plan only, and not the timing information.</p>
+        <p>Next, to activate XPLAIN style, use the
+            <codeph>SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA()</codeph> system
+        procedure:</p>
+<codeblock>-- turn on RUNTIMESTATISTICS for connection:
+<b>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);</b>
+<b>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);</b>
+-- Indicate that statistics information should be captured into
+-- database tables in the MYSCHEMA schema:
+<b>CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA('MYSCHEMA');</b>
+--execute queries, step through result sets, perform application processing...
+<b>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);</b></codeblock>
+<p>Although the syntax is different, the basic steps for working with
+    XPLAIN style are the same in a Java program.</p>
+<p>XPLAIN style is further refined by the use of XPLAIN-only mode. By
+    default, XPLAIN-only mode is off, which means that statements are
+    compiled and executed normally. When XPLAIN-only mode is on, statements
+    are compiled, but not executed. This is useful for investigating what
+    query plan has been selected for a statement, without actually
+    executing the statement. To activate XPLAIN-only mode, use the
+    <codeph>SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE()</codeph> system procedure:</p>
+<codeblock>
+    call SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE(1);
+</codeblock>
+</section>
+<section><title>Examples</title><p>Some examples of usage follow.</p>
+    <p>Retrieve the text of statements
+        which were captured, in order by the time when the statistics were
+    captured:</p>
+<codeblock>
+    select stmt_text, xplain_time from myschema.sysxplain_statements
+    order by xplain_time
+</codeblock>
+<p>Retrieve the text of statements which were captured, showing the
+    statements which took the longest time to execute first:</p>
+<codeblock>
+    select s.stmt_text, st.execute_time from myschema.sysxplain_statements s,
+           myschema.sysxplain_statement_timings st
+    where s.timing_id = st.timing_id
+    order by st.execute_time desc
+</codeblock>
+<p>Show the statements that were executed, together with the result sets
+    that each statement required:</p>
+<codeblock>
+    select st.stmt_text, rs.op_identifier 
+    from myschema.sysxplain_statements st
+    join myschema.sysxplain_resultsets rs
+         on st.stmt_id = rs.stmt_id
+</codeblock>
+<p>Find statements which resulted in an external sort being performed:</p>
+<codeblock>
+    select s.stmt_text, s.stmt_id, rs.op_identifier, srt.no_input_rows
+    from myschema.sysxplain_sort_props srt,
+         myschema.sysxplain_resultsets rs,
+         myschema.sysxplain_statements s
+    where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_id
+          and srt.sort_type = 'EX'
+</codeblock>
+<p>Show statements which resulted in a sort, sorted by the number of
+    rows which were sorted by that statement.</p>
+<codeblock>
+    select s.stmt_text, s.stmt_id, rs.op_identifier, srt.no_input_rows
+    from myschema.sysxplain_sort_props srt,
+         myschema.sysxplain_resultsets rs,
+         myschema.sysxplain_statements s
+    where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_id
+    order by srt.no_input_rows desc
+</codeblock>
+<p>Find statements which resulted in a tablescan of the COUNTRIES table,
+    and display the number of pages and rows that were visited by each scan:</p>
+<codeblock>
+    select st.stmt_text, sp.no_visited_pages, sp.no_visited_rows 
+    from myschema.sysxplain_scan_props sp, 
+         myschema.sysxplain_resultsets rs, 
+         myschema.sysxplain_statements st 
+    where st.stmt_id = rs.stmt_id and 
+          rs.scan_rs_id = sp.scan_rs_id and 
+          rs.op_identifier = 'TABLESCAN' and 
+          sp.scan_object_name = 'COUNTRIES'
+</codeblock>
+</section>
+</conbody></concept>
+

Propchange: db/derby/docs/trunk/src/tuning/ctun_xplain_style.dita
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message