?
Solved

DB2 v7.2 Table Function Performance Issue

Posted on 2009-02-14
15
Medium Priority
?
1,001 Views
Last Modified: 2012-05-06
Good day Experts,

We are currently working in DB2 environment and we wrote two DB2 User Defined Table Functions (UDF).  Let's called TableFunction_A, and TableFunction_B.  The TableFunction does the following:

TableFunction_B:
select col1, col2, col3 from table_B_1
union all
select col1, col2, col3 from table_B_2
union all
select col1, col2, col3 from table_B_3;

TableFunction_A:
select col1, col2, col3 from table_A_1
union all
select col1, col2, col3 from table_A_2
union all
select col1, col2, col3 from table_A_3
union all
select col1, col2, col3 from table(tablefunction_b()) as tabfuncb;

When we did the following test, we got some interesting performance:
(1) When we execute the TAbleFunction_B by itself, results come back within 30 seconds.  
(2) We take the select statement from the TableFunction_A and run it, results come back within 35 seconds.  
(3) But when we execute TableFunction_A by itself, it took over 25 minutes for the results to return.

As from the above, the select statement is the same in scenario (2) and (3), we expected the results should be somewhat the same, and it certainly surprises us.  

Is there anyone experience this and if someone knows what the cause to it so that we can get this fix?

Thank you very much for your time and your input is highly appreciated.

Thank you
Elliot
0
Comment
Question by:E_Mak
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 23641769
which version of db2 are you using?
have you tried to recreate the function? maybe db2 has a bad access plan cached
do you have the access plan of the query vs the access plan of the function?
0
 

Author Comment

by:E_Mak
ID: 23641886
The version of DB2 I am using is  v7.2.

WE have recreated the functions and we also tried different optimization plan for it.  

I do have access plan of the query vs the access plan of the function.  What we have noticed the following:
Access Plan of the Function
1. Contains many SYSIBM.GENROW
2. The total cost for the operation is much higher than the access plan of the query.

I have enclosed the access plan for both and hope you can spot something that I missed.  

Thank you very much
0
 

Author Comment

by:E_Mak
ID: 23641888
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 23643960
ok
i must ask, why do you use a function?
do you use input parameters or just the statements you wrote?
did you consider using views instead?
0
 

Author Comment

by:E_Mak
ID: 23644771
The reason why we are using table function because in our datawarehouse, we have minutes, day, month tables.  With the table function, we can pass a date range input parameter, and the table function will fetch the data from each individual tables, depending on the date range, it will use either one or all the tables.  

We did consider using views, but it does not allow logic in within.  For example, If I specified a 12 hours range, it will only fetch from the minute table, If I specified a week range, it will fetch from minute and day tables.  
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 23644941
Hi E_Mak,

Something that escapes most people is that a view is an atomic operation.  The view generates a result set (table) that is passed out of the view for the next operation in the query.  Explain plans tell us the order of each of the operations -- select, join, scan, sort, etc.  They indicate that the DBMS doesn't have any magic intelligence.  Instead, they show step-by-step what the DBMS is doing.

In this case, when you execute TableFunction_A, TableFunction_B returns a derived table that is then merged into another derived table via the UNION ALL.  It becomes a sort of nested query where the indexes can be used only on the inner-most subquery.  Note line 684 of the function's plan and line 1 of the query's plan.  The plans almost align at these points, yet the cost in the function plan is typically much higher per step.  For most of the rest of the queries, the per-step cost in the function plan is typically 8 to 10 times the cost in the query plan.

That's actually pretty typical of these kinds of operations.


I'm interested in the database (date warehouse) design.  It's probably beyond your scope to "fix" it, but having minute, day, and month tables suggests that this isn't a true data warehouse.  (It may be a data mart, but there are at least 3 overlapping (not normalized) kinds of tables just for the time dimension.)  I have trouble visualizing a database where this can be a good thing.


Good Luck,
Kent
0
 

Author Comment

by:E_Mak
ID: 23644981
Hi Kent

Thank you very much for the insight.  Yes, it is a datamart, not a datawarehouse.  We have inherited the datawarehouse and datamart.  The volume and the requirements make it quite difficult to fix the underlying issue with the DW and DM.  

Thanks
Elliot
0
 

Author Comment

by:E_Mak
ID: 23649755
Follow up with the above, I have been monitoring the execution, here is what I have found

1. When using table function (scenario #3),  it spends majority of the time in Prefetch Wait (over 80% of the time)
2. When using the select statements (scenario #2), there is no prefetch wait, just User CPU time and Sort time.

Does it make any sense?
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 23650073
Hi E_Mak,

DB2 can perform syncronous and asyncronous I/O. When it's doing async I/O, you see Prefetch Wait times and in this case you're seeing a lot of it. (My understanding is that DB2 actually maps the tablespace files over memory and performs syncronous I/O by direct memory access and lets the operating system's page fault handler do the I/O. It's hard to beat that performance.) The first mystery is why DB2 is executing the function using asnyc I/O. The second is why the wait times are so high.

I honestly don't know what has caused DB2 to switch to async I/O. Let me do a bit of research -- perhaps I can find the trigger.

Regarding the high wait times, this suggests that there are not enough I/O servers. In olden times, the standard rule was to start with as many servers as you had physical disks. With today's SAN environments most of the I/O comes through a single channel, albeit a very fast one.

Check the number of I/O servers on the database. Then bump it up a bit. Your post doesn't indicate the underlying hardware, so I'm going to assume unix. :)

db2 connect to mydatabase
db2 get db cfg | grep IOS

I believe that the default is 6. If the current value is less than 6, double it. Otherwise boost it by about 50%.


Kent
0
 
LVL 7

Assisted Solution

by:waynezhu
waynezhu earned 500 total points
ID: 23658862
You may try re-define TableFunction_A using 5 unions (just try to be to easy on  the optimizer):

TableFunction_A:
select col1, col2, col3 from table_A_1
union all
select col1, col2, col3 from table_A_2
union all
select col1, col2, col3 from table_A_3
union all
select col1, col2, col3 from table_B_1
union all
select col1, col2, col3 from table_B_2
union all
select col1, col2, col3 from table_B_3;

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 23658891
Hi Wayne,

Too bad they don't have an architect designing this.  All of the data needs to be in a single table so that the query filters on a single index.  Then, if necessary, partition the table by date.


Kent
0
 

Author Comment

by:E_Mak
ID: 23658959
It is very unfortunate that the system was designed that way.  It is very difficult to resolve any issue when my group is taking over the system.   The system was designed by an architect, but a different type of architect.  

The IO Server did help, but minor.  Redefining TableFunction_A is my next step.  Will keep you guys posted.

0
 
LVL 7

Expert Comment

by:waynezhu
ID: 23659458
It is a UDF right? Drop and re-create it?
0
 

Author Closing Comment

by:E_Mak
ID: 31546943
Thank you very much Guys.
0
 

Author Comment

by:E_Mak
ID: 23710477
Hi Wayne, Kent
The issue is so wide spread that there were multiple areas that we are looking into.  

The suggestions that you have provided were part of it.  And your suggestions are highly appreciated.

Thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Integration Management Part 2
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question