Menu
▾
▴
Tree [1b3cee] master / History
Read Me
PIOB means PL/SQL Input/Output Benchmark
PIOB is PL/SQL Input Output benchmarking tool.
It generates different kinds of I/O using Oracle database means
and measures it.
The PIOBs idea is same as SLOB (Silly Little Oracle Benchmark) -
PIOB benchmarks disk I/O using Oracle.
You can use PIOB for similar tasks -
tuning of disk subsystem on a machine where you have your Oracle.
However PIOB is different from SLOB in following aspects:
- it is OS and platform independent because it is implemented purely in
Oracle SQL*Plus, SQL and PL/SQL;
- it does not use Oracle AWR (AWR requires additional licensing);
- it takes snapshots of only relevant performance metrics;
- it is instrumented for monitoring its progress;
- SEED table is tuned to have 1 row per 8K block for more precise calculation
of random I/O;
- it uses RECYCLE buffer of minimal size to reduce caching;
- it keeps some configuration parameters in its repository;
Licensing
Free for personal and business use.
GNU General Public License v3 applies.
Read LICENSE.TXT
Warrany and liability
NO WARRANTY
THE PROGRAM IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY
WARRANTY. IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU.
SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY
SERVICING, REPAIR OR CORRECTION.
IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW THE AUTHOR WILL BE LIABLE TO YOU
FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES
ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT
LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY
YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER
PROGRAMS), EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH
DAMAGES.
Installation
Run
> sqlplus /nolog @install.sql
This will ask you for:
- sysdba account: for local database and if you are OSDBA you should enter /,
otherwise enter SYS or SYSTEM
- TNS name: for local database you can leave it blank,
otherwise enter TNS name prepended with @. Example: @REMOTEDB
- Datafile for PIO tablespace: enter full path for datafile of PIOB tablespace.
Uninstallation
Run
> sqlplus <SYSDBA logon> @uninstall.sql
This will stop all PIOB jobs, drop all PIOB schema objects, drop PIOB user
and PIOB tablespace if it is empty.
Usage
1. Run PL/SQL package PIOB_TOOL.RUN with or with at least one worker specified.
Examples:
Benchmarking for 5 min with using OLTP-like pattern
SQL> exec PIOB_TOOL.RUNT(300,READ_SHORT_WORKERS=>5,READ_LONG_WORKERS=>1,WRITE_LIGHT_WORKERS=>3);
Benchmarking with using Short Reader
SQL> exec PIOB_TOOL.RUNC(SHORT_READER_WORKERS=>1);
Benchmarking with using two Light Writer and custom cycle count
SQL> exec PIOB_TOOL.RUNC(WRITE_LIGHT_WORKERS=2, WRITE_LIGHT_COUNT=20000);
Benchmarking for 2 min with using Long Reader and Heavy Writer
SQL> exec PIOB_TOOL.RUNT(120,READ_LONG_WORKERS=>1, WRITE_HEAVY_WORKERS=>1);
Benchmarking with using two Redo Writers
SQL> exec PIOB_TOOL.RUN(WRITE_REDO_WORKERS=>2);
2. In another session you can monitor progress of PIOB
SQL> select * from PIOB.MONITOR;
You can stop PIOB anytime by calling PIOB_TOOL.STOP
3. After step 1 is finished you can get a report
SQL> set serveroutput on;
SQL> exec PIOB_TOOL.REPORT;
This will show report for last benchmark.
Use benchmark id for reporting previous benchmarks.
SQL> exec PIOB_TOOL.REPORT(2);
How it works
When you call PIOB_TOOL.RUN* it will create several Oracle Scheduler jobs for
workers that you specified with *_WORKERS parameter(s).
These jobs will simultaneusly execute workers.
Oracle I/O metrics will be saved just before workers start generating
actual I/O and right after that.
When you run PIOB_TOOL.REPORT the saved metrics will be used to calculate
more commonly used metrics: IO/s for short random I/O and MB/s for long sequential I/O.
Workers description
Workers are PL/SQL procedures that generates different types of Oracle I/O
by executing SQL statements. Query statements will generate reads, DMLs
will generate both reads and writes of different kinds and in different
proportions. Following describes I/O patterns for different types of workers.
READER_SHORT - <cnt> times randomly reads little number of rows from the table with <tbl#>.
I/O pattern:
SHADOW intensively reads using INDEX access, reading
one random table block and possibly one random index block each cycle.
DBWR barely writes.
LGWR barely writes.
ARCH barely reads and writes, when in Archivelog mode.
READER_LONG - <cnt> times full scans all rows from the table with <tbl#>.
I/O pattern:
SHADOW intensively reads using FULL TABLE SCAN access to all table blocks.
DBWR barely writes.
LGWR barely writes.
ARCH barely reads and writes, when in Archivelog mode.
WRITER_LIGHT - <cnt> times randomly updates two columns in little number of rows in the table with <tbl#>.
I/O pattern:
SHADOW intensively reads using INDEX access, reading
one random table block and possibly one random index block each cycle.
DBWR intensively writes modified table and index blocks.
LGWR moderately writes one changed column value.
ARCH moderately reads and writes, when in Archivelog mode.
WRITER_HEAVY - <cnt> times fully updates 256 rows in the table with <tbl#>.
I/O pattern:
SHADOW intensively reads using INDEX access, reading
one random table block and possibly one random index block each cycle.
DBWR intensively writes modified table and index blocks.
LGWR intensively writes 20 changed column values.
ARCH intensively reads and writes, when in Archivelog mode.
WRITER_REDO - <cnt> times fully updates one row <row#> in the table SEED (that is in NORMAL buffer pool).
I/O pattern:
SHADOW barely reads using INDEX access, reading
one same table block and possibly one same index block each cycle.
DBWR barely writes modified table and index block.
LGWR intensively writes 20 changed column values.
ARCH intensively reads and writes, when in Archivelog mode.
PIOB parameters
Parameters are stored in PIOB.PARAMETERS table.
check_recycle - default YES.
When set to YES PIOB will check if Oracle RECYCLE pool and
its size are optimal for PIOB.
storage_clause - default 'storage(buffer_pool RECYCLE)'
The Storage Clause that will be used when test tables are
created.
Notes
1. To eliminate undesirable impact of I/O that is generated by other Oracle
subsystems like AWR, Flashback, or Archiver you may want to disable them.
Refer to Oracle documentation how to disable Flashback and switch database
to NOARCHIVELOG.
To disable AWR refer to support note "Package for disabling AWR without
a Diagnostic Pack license in Oracle [ID 436386.1]"
It mentiones that dbmsnoawr.plb is available on OTN, but I have not found it there.
2. By default PIOB tries to minimize Oracle caching with intend to perform
reads and writes directly from/to disk.
However if yor benchmark target is to achieve most efficient caching
you may want to change this behaviour.
To do this set parameters following way:
SQL> exec PIOB_TOOL.SET_PARAMETER('check_recycle','NO');
SQL> exec PIOB_TOOL.SET_PARAMETER('storage_clause','storage(buffer_pool NORMAL)');
SQL> exec PIOB_TOOL.DROP_TABLES;
Enjoy!
Mark Malakanov, Dec 2012.