PL/SQL Performance
Date: 1998/05/16
Message-ID: <355DF4CF.BF7AAE3B_at_ix.netcom.com>#1/1
Hi All --
[Quoted] 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 [Quoted] [Quoted] 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.
[Quoted] 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 [Quoted] 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
[Quoted] A.full_product_number, A.ffn ... B.insname
[Quoted] 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)) ;
[Quoted] 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)
[Quoted] 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 CEST