Some Benchmark Results

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Wed, 8 Jun 2005 15:39:12 -0500
Message-ID: <E5D2567DDF0D03428A15664A78CA473CA84712@pscdalpexch02.perotsystems.net>

Playing around with my benchmark script and found the following results on one of my servers. The test details can be found below.=20

Question:=20

-> In test 5 logging is disabled but I "log file parallel write" becomes
primary wait, the only action that really improved was the DELETE ALL FROM TABLE. How do you "tune/reduce" this wait event? Does this relate to CPU speed at this point?

Observation:
-> dbwr_io_slaves introduced quite a bit of more wait from "io done" but
did not effect overall run time of the benchmark.=20

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D

• Ran 4 tests
  1. Ran as is.
  2. change db_writer_processes from 1 - 6, much faster
  3. tried dbwr_io_slaves=3D6 - pretty much same results as #2
  4. same as 2 plus _disable_logging=3Dtrue

The benchmark includes the followings tests and these represent the general times for 2-4 with the exception of the DELETE TABLE which was much fast with _disable_logging =3D true in test #4.

INSERT APPEND INTO BENCHMARK                       : 00:00:41.47
GATHER TABLES STATS                                : 00:00:57.13
CREATE TABLE LOGGING                               : 00:00:10.60
UPDATE COLUMN                                      : 00:03:37.02
DELETE ALL FROM TABLE                              : 00:01:46.30
DROP TABLE                                         : 00:03:26.47
CREATE TABLE NOLOGGING                             : 00:00:10.14
INSERT/COMMIT LOOP                                 : 00:00:04.65
BULK UPDATE                                        : 00:05:20.53
TRUNCATE TABLE                                     : 00:00:06.14
ADD COLUMN NUMERIC DEFAULT                         : 00:02:55.60
ADD COLUMN CHARACTER DEFAULT                       : 00:05:52.33
DROP NUMERIC COLUMN                                : 00:04:20.83
DROP CHARACTER COLUMN                              : 00:05:35.75
HARD PARSE                                         : 00:00:21.02

The interesting thing is to look at the changes in wait times from Statspack.

TEST 1

                                                                   Avg
                                                     Total Wait   wait
Waits
Event                               Waits   Timeouts   Time (s)   (ms)

/txn

---------------------------- ------------ ---------- ---------- ------
--------
log file switch (checkpoint         3,936      3,905      3,941   1001
0.4
buffer busy waits                     670        494      1,372   2047
0.1
log file parallel write            13,593          0        305     22
1.3
local write wait                       39         36         37    943
0.0
control file parallel write         1,360          0         29     22

0.1

TEST 2

                                                                   Avg
                                                     Total Wait   wait
Waits
Event                               Waits   Timeouts   Time (s)   (ms)

/txn

---------------------------- ------------ ---------- ---------- ------
--------
log file switch (checkpoint           998        980        995    997
0.1
buffer busy waits                     316        211        442   1399
0.0
log file parallel write            13,245          0        299     23
1.3
db file sequential read            15,574          0         24      2
1.5
control file parallel write           863          0         23     26

0.1

TEST 3

                                                                   Avg
                                                     Total Wait   wait
Waits
Event                               Waits   Timeouts   Time (s)   (ms)

/txn

---------------------------- ------------ ---------- ---------- ------
--------
io done                           537,831         46      2,211      4
53.3
log file switch (checkpoint         1,178      1,150      1,169    992
0.1
buffer busy waits                     262        181        440   1680
0.0
log file parallel write            13,503          0        256     19
1.3
control file parallel write           850          0         21     24

0.1

TEST 4

                                                                   Avg
                                                     Total Wait   wait
Waits
Event                               Waits   Timeouts   Time (s)   (ms)

/txn

---------------------------- ------------ ---------- ---------- ------
--------
log file parallel write           373,088         41      1,417      4
37.0
log file switch (checkpoint           896        881        893    997
0.1
buffer busy waits                     143         90        270   1889
0.0
log buffer space                    2,418          0         54     22
0.2
control file parallel write           882          0         23     26
0.1
db file sequential read            16,102          0         22      1

1.6

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 08 2005 - 16:44:14 CDT