Slow query in DB2 due to Cartesian

Recently one developer came to me complaining poor sql performance and here is how I investigated the issue.

Our environment:
AIX 7.1
DB2 9.7.0.7

db2top -d dbname

              ConnTime..:     15:10:01.316    UOW Start.:     15:10:01.322    Appl name.:            db2bp    DB2 user..:         CAIJI
              OS user...:            caiji    Agent id..:            62181    Coord DBP.:                0    Coord id..:            46105
              Client pid:         26149284    Hash joins:               10    Hash loops:                0    HJoin ovf.:                0
              SQL Stmts.:                1    Sorts.....:                0    Sort time.:            0.000    Sorts ovf.:                0
              Rows Read.:      548,603,258    Rows Sel..:                0    Read/Sel..:                0    Rows Wrtn.:                1
              Rows Ins..:                0    Rows Upd..:                0    Rows Del..:                0    Locks held:           10,756
              Trans.....:                0    Open Curs.:                0    Rem Cursor:                0    Memory....:           512.0K
              Dyn. SQL..:                1    Static SQL:                0    Cpu Time..:       167.325260    AvgCpuStmt:          167.325


              Start.....:     15:10:01.322    Stop......:              N/A    Cpu Time..:       167.325030    Elapse....:         0.000000
              FetchCount:                0    Cost Est..:    2,147,483,647    Card Est..:      173,053,520    AgentTop..:                1
              SortTime..:                0    SortOvf...:                0    Sorts.....:                0    Degree....:                1
              Agents....:                1    l_reads...:       12,440,420    p_reads...:                0    DataReads.:       12,378,643
              IndexReads:           61,775    TempReads.:                2    HitRatio..:          100.00%    MaxDbpCpu.:      0.000000[0]
              IntRowsDel:                0    IntRowsUpd:                0    IntRowsIns:                0

MERGE INTO CAIJI.BOND_BASE_INFO AS IN_T using (select * from   CAIJI.V_BOND_BASE_INFO_C where RCRD_ST <> '4') 
as SRC ON(IN_T.BOND_ID = SRC.BOND_ID OR IN_T.BOND_NO=SRC.CFAT_BOND_CD) AND IN_T.DELETE_MARK<>1 
 WHEN MATCHED and (IN_T.APPR_RCRD_USR_TMST<>SRC.APPR_RCRD_USR_TMST)
 OR IN_T.APPR_RCRD_USR_TMST IS NULL THEN  update set ...

An observation was that “Rows Read” increased rapidly during the sql execution and after checking the number of rows of two row sources, we can confirm that the poor performance was due to Cartesian product of them, which could reach more than 1.1 billion.

db2 "select count(*) from   CAIJI.V_BOND_BASE_INFO_C where RCRD_ST <> '4' with ur"

1          
-----------
      23161

db2 "select count(*) from CAIJI.BOND_BASE_INFO with ur"

1          
-----------
      51024

23161*51024 = 1181766864

Lessons Learned

1 Rows Read implies the rows processed during SQL execution
2 AIX p750 could process 1.1 billion rows in about 10 minutes, which means 2 million rows per second.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s