Re: PL/SQL Performance

I think you have major problems-- Oracle is not that slow... or at least I should not be that slow.

You probably need to bring in a consultant to review the configuration of the hardware,
sector size, distribution of data, indexes, size of table spaces, the database design.

Lynn Ranen wrote:

> 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
>

This is 6000-8000 rows a minute

> 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)) ;
>

This is a pretty poor looking query. All the lower() and IN's are relatively expensive.How and which fields are indexed? Have you run explain on the query to determine
if the indexes are being used and how many full table scans are being performed? A good index using county, plan_type, and deductibel could be useful.

> 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'));

  The exists ( select count(*) ... ) is pretty counter productive. The exists allows one row to answer the problem. By using a count(*), the entire query must be run to completion. A better example would be

        and exists ( select 'X' from chpa_county F....)

Mike Krolewski Received on Tue May 19 1998 - 00:00:00 CEST