Hüsnü announced the ancestor of this package a while ago in his Loading Oracle series. I developed this second release based on his idea and opened a project on sourceforge.net for the community’s contribution. In his announcement above Hüsnü argues on simple looping over concurrent loading with the insert performance of an index organized versus a heap organized table example. The first example I will be giving for concurrent load will be a famous OLTP example, using bind variables, and bitmap indexes locking behavior will be the second to come with another post.
But before getting into details with examples some marketing of course :) What is the need(or want) for PSODL;
– PSODL is completely PL/SQL based, so it is easy to setup, customize and use it for an Oracle database developer or administrator,
– PSODL is parametric, any pl/sql block can be configured for your loading requirements,
– PSODL by default produces each thread’s elapsed timing, top 3 latching and session statistics based on V$ views(based on Kyte’s runstats package),
– PSODL can be configured to produce event 10046 level 8 sql trace file for detailed profiling analysis if wanted,
– With PSODL’s outputs you can easily know the average and standard deviation statistics of your applications for the determinism of their response times under load,
– PSODL can be used on any Oracle editions and with releases higher than 10gR1(since DBMS_SCHEDULER is used).
Some warnings and constraints;
– PSODL errors can be followed from the database’s alert log file and dba_scheduler_job_run_details.status column,
– PSODL loads your scenarios at the very same time, but normally even on a heavy loaded OLTP system some 10s of the same requests won’t be starting at the very same time, so while setting up your scenarios better to remember this fact,
– PSODL can also be used on 9i or 8i releases if you replace DBMS_SCHEDULER with DBMS_JOB supplied package,
– PSODL produces trace files under your background dump destination since the threads you will be tracing here are job processes.
And some possible next release features;
– A brief Installation and User Guide will hopefully be documented,
– LATCH and STATS reporting will be enhanced,
– Statspack and after 10g ASH-AWR-ADDM performance tuning options will be investigated for possible integration,
– An Apex application will be developed for getting the parameters and reporting the outputs(charts) of the load scenario.
Below demonstrations were done under sys schema of a system as of;
SunOS 5.10 Generic_118833-17 sun4u sparc SUNW,Sun-Fire-880
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Code Listing 204a – bind vs. nobind demo for simple looping
runstats package source and setup
Code Listing 204b – bind vs. nobind demo for concurrent loading
pkg_cncrnt_loader package source and setup
Fighting against any kind of myth or guess requires testing, but your testing quality and strategy must be appropriate for your needs, if not you may be introducing new myths most possibly. PSODL will always have some theoretical boundaries but I believe that in time customized PSODLs will assist you more compared to simple looping for your projects’ success.
ps: since PSODL project on sourceforge.net is still waiting on pending status you may use this link temporarily for second release setup information.
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 