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

On Tue, 23 Mar 1999 18:04:59 -0000, "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')
>

I've found this sort of thing improves enormously by:

Modify the following parameters in init<sid>.ora

log_checkpoint_interval = 1000000000
log_buffer = 1048576
log_checkpoint_timeout = 0

increase the value of db_block_buffers depending on available memory each buffer will take one database block (probably 2K).

Also, add new larger redo log files and remove the existing ones - see then alter database add logfile, alter system switch logfile and alter database drop logfile commands. I use either 10M or 100M files.

>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). I had to extend the number of extents
>to 'unlimited' after Oracle compained that it had not enough extents for
>rollback (this error happened after about 2 minutes).
>
>Both databases are running on identical machines.
>
>Any ideas?

Given that SQLServer is designed for NT only, I would expect it to run slightly faster.

>2) when creating users how can i modify them so that i can issue select
>statements like 'SELECT * FROM Mytable' rather than 'SELECT * FROM
>MyTableSpace.MyTable' - i.e. they have a default tablespace to select from
>(like a default database for SQL Server)? I have tried configuring the
>default tablespace and granting permission on the relevant objects but this
>is obviously wrong since I get 'table/view does not exist' unless i specify
>the tablespace.
>

I think you are confusing tablespace with schema. However, what you need to do is define public synonyms for the tables.
>
Received on Fri Mar 26 1999 - 02:24:52 CST