Db2 (On Premises and Cloud)

Expand all | Collapse all

Sort memory consumes double sortheap cfg value

  • 1.  Sort memory consumes double sortheap cfg value

    Posted Wed September 15, 2021 11:31 AM
    hi,
    i have a database with SHEAPTHRES_SHR set to 50000 and SORTHEAP set to 5000.
    For the database 200MB of sort space in total for database and 20 MB per sort operation.
    I have a table with 1.8GB of data approx. [db2 "CREATE TABLE STAFF_EXCEPTIONS(STAFFID INTEGER NOT NULL,NAME VARCHAR(50 OCTETS),SALARY INTEGER ,INC INTEGER,NETSAL INTEGER)"]
    Scenario 1:
    db2 +c "alter table staff activate not logged initially"
    db2 +c "create index myidx on staff(staffid)"
    Monitor shared sort heap consumption is 5000 pages top. it reaches around 19.6 MB
    Total number of sort is only one.
    db2 drop myidx
    db2 reset monitor all
    Scenario 2:
    db2 "select staffid from staff order by staffid"
    Monitor shared sort heap consumption is 10000 pages top. it reaches around 40 MB
    Total number of sort is only one.
    why select query consumes twice the amount of sort memory? where as create index also uses sort memory but only consumes half of that.

    i performed the same test case in 9.7, in both scenarios it takes only 5000 pages of sort memory. man this is bad because it takes same time to complete the task but double the memory.

    thanks,
    Harish P


    ------------------------------
    Harishkumar Pathangay
    ------------------------------


  • 2.  RE: Sort memory consumes double sortheap cfg value

    Posted 9 days ago

    Hi Harish, you don't mention what level of Db2 you are running for the test, and if you are using Row Store or Columnar in your test in which you observe 2x the sortheap being used.

    Thanks.
    Ian

    Db2 Runtime and Optimizer Development



    ------------------------------
    Ian Finlay
    ------------------------------



  • 3.  RE: Sort memory consumes double sortheap cfg value

    Posted 9 days ago

    Hi,

    The scenario is observed in 10.5 Fp8 and 11.1 and 11.5 also with ROW organized tables.

    It is working properly in 9.7 Fp4

     

    I have even created videos demonstrating the scenario.

     

    https://youtu.be/CfiF5IEbwwU Using Shared Sort Memory

    https://youtu.be/TSEqdLT4ZFg using Private Sort Memory

     

    Thanks,

    Harish Pathangay

     

    Sent from Mail for Windows