Blog Aggregator - Amardeep Sidhu

Yeah, we use that software. The EPY - Payroll HCM - variety.

And if you do too, you know what a RPITA the pay calc COBOL program can be…

and it doesn’t help when Oracle’s CBO and its bugs joins forces to make it plain unbearable!

Here is an example of what can happen and a possible approach to cope with it.

First, the disclaimers:

1- you may see this if you run Peopletools 8.48 or

Well, I’ve been in Oracle Server Technologies for a whopping week and in that short time I’m reassured of two constants:

Blogging takes time (that I don’t have)
Focusing on a single product, and more specifically a feature within a single product […]

We’re off on an adventure trying to figure out why one of our 9.2.0.5 databases has poor response time during a certain period. During the investigation, I came across a process that was executing a query that had a function call in it. It wasn’t our problem, but was interesting none the less.

I started tracing the user’s session and found that for every row in table T the query was executing function getCount (the names have been changed and the function simplified to clarify the example).

SELECT x, getcount()FROM t

OK, seams reasonable enough, right. But inside getCount() was a query:

create or replace function getcount return integeras   c integer;begin   select count(*) into c from xyz where (code1=’7′ or code2=’3′);   return c;end;

Ah, here’s the query that showed up in the trace as being the top dog.

SELECT COUNT(*)FROMXYZ WHERE (CODE1=’7′ OR CODE2=’3′)

call     count       cpu    elapsed       disk      query    current        rows——- ——  ——– ———- ———- ———- ———-  ———-Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        1      2.47       8.55      57428      63059          0           1——- ——  ——– ———- ———- ———- ———-  ———-total        3      2.47       8.55      57428      63059          0           1

“Hmm, let me just fix this and let the developer know how to fix his problem” I thought to myself, “he’ll appreciate it.”

So I suggested the following query for the fix:

select count(*) from (select id from xyz where code1=’7′unionselect id from xyz where code2=’3′)

call     count       cpu    elapsed       disk      query    current        rows——- ——  ——– ———- ———- ———- ———-  ———-Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.05       0.05          0        666          0           1——- ——  ——– ———- ———- ———- ———-  ———-total        4      0.05       0.05          0        666          0           1

The number of logical reads was much better and the wall clock response time was much better as well. Perfectly suitable solution.

Well, proving that DBAs don’t know anything about how to write code, the fix ended up being:

create or replace function getcount return integeras   cursor c1 is select count(*) cnt from xyz where code1=’7′;   cursor c2 is select count(*) cnt from xyz where code2=’3′;   t integer := 0;begin   for i in c1 loop      t := t+i.cnt;   end loop;   for i in c2 loop      t := t+i.cnt;   end loop;   return t;end;/

SELECT COUNT(*) CNTFROMXYZ WHERE CODE2=’3′

call     count       cpu    elapsed       disk      query    current        rows——- ——  ——– ———- ———- ———- ———-  ———-Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.03       0.03          0        387          0           1——- ——  ——– ———- ———- ———- ———-  ———-total        4      0.03       0.03          0        387          0           1

SELECT COUNT(*) CNTFROMXYZ WHERE CODE1=’7′

call     count       cpu    elapsed       disk      query    current        rows——- ——  ——– ———- ———- ———- ———-  ———-Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.03       0.02          0        279          0           1——- ——  ——– ———- ———- ———- ———-  ———-total        4      0.03       0.02          0        279          0           1

OK, that works. No reason to do something the easy way when you can write code and do it the cool way.

Addition: As Gary points out in the coments, there is a potential in the new function for records with both a ‘3′ AND a ‘7′ to be picked up twice. In my actual data, there is a business rule that makes sure that doesn’t happen, but in general it is an issue.