One person called the quiz a "fiasco", adding that " honestly cannot find a decent explanation in any of the documentation."
Another wondered how a person might go about answering the quiz, posing these options:
1. Decide from the beginning that he simply does not know enough for answering it, and simply choose "None of the answers are correct" AND also DO THIS VERY FAST ( an option that most of the times is likely to minimize the number of wrong answers and also acquire an almost-zero time, as we already discussed in large debates several good times in the past, even recently ), and thus "gain" a not so bad score ... surely a much better one than his real knowledge ...
OR ( and this is really a BIG OR !!! )
2. Deciding to invest ( many would rather say ... "waste" ... considering that time does count much in this competition !!! ) a certain (rather longer than small) amount of time to look a little bit into the things and trying however to come up with as good an asnwer as possible, for a maybe completely new topic for him ... and then being "punished" for the high time and scoring even lower than a player taking the first action above ...
then concluding:
How deep are we expected to have dived into these very specialized features so that to be well prepared for what is still awaiting us during the PL/SQL Challenge ... so that to be able to still continue to play it without a feeling of total frustration ...that ... I dare bet ... will be experienced by not a few players (myself included) with today's quiz ...
Here are some of my thoughts:
1. Offering a quiz every day is quite a....challenge. If I only stick with "mainstream" functionality of PL/SQL, I can tell you right now that it will be very tough to continue to offer interesting new material. I also want to leverage the knowledge of developers from around the world, who know much more about various aspects of and applications of PL/SQL than I do.
2. While I appreciate that many players are very much in "competitive mode" at the PL/SQL Challenge, the most fundamental and important objective of the site is to deepen and broaden your knowledge of the PL/SQL language (including the many built-in packages that add to the base language).
3. You will undoubtedly encounter quizzes on topics with which you have no experience. And, yes, that means that almost certainly your score for that day will not be very high. But you should try not to be terribly frustrated by such quizzes and instead focus on the learning aspects.
Your thoughts?
At first I thought "holy s**t - I haven't even HEARD of DBMS_XA?"
ReplyDeleteThen I opened the PL/SQL packages and types docs, found DBMS_XA, saw what it was about, and saw that the default timeout was 60 seconds. Took a minute to just skim the doc for what was needed and then take an educated guess at the answer (I got it right too :-)
And now I've filed at the back of my head the knowledge that Oracle offers a way to have a transaction spanning multiple sessions. Something I never knew before, but right now I actually have an idea where I might possibly use that knowledge profitably in a project coming up in the next year.
So my 2 bits is that the quiz should be a mix of both things that we all should know and things that we would benefit from knowing. So far that mix has been just right IMHO :-)
Keep it up - I definitely wish to learn...
Kim said everything necessary. I never heard about it before, looked into documentation and managed to get rank 44. Not bad for a topic I knew nothing about.
ReplyDeleteUsually I try to solve everything from my knowledge, but in cases like this I "invest" time and someday I might be glad that I know that there is somthing to solve the problem I have right now.
Regards
Marcus
I keep it written on the whiteboard beside me:
ReplyDelete"I take the challenge to learn"
as a reminder to myself that I am not here PRIMARILY to get the best score.
The point is to challenge myself. I did not even know about DBMS_XA before this. I haven't the faintest idea why I should use it. That doesn't mean, however, that I might not find a good use...
-doug
I followed the path suggested by the original author, threw up my hands after a minute and chose "none of the above." That got me in the 20th percentile. But I consider it cheating to RTFM during the quiz, at least for my purposes of testing my knowledge.
ReplyDeleteI certainly looked this up afterwards, but like others found the documentation so convoluted that I stopped reading pretty quickly, rationalizing that I didn't see any reason I'd want to keep a single transaction going across multiple sessions.
So, it was a fair test of the knowledge of the topic, but an even better test of when you should stop muddling and pick up a book!
Sorry, my mistake, I actually guessed the first choice, not "none of the above", which is how I hit the 20% mark.
ReplyDeleteLike the three before me said, with some variations. My first thought was, that is a lot of code, but most is about setting up some process, doing something small and finalizing it and it is time depended? But it looks very interesting, but I have never heard of this package. I had now time to investigate and after googleing on DBMS_XA I was very confident on my answers (which where right and I got on place 46).
ReplyDeleteA good question on a complex issue which in real time issues with parallel data processing could be very useful.
So keep them coming and thanks for the lesson. Hopefully when I have use of this package, I remember the fact that it exists.
Hello All,
ReplyDeleteI did about the same what Kim said above,
with just a small but important difference, that is, I did not look at the DBMS_XA.SETTIMEOUT function,
which is the only one that describes exactly the meaning of the 60 seconds default ...
and that simply because this subprogram did not appear in the quiz ...
If you look into the chapter dedicated to this topic in the Advanced Application developer guide,
it only speaks about "SESSION TIMEOUT" and "SESSION WAIT TIME".
While the second one does indeed have a default of 60 seconds, its description seems to be something
totally different, namely:
"the number of seconds Oracle Database waits for a transaction branch that is being used by another session
before XA_RETRY is returned. The default value is 60 seconds."
which sounds like a wait for a lock to be released, no word here about rolling back automatically, a.s.o.
This whole chapter is one of the lowest level I have ever encountered in the whole Oracle documentation set,
and especially is very much missing some solid examples ...
Later on I found only two examples by Googling, indeed better than all what Oracle documentation told us ...
No reference at all to this package on ASKTOM ... and this maybe tells a lot ...
In summary:
If guessing, than throwing a fast "no correct answer" would have gained much more for those 50% correct
(usually even 75%), than for having learnt something during the quiz and having made a convinced but slow
75% correct choice...
The feature is nice by itself, but learning about it for the first time during a quiz I think it is
NOT the best way to stimulate somebody to learn ...
Usually you start learning when you hear a minimal amount of very basic information about
a topic and it raises your curiosity ...
I myself think sometimes that, maybe, quitting the competition and playing it just for learning
is definitely a better way to learn ...
But once you are competing, I think it is unfair towards most players to simply define
the PL/SQL Challenge as a "competition about pretty EVERYTHING !!!" ...
Let's all remember that many quizes were in fact purely SQL quizes "dressed" into a PL/SQL block ...
And SQL is REALLY, but REALLY ENDLESS !!!
So, maybe time has come to however establish some rules regarding the "extension limits"
of the topics touched in THIS competition.
As a first idea, maybe opening a completely separate challenge for top-advanced topics,
with a weekly/monthly quiz would be a possible starting point ...
A serious developer (a serious person in general) DOES NOT approach an exam without preparing for it.
And if the topic of the exam is "everything", you CANNOT really prepare for it ...
even not theoretically ... not to speak about practicing ...
Playing the challenge in a competitional way is an exam, no matter how anyone would try to deviate
the attention towards the learning aspect only.
Don't know how others feel about it, but I feel very unconfortable with failing an exam just because
you hadn't the faintest idea about what are you expected to know for that exam...
Tomorrow someone may start to serve quizes about some of the DBMS packages that only very clever DBA-s
know about and use ... ultimately they are also PL/SQL, aren't they ?
And what then ?
So, I think that time has come to really implement some reasonable limits to this specific competition,
and to think of other ones for top-specialized gurus ...
Frustration has NEVER been a motor for progress, in any field of life ...
Thanks & Best Regards,
Iudith
I think this was a really great quiz. Getting some points is good, getting new skills and tools is great.
ReplyDeleteI think this was so much better than just a test of which code will compile as written. Most of such things will be resolved by just fixing the issues the compiler points out.
Having all supplied packages and types as in scope for the quizes makes perfect sense to me. The more features I can pick up with just a few minutes of invested time, and then immediately turn around and use it to build better solutions at work, the more I'll like the challenge. It is exactly why I participate, not to show off but to learn.
This tool provides a form of nohup for the database, how cool is that? I never knew you could pass a transaction from one session to another and I can imagine many uses for it. Losing a few points is a cheap price for the kind of knowledge gained by such quizzes.
I was going to post something really witty and erudite about this quiz, but Kim and Mathias pretty much said it exactly for me. A quick look in the documentation was enough for me to connect the dots - and I learned something new in the process. I'm pretty sure I've come across a situation in the past where DBMS_XA might have helped.
ReplyDelete@stewstools: you're welcome to eschew looking up docs (there's a number of players who play this way), but this is expressly *not* cheating in the context of the Challenge. After all, looking up the docs incurs a time penalty which means that someone who knows the answer already will generally score higher than another who has to look it up (or test it, for that matter).
I treat the quiz as both a competition and as I would a word-a-day calendar. As a competitor, I am annoyed that those who quickly choose “none of these answers” instead of investing the time to properly evaluate the quiz may score higher than those who made legitimate efforts. Over the course of a quarter, however, it is unlikely that such attempts to subvert the scoring system will make a meaningful difference.
ReplyDeleteAs often occurs with word-a-day calendars, many times the quizzes address items with which I am already familiar but sometimes with subtle nuances I had not previously considered. Occasionally, as with the quiz on April 26, I am presented with a topic to which I have never been exposed. These are great learning opportunities.
Those who eschew consulting documentation or writing test code in order to answer the quizzes are definitely at a disadvantage when presented with the more esoteric topics. While I admire those who are able to perform well on the quiz based solely on their prior knowledge of PL/SQL, I seriously doubt that there is anyone who knows all of the functions, procedures, and types that are available in the Oracle supplied packages. The PL/SQL Challenge is not a certification exam, it is a challenge. A skilled PL/SQL developer should know how to efficiently use documentation and write test code to confirm one’s understanding. The time penalty addresses this real-world aspect of programming.
If I remember correctly Steven had mentioned somewhere on this blog that advanced quizzes will not be set as "one choice correct" as far as possible. This also applies to quizzes on rare or not so frequently used or new topics I guess.
ReplyDeleteBut in my opinion if such quizzes are set as "one choice correct", it would actually encourage players to lookout for the correct answer by the way of trying some code or looking at the documentation.
This would avoid score-conscious players to quickly choose "None of the choices are correct" option that might give them a good score.
This also means that those that invested time in finding the correct answer will not be "punished"(?).
I did invest a lot of time answering this quiz but unfortunately could not find the answer in a reasonable amount of time so took an educated guess which was wrong :(
I am not disappointed by failing the exam but happy that I learned something new and interesting. And I know now that next time I am going to pass the exam on this topic :) Many thanks to _Nikotin for that. Cheers !!!
I think Oracle should have to invest at least one row in the BASIC Oracle (not just PL/SQL, but CONCEPTS) documentation to tell everybody that YES, a transaction CAN be passed from one session to another, which means that one session CAN SEE UNCOMMITTED CHANGES of another session !!!
ReplyDeleteIs this such a trivial issue as NOT to be mentioned anywhere, after maybe the first thing that everybody learns about Oracle is that the opposite is definitely and always true
and that Oracle DOES NOT support "dirty reads" ...
If this single row was there, I suppose that pretty everybody would have known perfectly about it ...
If there really exists a new feature added to a specific version, then a feature like this should have been quoted on that list on the first place.
Also, some examples about what can be accomplished by using such a feature and not by other means would have been more than necessary ... and this preferrably without using only examples of PRO*C or OCI, that probably most developers are already not using anyway ... especially since PL/SQL came into being .
Best Regards,
Iudith
@iudith: about specific competitions. I think that is a bad idea. The quiz must be challenging even when it means sometimes that you know nothing about it. Several times since the start I've dived into the documentation to learn about things that looks interesting and are helpful in developing better skills.
ReplyDeleteA suggestion for specific competitions? If we make enough category's, everyone is a winner. That would be nice..... ;-)
Some questions I am very confident that I know the right answer. Some questions I have a much lower level of confidence. I'd never used the DBMS_XA package, so wasn't confident on this one (and I didn't get 100%).
ReplyDeleteHowever general PL/SQL knowledge should tell you that some functions deal with a failure through a return code rather than an exception, and in this case the variable was named "rc" as a clue.
If you don't know a specific topic, applying what you do know to make an *educated* or reasoned guess is an intermediate option
Hello all,
ReplyDeleteI think that Gary is right in that previous skills are always supposed to help, though, in this particluar case, a "return code" is usually also supposed to be checked after the subprogram call for analyzing the outcome...
It is in fact not bad to answer 75% right after a dive of 10 minutes (only !) into the documentation of a new feature...
What I really don't like is just the fact that,
due to "missing popularizarion", most of us came to learn about this feature FOR THE FIRST TIME during the quiz only ... instead of having heared about such an important and probably "far-shooting" feature on the numerous sources that we usually access, like web sites, symposiums, conferences, a.s.o.
It's true, better later than never, and Nikotin does really deserve our thanks for it,
I just would have preferred to hear about the whole topic BEFORE this quiz, that's all.
Ramesh above raised a very good point:
The all-or-nothing quizes are indeed the ones that exclude the "fast-no-right-choice" scorers, but, as this specific quiz especially demonstrates, taking such a "deep" feature and offerring a single correct versus several incorrect answers would have missed the central point about the feature ... in fact the one for whose subtlety the 10 minutes of fast diving into the documentation were unfortunately not enough ...
If time was endless, then maybe there are guru-like ones among us that would simply sit down and learn each and every DBMS package,
and finally beat all the others ...
But, as this IS NOT the case (unfortunately :( ), we need some guidance about what is the right itinerary to follow in chosing our learning preference order, because, without ignoring any feature, there are however features that are supposed to serve many and very large development purposes, while others are of more specialized use, going up to probably never having an opportunity to try and experiment with them, not to speak about mastering them ...
And this one especially fits this last category, added the fact that it contradicts the "Oracle common sense" that each of us does have about transactions (just imagine what would happen to a project if a developer relies on dirty reads as possible WITHOUT using this feature ...).
Everybody in this thread spoke about "educated guess"-es, I would say that what we miss is
an "educated learning guidance", that is, to put some ordering into our learning efforts,
life simply cannot be managed otherwise .
Thanks & Best Regards,
Iudith
Judith, I haven't yet fully researched this package but as far as what I know now, I can say it doesn't contradict with "Oracle Commonsense". If I am not mistaken it's like a distributed transaction within a single database.
ReplyDeleteI can't imagine right now in which scenarios it could be used practically but certainly it should be having its uses like the distributed transactions have.
It's good that we know about it now so when such case arises we know there is a way to handle it.
Regarding scoring, I will add one more point to my previous suggestion. Don't disclose the scoring method when the quiz is live so nobody knows if it is "one choice correct" or otherwise. Wouldn't this will make The Challenge more challenging? Also, this will not require any modifications to existing scoring algorithm which is intelligent and carefully designed.
Hello All,
ReplyDeleteYes, you are right about the scoring, maybe not disclosing it as "single choice" is a good idea.
Regarding the topic itself, what I meant by
"Oracle common sense" is that prior to this feature you COULD NOT have two user-initiated sessions that can participate in the same transaction, even not in a distributed one,
where it's true that you have two or more sessions, but only one of them is managed by the user or application, the other ones being remote sessions created automatically on behalf
of the initiating session and you were not able to attach by any means to those sessions and perform/see/continue work that belongs to the same initial transaction, which is anyway revolutionary, in my opinion.
Best Regards,
Iudith
I'm a little late commenting on this, but I think having random packages like this is exactly what the quiz can provide developers - a way to learn PL/SQL that you weren't previously familiar with. There are probably people out there using DBMS_XA all the time thinking "what's the big deal?"
ReplyDeleteI didn't know about it, I had a guess, then used my second guess - which was wrong.
When I saw the quiz on user defined aggregate functions, while I had used (copied usage) of one previously, I still had to have a semi-educated stab, and happened to get it right.
Perhaps if there were a quiz on dbms_random others might think the same thing? Who'se to know what is used out there? And by whom?
Dear all
ReplyDeleteHas anyone actually used DBMS_XA?
I need some advise on using DBMS_XA from PL/SQL with tightly coupled multiple branches under one global transaction. Basically, I've successfully written some PL/SQL code that in 3 different sessions attaches to 3 different branches of one global transaction and before ending each branch they can see each others uncommitted data. So far so good.
However, I'm not sure I completely understand how each branch must call xa_end, xa_prepare and xa_commit correctly using two phase commit and my calls result in errors like:
ORA-24767: transaction branch prepare returns read-only (XA error code 3 = Transaction was read-only and has been committed)
ORA-24756: transaction does not exist (XA error code -4 = XID is not valid)
ORA-02051: another session or branch in same transaction failed or finalized
This is the structure of my programs (3 SQL*Plus sessions):
main: Uses xid 123|0 (branch 0 of global transaction 123). This should be the coordinator that commits using two phase commit across the 3 branches
m1.xa_start tmnoflags
m2.DML
m3.Wait for thread A + B to manually be started and run xa_end
m4.xa_end tmsuccess
m5.xa_prepare
m6.xa_commit false
thread A: Uses xid 123|A (branch A of global transaction 123)
a1.xa_start tmnoflags
a2.DML -- thread A can see main and thread B's data
a3.xa_end tmsuccess
a4.xa_prepare -- required?
a5.Should we also call xa_commit false?
thread B: Uses xid 123|B (branch B of global transaction 123)
b1.xa_start tmnoflags
b2.DML -- thread B can see main and thread A's data
b3.xa_end tmsuccess
b4.xa_prepare -- required?
b5.Should we also call xa_commit false?
The failing steps are:
m5
m6
a4
a5
b4
b5
Before starting calling xa_end I see 3 rows in V$GLOBAL_TRANSACTION, eg (hex 7B = decimal 123):
FORMATID GLOBALID BRANCHID BRANCHES REFCOUNT PREPARECOUNT STATE FLAGS COUPLING
--------- -------- -------------------------------- -------- -------- ------------ ------ ----- ---------------
203348753 0000007B 00000000000000000000000000000000 3 3 0 ACTIVE 0 TIGHTLY COUPLED
203348753 0000007B 0000000000000000000000000000000A 3 3 0 ACTIVE 0 TIGHTLY COUPLED
203348753 0000007B 0000000000000000000000000000000B 3 3 0 ACTIVE 0 TIGHTLY COUPLED
Thanks a lot in advance.
Cheers
Finn
Finn, have you ever seen this table http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_xa.htm#g1017904 ? You need to call xa_end in all sessions to disassociate branch with session, call xa_prepare for all brahces (not necessary in the same session) and call xa_commit for the last one.
ReplyDeleteHi and thanks for your comment.
ReplyDeleteYes thanks, I've figured it out. This is poorly documented as it's not well explained how to handle the various return codes. Turns out that all but the last xa_prepare calls return dbms_xa.xa_rdonly (tightly coupled branches are combined -- "read only" optimization), the last one returns dbms_xa.xa_ok and this is when you should call xa_commit.
Now my next problem is that DBMS_XA doesn't work from within jobs (DBMS_JOB and DBMS_SCHEDULER). xa_start throws:
ORA-24789: start not allowed in recursive call
Now trying to find a workaround for this, please comment if you have any suggestions.
Thanks in advance.
Cheers
Finn
Hi Finn, as a workaround you can create dblink for database itself and call the procedure that use dbms_xa with @selflink.
ReplyDeleteExcellent suggestion, why didn't I think about that :-)? Perhaps because I read that there are restrictions on using database links with Oracle XA, but on a second read I think these restrictions only apply inside out (using database links from within an XA transaction) and not outside in (calling a stored procedure over a database link and the XA transaction is established inside this call).
ReplyDeleteThanks a lot and I'll update when I've tested this.
Cheers
Finn
Two additional comments: the procedure must be with pragma autonomous_transaction (or you will get ORA-24758), and beware of "ORA-04062: timestamp of procedure has been changed" while testing.
ReplyDeleteHi and once again thanks for your comments.
ReplyDeleteAre you using DBMS_JOB, DBMS_SCHEDULER or DBMS_PARALLEL_EXECUTE for your "tread sessions"? With DBMS_JOB xa_start returns -6 (Routine invoked in an improper context) with my test code.
Cheers
Finn
My test:
ReplyDeletedrop table t01;
create table t01 (a number);
insert into t01 values (1);
commit;
create or replace procedure pxa (i_branch varchar2) is
GTRID raw(16) := hextoraw('00000001');
BQUAL raw(16) := hextoraw('0000000'||i_branch);
xid DBMS_XA_XID := DBMS_XA_XID(GTRID, BQUAL);
rc PLS_INTEGER;
pragma autonomous_transaction;
begin
rc := DBMS_XA.XA_START(xid, DBMS_XA.TMNOFLAGS);
if i_branch = 'A' then
update t01 set a = a + 1;
elsif i_branch = 'B' then
update t01 set a = a * 2;
end if;
rc := DBMS_XA.XA_END(xid, DBMS_XA.TMSUCCESS);
end;
/
declare
i number;
begin
dbms_job.submit(i, 'pxa@selflink(''A'');');
dbms_job.submit(i, 'pxa@selflink(''B'');');
commit;
end;
/
select * from V$GLOBAL_TRANSACTION;
-- select * from user_jobs;
declare
GTRID raw(16) := hextoraw('00000001');
xid1 DBMS_XA_XID := DBMS_XA_XID(GTRID, hextoraw('0000000A'));
xid2 DBMS_XA_XID := DBMS_XA_XID(GTRID, hextoraw('0000000B'));
rc PLS_INTEGER;
begin
rc := DBMS_XA.XA_PREPARE(xid1);
rc := DBMS_XA.XA_PREPARE(xid2);
rc := DBMS_XA.XA_COMMIT(xid2, false);
end;
/
select * from t01;
Hi and thank your very much for your help, which is most appreciated!
ReplyDeleteI thought I was doing the exact same thing, just wrapped up in a slightly different way (the job's WHAT was a call to a local stored proc that called the thread code over the database link as opposed to your solution where you call the tread code over the database link directly in the job's WHAT). However, I must have forgotten the AUTONOMOUS_TRANSACTION somewhere in the process and I was also fighting in doubt transactions that I didn't purge correctly.
So the bottom line: This works combined with DBMS_JOB, DBMS_SCHEDULER and also DBMS_PARALLEL_EXECUTE. I've tested on 11.1.0.6.0, 11.1.0.7.0, 11.2.0.1.0, 11.2.0.2.0 and 11.2 XE Beta.
One suggestion to your code: You don't check the return values of any of the DBMS_XA calls. You need to do that as Oracle doesn't necessarily throw an exception upon failure.
Once again thank you very much!
Cheers
Finn
You’re welcome anytime!
ReplyDelete>One suggestion to your code: You don't check the return values of any of the
>DBMS_XA calls. You need to do that as Oracle doesn't necessarily throw an
>exception upon failure.
I remove all logging and the return values check to simplify before posting. I meant it self-granted in production code.
Finn, I saw your comment on OTN:
ReplyDelete>I'm trying to write a framework that helps building
>concurrent PL/SQL programs, leveraging multiple CPUs,
>where the concurrent sessions must share the same
>transaction.
>I must be able to support all editions of Oracle
>Database so using Parallel DML is not an option as
>that requires Enterprise Edition.
Unfortunately framework based on dbms_xa will not really parallel because of "In a tightly coupled branch, Oracle Database obtains the DX lock before running any statement."