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.
From Christopher:
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!
From William
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.
From Calvin
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 ;)
4 comments:
This is an interesting question.
From my personal experience I can say that some PL/SQL programmers are simply *too lazy* to learn something new.
I often hear "I did this way because I know it works", no matter if it is written as if we were still on Oracle 7.
Said that, the problem with the "What's new" chapter at every round is in that you often lack perspective.
It's not enough to advertise a new feature if you can't see a useful implementation for it.
A classical example of this if with certain analytic functions: you have a perfect description of what they do, but usually either you don't remember a certain function exists or you can't map the function to your problem.
For instance, I can think of many situations where DENSE_RANK can save the day, but I fail to find a good use case for PERCENTILE_DISC.
How probable is that I will ever use PERCENTILE_DISC if I can't clearly understand what is meant for?
Oracle has become a huge eco-system over time, knowing each and every nuance of it is becoming increasingly difficult and many programmers simply refuse to go back and refurbish their code, unless they are forced to do so.
The same way that some people still think that if you are selecting x% of rows from a table you definitely want to use an index (regardless of the data distribution in the table). Oracle concepts aren't typically taught in a school curriculum.... Database design is, but not Oracle fundamentals, including PL/SQL fundamentals. So I am sure most people just learn on the job. When they get the right answer they move on, regardless if there is a better way. You know the resources to learn PL/SQL properly are already available (you wrote / recorded most of them!), but a lot of people are typically just worried about getting past this fire to the next one and not learning properly, and with them there is nothing really you can do.
Many PL/SQL instructore are focused on solving the problem quickly, and they don't give performance any thought.
Plus, developers are evaluated on productivity, and tuning can take time that does n ot reflect in their quarterly productivity evaluations.
Let the DBA tune it, later . . .
Hello All,
Once somebody working at our local Oracle told us in a conference:
"It is not so important how high quality or bug-free your software is, it is much more important how many customers you will convince to buy it !".
For some reason, quantity always prevails upon quality, and, yes ...
unfortunately, you are judged by how many tasks you have accomplished, and not by how much maintenance those will require further due to bugs or performance problems.
DBA-s will NEVER be able to tune an application, the maximum they will do is to create eventually an additional index or drop one.
Tuning is an ART of developers,
it should "flow in your veins" as you write code, and not just when the users complain about slowness ...
Oracle documentation is good for those who have the time to read everything, I used to do this with the older versions, when this was not so much as it is today ...
And, what is critically missing, is the TIME to learn, play around and experiment with new features.
No manager will accept the idea that a developer should learn all the time, every day, and not just on a maybe 1 day conference that he attends ...
Learning is a profession and an art that should be practiced daily, not just because you have to solve a work task, but because you love to do it and it's your way of living.
Best Regards,
Iudith
Post a Comment