PL/SQL Performance

Hi All --

I’m still having an uphill battle with performance issues. Last week, I posted for help with a “long query”. Since then, I have looked harder at the issue and find that overall performance is horrible and completely unacceptable. Frankly, I can’t believe that the level of performance I am seeing could be considered acceptable by a company that did over $6 billion in sales last year.

Therefore, I must assume that something is terribly wrong with the system.

I developed a benchmarking package to time and record every function call in the most troublesome procedure. After noticing that most function/procedure calls (except those accessing DB tables) took about 0.40 seconds, I became suspicious. I decided to devise a “foolproof” test to see what is REALLY going on. The variable assignment,
 FOO := 1; where FOO is a NUMBER,
also takes between 0.40 and 2.47 seconds. Unbelievable!!! Imagine how this affects the entire application when it’s stuck inside of a 900 iteration loop.

This is Oracle 7.3.x running on Solaris. Last week we quadrupled the RAM and added another CPU. Neither of these had any affect on the times recorded. I’ve timed and tuned EVERY call to the DB. the customer is very unhappy and won’t even use the product (sound familiar?).

Has anyone performed similar benchmark tests? How were you’re times? I am thinking to upgrade to Oracle 8 and just forget the whole issue. Will this help me? My company just got a $10 million contract and I am REALLY hesitant to develop another product on Oracle if this is the performance I can expect. Any and all feedback appreciated.

Lynn
PS: for those who are interested or assisted me with query advice. Time results follow.

ORIGINAL QUERY:
TIME = 1.03 seconds ROWS returned = 134

SELECT
A.full_product_number, A.ffn ... B.insname FROM chpa_test_product A,

     chpa_insurer B
WHERE B.ffn = A.ffn
 AND A.county_id IN (select distinct F.id    from chpa_county F
   where lower(F.county) IN ('broward', 'dade'))  AND A.plan_type IN ('HMO','PPO','HMO','HMO','HMO')  AND A.deductible IN (0,100,250,500,750,2000)  AND LOWER(A.benefit_level) IN

('basic','standard','street','plusplan')
AND A.ffn IN (select distinct D.ffn

   from chpa_county D
   where lower(D.county) IN ('broward','dade')    AND D.ffn IN (select distinct C.ffn
     from chpa_insurer C)) ;

MODIFICATION #1 DROP THE LAST SUB-QUERY (which was not actually needed as several people pointed out -- thank you !!) TIME = 0.82 seconds ROWS returned = 134

<snip>
WHERE B.ffn = A.ffn
 AND A.county_id IN (select distinct F.id    from chpa_county F
   where lower(F.county) IN ('broward', 'dade'))  AND A.plan_type IN ('HMO','PPO','HMO','HMO','HMO')  AND A.deductible IN (0,100,250,500,750,2000)  AND LOWER(A.benefit_level) IN

('basic','standard','street','plusplan')
AND A.ffn IN (select distinct D.ffn

   from chpa_county D
   where lower(D.county) IN ('broward','dade'));

MODIFCATION # 2 USE EXISTS INSTEAD OF SUB-QUERY (I’m not sure if I used this feature properly -- but I didn’t get the correct results and it took forever)
TIME = 11.45 seconds ROWS returned = 222

<snip>
WHERE B.ffn = A.ffn
 AND A.plan_type IN ('HMO','PPO','HMO','HMO','HMO')  AND A.deductible IN (0,100,250,500,750,2000)  AND LOWER(A.benefit_level) IN

('basic','standard','street','plusplan')
 AND EXISTS (select count(*)
   from chpa_county F
   where A.county_id = F.id
   and A.ffn = F.ffn
   and lower(F.county) IN ('broward', 'dade')); Received on Sat May 16 1998 - 00:00:00 CDT