PL/SQL based Oracle Database Loader(PSODL) Release 2.0 on

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 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 – 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 is still waiting on pending status you may use this link temporarily for second release setup information.

WordPress is still banned in my country :(

I thought this was not a permanent problem but it has been over 6 months now, so I thought to create a workaround for the regular Turkish readers of this blog. My old blog at is still accessible, so from now on I will be publishing all my posts both here and there.

Under WordPress’s administration dashboard under blog stats we have All Time Top Posts and I also choosed most read 5 posts of this blog and imported them as well. But still Turkish Oracle blog followers will need some stuff mentioned here to access other wordpress blogs of course, even their own maybe. Here are some of my favorite Oracle blogs at WordPress, so really thanks to OpenDNS :)

Tanel Poder’s blog: Core IT for geeks and pros
Richard Foote’s Oracle Blog
Jonathan Lewis’s Oracle Scratchpad
Lutz Hartmann as sysdba

ps: ApEx 3.1 is now downloadable, check out below link for further information like how to use 3.1 on XE, OBEs of 3.1 and sample 3.1 applications;