Dear experts,
Here I have a very confusing optimizer behavior to consult:
There is one select statement which access the Database view VIAUFK_AFVC in my ERP ECC 6.06 system on DB2:
SELECT * FROM "VIAUFK_AFVC" WHERE "MANDT" = ? AND "AUFNR" = ? AND "VORNR" = ?
FETCH FIRST 1 ROWS ONLY OPTIMIZE FOR 1 ROWS WITH UR
The VIAUFK_AFVC is a view which is compose of 7 database table: AFIH, AUFK, AFKO, AFVC, AFVV, ILOA, AFVU. So the select statement would be joining the 7 database tables.
Now the statement is executing very slowly, and the execution plan is :
SQL Statement
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM "VIAUFK_AFVC" WHERE "MANDT" = ? AND "AUFNR" = ? AND "VORNR" = ?
FETCH FIRST 1 ROWS ONLY OPTIMIZE FOR 1 ROWS WITH UR
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Access Plan Opt Level = 5 ; Parallelism = None
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT ( Estimated Costs = 2.455E+06 [timerons] ) num_rows tot_cost i/o_cost
|
--- 1 RETURN 4.1881E-03 2.4550E+06 5.9170E+05
|
--- 2 NLJOIN 4.1881E-03 2.4550E+06 5.9170E+05
|
|-- 3 [O] NLJOIN 4.1881E-03 2.4550E+06 5.9170E+05
| |
| |-- 4 [O] NLJOIN 4.1881E-03 2.4549E+06 5.9169E+05
| | |
| | |-- 5 [O] NLJOIN 4.1881E-03 2.4549E+06 5.9169E+05
| | | |
| | | |-- 6 [O] HSJOIN 4.1881E-03 2.4549E+06 5.9169E+05
| | | | |
| | | | |-- 7 [O] FETCH AFVV 4.1881E-03 2.4549E+06 5.9168E+05
| | | | | |
| | | | | --- 8 RIDSCN 4.1881E-03 2.4549E+06 5.9168E+05
| | | | | |
| | | | | --- 9 SORT 4.1881E-03 2.4549E+06 5.9168E+05
| | | | | |
| | | | | --- 10 IXAND 4.1881E-03 2.4549E+06 5.9168E+05
| | | | | |
| | | | | |-- 11 NLJOIN 1.0187E+01 3.0291E+01 4.0007E+00
| | | | | | |
| | | | | | |-- 12 [O] FETCH AFKO 1.0000E+00 2.2705E+01 3.0007E+00
| | | | | | | |
| | | | | | | ------ 13 IXSCAN AFKO~0 #key columns: 2 1.0000E+00 1.5136E+01 2.0000E+00
| | | | | | |
| | | | | | ------ 14 [I] IXSCAN AFVV~0 #key columns: 2 1.0187E+01 1.5147E+01 2.0000E+00
| | | | | |
| | | | | --- 15 NLJOIN 3.7664E+03 2.4548E+06 5.9168E+05
| | | | | |
| | | | | |-- 16 [O] NLJOIN 3.7664E+03 2.3978E+06 5.8415E+05
| | | | | | |
| | | | | | |-- 17 [O] TBSCAN 3.7664E+03 2.3118E+06 5.7278E+05
| | | | | | | |
| | | | | | | --- 18 SORT 3.7664E+03 2.3116E+06 5.7271E+05
| | | | | | | |
| | | | | | | ------ 19 TBSCAN AFVC 3.7664E+03 2.3110E+06 5.7265E+05
| | | | | | |
| | | | | | --- 20 [I] FETCH AFVU 9.9999E-01 2.2844E+01 3.0178E+00
| | | | | | |
| | | | | | ------ 21 IXSCAN AFVU~0 #key columns: 3 9.9999E-01 1.5146E+01 2.0000E+00
| | | | | |
| | | | | ------ 22 [I] IXSCAN AFVV~0 #key columns: 3 9.9999E-01 1.5145E+01 2.0000E+00
| | | | |
| | | | --- 23 [I] NLJOIN 1.0000E+00 3.7974E+01 5.0178E+00
| | | | |
| | | | |-- 24 [O] FETCH AFIH 1.0000E+00 2.2820E+01 3.0159E+00
| | | | | |
| | | | | ------ 25 IXSCAN AFIH~0 #key columns: 2 1.0000E+00 1.5136E+01 2.0000E+00
| | | | |
| | | | --- 26 [I] FETCH ILOA 1.0000E+00 2.2714E+01 3.0018E+00
| | | | |
| | | | ------ 27 IXSCAN ILOA~0 #key columns: 2 1.0000E+00 1.5137E+01 2.0000E+00
| | | |
| | | --- 28 [I] FETCH AUFK 9.9999E-01 2.2784E+01 3.0110E+00
| | | |
| | | ------ 29 IXSCAN AUFK~0 #key columns: 2 9.9999E-01 1.5137E+01 2.0000E+00
| | |
| | --- 30 [I] FETCH AFKO 1.1113E-06 2.2706E+01 3.0007E+00
| | |
| | ------ 31 IXSCAN AFKO~0 #key columns: 2 1.0000E+00 1.5137E+01 2.0000E+00
| |
| --- 32 [I] FETCH AFVC 4.1108E-04 2.2735E+01 3.0033E+00
| |
| ------ 33 IXSCAN AFVC~0 #key columns: 3 9.9976E-01 1.5147E+01 2.0000E+00
|
--- 34 [I] FETCH AFVU 9.9999E-01 2.2845E+01 3.0178E+00
|
------ 35 IXSCAN AFVU~0 #key columns: 3 9.9999E-01 1.5147E+01 2.0000E+00
Checking the execution plan, I found the TBSCAN on table AFVC, which is the most expensive operation because there are 9,160,118 entries in the table.
And I wonder the root cause to these is the join order of the tables. With another join order there shouldn't be TBSCAN on the table AFVC.
When I test the same statement on another system, I got a pretty execution plan as follows:
SQL Statement
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM "VIAUFK_AFVC" WHERE "MANDT" = ? AND "AUFNR" = ? AND "VORNR" = ?
FETCH FIRST 1 ROWS ONLY OPTIMIZE FOR 1 ROWS WITH UR
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Access Plan Opt Level = 5 ; Parallelism = None
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT ( Estimated Costs = 8,607E+01 [timerons] ) num_rows tot_cost i/o_cost
|
--- 1 RETURN 3.0090E-05 8.6066E+01 1.1370E+01
|
--- 2 NLJOIN 3.0090E-05 8.6066E+01 1.1370E+01
|
|-- 3 [O] NLJOIN 1.5045E-04 7.6979E+01 1.0170E+01
| |
| |-- 4 [O] NLJOIN 7.5063E-04 6.7878E+01 8.9685E+00
| | |
| | |-- 5 [O] HSJOIN 2.5327E-01 5.4924E+01 7.2573E+00
| | | |
| | | |-- 6 [O] FETCH AFKO 1.0000E+00 1.5147E+01 2.0016E+00
| | | | |
| | | | ------ 7 IXSCAN AFKO~0 #key columns: 2 1.0000E+00 7.5719E+00 1.0000E+00
| | | |
| | | --- 8 [I] HSJOIN 2.5327E-01 3.9776E+01 5.2556E+00
| | | |
| | | |-- 9 [O] FETCH AUFK 1.0000E+00 1.5153E+01 2.0023E+00
| | | | |
| | | | ------ 10 IXSCAN AUFK~0 #key columns: 2 1.0000E+00 7.5720E+00 1.0000E+00
| | | |
| | | --- 11 [I] NLJOIN 2.5327E-01 2.4622E+01 3.2532E+00
| | | |
| | | |-- 12 [O] FETCH AFIH 1.0000E+00 1.5134E+01 2.0000E+00
| | | | |
| | | | ------ 13 IXSCAN AFIH~0 #key columns: 2 1.0000E+00 7.5718E+00 1.0000E+00
| | | |
| | | --- 14 [I] FETCH ILOA 2.5327E-01 9.4878E+00 1.2532E+00
| | | |
| | | ------ 15 IXSCAN ILOA~0 #key columns: 2 2.5327E-01 7.5720E+00 1.0000E+00
| | |
| | --- 16 [I] FETCH AFVC 2.9636E-03 1.2961E+01 1.7122E+00
| | |
| | ------ 17 IXSCAN AFVC~0 #key columns: 2 7.1128E-01 7.5736E+00 1.0000E+00
| |
| --- 18 [I] FETCH AFVV 2.0043E-01 9.1005E+00 1.2017E+00
| |
| ------ 19 IXSCAN AFVV~0 #key columns: 3 2.0043E-01 7.5743E+00 1.0000E+00
|
--- 20 [I] FETCH AFVU 1.9999E-01 9.0873E+00 1.2000E+00
|
------ 21 IXSCAN AFVU~0 #key columns: 3 1.9999E-01 7.5744E+00 1.0000E+00
The questions here is :
What can I do to make the former DB2 optimizer to generate the pretty execution plan like the latter one? The statistics are quiet up to date for these 7 tables. Do REORGs to these tables works?
Because I am not an DB expert , so I hope to get some suggestions from you.
Kindly let me know if you need more information.
Thanks in advance.
Waison
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.