Wednesday, March 29, 2006
Optimizer
There is a great debate in the database application development community. I have encountered many developers who prefer putting all their business logic in the application code and using the database only as a glorified file system.
You pick your battles carefully with these developers. I always thought the "line of last defense" was data integrity rules. When I encountered a particularly stubborn developer, I decided that I would focus on convincing him to, at the very least, use his expensive RDBMS to protect his data's integrity (through use of constraints and keys).
However, I have learned that there is actually a whole other battlefield to my rear. I just came across a database application that overrides the optimizer. Every database query this application ever executes includes a forced index.
What do I mean? Why does that bother me? Let me take a short step back to explain what I mean.
Think about when you execute a query to access some data. That data you are requesting could be from several tables, spread all over the disk. The optimizer does many things, among them to choose how every query is executed. That means, for instance, it selects in which order the tables will be joined, and which indexes will be used to access the data.
In the past, presumably when this application was written, Oracle used a rule-based optimizer (RBO), which used a set of rules to determine how to access the data. The RBO did not always make ideal choices. Neither did the early versions of the cost-based optimizer (CBO). That is why many developers took advantage of their ability to override the optimizers and tell it (for example) which join order or indexes to use.
But that's the past. Let's talk about the present.
For several years now, I've never experienced a legitimate need to override the optimizer. Usually re-generating my DBMS_STATS is all I need to do in order to guarantee good choices.
As Jonathan Lewis describes in his latest book Cost-Based Oracle Fundamentals, the CBO has come a long way. You are doing yourself a great disservice when you aren't updating your applications to take advantage of it.
Both his book, and Oracle's Database Performance and Tuning Guide describe the optimizer in more detail, and present the various ways you can leverage its power. I really can't think of any reason why we still need to fight these battles with even the most stubborn of developers.
You pick your battles carefully with these developers. I always thought the "line of last defense" was data integrity rules. When I encountered a particularly stubborn developer, I decided that I would focus on convincing him to, at the very least, use his expensive RDBMS to protect his data's integrity (through use of constraints and keys).
However, I have learned that there is actually a whole other battlefield to my rear. I just came across a database application that overrides the optimizer. Every database query this application ever executes includes a forced index.
What do I mean? Why does that bother me? Let me take a short step back to explain what I mean.
Think about when you execute a query to access some data. That data you are requesting could be from several tables, spread all over the disk. The optimizer does many things, among them to choose how every query is executed. That means, for instance, it selects in which order the tables will be joined, and which indexes will be used to access the data.
In the past, presumably when this application was written, Oracle used a rule-based optimizer (RBO), which used a set of rules to determine how to access the data. The RBO did not always make ideal choices. Neither did the early versions of the cost-based optimizer (CBO). That is why many developers took advantage of their ability to override the optimizers and tell it (for example) which join order or indexes to use.
But that's the past. Let's talk about the present.
For several years now, I've never experienced a legitimate need to override the optimizer. Usually re-generating my DBMS_STATS is all I need to do in order to guarantee good choices.
As Jonathan Lewis describes in his latest book Cost-Based Oracle Fundamentals, the CBO has come a long way. You are doing yourself a great disservice when you aren't updating your applications to take advantage of it.
Both his book, and Oracle's Database Performance and Tuning Guide describe the optimizer in more detail, and present the various ways you can leverage its power. I really can't think of any reason why we still need to fight these battles with even the most stubborn of developers.
Tuesday, March 28, 2006
Bookmark This Page
To ease the task of browsing my archives, I've put together an organised list of my previous posts. Bookmark this, because I will keep it up to date. I will also include a link to this post under Archives.
Note: If you comment on an older article, I will get an automatic email. So I will see it and I will respond to it, so please feel free to do so!
For Newbies:
Tuesday, December 20, 2005
20 Beginner Oracle Questions
Friday, June 17, 2005
Asking For Help - Tips on where to go and how to do it.
Wednesday, July 19, 2006
Finding Information - How do you find answers to other questions?
Thursday, July 14, 2005
Oracle Docs - For when people tell you to RTFM!
Best Practices:
Thursday, October 19, 2006
3 Easy Ways to Improve Your PL/SQL - Improving your code through instrumentation and bulk processing.
Friday, March 09, 2007
40 Tips From Tom (Kyte)
Tuesday, June 14, 2005
Bind Variables in PL/SQL - Short answer: PL/SQL binds all variables (with some exceptions like dynamic SQL)
Tuesday, January 24, 2006
Gathering Requirements
Wednesday, March 01, 2006
Handling exceptions - A how-to guide.
Friday, March 10, 2006
Handling Performance Issues
Wednesday, August 17, 2005
Keeping Tables Small - In terms of number of rows, not columns. Improve performance.
Monday, October 31, 2005
Oracle Packages - And why/how you should use them.
Monday, July 11, 2005
Specifying INSERT Columns - Why it's a good habit.
Wednesday, May 18, 2005
Steven Feuerstein on Refactoring
Tuesday, July 26, 2005
Use Constraints - A how-to guide for people to whom I BEG to let the database handle the data's integrity.
Monday, July 25, 2005
Use Views - Why they're handy.
Tuesday, September 12, 2006
Using Numerical Fields - Don't use them for fields that aren't real numbers.
Oracle Packages:
Wednesday, October 12, 2005
DBMS_OUTPUT.PUT_LINE
Thursday, November 24, 2005
DBMS_PIPE - For communication between sessions
Sunday, August 14, 2005
UTL_HTTP - Including an example of how to get a stock quote from the Internet.
Top 20 Lists:
Sunday, January 15, 2006
Oracle DO NOTs - From AskTom
Tuesday, December 20, 2005
20 Beginner Oracle Questions
Monday, September 12, 2005
20 Oracle Lessons - After my first few months of blogging.
Monday, December 19, 2005
20 PL/SQL Coding Tips - Inspired by an AskTom thread
Tuesday, October 17, 2006
Software Vendor Customer Support - How to improve your luck when dealing with support (ok there are only 14)
Book Reviews:
Wednesday, June 22, 2005
Expert One-on-One - A couple of mistakes from my favourite Oracle book.
Monday, May 16, 2005
Optimizing Oracle Performance (Millsap, Holt)
Wednesday, November 02, 2005
Oracle Insights: Tales of the Oak Table
Thursday, June 23, 2005
PL/SQL Books - A list of my three favourite PL/SQL books
Templates:
Thursday, June 30, 2005
OOP in PL/SQL? Yep
Wednesday, July 13, 2005
Stored Procedure template
Great Debates:
Wednesday, April 23, 2008
Bashing RDBMS
Tuesday, June 21, 2005
Natural vs Synthetic keys - Choosing primary keys.
Wednesday, March 29, 2006
Optimizer - Should we be overriding it in our application?
Monday, September 19, 2005
PL/SQL Code Storage: Files vs In-DB Packages
Wednesday, January 18, 2006
PL/SQL vs J2EE - Where should you put the business logic?
NULLs:
Tuesday, February 24, 2009
Get Rid of NULL - Possibly the only viable solution to these problems.
Friday, September 09, 2005
NULLs in COUNT - Why counting on a column might get you a different total.
Friday, June 10, 2005
NULLs in Oracle
Tuesday, May 17, 2005
NULL vs Nothing - ANSI SQL is unlike programming languages because NULL is not nothing.
Gotchas!
Monday, June 13, 2005
Blank Lines and SQLPlus
Friday, May 20, 2005
Multiple Foreign Keys on the Same ID
Monday, July 04, 2005
SQLCODE and SQLERRM in INSERTs
How-To Guides:
Wednesday, September 14, 2005
Analyzing Query Performance - using SQLTRACE and TKPROF
Friday, May 04, 2007
ANSI Joins - Or do you want to stick with the old school style?
Friday, April 18, 2008
Avoid Deprayments - Learn how to test that your database application is connecting
Tuesday, February 14, 2006
BPEL - For SOA
Tuesday, January 17, 2006
Bulk Binding: FORALL - Improve performance of bulk updates.
Monday, August 02, 2010
Change a User's Default Schema - Several ways.
Thursday, January 22, 2009
Cleaning up with ALL_TAB_COLS
Thursday, September 22, 2005
Column Name as a Variable
Thursday, June 16, 2005
Common Table Column Types - Two tables with columns on the same type, but not actually related.
Wednesday, August 24, 2005
COMPUTE - How to emulate a feature found in other languages
Saturday, June 18, 2005
Connect By - Heirarchical queries, something you need to know.
Monday, June 20, 2005
Decode - CASE's precursor.
Thursday, March 25, 2010
DECODE/CASE vs. Mapping Tables - Which should you use?
Thursday, November 10, 2005
DUAL Table - You've seen it, what is it?
Thursday, May 19, 2005
Dynamically assigning size of varchar2 - You do it in other languages, can you do it in PL/SQL (and how)?
Wednesday, May 25, 2005
ENUM in Oracle - Emulating a common programming feature in PL/SQL.
Friday, July 01, 2005
Extra Columns in a GROUP BY
Tuesday, May 09, 2006
Finding Nearby Rows. Three methods of solving similar requirements.
Wednesday, August 11, 2010
FIRST_ROWS vs ALL_ROWS - What's the difference?
Sunday, October 21, 2007
Global Temporary Tables. The ultimate work tables!
Monday, August 01, 2005
Import Export
Tuesday, February 23, 2010
Improving your SQL Queries
Monday, May 28, 2007
Multirow Inserts - Does Oracle support the ANSI SQL standard of inserting multiple rows? No. But here's how you can fake it.
Thursday, May 26, 2005
NOCOPY Hint - Improve performance by changing how variables are passed to PL/SQL procedures.
Saturday, July 23, 2005
Oracle BOOLEAN - PL/SQL has BOOLEAN, here's how to emulate it in Oracle's SQL
Friday, July 29, 2005
Oracle By Example - Bringing several concepts together to solve a problem.
Friday, June 24, 2005
Oracle Client - How to install
Tuesday, July 04, 2006
Oracle and Java
Monday, October 30, 2006
Oracle Passwords - Answering your common questions
Thursday, December 15, 2005
Oracle and Perl
Thursday, February 02, 2006
Oracle and SOA - Covers Oracle's Service-Oriented Architecture at a high level
Wednesday, February 22, 2006
Oracle Sequences - This is the one Oracle carried on its main page
Friday, February 20, 2009
Overlaps - Useful. Ever heard of it?
Thursday, September 01, 2005
Pivot and Crosstab Queries - A very useful technique for turning rows into columns, and vice versa
Monday, April 03, 2006
Pivot Queries Using Variable Number of Columns - Part 2 on pivot queries, when you don't know how many columns you need in advance.
Friday, September 30, 2005
PL/SQL Procedure Call Overhead - Is there one?
Friday, August 11, 2006
PL/SQL Procedure Call Overhead Re-visited - By Zsolt Lajosfalvi
Saturday, September 02, 2006
Protecting PL/SQL Code - Using wrap.
Tuesday, May 24, 2005
Random Numbers - How to generate them.
Monday, November 21, 2005
RAW Datatype
Tuesday, June 27, 2006
Recursion vs Iteration - What are they, what are the advantages of each one?
Tuesday, June 13, 2006
Refreshing Data - A High-level picture of the flow and what to keep in mind when designing your data import strategy
Thursday, October 06, 2005
ROWNUM and ROWID - And how they're used to solve various issues, and improve retrieval times.
Tuesday, February 07, 2006
TRANSLATE - What it is, how to use it.
Wednesday, August 10, 2005
UNION ALL - How and when to avoid performance hits
Monday, April 17, 2006
Updating Views - Can you do it, and if so, when.
Monday, June 27, 2005
Using Bad Names in Oracle
Tuesday, October 04, 2005
Using DECODE to exploit COUNT/NULL feature - Applying DECODE and our knowledge of COUNT/NULL together in a little trick to speed up a query.
Wednesday, June 15, 2005
Variable Constraints - Can you use variables in constraints? How?
Friday, November 10, 2006
View Constraints - Can you manage integrity using views?
Monday, July 18, 2005
Which instance am I in?
Tuesday, May 30, 2006
Windowing Clauses - How they are used to empower your analytic functions.
Other:
Monday, February 26, 2007
Fun With Tom Kyte - Get a laugh out of Oracle's king of wit
Thursday, April 05, 2007
Oracle Beefs - Here are mine. What are yours?
Tuesday, October 31, 2006
Oracle Gurus - What makes an Oracle guru?
Tuesday, February 21, 2006
Oracle Interview Questions - How to come up with useful ones.
Sunday, June 03, 2007
SQL Interview Questions - Here's what I ask. Prepare for your interviews.
Friday, May 25, 2007
What Makes a Great Oracle Blog?
Note: If you comment on an older article, I will get an automatic email. So I will see it and I will respond to it, so please feel free to do so!
For Newbies:
Tuesday, December 20, 2005
20 Beginner Oracle Questions
Friday, June 17, 2005
Asking For Help - Tips on where to go and how to do it.
Wednesday, July 19, 2006
Finding Information - How do you find answers to other questions?
Thursday, July 14, 2005
Oracle Docs - For when people tell you to RTFM!
Best Practices:
Thursday, October 19, 2006
3 Easy Ways to Improve Your PL/SQL - Improving your code through instrumentation and bulk processing.
Friday, March 09, 2007
40 Tips From Tom (Kyte)
Tuesday, June 14, 2005
Bind Variables in PL/SQL - Short answer: PL/SQL binds all variables (with some exceptions like dynamic SQL)
Tuesday, January 24, 2006
Gathering Requirements
Wednesday, March 01, 2006
Handling exceptions - A how-to guide.
Friday, March 10, 2006
Handling Performance Issues
Wednesday, August 17, 2005
Keeping Tables Small - In terms of number of rows, not columns. Improve performance.
Monday, October 31, 2005
Oracle Packages - And why/how you should use them.
Monday, July 11, 2005
Specifying INSERT Columns - Why it's a good habit.
Wednesday, May 18, 2005
Steven Feuerstein on Refactoring
Tuesday, July 26, 2005
Use Constraints - A how-to guide for people to whom I BEG to let the database handle the data's integrity.
Monday, July 25, 2005
Use Views - Why they're handy.
Tuesday, September 12, 2006
Using Numerical Fields - Don't use them for fields that aren't real numbers.
Oracle Packages:
Wednesday, October 12, 2005
DBMS_OUTPUT.PUT_LINE
Thursday, November 24, 2005
DBMS_PIPE - For communication between sessions
Sunday, August 14, 2005
UTL_HTTP - Including an example of how to get a stock quote from the Internet.
Top 20 Lists:
Sunday, January 15, 2006
Oracle DO NOTs - From AskTom
Tuesday, December 20, 2005
20 Beginner Oracle Questions
Monday, September 12, 2005
20 Oracle Lessons - After my first few months of blogging.
Monday, December 19, 2005
20 PL/SQL Coding Tips - Inspired by an AskTom thread
Tuesday, October 17, 2006
Software Vendor Customer Support - How to improve your luck when dealing with support (ok there are only 14)
Book Reviews:
Wednesday, June 22, 2005
Expert One-on-One - A couple of mistakes from my favourite Oracle book.
Monday, May 16, 2005
Optimizing Oracle Performance (Millsap, Holt)
Wednesday, November 02, 2005
Oracle Insights: Tales of the Oak Table
Thursday, June 23, 2005
PL/SQL Books - A list of my three favourite PL/SQL books
Templates:
Thursday, June 30, 2005
OOP in PL/SQL? Yep
Wednesday, July 13, 2005
Stored Procedure template
Great Debates:
Wednesday, April 23, 2008
Bashing RDBMS
Tuesday, June 21, 2005
Natural vs Synthetic keys - Choosing primary keys.
Wednesday, March 29, 2006
Optimizer - Should we be overriding it in our application?
Monday, September 19, 2005
PL/SQL Code Storage: Files vs In-DB Packages
Wednesday, January 18, 2006
PL/SQL vs J2EE - Where should you put the business logic?
NULLs:
Tuesday, February 24, 2009
Get Rid of NULL - Possibly the only viable solution to these problems.
Friday, September 09, 2005
NULLs in COUNT - Why counting on a column might get you a different total.
Friday, June 10, 2005
NULLs in Oracle
Tuesday, May 17, 2005
NULL vs Nothing - ANSI SQL is unlike programming languages because NULL is not nothing.
Gotchas!
Monday, June 13, 2005
Blank Lines and SQLPlus
Friday, May 20, 2005
Multiple Foreign Keys on the Same ID
Monday, July 04, 2005
SQLCODE and SQLERRM in INSERTs
How-To Guides:
Wednesday, September 14, 2005
Analyzing Query Performance - using SQLTRACE and TKPROF
Friday, May 04, 2007
ANSI Joins - Or do you want to stick with the old school style?
Friday, April 18, 2008
Avoid Deprayments - Learn how to test that your database application is connecting
Tuesday, February 14, 2006
BPEL - For SOA
Tuesday, January 17, 2006
Bulk Binding: FORALL - Improve performance of bulk updates.
Monday, August 02, 2010
Change a User's Default Schema - Several ways.
Thursday, January 22, 2009
Cleaning up with ALL_TAB_COLS
Thursday, September 22, 2005
Column Name as a Variable
Thursday, June 16, 2005
Common Table Column Types - Two tables with columns on the same type, but not actually related.
Wednesday, August 24, 2005
COMPUTE - How to emulate a feature found in other languages
Saturday, June 18, 2005
Connect By - Heirarchical queries, something you need to know.
Monday, June 20, 2005
Decode - CASE's precursor.
Thursday, March 25, 2010
DECODE/CASE vs. Mapping Tables - Which should you use?
Thursday, November 10, 2005
DUAL Table - You've seen it, what is it?
Thursday, May 19, 2005
Dynamically assigning size of varchar2 - You do it in other languages, can you do it in PL/SQL (and how)?
Wednesday, May 25, 2005
ENUM in Oracle - Emulating a common programming feature in PL/SQL.
Friday, July 01, 2005
Extra Columns in a GROUP BY
Tuesday, May 09, 2006
Finding Nearby Rows. Three methods of solving similar requirements.
Wednesday, August 11, 2010
FIRST_ROWS vs ALL_ROWS - What's the difference?
Sunday, October 21, 2007
Global Temporary Tables. The ultimate work tables!
Monday, August 01, 2005
Import Export
Tuesday, February 23, 2010
Improving your SQL Queries
Monday, May 28, 2007
Multirow Inserts - Does Oracle support the ANSI SQL standard of inserting multiple rows? No. But here's how you can fake it.
Thursday, May 26, 2005
NOCOPY Hint - Improve performance by changing how variables are passed to PL/SQL procedures.
Saturday, July 23, 2005
Oracle BOOLEAN - PL/SQL has BOOLEAN, here's how to emulate it in Oracle's SQL
Friday, July 29, 2005
Oracle By Example - Bringing several concepts together to solve a problem.
Friday, June 24, 2005
Oracle Client - How to install
Tuesday, July 04, 2006
Oracle and Java
Monday, October 30, 2006
Oracle Passwords - Answering your common questions
Thursday, December 15, 2005
Oracle and Perl
Thursday, February 02, 2006
Oracle and SOA - Covers Oracle's Service-Oriented Architecture at a high level
Wednesday, February 22, 2006
Oracle Sequences - This is the one Oracle carried on its main page
Friday, February 20, 2009
Overlaps - Useful. Ever heard of it?
Thursday, September 01, 2005
Pivot and Crosstab Queries - A very useful technique for turning rows into columns, and vice versa
Monday, April 03, 2006
Pivot Queries Using Variable Number of Columns - Part 2 on pivot queries, when you don't know how many columns you need in advance.
Friday, September 30, 2005
PL/SQL Procedure Call Overhead - Is there one?
Friday, August 11, 2006
PL/SQL Procedure Call Overhead Re-visited - By Zsolt Lajosfalvi
Saturday, September 02, 2006
Protecting PL/SQL Code - Using wrap.
Tuesday, May 24, 2005
Random Numbers - How to generate them.
Monday, November 21, 2005
RAW Datatype
Tuesday, June 27, 2006
Recursion vs Iteration - What are they, what are the advantages of each one?
Tuesday, June 13, 2006
Refreshing Data - A High-level picture of the flow and what to keep in mind when designing your data import strategy
Thursday, October 06, 2005
ROWNUM and ROWID - And how they're used to solve various issues, and improve retrieval times.
Tuesday, February 07, 2006
TRANSLATE - What it is, how to use it.
Wednesday, August 10, 2005
UNION ALL - How and when to avoid performance hits
Monday, April 17, 2006
Updating Views - Can you do it, and if so, when.
Monday, June 27, 2005
Using Bad Names in Oracle
Tuesday, October 04, 2005
Using DECODE to exploit COUNT/NULL feature - Applying DECODE and our knowledge of COUNT/NULL together in a little trick to speed up a query.
Wednesday, June 15, 2005
Variable Constraints - Can you use variables in constraints? How?
Friday, November 10, 2006
View Constraints - Can you manage integrity using views?
Monday, July 18, 2005
Which instance am I in?
Tuesday, May 30, 2006
Windowing Clauses - How they are used to empower your analytic functions.
Other:
Monday, February 26, 2007
Fun With Tom Kyte - Get a laugh out of Oracle's king of wit
Thursday, April 05, 2007
Oracle Beefs - Here are mine. What are yours?
Tuesday, October 31, 2006
Oracle Gurus - What makes an Oracle guru?
Tuesday, February 21, 2006
Oracle Interview Questions - How to come up with useful ones.
Sunday, June 03, 2007
SQL Interview Questions - Here's what I ask. Prepare for your interviews.
Friday, May 25, 2007
What Makes a Great Oracle Blog?
Friday, March 10, 2006
Handling Performance Issues
Typically performance issues are handled by looking for common symptoms and trying common solutions. Given a sufficient level of experience, this is successful quite often with minimal effort. But there are times where this does not work and you need a more systematic approach.
Let's take a look at one approach, bearing in mind:
1. This is a rough, first, high-level pass
2. It is NOT Oracle (or even database) specific
3. I am not including the details on the HOW
Also, some people may notice Cary Millsap's influence in this approach.
1. Rank the most significant performance issues, by specific application, from the business user's perspective.
2. Carefully measure the total time currently taken for each of these specific applications.
3. Determine exactly how fast the application would need to run in order to meet the business user's needs.
Now, for each business application in order of importance, perform steps starting at 4-9:
4. Break down the specific application into tasks.
5. Carefully measure how often each task is currently being executed, how long each execution currently takes, and from that, how much total time it currenty takes and what % of total time each task represents.
Now, for each specific task in order of total % of time taken, perform steps 6-9:
6. If you COMPLETELY ELIMINATED the time taken by this task and all tasks below it, would the performance goal be met?
YES: Continue, with step 7-9
NO: Stop until the situation changes. Continue to the next application, steps 4-9.
7. Predict how to reduce time spent for this task, either by:
a) Reducing the number of times this task is being done
OR
b) Reducing the time it takes to execute a task once
Note: If required, recursively perform steps 4-9 by breaking the task down further into sub-tasks.
8. Perform a cost-benefit analysis of your plan in step 7. Is it worthwhile? If so, do it.
9. Have you reached the performance goal of this application?
YES: Proceed to the next application and perform steps 4-9.
NO: Proceed to the next task and repeat steps 6-9.
Notes:
1. By doing things in order of business importance
a) the users are most likely to experience results
b) anything we "harm" is, by definition, less important
2. By addressing tasks in order of time spent
a) we get the best results first
b) adverse results will only affect tasks that take less time
3. By defining success up front, and performing a cost-benefit analysis before taking action, we can avoid wasting time by stopping either
a) when the goal is met
b) when the goal is proven to be impossible.
Since this is a rough sketch, I especially invite people's thoughts.
Let's take a look at one approach, bearing in mind:
1. This is a rough, first, high-level pass
2. It is NOT Oracle (or even database) specific
3. I am not including the details on the HOW
Also, some people may notice Cary Millsap's influence in this approach.
1. Rank the most significant performance issues, by specific application, from the business user's perspective.
2. Carefully measure the total time currently taken for each of these specific applications.
3. Determine exactly how fast the application would need to run in order to meet the business user's needs.
Now, for each business application in order of importance, perform steps starting at 4-9:
4. Break down the specific application into tasks.
5. Carefully measure how often each task is currently being executed, how long each execution currently takes, and from that, how much total time it currenty takes and what % of total time each task represents.
Now, for each specific task in order of total % of time taken, perform steps 6-9:
6. If you COMPLETELY ELIMINATED the time taken by this task and all tasks below it, would the performance goal be met?
YES: Continue, with step 7-9
NO: Stop until the situation changes. Continue to the next application, steps 4-9.
7. Predict how to reduce time spent for this task, either by:
a) Reducing the number of times this task is being done
OR
b) Reducing the time it takes to execute a task once
Note: If required, recursively perform steps 4-9 by breaking the task down further into sub-tasks.
8. Perform a cost-benefit analysis of your plan in step 7. Is it worthwhile? If so, do it.
9. Have you reached the performance goal of this application?
YES: Proceed to the next application and perform steps 4-9.
NO: Proceed to the next task and repeat steps 6-9.
Notes:
1. By doing things in order of business importance
a) the users are most likely to experience results
b) anything we "harm" is, by definition, less important
2. By addressing tasks in order of time spent
a) we get the best results first
b) adverse results will only affect tasks that take less time
3. By defining success up front, and performing a cost-benefit analysis before taking action, we can avoid wasting time by stopping either
a) when the goal is met
b) when the goal is proven to be impossible.
Since this is a rough sketch, I especially invite people's thoughts.
Wednesday, March 01, 2006
Handling exceptions
I've got a checklist of tasks I like to complete before beginning a new project. One important item on my list is to decide on how my exceptions are going to be handled.
By deciding in advance, I can save time by implementing exception handling only once, and also have much more consistent code. I also find that once I begin a project, I'm focusing on the logic and can easily miss handling exceptions properly, so my code is much more robust thinking about it in advance.
So what is an exception?
Well an exception is more about what it represents rather than what it IS. The existence of an exception means that something unexpected has failed in the run-time execution of your code. It also means that this execution has stopped.
Actually, I am slightly oversimplying the case, because you can actually create and raise an exception yourself without an unexpected failure, but generally you are only going to do this if you have either found something wrong, or you want to infuriate whoever is maintaining your code.
Ok, so you've got an exception. Now what?
Good question. Your first choice is to do nothing. That will cause your code block to fail, giving whatever executed this block an opportunity to do something. If you executed the block from SQL*Plus, (edit) all the (non-COMMITed) work in your block and all (non-autonomous) sub-blocks will be rolled back by the database.
Your other option is to do something. Your only opportunity to do this is in the EXCEPTION section of your block. In this section you can examine the type of exception, and handle it however you please.
Briefly, it looks like this:
Once you're in there, you can get information about your error by looking at SQLCODE and SQLERRM, which contain the number and a text message for the latest exception. Oddly enough (here's a little "gotcha") you can't just insert these values to a table, for reasons I discovered last July.
Incidentally, here is what the "do nothing" (edit or "hide the error") option looks like:
EXCEPTION WHEN OTHERS THEN NULL;
Edit: Read Tom Kyte's comments to learn why some people will strangle you with their bare hands if you ever do this.
I want to do something. Uhh ... what should I do?
That's an even better question, and one that not a lot of books or articles actually focus on. That's because you have to figure out for yourself what you want to do when something goes wrong. Do you want to write a message to a table that you can query later? Are there some business-specific steps you want to do? Do you want to email yourself? Do you want to write to a log? Do you want to send a text message to output?
Whatever you decide to do, don't forget to do any clean up, because this is your only chance. Like I said, execution of your code block is over, and there is no avoiding that, so if you have something to do, do it now!
What if I don't want the execution to stop?
Too bad! The only way around that is to decide what code falls into this category, and wrap it in its own, nested block. Let me explain (and demonstrate).
Let's say you have several tasks to perform, and regardless of whether the first one succeeds or not, you want to continue to do the other tasks. Since execution stops the moment you get an exception, this is a case where you should wrap the first task in its own block, and handle the exception there. It doesn't even have to be a separate function/procedure, you can just create an anonymous block right then and there, like this:
Don't forget to actually handle the exception in your nested block! As I said before, unhandled exceptions stop the execution of whatever called your block, too.
Incidentally, in some cases you actually want to stop the execution of the calling block, but you still want to do something right now in your current, failed block. But if you handle it, then the exception is gone, and the calling block will therefore just continue its execution. In that case, you can re-raise the exception. The only caveat is that whatever called your block will think that the exception came from your exception handling code, not the original point of failure. Keep that in mind.
Good stuff. So what do you do?
Personally, I like to define an exception package.
There I can do a few things. I like to define my own exceptions (including number and text message) using PRAGMA EXCEPTION_INIT. Then I can throw my custom exceptions around.
But more importantly I can write a few different procedures that can handle exceptions in standard ways, and then all I have to do in my exception blocks is choose which way I want to handle an error, and then send the required information (usually SQLCODE and SQLERRM) to these standard procedures.
Needless to say, I can put these packages together quite quickly by re-using tested, trusted code from previous projects.
A word on rollback
I mentioned previously that if you don't handle your exceptions, SQL*Plus will rollback your work. It is probably better for you if you decide what gets committed, and what gets rolled back, and when. Therefore you should be sure to include COMMIT (preferably with SAVEPOINTs) and ROLLBACK appropriately in your code. And while ROLLBACK is a topic for another day, I should at least tell you to be aware of autonomous transactions, which are special blocks that will commit its work right away, regardless of whether its calling block rolled back or not.
Whew, anything else?
Yeah, one more thing. Exceptions in the DECLARE block can't be caught in the same block, but rather by the calling block. Keep that in mind in your EXCEPTION block when your block calls blocks with a DECLARE section.
In closing, it's important to note that this is a huge topic, and I can't do it justice in one page. Fortunately Oracle has a great write-up on handling PL/SQL Errors in the PL/SQL User's Guide and Reference, see Chapter 7. It even includes a list of pre-defined exceptions and what they mean.
But the true master of PL/SQL Exception Handling is doubtlessly Steven Feuerstein. I urge you to buy his book on Oracle PL/SQL Programming, and check out Chapter 8 on exception handling. If at all possible, attend one of his live presentations.
By deciding in advance, I can save time by implementing exception handling only once, and also have much more consistent code. I also find that once I begin a project, I'm focusing on the logic and can easily miss handling exceptions properly, so my code is much more robust thinking about it in advance.
So what is an exception?
Well an exception is more about what it represents rather than what it IS. The existence of an exception means that something unexpected has failed in the run-time execution of your code. It also means that this execution has stopped.
Actually, I am slightly oversimplying the case, because you can actually create and raise an exception yourself without an unexpected failure, but generally you are only going to do this if you have either found something wrong, or you want to infuriate whoever is maintaining your code.
Ok, so you've got an exception. Now what?
Good question. Your first choice is to do nothing. That will cause your code block to fail, giving whatever executed this block an opportunity to do something. If you executed the block from SQL*Plus, (edit) all the (non-COMMITed) work in your block and all (non-autonomous) sub-blocks will be rolled back by the database.
Your other option is to do something. Your only opportunity to do this is in the EXCEPTION section of your block. In this section you can examine the type of exception, and handle it however you please.
Briefly, it looks like this:
EXCEPTION
WHEN ONE_KIND_OF_ERROR THEN
-- Do something here
WHEN OTHERS THEN
-- Handle all left over cases here
END
Once you're in there, you can get information about your error by looking at SQLCODE and SQLERRM, which contain the number and a text message for the latest exception. Oddly enough (here's a little "gotcha") you can't just insert these values to a table, for reasons I discovered last July.
Incidentally, here is what the "do nothing" (edit or "hide the error") option looks like:
EXCEPTION WHEN OTHERS THEN NULL;
Edit: Read Tom Kyte's comments to learn why some people will strangle you with their bare hands if you ever do this.
I want to do something. Uhh ... what should I do?
That's an even better question, and one that not a lot of books or articles actually focus on. That's because you have to figure out for yourself what you want to do when something goes wrong. Do you want to write a message to a table that you can query later? Are there some business-specific steps you want to do? Do you want to email yourself? Do you want to write to a log? Do you want to send a text message to output?
Whatever you decide to do, don't forget to do any clean up, because this is your only chance. Like I said, execution of your code block is over, and there is no avoiding that, so if you have something to do, do it now!
What if I don't want the execution to stop?
Too bad! The only way around that is to decide what code falls into this category, and wrap it in its own, nested block. Let me explain (and demonstrate).
Let's say you have several tasks to perform, and regardless of whether the first one succeeds or not, you want to continue to do the other tasks. Since execution stops the moment you get an exception, this is a case where you should wrap the first task in its own block, and handle the exception there. It doesn't even have to be a separate function/procedure, you can just create an anonymous block right then and there, like this:
BEGIN
BEGIN
-- Do task one.
EXCEPTION
-- Handle it!
END
-- Do task two
-- Do task three ...
EXCEPTION
-- Handle exceptions from task two, three, ...
END
Don't forget to actually handle the exception in your nested block! As I said before, unhandled exceptions stop the execution of whatever called your block, too.
Incidentally, in some cases you actually want to stop the execution of the calling block, but you still want to do something right now in your current, failed block. But if you handle it, then the exception is gone, and the calling block will therefore just continue its execution. In that case, you can re-raise the exception. The only caveat is that whatever called your block will think that the exception came from your exception handling code, not the original point of failure. Keep that in mind.
Good stuff. So what do you do?
Personally, I like to define an exception package.
There I can do a few things. I like to define my own exceptions (including number and text message) using PRAGMA EXCEPTION_INIT. Then I can throw my custom exceptions around.
But more importantly I can write a few different procedures that can handle exceptions in standard ways, and then all I have to do in my exception blocks is choose which way I want to handle an error, and then send the required information (usually SQLCODE and SQLERRM) to these standard procedures.
Needless to say, I can put these packages together quite quickly by re-using tested, trusted code from previous projects.
A word on rollback
I mentioned previously that if you don't handle your exceptions, SQL*Plus will rollback your work. It is probably better for you if you decide what gets committed, and what gets rolled back, and when. Therefore you should be sure to include COMMIT (preferably with SAVEPOINTs) and ROLLBACK appropriately in your code. And while ROLLBACK is a topic for another day, I should at least tell you to be aware of autonomous transactions, which are special blocks that will commit its work right away, regardless of whether its calling block rolled back or not.
Whew, anything else?
Yeah, one more thing. Exceptions in the DECLARE block can't be caught in the same block, but rather by the calling block. Keep that in mind in your EXCEPTION block when your block calls blocks with a DECLARE section.
In closing, it's important to note that this is a huge topic, and I can't do it justice in one page. Fortunately Oracle has a great write-up on handling PL/SQL Errors in the PL/SQL User's Guide and Reference, see Chapter 7. It even includes a list of pre-defined exceptions and what they mean.
But the true master of PL/SQL Exception Handling is doubtlessly Steven Feuerstein. I urge you to buy his book on Oracle PL/SQL Programming, and check out Chapter 8 on exception handling. If at all possible, attend one of his live presentations.
