Database Reference
In-Depth Information
Table 4.2
Calc Time by Number of Processors
Processors per
Calculation Script
1
Processor
2
Processor
3
Processor
5
Processor
6
Processor
total Elapsed time
133 seconds
79 seconds
68 seconds
42 seconds
62 seconds
Table 4.3
Calculation Speed by Processor
Processors per
Calculation Script
1
Processor
2
Processor
3
Processor
5
Processor
total Elapsed time
653 seconds
663 seconds
698 seconds
773 seconds
So, what do the test times in table 4.2 tell us? Clearly this database likes processors.
In this case, the clock seems more accurate than the task ratio calculation. take special note
to compare the times between five and six processors. This is not an anomaly. This single
aggregation continued to respond favorable to additional processors. once the number of
processors specified in the script equaled the number available on the server, performance
dropped. The conclusion is that it is important to leave a processor for the operating system.
Can this test be considered real world? Certainly, if this calculation is run in batch
mode where it is the only calculation running. What happens if there are multiple calcu-
lations? Will parallelism still be faster? oracle suggests that if the database must support
highly concurrent calculations, such as in a Planning application, CALCPArALLEL
should be used with great care. The theory is that when there are multiple calculations
executed concurrently, the number of processors on the server can be exceeded when
multiple processors are specified in the script. to prove this out, I created four identical
databases so that block locking would not be an issue. Four maxL scripts were launched
simultaneously from a single CmD Windows batch file. none of the scripts share cache
resources other than the operating system file cache. There will be disk contention
because the storage is common to all. The calculation is the previous aggregation script
that showed improvement as the number of processors increased (table 4.3).
The results are interesting. The first test reserved four processors, one for each of the
maxL scripts. The second test occupied eight processors and so on. The tests clearly show
that requesting more processors than are available on the server degrades the performance.
If this were a real world test where this script was run concurrently, what is the cor-
rect number of processors? That all depends on whether we are tuning for worst case,
best case, or average. Worst case, the number of processors should be one; best case, we
might assume that it is unlikely that multiple scripts would ever be run, so the number
of processors would be set to five. For the average case, two processors show the largest
performance boost when the script is run stand alone, but only suffers slightly with four
competing calculations. A specification of two processors takes advantage of parallel
processing when fewer calculations are in the mix, but does not suffer the huge hit that
is seen with greater than two processors.
4 .7.1
Block Count as a Speedometer
When trying to quantify calculation scripts overall, time is the metric that counts, but I
also like to use data blocks. The log file captures information about reads and writes of
the databases that are invaluable. not only can I/o information help with tuning a script,
it also can provide information about the data distribution within the database. recently,
