Re: TPC-H Benchmark Optimization
- From: Paul Drake <discgolfdba@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Jul 2004 10:11:53 -0700 (PDT)
--- "Raphael A. Bauer"
<raphael.bauer@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Hi members,
>
> i am just wondering if anybody knows a good place
> where to find general
> optimization strategies for the TPC-H Benchmark and
> Oracle 9i.
> (eg "using this MV performs good with those
> queries.. and these Indexes
> are cool for...").
> ..
> Thanks !
>
> Raphael
Raphael,
I recall seeing a post by Jonathan Lewis regarding the
use of single table hash clusters with tpc-c.
Aren't the create table scripts usually supplied in
the appendix of the reports?
Does the -H stand for "ad-hoc", "Histograms", "Heroic
dose of hardware" or "Hectare of server room to
accomodate the hardware footprint"?
It would appear from the HP DL740 + Oracle 10g that
RAC + partitioning is used, as well as 4 MB cache per
CPU, 8 GB memory per node, 896 physical disks.
"Implementation of RAID
Implementations may use some form of RAID to ensure
high availability. If used for data, auxiliary storage
(e.g.
indexes) or temporary space, the level of RAID used
must be disclosed for each device.
RAID 0+1 was used for the entire database and redo log
files."
"Data Storage Ratio
The data storage ratio must be disclosed. It is
computed by dividing the total data storage of the
priced
configuration (expressed in GB) by the size chosen for
the test database as defined in 4.1.3.1. The ratio
must be
reported to the nearest 1/100th, rounded up.
10.75"
so they used less than 1/10th of the available
storage.
sounds good to me, I like using about 1/8th.
http://www.tpc.org/results/FDR/tpch/HP_%20ProLiantDL740Cluster_32P_040302_FDR.pdf
controlfiles were on raw volumes
db_block_size=16384
db_file_multiblock_read_count = 128
pga_aggregate_target=6g
strange, they appeared to not have
session_cached_cursors set.
they loaded from flat files using external tables.
orders tables range partitioned into 84 divisions,
hash sub partitioned into 64 tablespaces, degree of
parallelism 64. indexes had initrans 10, freelist
groups 8 (= number of nodes) freelists 99. I don't
think that they were too worried about disk space
usage;). customers tables were hash partitioned.
parts supplier tables were in hash partitioned IOTs.
segment compression was used.
they pre-allocated extents - big ones.
its only 131 pages, why not read the entire doc?
Pd
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
