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

From: keith boulton <boulke_at_globalnet.co.uk>
Date: Sat, 27 Mar 1999 15:35:16 GMT
Message-ID: <36fccaaa.8102911@195.147.246.90>

I've just been testing various options.

Significant init.ora parameters:
#always_anti_join=hash
db_file_multiblock_read_count = 128
# experiment to see this is actually limited to 64k
db_block_buffers = 20000
# 20000 4k blocks = 80M buffer

log_checkpoint_interval = 1000000000
log_buffer = 10485760
sort_area_size = 65536
log_checkpoint_timeout = 0
rollback_segments = ( rb_big )
# a single large rollback segment for my testing
Redo logs where 10M each.

After creating an employee table containing 131072 records with a primary key on employeecode and an empty acltable with a primary key on employeecode, groupname and setting sort_area_size and sort_area_retained_size to 1048576:

SQL> INSERT INTO ACLTable (GroupName, EmployeeCode)   2 SELECT 'X', Employee.EmployeeCode   3 FROM Employee
  4 WHERE Employee.EmployeeCode In

  5     (SELECT EmployeeCode
  6     FROM Employee ) 

  7 AND Employee.EmployeeCode Not In   8 (SELECT ACLTable.EmployeeCode

  9     FROM ACLTable
 10      WHERE GroupName = 'X');

131072 rows created.

 real: 9354

The statement executes in ~ 10 seconds

Repeating the execution so no rows are inserted takes ~8 seconds.

Examination of the access path shows why it is so slow: Execution Plan

---

   0 INSERT STATEMENT Optimizer=CHOOSE (Cost=46 Card=6554 Bytes=104864)

   1 0 FILTER

   2    1     HASH JOIN (Cost=46 Card=6554 Bytes=104864)
   3    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=11 Card=6554 B
          ytes=58986)

   4    2       TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=11 Card=131072
           Bytes=917504)

   5    1     INDEX (UNIQUE SCAN) OF 'ACLTABLE_PK' (UNIQUE) (Cost=2 Ca
          rd=1 Bytes=9)

Two full table scans of the employee table are required, followed by a hash join to identify the employees and then an index search to check if the acltable already has an entry. sort_area_size has no direct impact, but is used in the calculation of hash_area_size. Setting sort_area_size and sort_area_retained_size to 65536 increases the elapsed time to ~20 seconds. Explicitly setting hash_area_size to 10485760 reduces the elapsed time to ~8 seconds wether inserting 0 rows or 130000.

Removing the nested query reduces elapsed times to ~6 seconds.

Using minus rather than not in:
INSERT INTO ACLTable (EmployeeCode, GroupName) SELECT Employee.EmployeeCode , 'X'
FROM Employee
minus
SELECT ACLTable.EmployeeCode, 'X'
  FROM ACLTable
   WHERE GroupName = 'X';

reduces elapsed time to 5 seconds if sort_area_size is 1048576.

There was slight variability (+/- 1 second) depending on the analyze of the acltable being done when the table was full or empty;

All tests were done on an Pentium II 400 MHz with 384M RAM (Oracle taking about 128M) with the redo logs, tables and indexes on separate 7200 RPM IDE disks under NT Workstation 4.0 SP 3. Received on Sat Mar 27 1999 - 09:35:16 CST