Overview
The Test IO utility is devised specifically to test disk I/O response on a known query with known data and is based on the benchmark disk IO performance. There are many parameters that can be changed in the Oracle database to improve database performance in general. There can be multiple reasons causing the system to be slow, sometimes it could be an issue with the DB level disk performance. To resolve this issue, run an Oracle DB level test to check if DB level disk performance is, or is not an issue. The test should not be dependent on INFA product(s) and can be run on any DB irrespective of the fact whether it is, has, or does not have INFA product(s) installed along with it. The test performs bulk operations on a few millions of records. Oracle statistics are then used to assess the disk IO performance.
Download
- test_io.zip (See the Attachment section below)
Usage
The file test_io.zip contains: test_io.dmp & test_io.sql
- Import test_io.dmp into your schema: $ imp <my_user>/<my_pass> fromuser=thin_sp2 touser=<my_user> file=test_io ignore=y
- Login to the same user and run test_io.sql script: $ sqlplus <my_user>/<my_pass>@<mytns> SQL> @test_io
- Then cd to your Oracle udump directory and use tkprof to generate the output file.
$ tkprof machinename_ora_nnnn_test_io.trc test_io.txt sys=no
Sample Output
The output that we are specifically interested in is for the INSERT /*+ append portion of the txt file.
INSERT /*+ append noparaellel */ INTO T8 SELECT /*+ noparallel (a) */ * FROM
T1 A
call | count | cpu | elapsed | disk | query | current | rows
|
Parse | 1 | 0.01 | 0.00 | 0 | 1 | 0 | 0 |
Execute | 3 | 67.01 | 105.08 | 249720 | 256276 | 260011 | 20000000 |
Fetch | 0 | 0.00 | 0.00 | 0 | 0 | 0 | 0 |
total | 4 | 67.03 | 105.09 | 249720 | 256277 | 260011 | 20000000 |
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 (recursive depth: 1)
Rows | Row Source Operation |
2 | LOAD AS SELECT (cr=256276 pr=249720 pw=251848 time=105085835 us) |
20000000 | TABLE ACCESS FULL T1 (cr=251896 pr=249718 pw=0 time=60009296 us) |
Elapsed times include waiting on following events:
Event waited on | Times Waited | Max. Wait | Total Waited |
control file sequential read | 10 | 0.00 | 0.00 |
db file scattered read | 8413 | 0.01 | 36.27 |
db file sequential read | 277 | 0.00 | 0.06 |
direct path write | 5896 | 0.00 | 0.00 |
Usage Instructions
- Search the Test_IO output for the statement OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS as follows:
- Verify if 20 million records were processed. If it was not, something went wrong with the script.
- Review the number of seconds seen in the total elapsed time.
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call | count | cpu | elapsed | disk | query | current | rows |
Parse | 735 | 0.01 | 0.05 | 0 | 1 | 0 | 0 |
Execute | 738 | 43.69 | 76.72 | 511988 | 518341 | 523816 | 20000488 |
Fetch | 733 | 0.02 | 1.22 | 194 | 1294 | 0 | 489 |
total | 2206 | 43.73 | 78.00 | 512182 | 519636 | 523816 | 20000977 |
3. Informatica identifies the following levels of performance based on the following average timings to be:
- Total Elapsed Time: 30 seconds: Ideal goal, however can require significant storage tuning
- Total Elapsed Time: 50 seconds: Great performance
- Total Elapsed Time: 80 seconds: Satisfactory performance for low volumes. Could do better
- Total Elapsed Time: 120+ seconds: Poor performance
Comments