Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: TEMP operator instead of SORT

    Hello, I generated explain plan for a simple query with a group by clause. I thought a SORT operator would show up in the explain plan because of group by clause but instead a TEMP operator is there. May I know the reason for this and which one if good.

    Plan is as below
    Original Statement:
    ------------------
    select wmi
    from smtumt.wmi_mapping
    order by wmi desc
    with ur


    Optimized Statement:
    -------------------
    SELECT Q1.WMI AS "WMI"
    FROM SMTUMT.WMI_MAPPING AS Q1
    ORDER BY Q1.WMI DESC

    Access Plan:
    -----------
    Total Cost: 0.386435
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    150
    DTQ
    ( 2)
    0.386435
    0
    |
    150
    TBSCAN
    ( 3)
    0.300287
    0
    |
    150
    TEMP
    ( 4)
    0.140275
    0
    |
    150
    IXSCAN
    ( 5)
    0.126729
    0
    |
    150
    INDEX: SYSIBM
    SQL061128113118970
    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This query used an index to resolve the query, and indexes are already sorted. I don't see a group by.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    db2udbgirl, and what is the problem? Total Cost is 0.386435. It shouldn't be a performance problem, is it?

  4. #4
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    Thanks for your comment. I do not face any performance problem but just trying to understand the explain plan.

    Marcus, sorry I meant 'group by' clause by mistake instead of order by.

    Next question: If TEMP operator appears on explain plan, does it imply that piped sort is happening internally? If not, how can I differentiate a piped/non-piped sort from explain plan.

    Thanks again.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is no sort. The data is accessed via the index, which is in the same order as the "order by" so no sort is needed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    ok, how can I identify whether a piped or non-piped sort is happening from explain plan ?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not 100% sure, but I think that DB2 sometimes decides that at execution time based on available memory.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If I remember correctly, then the plan indicates a piped sort in the detailed description to the nodes in the plan. For example, if you have a SORT operator (which you don't need here as Marcus_A explained), then look at the details of that SORT operator. It will tell you whether it is piped or not.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    If I remember correctly, then the plan indicates a piped sort in the detailed description to the nodes in the plan. For example, if you have a SORT operator (which you don't need here as Marcus_A explained), then look at the details of that SORT operator. It will tell you whether it is piped or not.
    The exception would be when you encounter a sort overflow at runtime, which means the sort had to be materialized to a temp table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    The following plan uses a SORT operator but I do not see in explain plan any info about piped/non piped sort. Pls let me know if I'm missing something.

    Original Statement:
    ------------------
    select *
    from smtumt.wmi_mapping
    order by wmi desc
    with ur

    Plan
    Total Cost: 193.128
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    156
    DTQ
    ( 2)
    193.128
    15
    |
    156
    TBSCAN
    ( 3)
    193.032
    15
    |
    156
    SORT
    ( 4)
    193.032
    15
    |
    156
    TBSCAN
    ( 5)
    192.964
    15
    |
    156
    TABLE: SMTUMT
    WMI_MAPPING
    SORT Operation Details:
    4) SORT : (Sort)
    Cumulative Total Cost: 193.032
    Cumulative CPU Cost: 663200
    Cumulative I/O Cost: 15
    Cumulative Re-Total Cost: 0.11493
    Cumulative Re-CPU Cost: 270354
    Cumulative Re-I/O Cost: 0
    Cumulative First Row Cost: 193.032
    Cumulative Comm Cost: 0
    Cumulative First Comm Cost: 0
    Estimated Bufferpool Buffers: 15

    Arguments:
    ---------
    DUPLWARN: (Duplicates Warning flag)
    FALSE
    NUMROWS : (Estimated number of rows)
    156
    ROWWIDTH: (Estimated width of rows)
    68
    SORTKEY : (Sort Key column)
    1: Q1.WMI(D)
    TEMPSIZE: (Temporary Table Page Size)
    16384
    UNIQUE : (Uniqueness required flag)
    FALSE

    Input Streams:
    -------------
    2) From Operator #5

    Estimated number of rows: 156
    Partition Map ID: 3
    Partitioning: ( 0)
    Single Node (# 0) Partition
    Number of columns: 9
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.$RID$+Q1.UMT_UPD_TIMSTM+Q1.UMT_UPD_USR
    ------------
    +Q1.$RID$+Q1.UMT_UPD_TIMSTM+Q1.UMT_UPD_USR
    +Q1.DWH_UPD_TIMSTM+Q1.DWH_EFCTV_TIMSTM
    +Q1.SOURCE_SYSTEM_CD+Q1.USER_ACTION_REQ_FLG
    +Q1.SOURCE_SYSTEM_GRP_CD+Q1.WMI

    Partition Column Names:
    ----------------------
    +NONE


    Output Streams:
    --------------
    3) To Operator #3

    Estimated number of rows: 156
    Partition Map ID: 3
    Partitioning: ( 0)
    Single Node (# 0) Partition
    Number of columns: 8
    Subquery predicate ID: Not Applicable

    Column Names:
    ------------
    +Q1.WMI(D)+Q1.UMT_UPD_TIMSTM+Q1.UMT_UPD_USR
    +Q1.DWH_UPD_TIMSTM+Q1.DWH_EFCTV_TIMSTM
    +Q1.SOURCE_SYSTEM_CD+Q1.USER_ACTION_REQ_FLG
    +Q1.SOURCE_SYSTEM_GRP_CD

    Partition Column Names:
    ----------------------
    +NONE

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since you don't do anything before/after the sort, I'm not very much surprised that you don't see any piping. You should add a UDF call or something else that consume the sorted data before it is returned to the client.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    I generated the plan for this query
    select TRANSACTION_ID, substr(VEH_IDENT_NBR,1,5), SRC_TXN_ID, SVC_LABR_OPRTN_CD, CAMPAIGN_NBR from card.transaction order by src_txn_id;

    The plans remains the same but I see a line item named "SPILLED : (Pages spilled to bufferpool or disk)". Does this mean Non-piped sort is used ?

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    5.22654e+08
    MDTQ
    ( 2)
    1.10367e+07
    3.97515e+06
    |
    7.46649e+07
    TBSCAN
    ( 3)
    1.0838e+07
    3.97515e+06
    |
    7.46649e+07
    SORT
    ( 4)
    1.02049e+07
    3.65451e+06
    |
    7.46649e+07
    TBSCAN
    ( 5)
    5.9553e+06
    3.33387e+06
    |
    7.46649e+07
    TABLE: CARD
    TRANSACTION

    4) SORT : (Sort)
    Cumulative Total Cost: 1.02049e+07
    Cumulative CPU Cost: 4.53529e+11
    Cumulative I/O Cost: 3.65451e+06
    Cumulative Re-Total Cost: 0
    Cumulative Re-CPU Cost: 0
    Cumulative Re-I/O Cost: 320642
    Cumulative First Row Cost: 1.02049e+07
    Cumulative Comm Cost: 0
    Cumulative First Comm Cost: 0
    Estimated Bufferpool Buffers: 3.67795e+06

    Arguments:
    ---------
    DUPLWARN: (Duplicates Warning flag)
    FALSE
    NUMROWS : (Estimated number of rows)
    74664872
    ROWWIDTH: (Estimated width of rows)
    72
    SORTKEY : (Sort Key column)
    1: Q1.SRC_TXN_ID(A)
    SPILLED : (Pages spilled to bufferpool or disk)
    344078
    TEMPSIZE: (Temporary Table Page Size)
    16384
    UNIQUE : (Uniqueness required flag)
    FALSE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •