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

First thing FIRST:
1. Why are you joining EMPLOYEE table to ITSELF ?

    "WHERE Employee.EmployeeCode In (SELECT EmployeeCode FROM Employee) "

• this by it self is a killer ! 2. You are using to many SUBQUERIES Oracle doesnt like it, TUNE your select, or use PLSQL cursors instead 3. Check your RBS(rollback segments) -- size them properly give it at least 20 MINEXTENs @ 512k or 1m or 5m, depending on your LOADs use THAT RBS for your load by: SQL> SET TRANSACTION USE ROLLBACK SEGMENT <RBS_NAME>; or SQL> EXECUTE dbms_transaction.use_rollback_segment('<RBS_NAME>'); You can later check which RBS is being used:

select s.username

,        rn.name
from     v$session   s
,        v$transaction t
,        v$rollstat    r
,        v$rollname    rn
where    s.saddr = t.ses_addr
and      t.xidusn = r.usn
and      r.usn = rn.usn;

OR use PLSQL and do COMMITs avery 1000 rows you load, if you can't create BIG ROLLBACK SEGMENT. 4. selecting from tables that belong to a USER has nothing to do with the tablespace those tables are IN,

    if you dont want to specify alias:
SQL> select * from USER.TABLE_NAME;

   you can do this:
SQL> grant select on <TABLE_NAME> to <USER_NAME>; SQL> create public synonym <SYNONYM_NAME_same_as_table_name> for <TABLE_NAME>;

   after you do this you can select by specifying the SYNONYM_NAME: SQL> select * from <SYNONYM_NAME>;

Vitaliy Mogilevskiy

Julian Cowking 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). 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?
>
> 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.
>
> Many thanks in advance,
>
> Julian
Received on Tue Mar 23 1999 - 17:25:04 CST