Question: How do I generate automated test
data for oracle? I don’t want to buy a benchmarking tool, but I also want to do
it right, a representative test case with real SQL statements. How do the
professional DBA’s capture SQL workloads for testing?
Answer: First, try to use REAL data, not contrived
test case data. You want a representative bench, but some folks use
dbms_random for generating data. I would write my own, or use
SQL Tuning Sets (STS) in 10g, or use the
SQL Performance Analyzer in 11g.
Also, see these notes on generating test data for Oracle,
Oracle test data & date generation with SQL, and
Generating random test data with dbms_random and my notes here on
Oracle benchmark
testing.
There is a book on
Oracle benchmarking, but it promotes a third-party tool.
Oracle test data generation testing tools
There are several tools and methods for generating test
data for Oracle:
-
STATSPACK - You can easily write your own SQL
capture, directly from the STATSPACK stats$sqlstat table.
-
AWR - AWR captures historical SQL in the
dba_hist_sqlstat table.
-
SQL Tuning Sets – This 10g tool grabs SQL from
the library cache or Oracle.
-
SQL Performance Analyzer (SPA)
– This new 11g tool captures representative SQL workloads.
-
Mercury LoadRunner - A popular tools for
simulating the real-world effect of an Oracle system change.
-
Quest Benchmark Factory - A complete environment
for showing the effects of an Oracle change.
-
DBGEN - The TPC-H tools, dbgen and qgen allow
for the development of anywhere from a gig to a terabyte databases.
-
Hammerora - The Hammerora product provides a
means to create a pseudo-TPC-C database and generate loads against it.
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
