Exploring Parallel Processing with Oracle-part 1

 There are a lot of web content on parallel processing in general out there and i just wanted to post some of the useful links/references related to Oracle (i am not  touching Exadata or other Oracle fusion platform related technologies here) database/plsql in general as an introduction/quick reference.

The main motivating factor would of course be dependent on the use case you have in hand and a correct understanding of your existing codebase/application would help in terms of where you stand with respect to your performance/scalability requirements.

• There are 2 basic options to parallel processing using Oracle in particular with PL/SQL.
  • You can use the Parallel Query (PQ) feature of Oracle. This parallelises SQLs by breaking large scans into a number of smaller scans and running these in parallel. You can also run PL/SQL via PQ by defining a parallel enabled PL/SQL pipeline table function.  
  • This second method is rolling your own parallel processing in PL/SQL. With 11g you can use DBMS_PARALLEL_EXECUTE. You can use DBMS_JOB to run parallel processes. You can use message queues and database pipes (or even plain SQL tables) for IPC. You can use DBMS_LOCK for implementing semaphores and mutexes.
  
   
• Also factor in your main motivating need to do parallel processing: i.e Speed-up or ScaleUp .i.e 
• SpeedUp means : If your current single threaded process/code takes T(1) time and you see this unacceptable and want to reduce it i.e speed up with "p" processes/threads then your new improved time would be T(p). In general T(1)/T(p)=S(p)  has an upper bound called Amdahl bound which is 1/(sigma) as p increases to higher values. This is called diminishing returns for more/higher values of p. Here this "sigma" factor is the serial factor/portion in your current workload T(1).                                  i.e T(1) = (1-sigma)*T(1) + (sigma)*T(1)  and T(1)/T(p) = S(p) = p/(1 + (p-1)*sigma) . Hence as p -> infinity the speedup factor curve with p additional threads would hit a aymptotic limit of 1/(sigma). Note: Here the sigma portion represents that serial portion in your current workload which cannot be parallelized at anytime eg: a setup and tear down steps which can run only in one thread. 
• ScaleUp means: Here you have or foresee some scalability issues in scaling to more workload with your current single threaded code/process. If you have a workload input parameter say N with your throughput for this input as T(N). Now you want to scale to higher workloads of this input workload parameter N as linear as possible i.e closer to linear scalability i.e The chart/graph of T(N) vs N is close to linear as possible. Though linear scalability of throughput T(N) vs N is ideal and you cannot achieve you would want to stay close to it. Contention and coherency are 2 factors that affect scalability . In real world your throughput would never increase forever in a given hardware setup and you would be interested to see your boundary limit beyond which throughput may fall off i.e become retrograde. This is something you can advise your customers i.e on a given hardware you can go upto this N(max) value.
• Sometimes you want both Speedup as well as Scaleup i.e you want the batch completion time to stay as close to same as T(1) even with increasing workloads. This may or maynot be practical always and it depends.

General  Parallel Processing Patterns : http://parlab.eecs.berkeley.edu/wiki/patterns/patterns  

 Above link is a very useful link to have a look at all kinds of parallel processing patterns at one shot/glance for interested folks.

1 ) Data Parallelism  In Oracle:

• It is always advisable to read /understand that SQL level parallelism via  PDML/PDDL can be used wherever possible in Oracle EE db. i.e enterprise versions whenever your use cases demand. Along with this query level parallelism one would also be using some sort of parallel procedural processing (if you plan for multiple threads of procedural processing) to break your main job/process into sub jobs/tasks. 

• For a  general difference between the above two features read the first link below followed by other useful references in any order.  Parallel DDL/DML(query level parallelism inbuilt in Oracle EE)  vs dbms_parallel_execute : https://forums.oracle.com/forums/thread.jspa?messageID=10430545&tstart=0

• Asktom writeup :http://asktom.oracle.com/pls/apex/f?p=100:12:0::NO::P12_QUESTION_ID,P12_PREV_PAGE:5026576100346976091,11

• Pipelined Parallel Table Functions in PLSQL : parallel enabled PL/SQL pipeline table function.  ; 

• http://www.oracle-developer.net/display.php?id=429

• Document Reference:   http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_parallel_ex.htm  (Package reference in 11gR2 db)

• PLSQL Tuning : http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#i52932

• http://toadworld.com/Portals/0/GuyH/Articles/Oracle%20Parallel%20SQL%20Part%203.pdf

• http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30plsql-086044.html  (By Steven Feuerstein )

Note on How Oracle implements dbms_parallel_execute: In Oracle database from 11gR2 onwards the parallel framework provided by dbms_parallel_execute package  used to submit/implement the parallel sub-tasks/jobs  via dbms_scheduler job processes governed by job_queue_processes init.ora parameter(prior to 11g one may also have to write your own parallel framework (DoItYourselfParallelism as Tom refers often and then also submit/implement the tasks via dbms_jobs package.(  dbms_jobs  is very similar to dbms_scheduler though dbms_scheduler is more sophisticated and better integration with RAC,node-affinity etc) . Loosely you can refer to these launched slave processes as different threads  processing these sub tasks (but behind the scenes they may be separate OS processes but this implementation is transparent to you and it is upto Oracle to use process or threads based on the particular port of OS. Except on windows on most unix like OS ports,Oracle may use separate OS shadow processes only for dbms scheduler jobs spawned by the subtasks . Again this is immaterial to you/transparent as Oracle would give you control over sub-tasks status,start/stop mechanisms along with knobs to adjust the degree of processing generally needed by you.

Things/Factors to consider in "data parallelism" pattern are following things:

• Initial Setup:What is the setup needed for breaking down the data to be processed into buckets to suit your needs. The breakup may need some understanding of data so that the buckets/sub groups are balanced. May need fine-tuning or further fine grained functional breakup depending on better failure control/balance needs.
• What is the atomic unit of work in any of your typical iteration/task. i.e It could end up being a INSERT/UPDATE i.e a DML or some simple numerical calculation steps in PLSQL( since we have already assumed data-parallelism pattern these numerical calculations would not be compute intensive or some complex mathematical ones but rather some simple ones ). 
• Balance of PQ and parallel processing How to balance or rather mix cleverly Parallel DML/DDL also in your processing . One thing to note here is esp in RAC/cluster database setups you need to watch for inter-node overheads. i.e to limit movement of data over RAC interconnect so that data is as local as possible.(I think dbms_scheduler used by dbms_parallel_execute here would use RAC service names and you have better control over which nodes participate in your parallel processing etc. One has to explore this further and test it)
• Knobs/End-User interface:  Some useful terminologies and patterns for reference
• Oracle Fusion Applications have a UI Design Pattern for this here.

Few essentials to focus

Dr.Neil Gunther brought out clearly in a series of articles in his blog on importance of Stretch Factor(R/S) along with OS-run queue& CPU Utilization.

Infact there was surprisingly very similar attempts/approaches (there is a book written from another gentleman Dr Leonid Grinshpan on queueing theory mentioned elsewhere caught my recent attention. All this is good and reinforces the concepts clearly in my mind.

But i do find Dr Neil Gunther's blog articles very clear to the point ( Being a mathematician and done some thesis work on Probability i am usually very sharp/quick to catch anything on queuing theory with math involved along with my technical background on Telecom and Database systems)

For a performance resultant following checklist of items may be useful:

Whenever your scalability/perf test workloads clearly stretch some of the resources in your setup viz CPU and/or disk storage so that the

Stretch factor (R/S ) goes way more than acceptable SLA values it is time to stop and think a little on following lines:

i) Just a very quick check of the Hardware/Software setup to spot low hanging fruits ( This need not be too invasive and some of the things like centralized storage( NAS/filer storage if you are using) may be beyond your reach along with your server internal bus bandwidth etc. All you can do is to document what you see briefly and move on. i.e Is it a single headed NAS with a NVRAM write cache ON?, how many LUNs you had used, How were the LUNs carved out of the filesystem at filer end etc.

If you dont have clues/answers do not worry and move on. You can always understand that storage with latest trends should behave like reading of the memory with the limitation of network topology (software/hardware adaptors/HBA, NIC and mode of transport/congestion etc) to the storage. Ofcourse db workloads are little tricky as some of them have subtle dependencies as storage , more on that later.

Also do not worry if the Stretch Factor (R/S ) is too great than to account only from a single queued resource. This would only point that in addition to that resource there is another resource or network traffic congestion coming into play here. This is where you can get to understand either you can add up another stages in your model of your transaction.

ii) Having done a quick check of the CPU subsystem, Network, Storage and Memory related you have to consider carefully the modelled application workload to ensure any part of it can be safely turned off ( Ideally you would like to have not more than 2 transaction types

mixed). Tuning the unnecessary application workload parts viz bugs in software causing extra burden on resources is the most beneficial and which you can work with development. This would mean avoiding some fired sql/plsql units,network roundtrips,meta sqls in transactions would bring greater advantage.

iii) A very important side effect if CPUs in one of your servers get stretched beyond its knee ( For M/M/2 it is roughly 0.65 or 65%, M/M/4 it is 0.8 ( i need to reverify as i am writing these quickly off my head the values) is to understand if at some point the OS/kernel took off your appl or backend/db usually off the CPU for a brief period when it is operating way beyond the knee cpu resource utilization. You can spot this easily with any decently sampled OS monitor tools. For eg: i have seen linux kernels sometimes when sustained >80% of cpu usage take off the appl/db from cpu.This is usually a bug in OS kernel.

Overall you want to be on CPU always to be winning.

Thought process skills for SPE

Dr Neil Gunther rightly mentions that hardware systems are increasingly becoming more&more commodity "black boxes" with lot of hidden complexities in each subsystem(cpu,memory,disk and network subsystems) and the onus is on the performance resultant to understand the Application atleast to get the basics if not fully for software performance&scalability. William Louth(JXInsight CTO) has also been recently stressing the same indirectly when he was presenting QoS concepts for Applications in cloud ("Applicaton is the network"). No doubt this would mean a little more steeper learning curve for anyone trying to bring about significant results in performance,scalability in shortest possible time but any such application centric efforts(be it in knowledge acquisition,understanding runtime behavior from Software performance engineering perspective)  would be more beneficial in terms of cost-benefit analysis.A glassbox if not a whitebox testing holds the key and right choice/freedom in choosing the tools/models for experienced people saves time&cost in long run to do the right/relevant work.

Joy of sharing knowledge and being seen as ignorant

  Always it is good to share what you know for this sharing  allows you to know more and the knowledge shared grows by getting contributed by all.
 However sometimes it is best to keep quiet to let others think that they know all about what you know during discussions which can otherwise turn to unnecessary debates.
You are in fact doing good by not disturbing/agitating them by this and allowing them to go with a peaceful,happy mind.
Moreover You can also get on with your thoughts/ideas and what you wanted to do in a peaceful manner without harming each other's minds.
This is the purest form of non-violence  for i believe that any form of conflict is in essence nothing but violence.
I learnt in experience that by not  reacting in such situations does a world of good rather than trying to unsettle each other's minds.
In these days of too much emphasis on Self-managing Applications people do not realize the true potential of Self-Awareness among Humans and
the pattern recognition that humans are best at than machines.

Losing the Big Picture

Sometimes it happens that we tend to get lost in following some
processes/standards so religiously and lose the sight of big picture or
the essence/crux of what we intended to achieve in first place in a
timely fashion.

Following processes/standards is good but it should not hamper or come
as a stumbling block to achieve your principal objectives in time when
they matter the most.

Anyway this is not something unique to Software Industry but in general
applies to any walk of life. From time immemorial all religions
themselves have seen many new philosophies emerge inside their realm
whenever people following the existing ones lose the sight of big
picture and become too much involved in processes to the point of losing
the essence/crux.

Man is a tool-loving animal

Excessive proliferation of tools and technologies in today's software
(this is also good in some aspects,thanks to open and/or free software
movement) ecosystem illustrates this and the plethora of seemingly
similar tools/technologies in IT can confuse even the best learned.
I often find too much engineering where there would hardly be any
significant difference in functionality and/or performance (Scaling is a
different phenomenon with different degrees of expectations and
compromises).No wonder we as humans are tool loving animals and also the
fact that no tool is even near perfect to cater/accomodate to all
situations at any given point drives this need.

5 Tools that i am personally interested in field of Application
runtimes/profiling are:

i) DTrace on Solaris ( may take some time for me to try out on linux)
ii) JRockit Flight Recorder and any plans from JRockit team to
supply a friction-free logging library ( ps: i am not starting any
raging controversy here on whether logging is good/bad or
merits/demerits over diagnostics)
iii) Azul systems diagnosis
iv) JXInsight
v) Yourkit

( in no specific order pls !!!!)

I havent used/seen DTrace for java apps so i do not know at this stage
how it could help to lead all the way up to showing stack trace in
JVM/user space esp in context of JRockit where the
code-compilation/conversion is different than Sun Hotspot JVM which
belonged to Solaris land.

Back to sql land, i had always maintained model is the code in all
walks of software and in particular Dan Tow's diagrammatic way of
visualizing a SQL to understand if CBO really chose the best possible
plan ( one can argue it is the job of CBO and why we bother the internal
algorithm of choosing a plan etc and trust me you would need it at some
point!) had been running in my mind quite for sometime since 2006.
Even wondered why someone didnt take it up to automate that to provide a
visual way of looking at things and stumbled upon this article
(http://sites.google.com/site/embtdbo/sql-tuning-1#TOC-Visual-SQL-Tuning-in-Action
) which shows that people do have similar intentions as mine. In
Software esp in performance management there is no revolution but a
constant evolution of ideas and thoughts that drive things.

Converging JVMs and DTrace for Linux

The news is officially out and as expected the 2 popular JVMs(Sun
Hotspot and JRockit) are getting converged and also a DTrace port for
linux getting started to mature.
The JVM itself being a C/C++ runtime would in my opinion go through some
changes esp in context of better diagnosis and better integration with
other underlying layers in future but improvements in
performance/scalability need to be tested out as it may not be too clear
at this point

Benchmark wars and Number Games

What differentiates a seasoned performance engineer from a
developer(however senior he/she ) is that the seasoned performance
engineer doesnt have to rerun several test runs periodically of more or
less same load nature/workloads to identify bottlenecks in code and can
spot issues in design that can cause performance and scalability issues
in long run much more easily with careful/concentrated fewer test runs
saving lot of heat in arguments(saving fuel and energy) and most
importantly much earlier in dev cycles helping everyone.

Sounds nice to have such folks on board! But wait a minute all this is
good only if the Dev folks listen to the voices and in a predominantly
developer dominated organizations it simply doesnt happen due to various
reasons and even the seasoned performance engineer goes through tough
times of re-running tests again and again to spot the same old
things/cries which he/she would have already cried on top of his/her
voice a thousand times over the roof.

Also much more valid is the quality of ideas and tools he/she uses to
arrive at results quickly can often surprise even the most senior dev
folks that they initially try to initimidate/resist such changes.

No offense meant to any developer folks reading this!

Impatient Patient and a Helpless Doctor Syndrome

Working to help in performance and stability in agile development
environments/developer eco systems poses some interesting challenges for
performance engineering folks.

I get reminded of what i call a "Impatient Patient and Helpless Doctor"
syndrome in such eco/environments.

The development staff mostly very senior and already given you an
impression of they know/understand their code/systems they develop well
enough that they want to be helped only in understanding what they need
or think need to be done.

This is like a Impatient Patient but very learned/educated at same time
requesting the doctor to get him/her checked and get rid of all "XYZ"
scalability and performance issues. What follows is the doctor initially
goes into a Helpless mode as the Patient itself suggests all
methods/tests be performed on him/her and often indicative of the
cure/medicine for the assumed illness.

Not to say that only a Successful doctor helps the patient to come out
of such mental concoctions and manages to truly help the patient in the
end winning their confidence. But this doesnt come that easily and
chances that the doctor though clever enough can become crippled and
helpless to the extent of losing interest in the subject can very well
happen.

Lesson learnt is : The patient should allow the doctor to do his/her job
without imposing too many blockades and doctor also give some mental
peace to the mentally agitated patient along with cures. Needless to say that the patient need to be cured really but providing mental peace to patient helps.

Software modeling and Issues

Understanding the problem/activity to solve more deeply and knowledge
of various architectural design patterns helps to evolve a
reliable,performant and scalable solution. Applying same design pattern
or getting into a fixed/rigid way of solving all problems is a major
issue and cripple the functioning of software. eg: Presentation,
compute,data oriented problems are all unique and one cannot apply a
same pattern to all these problems.
Also One need to understand is no matter how hard you analyze and design
in development or testing there would be some disruputive innovation
coming along which may force you to rethink your design if not in near
future, These disruptive innovations are inevitable and one cannot
accomodate for them in design but you can anticipate minor changes and
provide "knobs" in design to turn on/off certain minor but yet can
change performance/scalability to some extent.

It is not surprising atleast for me having spent last 10+years in
software testing on large systems to see that most of the java apps
still suffer from Concurrency and GC issues.
Lot of research is going into Concurrency area with functional
constructs similar to erlang,clojure,scala etctype coming into java 7
and more into atomic locking constructs,scaling across cores.
This is still a growing area with Software/Hardware Transactional Memory
, Message passing,shared state with more controls etc all the techniques
explored in terms of code clarity, time/space trade offs etc . Even
hardware/software co-design like that of Oracle's Exalogic,Azul compute
appliances etc)
Another area is embracing with some sort of predictive/limited or
avoiding as much as GC as possible.

I had been involved in Software testing from stability,performance,
scalability aspects right from 2000 starting with mainframes(COBOL,
CICS) to latest apps running on JVM using various design patterns.
Manytimes i tell people on things to watch out for or my opinions which
they do initially neglect and later on come back to me to say "Oh yes
you said that sometime back...i didnt get it..."

Concurrency and scaling choices

Let me make it clear for whoever is reading my blog the following:

"I neither speak for my company i work for
nor my company speaks for me.
All the ideas,thoughts and impressions on the tools i list in my blog
are my own "

JSR 166 - concurrency utilities apply for JS2E 1.5 onwards whereas JSR
237 is an attempt to take it to J2EE 1.4 onwards at the container level.
JSR 173 - Streaming xml parser aka pull parser is something that i
find needed for certain situations and is not thought of most by people
when comes to XML parsing.
(Roguewave and VTD-XML are other choices i hear from my friends but i
dont know much that i want to think about sometime later)

JSR 107 - JCache/caching in java is another big area that interests
me with many technologies emerging to support this area,
( Oracle's Coherence,JGroups with Infinispan, Gigaspaces,
Terracoata,GridGain, Open source Hazelcast are some of the useful ones
if one is interested to explore this area each one
with varying capabilities and usecases)

JSR 166 and JSR 173

For some strange reason i found myself needing to know the design
decisions on the 2 JSR s 166 and 173.
If time permits would cover a blog on why i feel these two run in my
mind of late and the importance of them with regards to
performance&scalability.

On a side note, i see lot of people often either use wrong API or
reinvent the wheel possibly they dont know the merits or unable to use
the standard tested APIs/utilities
and sometimes stray into disasters both correctness and performance of
the intended functionality.

Most often Stack/runtime does it better than you

Throughout my interaction with many developers/experienced people i
sometimes find it hard to explain/convince people that the code written
by runtime is cheaper and well thought of than trying to do the same in
application layer.

Most of the tracking/diagnostic are well handled by the technology stack
or the runtime. eg; Your OS,DB or your VM can do much cheaply and safely
the tracking and diagnostic capabilities for your application running on
top of them. So there is no reason why you one may need to do that same
in your application code and you can focus on your business/logic of the
application. Yet one may have to ocassionally use the underlying
diagnostic facilities/external API exposed to add some context which may
be the only thing i see missing in the underlying diagnostic
capabilities exposed by the stack/runtime for you.

Having said that things are different for each stack/runtime today and
the extent to which you can use the underlying diagnostic may vary and
rarely you may see benefit in writing some code on your own for doing a
diagnostic tracking/control mechanism

Do surrogates really suck?

Do Surrogates really suck in performance profiling?

I was going through an article on Performance of applications by a noted
Oracle expert where it mentioned that surrogates suck when it

comes to profiling applications for response time. Let me make it very
clear that i dont dispute the person here but to drive a point that
While response time is ideally the best to measure and understand the
profile of an application/process it may not always be possible to do
that considering overheads. In such cases a careful choice of the
surrogate measure depending on the technology should help in
understanding the profile. So the answer is yes and no.

For me manytimes the thoughts and ideas which i bring in from the
various fields of exposure help me in understanding the performance of a
system or even better/cleaner ways.

Model is thy code

I rarely get time to write on my blog over the last few years. But when
i do there are few good motivations/forces which drive me to write

on things which i consider are important.

After over 10 years in IT I am thrilled to see that i have been always
working on projects which have stability&performance as one of the

key goals/objectives( if not the most important) and was lucky enough to
put my hands on various technologies starting from

IBM Mainframes(MVS/OS 390,JCL,COBOL,CICS.REXX and DB2 to some extent),
UNIX C,C++ saga,
VB,ASP,HTML*, Web technologies
.NET,J2EE systems,
Teradata and ofcourse Oracle technologies

Some of the important projects that i have worked include a one for a
major Telco(for its IT LOB) in UK in their core Intercarrier-billing

and Provisioning systems/OSS-big Legacy conversion project. I had also
been a witness to SOA/WebServices tech(as of 2004) that i happened

to come across in one of the projects which though didnt materialize for
the customer.

It has been a mix of all round exposure in IT and Business side that i
have been witnessing all through these years and thanks to my Alma

Mater consulting firm that i got chance to put my hands on in all these
areas.All along these years i keep getting a more deeper

understanding of the trio- People, Processes and technologies in
projects and how they shape up and interact with each other to deliver.
Often not surprisingly it is the people who play a positive/negative
dominant role resulting in successful/failed projects.
It is a nightmare to imagine working in a large conversion of a big
system X where several teams/stakeholders are involved.
The problem is not in technology/tools or the interaction between
systems but the complexity in interaction with people of various systems

that the project depends on.

Back to the topic of this blog post, "Model is thy code" . I had been
saying this for long time and in fact is one of my favorite

quotes(Ofcourse i dont claim/do not know who may have coined it first)
which i coined way back in 2003 i.e "Model is the code" and most of

the libraries that i come across miss this pivital concept. If your API
is not based on a solid model then you are sure to see your

software using it develop usability/scalability issues in long run.
Correctness/Completeness is a important 'C' factor along with usual

other C's that pundits claim for performance&scalability
(C-Concurrency/parallelism in today's multi-core/proc world,C-Contention
and

C-Coherency).Often It just simply stops at correctness and it doesnt
make sense to move forward.
Though it may not be possible for someone interested in other 'C's to
always ensure 'C-Correctness' factor atleast one should take a step

and think on it for it is posssible that your other 'C's may get
affected or doesnt make sense to proceed ahead.

Looking at the crap/nonsense that is out in blogs/sites on Software
performance & scalability i can only feel pity for the sheer lack of
understanding on the subject and the dangerous mix of false claims/ideas
of those who havent had any hands on on the technologies involved,

Enough of my time has been spilt on Software testing& performance.It is
time i take initiative to move onto something afresh that keep me going,

Making a one-line description mandatory as part of SQL syntax

Has anyone thought about making a comment/a one-line or optionally
multiple lines describing what rows
a sql tries to fetch as a mandatory sql syntax.?
I am sure people might have thought about this but wonder if it's
feasible...

KYD Factor -It keeps coming back again and again

I have faced many times in oracle projects trying to do one thing esp when i am asked to
maintain, enhance or test the performance of backend code written in oracle sql,plsql:
        "The KYD -Know Your Data Factor" - Understand your data in tables as well as structures involved
Sometime back David Aldridge mentioned this as one of the points in writing good sqls,
Probably i would like to keep a copy of this whitepaper from Dan Tow by my side always..

How much time one saves by just understanding this in first place, rather than beating around the bush trying to refactor sqls
which may or may not give you the optimal solution in the long run.

(Quoted directly from above Dan Tow link:
"
Code What You Know

To understand the database design well enough to write functionally-correct code likely to perform well from the start, you should be able to answer a series of questions with confidence:

• What set of entities does each table represent?
• What is the complete primary key to each table?
• What set of entities does each view represent?
• What is the virtual primary key of each view?
• Roughly how many rows in production will there be in each table or view? 

It is surprising how often owners of broken code cannot answer these very basic questions, but it is hardly a surprise that the result, without this understanding, is broken code!"
"

Transactional processing in Messaging Systems

As With any other db/Persistence API , queueing/messaging API must(May
be It should be re-emphasized) support transactional API
and no wonder Oracle AQ supports this.
Set based processing , Enqueueing and Dequeueing array of messages, XML
based payloads
possible with AQ.
While Oracle AQ supports Transactional API in Persistent Queues which it
conveniently leverages the Oracle Database Tables (Queue Tables,IOTs to
be precise)
it was not supporting transactional API in Buffered messages.
EnQueueing and DeQueueing into Persistent Messages have the same
overhead as of doing Select,Insert and/or delete into IOT tables as the
case maybe.
Buffered Messages dont have this overhead with the downside of retention.

Question on "nested txns" vs "autonomous txns"]

For people who have requirements to code a Autonomous txn in Oracle which is called in general as a "Nested Top level (sub)transaction",
Things to watchout are esp in the context of temp tables,
Autonomous transactions just happen in another transaction but within the same session of parent/main txn and so your temp tables/gtt s created in main txn should be accessible from within autonomous txn except for the uncommitted changes done to it in main txn immediately (Infact if you cannot even populate a same gtt/temp table which has been already populated in main txn, you would get error in autonomous txn )

I did some study and found that use of autonmous txn is 99% for auditing/logging purposes and as Thomas Kyte(asktom) says any other use of them is sure a problem in design/code.You would need to really check your logic for such a use case before you decide some txn as a autonomous txn.


I would summarise the following for autonomous and nested txns:

Autonomous Txn:

• Autonomous txn is just a different independent transaction from the main/parent txn but within the same session . Hence does not share transactional resources as that of main/parent txn.
• Cannot see uncommitted changes in main/parent txn for consistency reasons. (Note: Consistency is always at transactional level and not at session level)
• After a commit in autonomous txn you return immediately to the transactional context of main/parent transaction.i.e after a commit in autonomous txn you are back to parent txn.
  
• Autonomous txn since they operate within the same session they can access the gtt/temp tables but cannot see data in them already populated by main/parent txn. Infact they get error when they try to populate them if done already in parent txn. But they can populate them if not already populated in parent/main txn which would be a rare case.
• Changes made in autonomous txn are visible to parent/main txn based on isolation level set by main/parent transaction using the "set transaction isolation level .." statement in pl/sql. Oracle,by default makes committed changes visible(i,e "read committed" isolation level is default for any DML statement level) but "serializable" isolation level can be set for a transaction level using "set transaction isolation level serializable" statement for multi-statement read consistency. i.e the main/parent transaction would not see any committed changes made later by other transactions including autonomous txn which is also a different txn. Hence if your parent/main txn starts with a set transaction ..serializable it wont see any committed changes done in the autonomous child txn of it. Normally you would use this "serializable" isolation level for a short time OLTP txns and most often go with default "read committed" statement level.
• Exceptions raised from within autonomous txn get rolled back to transaction level and not to statement level.
  
• Use of Autonomous txn in XA/distributed env was not supported in 9i and not sure of complications in later releases.
  
• Autonomous txn use cases are very rare and 99.99% they are for logging/auditing purposes.

Nested Txn:

• For Oracle nested txn simple mean a transaction done from within a parent/main txn as i explained already.You can only set savepoints and rollback to them as you know already. You can use JDBC 3.0 standard savepoints interface for this in java or use pl/sql savepoints to rollback incrementally as you said.
• Transaction isolation levels can also be set using jdbc APIs. Oracle as said above only support default "read committed" and "serialization" levels.
  
• In Oracle Nested txns always see uncommitted changes in parent/main txn and changes in nested child txns are also always visible for parent txn.
  

Please see autonomous and nested transaction example.

Autonomous txn Example (In PL/SQL)
create table audit_test
(
name varchar2(20),
join_date date,
identifier varchar2(200),
log_id number
)
/
Truncate table audit_test
/
Create or replace procedure commit_test
is
pragma autonomous_transaction ;
v_nr number;
begin
select nvl(max(log_id),0) into v_nr from audit_test;
dbms_output.put_line('Maximum before autonomous txn In Child:'||v_nr); --(0)Autonomous Child txn wont see uncommitted changes in Parent.
insert into audit_test values('laksA',sysdate-1,'TestA',2);
commit; --Autonomous Child txn has to a commit/rollback always
end;
/
declare
v_nr number ;
begin
set transaction isolation level serializable name 'Parent'; -- named 'Parent' txn
-- This main/parent transaction sees db as of this time for multi -statement consistency
-- Without "serialization" isolation level this main txn would see the committed changes of autonomous child txn below
insert into audit_test values('laks',sysdate,'Test',1) ;
commit_test; -- calls autonomous child txn
select max(log_id) into v_nr from audit_test ;
dbms_output.put_line('Maximum after autonomous txn In Parent : '|| v_nr); --Output should be 1 with "serializable" and 2 without it in parent txn.
rollback ; -- Doesnt affect committed changes of the autonomous child transaction.
end;
/

Nested Transaction eg (In PL/SQL_)
create table audit_test
(
name varchar2(20),
join_date date,
identifier varchar2(200),
log_id number
)
/
Truncate table audit_test
/
Create or replace procedure commit_test
is
v_nr number;
begin
-- set transaction name 'Child' ; You cannot start a true nested txn like this.
select nvl(max(log_id),0) into v_nr from audit_test ;
dbms_output.put_line('Maximum before child txn in Child:'||v_nr); --(1) Nested Child txn always sees uncommitted changes in Parent txn
insert into audit_test values('laksA',sysdate-1,'TestA',2);
commit; --Everything done in Child as well as in Parent prior to callign Child gets committed.
end;
/
declare
v_nr number ;
begin
set transaction isolation level serializable name 'Parent'; -- named 'Parent' txn
-- This main/parent transaction sees db as of this time for multi -statement consistency
-- Parent would always sees uncommitted/committed changes in nested child txn.
insert into audit_test values('laks',sysdate,'Test',1) ;
commit_test; -- calls nested child txn named 'Child'
select max(log_id) into v_nr from audit_test ;
dbms_output.put_line('Maximum after Child Txn in Parent '|| v_nr); --Output should be 2 in parent txn always.
rollback ; --No Use at all .
end;
/

Looks like there is no true nested transaction API support in Oracle.

All these transaction concepts esp isolation level for a transaction implemented by DBMS vendors are requirements from TPC(Transaction Processing performance council) which sets few standards and req, to publish benchmark results.
JTA/JTS (java transaction API/java transaction service) is also a driving force for Transaction API provided by vendors. Oracle implementation of transaction API is much different from other DB vendors and also for performance/integrity reasons Oracle doesnt provide some features.

Cheat Attacks And Kernel Patches

It seems i blog once in a new moon these days.
I was going through an article http://www.cs.huji.ac.il/~dants/papers/Cheat07Security.pdf
Thought very nice and let me share it with readers of my blog.
Another interesting article i read on a Linux site is on a kernel patch introducing 2 new metrics PSS(Proportional Set Size) and USS(Unique Set Size) to find out more exactly how a process is using memory in a Linux computer system.
While existing VSS (Virtual memory size) and RSS(Resident size) of a process give you a picture of how much memory a process uses they never give you the exact picture of how much really your process contributes in memory use.
Here is the link , read on..
http://www.linuxworld.com/news/2007/042407-kernel.html
And one of my friends joked about writing only technical articles in my blog which makes it rather boring for him , i 've decided to add interesting incidents in my professional life as well
here.