Re: TPC-H Benchmark Optimization

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Wed, 14 Jul 2004 10:11:53 -0700 (PDT)
Message-ID: <20040714171153.20821.qmail@web20426.mail.yahoo.com>

• "Raphael A. Bauer" <raphael.bauer_at_informatik.hu-berlin.de> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 14 2004 - 12:09:09 CDT