Oracle v7.3.2 Benchmark Tests

The following is a comparison of the execution time (in seconds) of 10 different benchmark tests. The test were performed on a V7.14 and a v7.3.2 database. The databases are identical, except for upgrading database objects. The v7.1.4 datafiles and parameter files were copied to different directories on the same system and same devices for the v7.3.2 database.

Does anyone have any ideas why v7.3.2 does not perform as well as v7.1.4? I have tried changing several of the initialization paramters (COMPATIBILITY, SORT_DIRECT_WRITES), and none have changed the perfomance. In fact setting SORT_DIRECT_WRITES to FALSE made matters worse.

Summary of statments in benchmark tests:

bench01	INSERT INTO using SELECT ... FROM ... WHERE ...
bench02	DELETE FROM ... WHERE ...
bench03	SELECT ... FROM ... WHERE ... GROUP BY ... (with SQL*Plus Formatting)
bench04	PL/SQL: 2 CURSORs, 2 LOOPs, 1 SELECT  (with formatted output)
bench05	PL/SQL: 2 SELECTs, 1 INSERT, and 1 UPDATE (no cursors)
bench06	PL/SQL: 2 SELECTs, 1 INSERT, and 1 UPDATE
bench07	PL/SQL: 1 CURSOR(select ... from ... where ... group by ... order by)


	(with formatted output)

bench08	PL/SQL: 2 SELECTs, 1 INSERT, and 1 UPDATE
bench09	PL/SQL: 1 CURSOR, 2 SELECTs,  1 LOOP(INSERT, UPDATE),1 UPDATE
bench10	SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...


	(with SQL*Plus Formatting)

Here's the results:

                v7.1.4 RULE v7.3.2 RULE v7.3.2 COST
SERIAL (executed 3 times in series)

bench01		 14.0		 15.9		 22.0
bench02		  8.8		  8.8		  9.1
bench03		 54.6		 71.2		 30.5
bench04		 22.4		 31.2		 12.9
bench05		  1.2		  1.5		  1.5
bench06		  0.9		  1.2		  1.2
bench07		104.8		119.3		 92.7
bench08		  0.9		  0.9		  0.9
bench09		  3.9		  4.7		  4.5
bench10		 76.3		100.0		 44.2
TOTAL		287.8		354.7		219.5

PARALLEL (executed in series using 3 simultaneous SQL*Plus sessions)
bench01		 26.4		 23.7		 34.5
bench02		 20.8		 19.7		 19.7
bench03		 63.5		 77.2		 39.8
bench04		 18.3		 31.9		 25.9
bench05		  1.2		  1.5		  1.4
bench06		  1.1		  1.2		  1.2
bench07		247.8		220.9		224.4
bench08		  1.0		  1.0		  1.0
bench09		  5.2		  5.5		  6.8
bench10		136.5		159.6		 69.7
TOTAL		521.8		542.2		424.4 

COMBINATION (executed 3 times in series using 3 simultaneous SQL*Plus sessions)
bench01		 54.8		 44.8		 58.8
bench02		 44.6		 40.3		 40.8
bench03		 95.6		119.2		 73.2
bench04		 21.8		 25.9		 27.6
bench05		  1.5		  1.7		  1.5
bench06		  1.4		  1.6		  1.4
bench07		393.6		346.5		451.3
bench08 	  1.4		  1.3		  1.1
bench09		  8.9		  9.4		  6.4
bench10		191.4		199.9		122.9
TOTAL		815.0		790.6		785.0

Darren Darnell
Database Systems Team
Edward Jones
201 Progress Parkway
Maryland Heights, MO 63043-3042

 Phone:  314.515.1172 
   FAX:  314.515.2599
e-mail:  darren.darnell_at_edwardjones.com