 |
|
Oracle Tips by Burleson |
Query
Processing
The qgen program
produces 22 example decision support system (DSS) queries. The queries
use aggregation, subqueries, order
bys and group bys to simulate the processing in a DSS environment. The
queries were placed into a single file and run back-to-back in the
tests. STATSPACK and custom scripts were used to monitor the database.
SSD Results
In the SSD test runs, the following configurations
were tested:
-
Base run to load buffers
-
No loggingon
all tables and no archive log setting
-
No
loggingon all tables with
archive logging
-
Logging and archive logging
The SSD runs showed very constant times for all
the various configurations after query eight was run in the base load
run number one. This type of profile recurred after a shutdown
startup, as shown in the graph in Figure 5.1. The entire set of seven
total runs only required three days to process from May 28, 2004
through June 1, 2004. These were not run back-to-back but as time
allowed. Figure 5.1 shows the comparisons of the various SSD
configurations.
As shown in Figure 5.1, even the poorest
performing query, query number one, required just more than 600
seconds to complete in its worst run, run number seven. The complete
query timings are shown in Table 5.1. Run number six, with archive
loggingturned on, showed nearly
identical query timing results as compared with previous runs, until
the next to last three queries. After a restart, query number seven
showed similar performance to run number one, even with archive
logging turned on. The three poorly, relative to the other SSD runs,
performing queries returned to normal performance levels.
Using the SSD array, the use of loggingand archive logging had little or no effect on
performance.
The above book excerpt is from:
Oracle RAC & Tuning with
Solid State Disk
Expert Secrets for High
Performance Clustered Grid Computing
ISBN
0-9761573-5-7
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm
|
