Re: ORACLE 8.0.3.0.0 is extremely slow vs SQL7 for medium database

In article <7d8l2j$m9n$1_at_starburst.uk.insnet.net>,   "Julian Cowking" <jcowking_at_hrms.co.uk> wrote:
> I am writing an app which I need to work with both SQL server 7 and Oracle
> 8x
>
> Here are my problems...
>
> 1) with small databases things are very comparible between the two. However
> when using a medium database (100,000 employees, say) Oracle begins to
> perform very poorly in comparison with SQLServer 7. Now, I thought that
> Oracle was supposed to come into its own with larger databases. I am a
> complete newbie to Oracle so, I imagine that I must start some tweeking to
> get things really flying.
>
> Here is the SQL being executed
>
> INSERT INTO ACLTable (GroupName, EmployeeCode) SELECT 'X',
> Employee.EmployeeCode FROM Employee WHERE Employee.EmployeeCode In (SELECT
> EmployeeCode FROM Employee) AND Employee.EmployeeCode Not In (SELECT
> ACLTable.EmployeeCode FROM ACLTable WHERE GroupName = 'X')
>
> This executes in about 5 seconds in SQL7 when the Employee table has 100,000
> records in it (if the result of the INSERT query is not many rows and about
> 15-20 seconds if it has to insert 100,000 rows). However with Oracle this
> query takes ages (over 20 minutes).

Oracle requires a pretty good knowledge of the tuning parameters available in order to utilize it to its fullest potential. Can you share with us what is in your init parm file? You may not have enough database block buffers assigned, or perhaps there are some other parameters which require review.

Also, you should check to see if you are running in Rule based or Cost based mode. If you're in cost based optimization mode, you need to make sure your tables and indexes have been analyzed to collect statistics.

Tony Rodgers

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Mar 24 1999 - 09:16:22 CST