Archives of the TeradataForum

Message Posted: Tue, 27 Aug 2002 @ 17:37:30 GMT


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

Subj:		Re: Overhead

From:		John Hall

If anybody has that kind of information, whether a snippet or something more detailed, I would be glad to collect it and put it in the Library section of the website.

The problem that I see about such a list is that there is going to be a lot of conditions which would need to be detailed (ie- software release, hardware platform, etc).

What might be of more benefit is looking at the costs associated with a given join plan (I have never seen documentation that would help in understanding the output below, but you should be able to make some guesses as you read through it).

You can get this information by:

- Start a telnet session to a Node and logon as 'root'.

- Start terminal logging for the telnet session. The output can be voluminous and a portion of it may be lost if not logging to disk.

- Enter the following command:

cnsterm 5

This will open a window to the Supervisor I/O screen (Screen #5):

- Start a new telnet session and logon to the Node.

- If desired, start terminal logging for this telnet session. Start a BTEQ session and logon to the Teradata.

- Enter the following command:

diagnostic joinplan on for session;

diagnostic joinplan on for request;

- Enter your SQL statement in the BTEQ session. The detailed information will be displayed on the Supervisor I/O screen.

So for example, on the BTEQ session:

    diagnostic joinplan on for session;

    *** Null statement accepted.
    *** Total elapsed time was 1 second.

    sel a.col_1
       from temp.test02   a
          , temp.test03   b
       where a.col_1 = b.col_1
         and b.col_2 > 990
       group by 1    order by 1;

    *** Query completed. 10 rows found. One column returned.
    *** Total elapsed time was 8 seconds.

      col_1
-----------
        991
        992
   .
   . Remaining output suppressed
   .

Will result in the following output on the Supervisor I/O screen:

test_12:/ [ 230 ] cnsterm 5

Attempting to connect to CNS...completed.

--------- Optimizer Cost Variables ----------
 Block Sizes
  Table Block Size  1.22880E+04
  Spool Block Size  3.27680E+04
  Index Block Size  3.27680E+04

 Disk Read Delays in ms
  Table Scan  2.89855E+01
  Spool Scan  4.16667E+01
  Index Scan  4.16667E+01

 Disk Write Delays in ms
  Table Scan  8.69565E+01
  Spool Scan  1.25000E+02
  Index Scan  1.25000E+02

 Disk Array Read Throughput in ops/sec
  Table Scan  1.12500E+03
  Spool Scan  7.34000E+02
  Index Scan  7.34000E+02

 Disk Array Write Throughput in ops/sec
  Table Scan  2.79500E+02
  Spool Scan  1.97000E+02
  Index Scan  1.97000E+02

 OptBMWriteReadRowCost  4.82254E-03

 CPU Path lengths in ms
  OptRowAccessCost      4.40000E-03
  OptOutputRowCost      1.15000E-02
  OptRedistributeCost   3.05000E-02
  OptSynonymCost        3.45000E-02
  OptRowIDCost          3.45000E-02
  OptBMANDRowCost       7.92000E-01
  OptBitCost            2.50000E-04
  OptCharFieldCost      1.50000E-03
  OptNumFieldCost       1.50000E-03
  OptOvhdOfRowCompCost  3.14481E-04

 Network costs in ms
  OptByNetRedOvhd  1.72414E-01
  OptByNetRedCost  1.11636E-04
  OptByNetDupOvhd  5.40541E-02
  OptByNetDupCost  1.12778E-04

 Environment Variables
  OptCpusPerPMA                  4
  OptVampsPerPMA                 8
  OptVampsPerCPU       2.00000E+00
  OptPMAsPerDiskArray  1.00000E+00
  OptTotalPMAs                   2

 Weights
  OptSysCPUWeight   1.00000E+00
  OptSysDiskWeight  1.00000E+00
  OptSysNetWeight   1.00000E+00

--------- Optimizer Cost Variables ----------
 Block Sizes
  Table Block Size  1.22880E+04
  Spool Block Size  3.27680E+04
  Index Block Size  3.27680E+04

 Disk Read Delays in ms
  Table Scan  2.89855E+01
  Spool Scan  4.16667E+01
  Index Scan  4.16667E+01

 Disk Write Delays in ms
  Table Scan  8.69565E+01
  Spool Scan  1.25000E+02
  Index Scan  1.25000E+02

 Disk Array Read Throughput in ops/sec
  Table Scan  1.12500E+03
  Spool Scan  7.34000E+02
  Index Scan  7.34000E+02

 Disk Array Write Throughput in ops/sec
  Table Scan  2.79500E+02
  Spool Scan  1.97000E+02
  Index Scan  1.97000E+02

 OptBMWriteReadRowCost  4.82254E-03

 CPU Path lengths in ms
  OptRowAccessCost      4.40000E-03
  OptOutputRowCost      1.15000E-02
  OptRedistributeCost   3.05000E-02
  OptSynonymCost        3.45000E-02
  OptRowIDCost          3.45000E-02
  OptBMANDRowCost       7.92000E-01
  OptBitCost            2.50000E-04
  OptCharFieldCost      1.50000E-03
  OptNumFieldCost       1.50000E-03
  OptOvhdOfRowCompCost  3.14481E-04

 Network costs in ms
  OptByNetRedOvhd  1.72414E-01
  OptByNetRedCost  1.11636E-04
  OptByNetDupOvhd  5.40541E-02
  OptByNetDupCost  1.12778E-04

 Environment Variables
  OptCpusPerPMA                  4
  OptVampsPerPMA                 8
  OptVampsPerCPU       2.00000E+00
  OptPMAsPerDiskArray  1.00000E+00
  OptTotalPMAs                   2

 Weights
  OptSysCPUWeight   1.00000E+00
  OptSysDiskWeight  1.00000E+00
  OptSysNetWeight   1.00000E+00

 Entering OptRelation
 Relation  1 is a user table.
 Table name: b, OrderByPi:  TRUE, SortOrder:  TRUE
 Connected   =  TRUE
 NewFieldIDs =  TRUE
 JoinRelNo   =     0
 TblRowSize  =  2.00000000E 001
 TblRows     =  1.01000000E 003
 AmpRows     =  6.40000000E 001
 UsedStats   =  TRUE
 UniqueIndex =            0

 Terms:
 * Misc. term: NumRels= 1 RelNo= 1 [ 1]
 ValidSel = F
 TermPos: 0, NFlag: N, OJFlag: Inner, NotINP: F, GKTerm: F, NotInT: F
 Left Nesting Level :[ 1], Right Nesting Level :[ 1]
   jjhall.b{T19,64850,v4,a200}.col_2{F1026} > 990 {INTEGER}

Refd Row Ptr
 AsgnList(
  {RelNo 1}.col_1{F2} = XFORM({RelNo 1}.col_1{F1025},INTEGER)
  )

 Resulting Spool File:
    Cardinality      =  1.00000000E 000
    TotalRows        =  1.10000000E 001
    WrstTotRows      =  1.10000000E 001
    MaxALLRows       =  1.01000000E 003
    Cost to read     =  4.16754667E 001
    Average row size =  1.70000000E 001
    Output  row cost =  1.30000000E-002
    SingleRow        = FALSE
    SingleAMP        = FALSE
    Unique           = FALSE
    Confidence       = OptHighConfidence
    GroupKey = [ 1]    RelNoSet = [ 1]
    NLvl = [ 1],   OutJNLvl = []
    JoinSet = [69,78]

 Relation  2 is a user table.
 Table name: a, OrderByPi:  TRUE, SortOrder:  TRUE
 Connected   =  TRUE
 NewFieldIDs =  TRUE
 JoinRelNo   =     0
 TblRowSize  =  2.00000000E 001
 TblRows     =  1.01000000E 003
 AmpRows     =  6.40000000E 001
 UsedStats   =  TRUE
 UniqueIndex =            0
 Terms:
 *** No terms ***

Refd Row Ptr
 AsgnList(
  {RelNo 2}.col_1{F2} = XFORM({RelNo 2}.col_1{F1025},INTEGER)
  )

 Resulting Spool File:
    Cardinality      =  6.40000000E 001
    TotalRows        =  1.01000000E 003
    WrstTotRows      =  1.01000000E 003
    MaxALLRows       =  1.01000000E 003
    Cost to read     =  4.22298667E 001
    Average row size =  1.70000000E 001
    Output  row cost =  1.30000000E-002
    SingleRow        = FALSE
    SingleAMP        = FALSE
    Unique           = FALSE
    Confidence       = OptHighConfidence
    GroupKey = [ 2]    RelNoSet = [ 2]
    NLvl = [ 1],   OutJNLvl = []
    JoinSet = [69,78]

 * Bind term: RelNo  2 FldId 1025  =  RelNo  1 FldId 1025
 TermPos: 0, NFlag: N, OJFlag: Inner, NotINP: F, GKTerm: F, NotInT: F
 Left Nesting Level :[ 1], Right Nesting Level :[ 1]
   jjhall.a{T19,64849,v2,a100}.col_1{F1025} =
jjhall.b{T19,64850,v4,a200}.co
l_1{F1025}

 Entering OptJPlan
 OPTTDESC:

 Relation: 1
 Table Description:
   Number of Rows   6.10000000E 001
   Hashed?          TRUE
   Min Row Length     20
   Max Row Length     20
   Index count         2
   Index   Index                     Row             Index   Field
   Num     ID      Value Count       Size    Unique  Fields  ID(s)
   ------  ------  ----------------  ------  ------  ------  ------
        1       1   1.00100000E 003      20   FALSE       1    1025

 End of Table Description

 Join Table Sets: (Index:Set)
( 1: 2)

 Relation: 2

 Table Description:
   Number of Rows   6.70000000E 001
   Hashed?          TRUE
   Min Row Length     20
   Max Row Length     20
   Index count         2
   Index   Index                     Row             Index   Field
   Num     ID      Value Count       Size    Unique  Fields  ID(s)
   ------  ------  ----------------  ------  ------  ------  ------
        1       1   1.00100000E 003      20   FALSE       1    1025

 End of Table Description

 Join Table Sets: (Index:Set)
( 1: 1)
 ---------
  1 -  2

 In PickJoin, looking at a 2-way join with these terms:
 * Bind term: RelNo  2 FldId 1025  =  RelNo  1 FldId 1025
 TermPos: 0, NFlag: N, OJFlag: Inner, NotINP: F, GKTerm: F, NotInT: F
 Left Nesting Level :[ 1], Right Nesting Level :[ 1]

   jjhall.a{T19,64849,v2,a100}.col_1{F1025} =
jjhall.b{T19,64850,v4,a200}.co
l_1{F1025}

 Merge   Join InnerJoin
 DerivedOuterJ =  TRUE
( direct          1,  direct          2) costs 5.85430936E 001
 PreJSort = SortNone
 PostJSort = SortNone
 RightRelNo =            2
 GroupingField =            0
 ConnTermInfo = 0:0
 OuterJTermPtr = 0:0
 Relation  1 accessed directly.
 Relation  2 accessed directly.
 Join decomposition chose join: (TotalCost =  5.85430936E 001)
 Relation  3 is a Merge   Join InnerJoin
 DerivedOuterJ =  TRUE
( direct 1,  direct 2) costs 5.85430936E 001
 PreJSort = SortNone
 PostJSort = SortNone
 RightRelNo =            2
 GroupingField =            0
 ConnTermInfo = 0:0
 OuterJTermPtr = 0:0
 Connected   = FALSE
 NewFieldIDs = FALSE
 JoinRelNo   =     0

 Conditional expression:
 * Bind term: RelNo  2 FldId 1025  =  RelNo  1 FldId 1025
 TermPos: 0, NFlag: N, OJFlag: Inner, NotINP: F, GKTerm: F, NotInT: F
 Left Nesting Level :[ 1], Right Nesting Level :[ 1]
   {Row2}.col_1{F1025} = {Row1}.col_1{F1025}
 Resulting Spool File:
    Cardinality      =  1.00000000E 000
    TotalRows        =  1.20000000E 001
    WrstTotRows      =  1.20000000E 001
    MaxALLRows       =  1.11100000E 004
    Cost to read     =  4.16754667E 001
    Average row size =  1.90000000E 001
    Output  row cost =  1.45000000E-002
    SingleRow        = FALSE
    SingleAMP        = FALSE
    Unique           = FALSE
    Confidence       = OptLowConfidence
    GroupKey = [ 3]    RelNoSet = [ 1, 2, 3]
    NLvl = [ 1],   OutJNLvl = []
    JoinSet = [ 1, 2, 3], SNLvl = []
, OQry = 0, ORelno1 = 0, ORelno2 = 0

Total cost OF One Look-Ahead Join Plan =  5.85430936E 001
 Leaving OptJPlan

 Relation  3 built in local spool.
 Target is spool No.  2.
 Number of Join Plans        = 1
 Number of calls to OptBJoin = 1
 Leaving OptRelation
 Entering OptRelation
 Relation  1 is spool file  3
 Connected   = FALSE
 NewFieldIDs = FALSE
 JoinRelNo   =     0
 Conditional expression:
 * Misc. term: NumRels= 0 RelNo= 0 []
 ValidSel = F
 TermPos: 0, NFlag: N, OJFlag: Inner, NotINP: F, GKTerm: F, NotInT: F
 Left Nesting Level :[ 1], Right Nesting Level :[ 1]
    TRUE
 Unique Field1:
 AsgnList(
  {RelNo 1}.{F1} = XFORM({RelNo 1}.{F1},INTEGER)
  )

 Resulting Spool File:
    Cardinality      =  7.50000000E-001
    TotalRows        =  1.20000000E 001
    WrstTotRows      =  1.20000000E 001
    MaxALLRows       =  1.20000000E 001
    Cost to read     =  4.16732667E 001
    Average row size =  1.70000000E 001
    Output  row cost =  1.30000000E-002
    SingleRow        = FALSE
    SingleAMP        = FALSE
    Unique           = FALSE
    Confidence       = OptLowConfidence
       FieldID    2
    GroupKey = [ 1]    RelNoSet = [ 1]
    NLvl = [ 1],   OutJNLvl = []
    JoinSet =
[17,20,50,51,65,71,82,86,87,90,93,94,95,98,99,100,101,102,103,
106,
107,108,109,111]
 *** No terms ***

 Relation  1 placed in local spool.
 Target is spool No.  1.
 Leaving OptRelation


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

Attachments

Library

Quick Reference