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

1. You can also check your SORT_AREA_SIZE settings in the init.ora to make sure all sorts are done in MEMORY. If you dont have enough RAM size your TEMP tablespace properly defaul storage (initial <SORT_AREA_SIZE+DB_BLOCK_SIZE> next <SORT_AREA_SIZE+DB_BLOCK_SIZE> pctincrease 0)
2. You can also CACHE the table in the MEMORY this way ORACLE will do all the reads from SGA not from the DISK: SQL> alter table <TABLE_NAME> cache;
3. You can check if SGA is full by running this:

 select round(used.bytes /1024/1024 ,2)  used_mb
 ,      round(free.bytes /1024/1024 ,2)  free_mb
 ,      round(tot.bytes  /1024/1024 ,2)  total_mb
 from (select sum(bytes) bytes
      from   v$sgastat
      where  name != 'free memory')        used
 ,    (select sum(bytes) bytes
      from   v$sgastat
      where  name = 'free memory')         free
 ,    (select sum(bytes) bytes
      from   v$sgastat)                    tot

IF nesessary increase SGA (shared_pool_size) size in the INIT.ORA and recycle the database
for changes to take effect.

There lots of things to look at, I dont want to re-write the book !

Good luck!

VItaliy Mogilevskiy

Julian Cowking wrote:

> Vitaliy,
>
> Is there any way i can force a table within a schema to use a specific
> rollback segment? (Since I am trying to run the same code for SQLServer and
> Oracle is there any way this could be configured through, say, Enterprise
> manager?)
> At the moment it seems to be random. The 'SET TRANSACTION USE ROLLBACK
> SEGMENT <RBS_NAME>;' statement has help enormously but this is used via
> SQLWorksheet and would be annoying to put in the middle tier logic for my
> components.
>
> However, using this has helped me able to get all the rows in. When using
> this method it takes just over 2 minutes to insert 100,000 records. If the
> query needs to insert NO records (but still checks all 100,000 possible
> inserts) the time taken is 15 seconds.
> Although this is much better than before, compared to SQL Servers
> comparitive times of 17sec and 6sec respectively, Oracle is still lagging.
> I have configured the extents, and RSs (although still need to know how to
> use them without specifying it in SQL) and although this has made a BIG
> difference SQL7 is still considerably ahead.
>
> Any futher ideas?
>
> Many thanks
>
> Julian
Received on Wed Mar 24 1999 - 11:23:12 CST