Archives of the TeradataForum

Message Posted: Thu, 25 May 2006 @ 11:31:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I'm using a evaluation version of the Teradata s/w (V2R.05.00.00.11). The optimizer seems to make an erroneous estimate of the table size even though

The data is distributed evenly across AMPs

No Target Level Emulation information/ stale statistics is present

This case appears with tables on which statistics were never collected.

Is this a known bug which was fixed in newer releases?

Given below is a sample script output that shows the erroneous estimates.

Marcus

     BTEQ 08.02.00.00 Thu May 25 14:35:30 2006

     +---------+---------+---------+---------+---------+---------+
     /********
     purpose: Script shows that the optimizer is making erroneous estimates
     of number of records in a table that's
                  recently created (make sure that stats were not collected
     on the table after table creation).

              No TLE data is present that influences this estimate.

              The behaviour disappears if statistics are collected and then
     dropped...

     *********/

     .logon demotdat/dbc,

      *** Logon successfully completed.
      *** Transaction Semantics are BTET.
      *** Character Set Name is 'ASCII'.

      *** Total elapsed time was 1 second.

     +---------+---------+---------+---------+---------+---------+

     database testuser1;

      *** New default database accepted.
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     SELECT * FROM dbc.dbcinfo;

      *** Query completed. 2 rows found. 2 columns returned.
      *** Total elapsed time was 1 second.

     InfoKey                        InfoData
     ------------------------------
     --------------------------------------------
     RELEASE                        V2R.05.00.00.11
     VERSION                        05.00.00.11

     +---------+---------+---------+---------+---------+---------+

     SHOW TABLE EMP;

      *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.

     +---------+---------+---------+---------+---------+---------+
     CREATE SET TABLE TESTUSER1.EMP ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           idno INTEGER,
           name CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
           deptno INTEGER)
     PRIMARY INDEX ( idno );


     +---------+---------+---------+---------+---------+---------+

     Select count(*) from emp;

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

        Count(*)
     -----------
             499

     +---------+---------+---------+---------+---------+---------+

     /*********Check table size across AMP's************/

     SELECT vproc, currentperm
     FROM DBC.TABLESIZE
     WHERE databasename='testuser1'
      And tablename='emp';

      *** Query completed. 2 rows found. 2 columns returned.
      *** Total elapsed time was 1 second.

      Vproc          CurrentPerm
     ------  -------------------
          0                9,216
          1                9,216

     +---------+---------+---------+---------+---------+---------+


     /*********Observe the errorneous estimate in Explain - 2 rows are
     expected*/

     EXPLAIN Sel * FROM EMP;

      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.

     Explanation
     -------------------------------------------------------------
       1) First, we lock a distinct TESTUSER1."pseudo table" for read on a
          RowHash to prevent global deadlock for TESTUSER1.EMP.
       2) Next, we lock TESTUSER1.EMP for read.
       3) We do an all-AMPs RETRIEVE step from TESTUSER1.EMP by way of an
          all-rows scan with no residual conditions into Spool 1
          (group_amps), which is built locally on the AMPs.  The size of
          Spool 1 is estimated with low confidence to be 2 rows.  The
          estimated time for this step is 0.03 seconds.
       4) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.
       -> The contents of Spool 1 are sent back to the user as the result of
          statement 1.  The total estimated time is 0.03 seconds.

     +---------+---------+---------+---------+---------+---------+


     /*********Observe that there is no TLE data that influences the
     errorneous estimates*/

     DIAGNOSTIC HELP SAMPLES;
      *** Failure 5545 The TLE feature has not been enabled.
                     Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     DIAGNOSTIC HELP COSTS;
      *** Failure 5545 The TLE feature has not been enabled.
                     Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     HELP STATS EMP;
      *** Failure 3624 There are no statistics defined for the table.
                     Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+


     /*********Now collect statistics and observe Explain***********/

     COLLECT STATS ON EMP COLUMN IDNO;

      *** Update completed. One row changed.
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     EXPLAIN Sel * FROM EMP;

      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.

     Explanation
     -------------------------------------------------------------
       1) First, we lock a distinct TESTUSER1."pseudo table" for read on a
          RowHash to prevent global deadlock for TESTUSER1.EMP.
       2) Next, we lock TESTUSER1.EMP for read.
       3) We do an all-AMPs RETRIEVE step from TESTUSER1.EMP by way of an
          all-rows scan with no residual conditions into Spool 1
          (group_amps), which is built locally on the AMPs.  The size of
          Spool 1 is estimated with high confidence to be 499 rows.  The
          estimated time for this step is 0.03 seconds.
       4) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.
       -> The contents of Spool 1 are sent back to the user as the result of
          statement 1.  The total estimated time is 0.03 seconds.

     +---------+---------+---------+---------+---------+---------+


     /*********Now drop statistics and observe Explain***********/

     DROP STATISTICS ON EMP;

      *** Update completed. One row changed.
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+

     EXPLAIN Sel * FROM EMP;

      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.

     Explanation
     -------------------------------------------------------------
       1) First, we lock a distinct TESTUSER1."pseudo table" for read on a
          RowHash to prevent global deadlock for TESTUSER1.EMP.
       2) Next, we lock TESTUSER1.EMP for read.
       3) We do an all-AMPs RETRIEVE step from TESTUSER1.EMP by way of an
          all-rows scan with no residual conditions into Spool 1
          (group_amps), which is built locally on the AMPs.  The size of
          Spool 1 is estimated with low confidence to be 496 rows.  The
          estimated time for this step is 0.03 seconds.
       4) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.
       -> The contents of Spool 1 are sent back to the user as the result of
          statement 1.  The total estimated time is 0.03 seconds.

     +---------+---------+---------+---------+---------+---------+

     .logoff;
      *** You are now logged off from the DBC.
     +---------+---------+---------+---------+---------+---------+

     .quit;
      *** Exiting BTEQ...
      *** RC (return code) = 8

	<Prev	Next>		<<First	<Prev	Next>	Last>>