Test IO Utility

Version 1

    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

    1. Import test_io.dmp into your schema: $ imp <my_user>/<my_pass> fromuser=thin_sp2 touser=<my_user> file=test_io ignore=y
    2. Login to the same user and run test_io.sql script: $ sqlplus <my_user>/<my_pass>@<mytns> SQL> @test_io
    3. 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       

    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

    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:

     

      1. Verify if 20 million records were processed. If it was not, something went wrong with the script.
      2. 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