After my High Performance PL/SQL presentation at Kscope12, an attendee came up to ask for my help. She and another developer from the same company were missing lots of the conference due to a production problem - and they were at that moment running a job to load millions of rows of data. "Usually takes 60 hours," she told me. Yikes! And she showed her code.
It consisted of a cursor FOR loop with single-row inserts inside it. Did I think that FORALL would help in this case?
"OH, YEAH!" I replied.
So I gave her some advice on converting the code to working with collections and bulk processing. Hopefully, it worked out all right - in the code. Their Kscope12 experience was, unfortunately, a disappointment, because they had to miss so many sessions as they put out their (self-inflicted?) fires.
I'd already returned to Chicago from San Antonio when this thought suddenly struck me:
On the one hand, I am certainly glad that at least some of the developers who attended my High Performance PL/SQL class learned something new. On the other hand, how is it possible that there are still PL/SQL developers - especially developers who care enough about their craft to attend a conference like Kscope - who are not aware of the most powerful optimization features in the PL/SQL language - features that were added in Oracle8i (1999)?
This under-utilization and limited awareness of many, key and now longstanding features of PL/SQL is very disturbing. Sure, people are busy. Not everyone has the time to research new features in PL/SQL every time Oracle releases a new version of the database. But, heck, every PL/SQL User Guide comes with a "What's New" section. It surely doesn't take that long to skim through the topics and make sure you aren't missing anything that could have a big impact in your shop.
I feel like some of the blame for this situation has to fall on the teachers and providers of the PL/SQL language. In other words, people like, well, me, product manager(s) back at Oracle Corporation, content managers at Oracle Technology Network. All of us. Somehow we are not communicating clearly enough, nor in the right places, nor at the right time.
Which has me thinking about coming up with "PL/SQL Crash Course" that very succinctly reviews every key, non-trivial feature of the PL/SQL language, outlining clear use cases to which the feature should be applied. Something that can be read or watched in an hour. Something you can go back to for a quick refesher and orientation. Or a quiz on the PL/SQL Challenge that is designed to test your ability to recognize patterns in code that call for the application of certain features.
So that's what I'm thinking about. How about you? Any ideas for how to ensure that our developer community is at least aware of all the goodies Oracle offers in this language? Just hit Reply!
And I did receive a number of replies, so I thought I would post them here and invite my blog readers to comment, as well.
I blame it on the way the Oracle documentation is structured. The documentation is structured so that the first encounter to solve a "batch" insert (or batch anything), is to use the "old" non-bulk-processing LOOP structures.
We as humans are designed to spend just enough time to find the answer we are looking for without reading the entire documentation. Reading the entire documentation would be too expensive from an human evolutionary stand-point.
So the simple fix for this is to make the first introduction of LOOP structures, LOOP structures using the Bulk-Processing Syntax ! (duh - kind of like the little girl who told the firemen to let the air out of the tires of the truck so they could wedge it from the bridge.)
Push the old syntax to the back, and start with bulk-processing syntactical structures. Additionally, in cases where there is no performance degradation when using bulk-processing syntax in a situation where you do not need it, completely get rid of the old syntax from the documentation and only include the bulk-processing syntax.
Hope that makes sense.
There is one more solution to this problem - but I'm currently filing a patent for that. :-) Stay tuned!
I am very glad to hear you are going to do this. I am one of those time constrained developers who has missed out on not only on what came out in 8i, but I probably have big holes in the basics as I taught myself PL/SQL on the job and have never really taken any substantial training. Having a compilation of the “non-trivial” features in one place would be huge for me.
I don’t know if it possible, but I would find it handy if I could access this via Toad. They have the Search feature that accesses various sources (Toad World, Knowledge Base), so maybe it could be added as an item there? I know not everyone uses Toad, but it would reach a lot of this that way.
First, thanks for all the work you have done promoting PL/SQL and trying to get this information out to the masses.
You asked for input “Getting the information out”. We, like many institutions, are on tight budgets so traveling to these conference in just not in our budgets. We try our best to keep up with the new “goodies” but there is really no one place to find them. You can do a Google search and retrieve many hits, most of which answer specific questions and generally do not go far enough into the nuts-and-bolts of how to do it.
I have even tried to schedule a session with Quest folks on an overview of the new functionalities of TOAD with no luck.
It is hard to know what to ask for if you do not know it exists.
I think your idea of a “PL/SQL Crash Course” is a great idea. One company that we work with, Axosoft, has all their training videos on YouTube so they are easily accessible, but that is just one example.
It would seem if Oracle wants folks to buy and use their software, they would make it easier to learn how to use it in the most efficient and effective manner possible.
I gave it a thought - how is it possible to not know things like FORALL? Currently my company is running recruitment process where I am responsible for interview and evaluation of oracle skills (together with my colleagues) . I have some conclusions after those and looking at my personal experience.
Lets start with my own experience. I had luck to join current employer(name not important). I've learned most of the things related to performance here or on a training we were sent to and paid by
employer. I think it was worth it for both sides. I was able to use it on daily basis(not all of it of course since we use for example Foglight Performance Analysis. From bigger achievements I can mention 10x faster nightly batch. We are planning migration from 10g(windows) to 11g(oracle linux distribution with raw filesystem). I wasn't aware of new features. I made a research in documentation. And to be honest I will check one for 9 as well since I found few in 10g docs I wasn't aware of. I started my journey with oracle when there was version 8. So yes it is possible. Some features are not enabled/installed in our databases. To name two: application contexts and Java. Some features are blocked due to security policies like Java though you could say that properly configured will cause close to zero security issues. But back to the question from my own experience.
Why it was possible for me to not know some features:
- lack of training or expert help(when I was starting, it was mostly "Do this. Here you can find Oracle documentation: ..."). This was my first job for 6 years. Best bit was flexible hours after university (60 hours per month was ok for both sides).
- none of us was/is born expert. Everyone has to learn.
- little time between deliveries(currently we deliver twice more often then we did before)
- firefighting instead of preventing and improving attitude in the company(if it's 1h or 3h who cares if all ends before people start applications). No one will pay for 1+ week of work on improvements since there are things waiting that will generate $$$. It all changes dramatically if $$$ flow is disrupted by performance issues. Then there is race to fix things. Bigger refactoring? - forget it.
- little self investments(not my case since I buy and read tech books quite regularly). I love yours pl/sql best practices and Tom's effective oracle by design.
- disabled features even on development environments and general formal problems enabling/testing them(admin needs time to do things = time = $$ and so on or just lazy admin).
- no internal trainings or some kind of knowledge exchange. You know if you can ask person for advice, but they don't share it on their own. Not judging here.
- basically I like it the way General Practitioners work in some countries. They have to spend part of their job learning and reading new things and practices.
When it comes to applicants. I saw few types:
- come with only forms/reports/designer background and little oracle knowledge(bulk collect? bind variable?)
- worked on version 7 or Microsoft SQL server only
- worked with closed system so they could add functionality around it
- were held off by architects - don't use this and that
- changed employer and/or technologies too often to actually learn something well in one
How to make people aware of best practices/tools/designs?
Oracle documentation is good source for checking things though might be overwhelming if you try to read it like a book. Not that I did try ;).
I think yours best practices + Tom's book + asktom = almost all you need if you have some medium experience in SQL PL/SQL. The rest you can handle with Google search/own solutions.
Speaking of SQL and PL/SQL since I'm not an expert in other products. I think even if you make Oracle wiki with all the things clearly explained/in bold etc and almost ready to use. You will find many people too lazy/busy/... to read it.
PS. sorry for any mistakes i could've make. English is not my native language and I use it more to read lately then to write ;) Also my essay marks in school were rather opposite to math ones ;)