Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Linda Webb

Got breaking Oracle news?    Burleson Consulting Oracle News

Click here for more Oracle News Headlines

 


Free Script to Measure max I/O speed of your Oracle Server
 

Andy Rivenes has published a nice test script to perform a Logical I/O test rate.  Because physical I/O is disk/network intensive and logical I/O (a consistent get from the data buffer cache) is CPU-intensive, this script is useful for calculating the workload capacity of Oracle servers with large buffer caches (i.e. a 64-bit Oracle server).

 

FILE- cpu_test.sql -- -- AUTHOR- Andy Rivenes

 

This script computes logical I/O per second for the given processor(s), and assuming that logical I/O is CPU-centric, this script will give a high-water-mark (HWM) for buffered I/O throughput, a critical metric for Oracle server capacity planning. 

PROMPT > Logical I/O Rate Test ;
PROMPT >  This script will generate output that can be used to ;
PROMPT >  correlate Oracle logical I/Os to CPU capacity and speed. ;
PROMPT >  This can be used to compare CPU speed between platforms and ;
PROMPT >  Oracle releases or to correlate workload capacity for the ;
PROMPT >  system in question. ;
PROMPT > ;
PROMPT >  The basic premise is that Oracle logical I/Os translate directly ;
PROMPT >  to CPU usage. By measuring the time it takes to execute a series ;
PROMPT >  of logical I/Os we can measure the rate a CPU can execute a ;
PROMPT >  logical I/O.  Since all logical I/Os are not the same, the numbers ;
PROMPT >  produced by this test should be considered theoretical maximums. ;
PROMPT >  These numbers will give values that can be used to measure CPU ;
PROMPT >  capacity and relative speed for an Oracle database, and should be ;
PROMPT >  within an acceptable margin of error. ;
PROMPT > ;
PROMPT >  NOTE: It is important that physical reads remain constant for each ;
PROMPT >  loop in order to insure that only logical I/Os affect the timing. ;
PROMPT >  A busy SGA may prevent this as well as CPU queuing for overall ;
PROMPT >  timing.  It is recommended that this test be run on an idle, or ;
PROMPT >  near idle machine. ;
PROMPT > ;
PROMPT >  The results of this script show the session logical reads ;
PROMPT >  performed for each loop, which should be constant, and the total ;
PROMPT >  time, which is in hundredths of a second. The statistic "physical ;
PROMPT >  reads" is included to verify that all blocks were cached. ;
PROMPT >  These values can be used to calculate the maximum logical I/Os per ;
PROMPT >  second that can be executed: ;
PROMPT > ;
PROMPT >  session logical reads / ( total time / 100 ) = maximum logical ;
PROMPT >  I/Os per second ;
PROMPT > ;
PROMPT >  If calculating for capacity against existing information ;
PROMPT >  (e.g. SYSMON data) then the total period (elapsed time) must be ;
PROMPT >  known, the total number of CPUs available, and some fudge factor ;
PROMPT >  (e.g. CPU queuing, SMP scalability): ;
PROMPT >    Interval capacity (with 20% fudge factor) = ;
PROMPT >      logical I/Os per second * total time * # of CPUs * .8 ;

declare
  cursor dbtest_cur is
    select col2
      from dbtest;
  --
  cursor stat_cur is
    select b.name,
           a.value
      from v$sesstat a,
           v$statname b,
           v$session se
     WHERE se.audsid = USERENV('SESSIONID')
       AND a.statistic# = b.statistic#
       AND se.sid = a.sid
       AND b.name IN ('session logical reads',
                      'physical reads',
                      'CPU used when call started',
                      'CPU used by this session',
                      'buffer is pinned count',
                      'consistent gets',
                      'db block gets')
     ORDER BY b.class,
           b.name;
  --
  var_col      varchar2(100);
  var_start    number;
  var_end      number;
  var_ctr      integer;
  var_loop     integer;
  var_lio_beg  integer;
  var_lio_end  integer;
  var_phy_beg  integer;
  var_phy_end  integer;
  var_cpu      integer;
begin
  --
  -- Get the number of CPUs
  --
  select value
    into var_cpu
    from v$parameter
   where name = 'cpu_count';
  --
  -- Load all blocks into the buffer cache
  --
  for dbtest_rec in dbtest_cur loop
    null;
  end loop;
  --
  for var_loop in 1..4 loop
    --
    dbms_output.put_line('**********');
    dbms_output.put_line('Loop > '||to_char(var_loop));
    dbms_output.put_line('**********');
    --
    for stat_rec in stat_cur loop
      dbms_output.put_line(stat_rec.name||' = '||to_char(stat_rec.value));
      if stat_rec.name = 'session logical reads' then
        var_lio_beg := stat_rec.value;
      elsif stat_rec.name = 'physical reads' then
        var_phy_beg := stat_rec.value;
      end if;
    end loop;
    dbms_output.put_line('*');
    --
    select hsecs
      into var_start
      from v$timer;
    --
    for var_ctr in 1..20 loop
      for dbtest_rec in dbtest_cur loop
        null;
      end loop;
    end loop;
    --
    select hsecs
      into var_end
      from v$timer;
    --
    for stat_rec in stat_cur loop
      dbms_output.put_line(stat_rec.name||' = '||to_char(stat_rec.value));
      if stat_rec.name = 'session logical reads' then
        var_lio_end := stat_rec.value;
      elsif stat_rec.name = 'physical reads' then
        var_phy_end := stat_rec.value;
      end if;
    end loop;
    dbms_output.put_line('**');
    --
    dbms_output.put_line( 'total time: '||to_char(var_end - var_start) );
    if ( var_phy_end - var_phy_beg ) = 0 then
      dbms_output.put_line( 'LIOs/sec per CPU: '||
        to_char(ROUND((var_lio_end-var_lio_beg)/((var_end-var_start)/100),0)) );
      dbms_output.put_line( 'LIOs/sec system total: '||
        to_char(ROUND((var_lio_end-var_lio_beg)/((var_end-var_start)/100),0)*var_cpu) );
    else
      dbms_output.put_line( 'Physical reads took place, timing not valid.' );
    end if;
  end loop;
end;
/
--
SELECT a.sid,
       a.statistic#,
       SUBSTR(b.name,1,40) name,
       a.value
  FROM v$sesstat a,
       v$statname b,
       v$session se
 WHERE se.audsid = USERENV('SESSIONID')
   AND a.statistic# = b.statistic#
   AND se.sid = a.sid
   AND b.name IN ('session logical reads',
                  'physical reads',
                  'CPU used when call started',
                  'CPU used by this session',
                  'buffer is pinned count',
                  'consistent gets',
                  'db block gets')
 ORDER BY b.class,
       b.name
/
--
SPOOL off;
--
drop table dbtest
/

 

 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational