db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r898944 - in /db/derby/docs/trunk/src/tuning: ctunperf25864.dita ctunperfstatistics.dita ctunstats849505.dita tuningderby.ditamap
Date Wed, 13 Jan 2010 21:06:18 GMT
Author: bpendleton
Date: Wed Jan 13 21:06:18 2010
New Revision: 898944

URL: http://svn.apache.org/viewvc?rev=898944&view=rev
Log:
DERBY-3259: Tuning manual needs performance tip on compressing tables

This change adds a new tip to the Performance Tips and Tricks section
of the Tuning Derby manual. The new tip recommends the use of either
SYSCS_UTIL.SYSCS_COMPRESS_TABLE or SYSCS_UTIL.SYSCS_UPDATE_STATISTICS
to ensure that the table statistics are accurate, since accurate
statistics can be crucial in achieving query plans with acceptable performance.


Added:
    db/derby/docs/trunk/src/tuning/ctunperfstatistics.dita   (with props)
Modified:
    db/derby/docs/trunk/src/tuning/ctunperf25864.dita
    db/derby/docs/trunk/src/tuning/ctunstats849505.dita
    db/derby/docs/trunk/src/tuning/tuningderby.ditamap

Modified: db/derby/docs/trunk/src/tuning/ctunperf25864.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctunperf25864.dita?rev=898944&r1=898943&r2=898944&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctunperf25864.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctunperf25864.dita Wed Jan 13 21:06:18 2010
@@ -28,6 +28,9 @@
 <li><i><xref href="ctunperf10679.dita#ctunperf10679">Create indexes, and
make sure they are being used</xref>.</i> Indexes
 speed up queries dramatically if the table is much larger than the number
 of rows retrieved.</li>
+<li><i><xref href="ctunperfstatistics.dita#ctunperfstatistics">Ensure
+            table statistics are accurate</xref></i>, since missing or out
+    of data statistics can result in poor query plan selection.</li>
 <li><i><xref href="ctunperf54492.dita#ctunperf54492">Increase the size
of the data page cache</xref></i> and prime
 all the caches.</li>
 <li><i><xref href="ctunperf10065.dita#ctunperf10065">Tune the size of database
pages</xref>.</i> Using

Added: db/derby/docs/trunk/src/tuning/ctunperfstatistics.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctunperfstatistics.dita?rev=898944&view=auto
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctunperfstatistics.dita (added)
+++ db/derby/docs/trunk/src/tuning/ctunperfstatistics.dita Wed Jan 13 21:06:18 2010
@@ -0,0 +1,69 @@
+<?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="ctunperfstatistics">
+<title>Ensure table statistics are accurate</title>
+<prolog><metadata>
+<keywords>
+    <indexterm>Cardinality statistics<indexterm>ensuring accuracy</indexterm></indexterm>
+    <indexterm>statistics<indexterm>cardinality, ensuring accuracy</indexterm></indexterm>
+    <indexterm>Selectivity<indexterm>accuracy due to cardinality statistics</indexterm></indexterm>
+
+</keywords>
+</metadata>
+</prolog>
+<conbody>
+    <p>As described in <xref href="ctunstats849251.dita#ctunstats849251">
+        What are cardinality statistics?</xref>,
+    the cardinality statistics for a table influence the optimizer's
+    choice of a query plan
+for a query which accesses the table's data.
+If the cardinality statistics are missing or out of date, the optimizer may
+choose an inferior query plan, resulting in poor performance.
+</p>
+<p>
+One common situation which can causing missing or out of date statistics is
+when indexes are created before the data is added to the tables.
+Cardinality statistics are automatically updated in certain situations, such as
+when an index is added to an existing non-empty table.
+<xref href="ctunstats57373.dita#ctunstats57373">When cardinality
+    statistics are automatically updated</xref> describes the automatic
+statistics updates in more detail.
+However, adding, updating, and deleting data after the index
+has been created can cause the cardinality statistics to become stale; see
+<xref href="ctunstats849505.dita#ctunstats849505">
+    when cardinality statistics go stale</xref> for more information about
+what can cause missing or out of date statistics..
+</p>
+<p>
+To ensure that the statistics are available and accurate, you can run either
+of the following built-in system procedures:
+<ul>
+<li><codeph>SYSCS_UTIL.SYSCS_COMPRESS_TABLE</codeph></li>
+<li><codeph>SYSCS_UTIL.SYSCS_UPDATE_STATISTICS</codeph></li>
+</ul>
+Note that the <codeph>SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE</codeph>
+system procedure does <b>not</b> update statistics as part of its
+processing.
+</p>
+<p>
+You can find additional information about these system procedures, including
+the syntax for invoking them, in the
+<ph conref="../conrefs.dita#pub/citref"></ph>.
+</p>
+</conbody></concept>

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

Modified: db/derby/docs/trunk/src/tuning/ctunstats849505.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctunstats849505.dita?rev=898944&r1=898943&r2=898944&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctunstats849505.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctunstats849505.dita Wed Jan 13 21:06:18 2010
@@ -31,6 +31,22 @@
 means that statistics can go stale. Stale statistics can slow your system
 down, because they worsen the accuracy of the optimizer's estimates of
 selectivity.  </p>
+<p>
+    Most of the statistics information that 
+    <ph conref="../conrefs.dita#prod/productshortname"></ph>
+    uses is automatically kept up
+    to date as part of underlying index and table maintenance.
+    This information includes the count of rows in the table and
+    the distribution of data in indexes.
+</p>
+<p>
+    The one piece of information that is not kept up to date is the
+    average number of duplicates for columns in an index.
+This statistic is given a default and
+then is updated whenever you create an index or update the statistics by
+running either the <codeph>SYSCS_UTIL.SYSCS_UPDATE_STATISTICS</codeph> or the
+<codeph>SYSCS_UTIL.SYSCS_COMPRESS_TABLE</codeph> built-in system procedure.
+</p>
 <p>Statistics are likely to be stale if the number of distinct values in an
 index has changed significantly. This can happen often or rarely, depending on
 the nature of the column being indexed. You can refresh cardinality statistics

Modified: db/derby/docs/trunk/src/tuning/tuningderby.ditamap
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/tuningderby.ditamap?rev=898944&r1=898943&r2=898944&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/tuningderby.ditamap (original)
+++ db/derby/docs/trunk/src/tuning/tuningderby.ditamap Wed Jan 13 21:06:18 2010
@@ -43,6 +43,8 @@
 </topicref>
 <topicref href="ctunperf10679.dita" navtitle="Create indexes, and make sure they are being
used">
 </topicref>
+<topicref href="ctunperfstatistics.dita" navtitle="Ensure table statistics are accurate">
+</topicref>
 <topicref href="ctunperf54492.dita" navtitle="Increase the size of the data page cache">
 </topicref>
 <topicref collection-type="family" href="ctunperf10065.dita" navtitle="Tune the size of
database pages">



Mime
View raw message