 |
|
Oracle Tips by Burleson |
Benchmark
Methodology
In many benchmark tests multiple "tricks" are used
to obtain the desired results, for example some tests use undocumented
Oracle parameters to force Oracle into a "federated" architecture
during RAC tests. Others use excessive memory settings or require
hardware that would never be utilized in a true operating situation.
The purpose of these tests is not to skew the results in favor of a
desired outcome, but to represent the SSD architecture as it would be
applied in a standard environment. Therefore the desired methodology
used during this test had to meet the following requirements:
-
Be easily repeated by anyone desiring to do so
-
Not involve complex, undocumented initialization
parameters
-
Utilize off-the-shelf components in a standard
configuration
-
Database size and configuration should be as
"real-world" as possible
To meet these requirements it was decided to
utilize the TPCH benchmark
database and query generator. The
dbgenand qgenprograms are available as a download from the
www.tpc.org
website. In order to ensure the test was applicable to as wide an
audience as possible the database size was set at 20 gigabytes, which
for the TPCH benchmark translated into 54 gigabytes once indexes and
support tablespaces are added.
The test method involved the following steps:
1.
Download and compile the TPCH benchmark programs using standard
options and C compiler. The selected compile options where for
Solaris, SQL Server. Oracle was not a listed option, but SQL Server
provided standard SQL statements.
2.
Ftp'ed 20000511.tar.z, which was downloaded from www.ftp.org,
to aultlinux1 and used gunzip and tar to extract files.
3.
cd'ed to dbgendirectory
4.
Used cp to copy makefile.suite to makefile
5.
Used vi to edit makefile to set variables:
DATABASE =
SQLSERVER (Oracle option not available)
MACHINE=SUN
(closest to LINUX, DOS didn't work)
WORKLOAD = TPCH
6.
Ran command "make"
7.
Download and install latest Oracle 9i database and required
patch (3095277) to bring the database to 9.2.0.4 on the internal disk
drives for Linux server.
8.
Download and install client software on second Linux box
9.
Configure solid state drive and SCSI/ATA drives using standard
Linux disk formatting and file systems commands using filesystem 2
no-journaling devices.
pvcreate -d /dev/sda
pvcreate -d /dev/sdbvgcreate -l 256 -p
256 -s 128k /dev/dss_volume /dev/sda
vgcreate -l 256 -p
256 -s 128k /dev/dss_volume2 /dev/sdb
lvcreate -L 10g
/dev/dss_volume /dev/dss_vol11
lvcreate -L 10g
/dev/dss_volume /dev/dss_vol12
lvcreate -L 10g
/dev/dss_volume /dev/dss_vol13
lvcreate -L 8g
/dev/dss_volume /dev/dss_vol14
lvcreate -L 8g
/dev/dss_volume /dev/dss_vol15
lvcreate -L 8g
/dev/dss_volume /dev/dss_vol16
lvcreate -L 8g
/dev/dss_volume /dev/dss_vol17
lvcreate -L 10g
/dev/dss_volume2 /dev/dss_vol21
lvcreate -L 10g
/dev/dss_volume2 /dev/dss_vol22
lvcreate -L 10g
/dev/dss_volume2 /dev/dss_vol23
lvcreate -L 8g
/dev/dss_volume2 /dev/dss_vol24
lvcreate -L 8g
/dev/dss_volume2 /dev/dss_vol25
lvcreate -L 8g
/dev/dss_volume2 /dev/dss_vol26
lvcreate -L 8g
/dev/dss_volume2 /dev/dss_vol27
e2mkfs
/dev/dss_vol11 through /dev/dss_vol27
mkdir /u01
through /u14
chown oracle.dba
/u01 through /u14
mount
/dev/dss_vol11 /u01 through /dev/dss_vol27 /u14
10.
In order to create the database, the
dbcautility provided by Oracle was utilized to
generate a
standard set of scripts for a DSS database excluding the example
schemas.
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
connect / as
SYSDBA
set echo on
spool /home/oracle/assistants/dbca/logs/CreateDB.log
startup nomount pfile="/home/oracle/admin/dss/scripts/init.ora";
CREATE DATABASE dss
MAXINSTANCES 2
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/oracle/oradata/dss/system01.dbf' SIZE 250M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/u05/oracle/oradata/dss/temp01.dbf' SIZE 400M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE '/u04/oracle/oradata/dss/undotbs01.dbf' SIZE 200M
REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 ('/u02/oracle/oradata/dss/redo01.log') SIZE 102400K,
GROUP 2 ('/u03/oracle/oradata/dss/redo02.log') SIZE 102400K,
GROUP 3 ('/u02/oracle/oradata/dss/redo03.log') SIZE 102400K;
spool off
exit;
createdbfiles.sql
connect / as
SYSDBA
set echo on
spool /home/oracle/ora9i/assistants/dbca/logs/CreateDBFiles.log
CREATE
TABLESPACE "DRSYS" LOGGING DATAFILE
'/u02/oracle/oradata/dss/drsys01.dbf' SIZE 20M REUSE AUTOEXTEND
ON NEXT 640K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
AUTO ;
CREATE
TABLESPACE "TOOLS" LOGGING DATAFILE '/u02/oracle/oradata/dss/tools01.dbf'
SIZE 50m REUSE AUTOEXTEND ON NEXT 128K
MAXSIZE 8192m
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE
TABLESPACE "XDB" LOGGING DATAFILE '/u03/oracle/oradata/dss/xdb01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K
MAXSIZE
UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
spool off
exit;
createDBCatalog.sql
connect / as
SYSDBA
set echo on
spool
/home/oracle/assistants/dbca/logs/CreateDBCatalog.log
@/home/oracle/rdbms/admin/catalog.sql;
@/home/oracle/rdbms/admin/catexp7.sql;
@/home/oracle/rdbms/admin/catblock.sql;
@/home/oracle/rdbms/admin/catproc.sql;
@/home/oracle/rdbms/admin/catoctk.sql;
@/home/oracle/rdbms/admin/owminst.plb;
connect SYSTEM/manager
@/home/oracle/sqlplus/admin/pupbld.sql;
connect SYSTEM/manager
set echo on
spool /home/oracle/assistants/dbca/logs/sqlPlusHelp.log
@/home/oracle/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off
exit;
intermedia.sql
connect / as
SYSDBA
set echo on
spool /home/oracle/assistants/dbca/logs/interMedia.log
@/home/oracle/ord/im/admin/iminst.sql;
spool off
exit;
context.sql
connect / as
SYSDBA
set echo on
spool /home/oracle/assistants/dbca/logs/context.log
@/home/oracle/ctx/admin/dr0csys change_on_install DRSYS TEMP;
connect CTXSYS/change_on_install
@/home/oracle/ctx/admin/dr0inst /home/oracle/bin/oractxx9.dll;
@/home/oracle/ctx/admin/defaults/dr0defin.sql AMERICAN;
spool off
exit;
xdbprotocol.sql
connect / as SYSDBA
set echo on
spool /home/oracle/assistants/dbca/logs/xdb_protocol.log
@/home/oracle/rdbms/admin/catqm.sql change_on_install XDB TEMP;
connect / as SYSDBA
@/home/oracle/rdbms/admin/catxdbj.sql;
spool off
exit;
jserver.sql
connect / as
SYSDBA
set echo on
spool /home/oracle/assistants/dbca/logs/JServer.log
@/home/oracle/javavm/install/initjvm.sql;
@/home/oracle/xdk/admin/initxml.sql;
@/home/oracle/xdk/admin/xmlja.sql;
@/home/oracle/rdbms/admin/catjava.sql;
spool off
exit;
connect / as
SYSDBA
set echo on
spool /home/oracle/assistants/dbca/logs/postDBCreation.log
@/home/oracle/rdbms/admin/utlrp.sql;
shutdown ;
connect / as SYSDBA
set echo on
spool /home/oracle/ora9i/assistants/dbca/logs/postDBCreation.log
create spfile='/home/oracle/database/spfiledss.ora' FROM pfile='/home/oracle/admin/dss/scripts/init.ora';
startup ;
exit;
11.
Next, scripts were manually generated to create required
the
tablespaces to support the benchmark (dss_dataand
dss_index).
This is the script for the DSS portion of
the benchmark. The SCSI/ATA portion script was identical except for
the substitution of the appropriate SCSI/ATA filesystem, for
example,
u08 for u01, u09 for u02, etc.
create tablespace
dss_data datafile
'/u01/oracle/oradata/dss/dss_data01.dbf'
size 9000m,
'/u02/oracle/oradata/dss/dss_data02.dbf'
size 8000m,
'/u03/oracle/oradata/dss/dss_data03.dbf'
size 8000m
extent management
local segment space management auto
/
create tablespace
dss_index datafile
'/u04/oracle/oradata/dss/dss_index01.dbf'
size 8300m,
'/u06/oracle/oradata/dss/dss_index02.dbf'
size 7500m,
'/u07/oracle/oradata/dss/dss_index03.dbf'
size 7500m
extent management
local segment space management auto
/
12.
After the required DSS tablespaces are created the DSS_ADMIN
user is created using the following manually generated script.
create user
dss_admin identified by dss_admin
default tablespace
dss_data
quota unlimited on
dss_data
quota unlimited on
dss_index;
grant resource,
dba, connect to dss_admin
/
13.
Once the tablespaces and user were in place the
dss_admin schema was built based on the table descriptions given in
the DSS benchmark documentation. A manual script was generated to
create these tables.
-
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
create table
PART
(p_partkey number not null,
p_name varchar2(55),
p_mfgr char(25),
p_brand char(10),
p_type varchar2(25),
p_size integer,
p_container char(10),
p_retailprice decimal(12,2),
p_comment varchar2(23),
constraint part_pk primary key (p_partkey)
using index
tablespace dss_index) nologging;
create table
REGION
(r_regionkey number not null,
r_name char(25),
r_comment varchar2(152),
constraint region_Pk primary key (r_regionkey)
using index
tablespace dss_index) nologging;
create table
NATION
(n_nationkey number not null,
n_name char(25),
n_regionkey number not null,
n_comment varchar2(152),
constraint nation_pk primary key (n_nationkey)
using index
tablespace dss_index,
constraint nat_region_fk foreign key (n_regionkey)
references region(r_regionkey)) nologging;
create table
SUPPLIER
(s_suppkey number not null,
s_name char(25),
s_address varchar2(40),
s_nationkey number not null,
s_phone char(15),
s_acctbal decimal(12,2),
s_comment varchar2(101),
constraint supplier_pk primary key (s_suppkey)
using index
tablespace dss_index,
constraint supp_nation_fk foreign key (s_nationkey)
references nation(n_nationkey)) nologging;
create table PARTSUPP
(ps_partkey number not null,
ps_suppkey number not null,
ps_availqty integer,
ps_supplycost decimal(12,2),
ps_comment varchar2(199),
constraint partsupp_pk primary key (ps_partkey, ps_suppkey)
using index
tablespace dss_index,
constraint partsupp_part foreign key (ps_partkey) references
part(p_partkey),
constraint partsupp_supp foreign key (ps_suppkey) references
supplier(s_suppkey)) nologging;
create table CUSTOMER
(c_custkey number not null,
c_name varchar2(25),
c_address varchar2(40),
c_nationkey number not null,
c_phone char(15),
c_acctbal decimal(12,2),
c_mktsegment char(10),
c_comment varchar2(117),
constraint customer_pk primary key (c_custkey)
using index
tablespace dss_index,
constraint cust_nation_fk foreign key (c_nationkey) references
nation(n_nationkey)) nologging;
create table
ORDERS
(o_orderkey number not null,
o_custkey number not null,
o_orderstatus char,
o_totalprice decimal(12,2),
o_orderdate date,
o_orderpriority char(15),
o_clerk char(15),
o_shippriority integer,
o_comment varchar2(79),
constraint orders_pk primary key (o_orderkey)
using index
tablespace dss_index,
constraint order_cust_fk foreign key (o_orderkey) references
orders(o_orderkey)) nologging;
create table
LINEITEM
(l_orderkey number not null,
l_partkey number not null,
l_suppkey number not null,
l_linenumber integer,
l_quantity decimal(12,2),
l_extendedprice decimal(12,2),
l_discount decimal(4,2),
l_tax number,
l_returnflag char,
l_linestatus char,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment varchar2(44),
constraint lineitem_pk primary key (l_orderkey,l_linenumber)
using index
tablespace dss_index,
constraint linei_partsupp_fk foreign key (l_partkey,l_suppkey)
references partsupp(ps_partkey, ps_suppkey)) nologging;
14.
Next, the flat files used to load the database were generated
using the dbgenutility:
$ export DSS_PATH=/home/oracle/benchmark/tables
$ dbgen –s 20 –C
10
15.
Once the database was ready and the required flat files
generated, the Oracle supplied SQLLoader utility was used to load the
database. Example parameter files used and example of loaded data are
provided in Appendix A. The load process was scripted.
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
#!/bin/bash
set +x
date
sqlldr dss_admin/dss_admin control=part1.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part2.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part3.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part4.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part5.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part6.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part7.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part8.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part9.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=part10.ctl log=part.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=region.ctl log=region.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=nation.ctl log=nation.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier1.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier2.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier3.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier4.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier5.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier6.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier7.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier8.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier9.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=supplier10.ctl log=supplier.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp1.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp2.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp3.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp4.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp5.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp6.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp7.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp8.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp9.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=partsupp10.ctl log=partsupp.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer1.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer2.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer3.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer4.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer5.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer6.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer7.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer8.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer9.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=customer10.ctl log=customer.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders1.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders2.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders3.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders4.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders5.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders6.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders7.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders8.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders9.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=orders10.ctl log=orders.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem1.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem2.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem3.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem4.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem5.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem6.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem7.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem8.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem9.ctl log=lineitem.log rows=1000
bindarraysize=1000000
sqlldr
dss_admin/dss_admin control=lineitem10.ctl log=lineitem.log rows=1000
bindarraysize=1000000
date
16.
Once the database was built and loaded with data, the indexes
were created using a manually generated index creation script. The
full script is located in Appendix A.
17.
The index build completed the data loading. Next the schema was
analyzed to ensure that the cost-based optimizer (CBO) had proper
statistics with which to determine proper query paths. Here is the
script used to generate the statistics.
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
spool
analyze.log
select to_char(sysdate,'dd-mon-yy hh24:mi.ss') from dual;
execute dbms_stats.gather_schema_stats('DSS_ADMIN');
select to_char(sysdate,'dd-mon-yy hh24:mi.ss') from dual;
spool off
exit;
18.
After the database was prepared, the
qgenutility was utilized to generate the 22
standard queries
for the TPCH Benchmark. A complete set of which is listed in Appendix
B. For the most part the 22 scripts could be run as is, however there
were SQLServer specific function calls which had to be altered into
their Oracle counterparts.
$ export DSS_PATH=/home/oracle/benchmark/gen_queries
$ export DSS_QUERY=/home/oracle/benchmark/queries
$ qgen>/home/oracle/benchmark/gen_queries/queries.lst
19.
Once the database was built, loaded, indexed and analyzed, the
22 standard queries were run against the database for various
configurations of temp, redo, archive logging and memory
configurations.
20.
After completing the test on the SSD architecture, the database
was dropped and rebuilt using the above procedures on the SCSI/ATA
drives
Rebuild indexes on the SCSI/ATA database using the
standard scripts.
Re-analyzed the SCSI/ATA database DSS_SCHEMA using
the standard script.
Re-ran the 22 standard queries against the SCSI/ATA
database for various configurations of temp, redo, data, archive
logging and memory configurations.
Compiled and analyzed all results and generated
report.
The above methodology proved flexible enough to
deal with loss of the SCSI array, require rebuilds due to power
outages and several other situations which were encountered during the
SCSI/ATA testing.
The above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
|
