Saturday, August 11, 2012
The Two Ways of Doing a Job
Whether it's deployment, development, performance tuning, troubleshooting or something else, there are two fundamentally different ways of doing your job: doing it fast and doing it completely.
Doing it Fast
Sometimes you can make a case for doing something fast. If you're dealing with something you're only going to do once, in a problem space you're either already deeply familiar with or couldn't care less about, and have a number of other competing priorities for your time, that's usually when a case for doing something fast can be made.
Doing something fast usually means trying a few quick ideas, either from your own toolbelt or something you saw on this site or somewhere else, and hacking away until it works, whether you understand what you're doing or not.
It's certainly the most time-efficient way of doing your job, at least in the short-term, and at some risk. Unfortunately there are some long-term consequences to this approach.
Doing it Completely
What exactly does it mean to do a job completely? Even when someone knows what they're doing and does an excellent job, the failure to properly document it, test it, communicate it and cross-train the team generally eliminates most of the advantages of doing the job in the first place. Given that completing the task is generally harder and more time-consuming than those other tasks, it's tragic how someone is doing 80% of the work for 20% of the benefit.
A frequent mantra we've always shared among us is "the job is never done unless it's tested and documented." There's no better way to describe the fundamental difference between doing a job fast and doing it completely. For instance, we don't allow a customer ticket to be closed, nor a project task to be marked off until it has been appropriately tested and the work has been appropriately recorded in the appropriate place.
It may sound like common sense, especially for someone who is sufficiently dedicated to their profession to read sites like this, but it's alarming how many we come across who simply don't properly research the technology with which they're working, document what they're doing, fully test their solution (with the users), all the while communication their work with those involved.
Consequently these people generally can't do their jobs any better in year five than they could in year two, support systems no easier to maintain in year five than in year two, spend a lot of their time fighting avoidable fires (though some of them actually like this), generally dislike their jobs, but don't have the reputation or knowledge to get other opportunities.
Doing a job completely involves five primary differences to doing a job fast: investing the time to understand the technology, taking the steps to completely the job properly, documenting the work, testing it thoroughly, and communicating with others.
1. Understand the Technology
Doing a job completely means looking at every task as an opportunity to improve your knowledge in the relevant technology. For example, coming to a site like this is an excellent way to get a deeper understanding not only of how to solve a problem, but how the particular component in question works.
Get a few books, talk to a few experts, work with the technology yourself (in a private development environment, initially) and develop a thorough understanding. You originally paid thousands of dollars for your education, here's your chance to get it on the job for free (or sometimes even get paid for it).
You chose this profession for a reason - you love it. You should really love the deeper understanding of the technology this investment in time and effort will give you. Did you really get into this field to hack blindly at quick fixes? To apply other people's solutions that you don't even understand?
A thorough understanding will also help you do your job faster and better in the future, finding even better solutions to even tougher challenges, all the while reducing the chances of making mistakes.
2. Do the Job Properly
Every time you do a job the goal has to be to leave the system more robust, maintainable, faster and more error-free than when you found it, and by the largest degree possible.
For example, when writing code, including good exception-handling, inline documentation, and instrumentation (debug messages). Write your code so that it is reusable in other systems. Furthermore, load the code into source control, and put good comments when you check it in. Conduct a code review with your team when you're done. There are any number of ways to improve the code, these are just suggestions (and here are a few more tricks to improve your PL/SQL code).
Speaking of suggestions, when tracking down a performance issue, set up automated performance test procedures. Evaluate performance-testing software and get one set up in your development environment, which pre-loaded tests that can run at the touch of a button. Work with other teams or customers that support similar technologies and create a shared standard.
When designing or deploying a system, think about security and scalability, not just the checklist of functionality requirements. Make sure your system is appropriately designed and sized for future growth, and maintenance - build a development, testing and production environment.
When troubleshooting, doing the job properly means getting to the root cause of the problem, not just the quick fix. There are even ways to add value when writing SQL queries. These are the types of value-added activities that make everything you do in your career progressively easier, and your systems progressively more robust and maintainable.
There are lots of possibilities (here are a few more). These are the various differences between doing a job properly and just doing a job fast. It's the difference between professionals who love their job and do it well, and those who just do their time and wonder why they don't get anywhere.
At least have the conversation with yourself. Have you found the root cause, is there something you can do to make future development easier, are there other performance issues you can track down while you're in there, or ways to help find them in the future?
3. Document
Part of doing a job properly is documenting your work. Document the requirements before you begin, document your designs, deployment plans, tests, how to maintain it - whatever is appropriate.
They secret to writing good documentation is pretending you're someone else, and you're reading this in the future. Maybe you're someone maintaining the work, or someone doing something similar somewhere else, or maybe it broke and you're someone fixing it.
Anticipate what this individual is looking for. If they're doing something similar, what are the exact steps needed to do it from scratch, including explanations behind each step? If something broke, what do you think it would be, what tests would prove it, and how would they fix it?
Be as thorough as that future individual (which might be you!) would need you to be, including the requirements of the job, names and dates, the tests and their results.
4. Test
If the three rules of real estate are location, location, location then the three rules of our profession is test, test, test.
Years ago we termed the phrase deprayment to refer to any deployment that wasn't tested. Rather than deploy-and-test, we had one individual on our team that would unfortunately deploy-and-pray (thus "deprayment"). Don't do this, because it generally hurts others more than it does you, especially since there are many effective ways to test practically anything.
Test it yourself, have someone on your team test, have the vendor test it, have those who support systems that touch yours test it, and have the user test it. Everyone is testing it from their own perspective.
Record your tests, include steps, results, dates, and names. If something breaks later, even something unrelated to what you're doing, knowing what tests yielded which results on which day can be very useful troubleshooting information indeed.
It also shows a tremendous amount of respect for your system, the people that use it, and those systems that depend on it, or upon which it depends. It reinforces the message that your system is important, and will generally result in high level of cooperation with all of them. A level of commitment that will help you do your job more successfully, and at much lower risk since so many people have knowledge about what's going on and who's involved.
Testing is also a great way to deal with any issues that come up. Despite your best efforts sometimes things happen, but if you have evidence that your solution was properly tested it will not only help you fix this new problem but also demonstrate to your customers and managers that it wasn't likely caused by incompetence or neglect.
That may sound harsh, but how else do customers and managers know if you're doing your job properly or not? Good documentation, testing and above all the communication it entails is the best way for them to know that there's a difference between you and a non-professional.
5. Communicate
I've very rarely ran into a situation where someone was accused of over-communication. Miscommunication, sure, but not over-communication. If in doubt simply ask those with whom you have relationships what level of communication they would like (and document it).
One important type of communication is within your team. Get your peers to review your requirements, your deployment plan, your tests, your results, and so on. Not only can they find your mistakes, or give you ideas to improve, but it will also give them the knowledge they need to quickly hop in and help if something goes wrong - even in your absence.
It also makes your job more fun being reminded that people actually care what you did and how you did it.
Bottom Line
Though it's not always easy for customer and managers to identify the difference between quality work and non-quality work, it's real, and it's big.
The difference between professionals who love their job, do it well, and get better and better every year while their systems become more and more stable, is that they're more interested in doing their job completely than in doing it fast.
They work to understanding the technology thoroughly, do the job properly to leave a system better off every time they touch it, document everything they do thoroughly, test it completely, and communicate regularly with users, peers, vendors and other technology groups.
A lot of this may indeed sound like common sense for someone who's already looking at a site like this, but if someone forwarded you this link perhaps they're trying to tell you something. In the long run you'll enjoy your work a lot more and get further ahead if you understand the difference between doing a job completely and doing it fast.
Doing it Fast
Sometimes you can make a case for doing something fast. If you're dealing with something you're only going to do once, in a problem space you're either already deeply familiar with or couldn't care less about, and have a number of other competing priorities for your time, that's usually when a case for doing something fast can be made.
Doing something fast usually means trying a few quick ideas, either from your own toolbelt or something you saw on this site or somewhere else, and hacking away until it works, whether you understand what you're doing or not.
It's certainly the most time-efficient way of doing your job, at least in the short-term, and at some risk. Unfortunately there are some long-term consequences to this approach.
- Your solution may have introduced a problem somewhere else
- The problem could potentially be a more severe one that blows up in your face
- If you broke something there's no record of what you did exactly
- If the problem re-appears at some other place or time you're no better equipped to deal with it.
- As a corollary, you're no better at your job tomorrow than you were today
- Furthermore the system is no faster and no more stable tomorrow than it was today
- Nobody else knows what you did, how to maintain it, nor how to fix it should it break
- You're at risk of developing a reputation for shoddy work with your client, boss or peers
- At the very least, you certainly didn't do anything to develop a good reputation
- You probably didn't enjoy it
Doing it Completely
What exactly does it mean to do a job completely? Even when someone knows what they're doing and does an excellent job, the failure to properly document it, test it, communicate it and cross-train the team generally eliminates most of the advantages of doing the job in the first place. Given that completing the task is generally harder and more time-consuming than those other tasks, it's tragic how someone is doing 80% of the work for 20% of the benefit.
A frequent mantra we've always shared among us is "the job is never done unless it's tested and documented." There's no better way to describe the fundamental difference between doing a job fast and doing it completely. For instance, we don't allow a customer ticket to be closed, nor a project task to be marked off until it has been appropriately tested and the work has been appropriately recorded in the appropriate place.
It may sound like common sense, especially for someone who is sufficiently dedicated to their profession to read sites like this, but it's alarming how many we come across who simply don't properly research the technology with which they're working, document what they're doing, fully test their solution (with the users), all the while communication their work with those involved.
Consequently these people generally can't do their jobs any better in year five than they could in year two, support systems no easier to maintain in year five than in year two, spend a lot of their time fighting avoidable fires (though some of them actually like this), generally dislike their jobs, but don't have the reputation or knowledge to get other opportunities.
Doing a job completely involves five primary differences to doing a job fast: investing the time to understand the technology, taking the steps to completely the job properly, documenting the work, testing it thoroughly, and communicating with others.
1. Understand the Technology
Doing a job completely means looking at every task as an opportunity to improve your knowledge in the relevant technology. For example, coming to a site like this is an excellent way to get a deeper understanding not only of how to solve a problem, but how the particular component in question works.
Get a few books, talk to a few experts, work with the technology yourself (in a private development environment, initially) and develop a thorough understanding. You originally paid thousands of dollars for your education, here's your chance to get it on the job for free (or sometimes even get paid for it).
You chose this profession for a reason - you love it. You should really love the deeper understanding of the technology this investment in time and effort will give you. Did you really get into this field to hack blindly at quick fixes? To apply other people's solutions that you don't even understand?
A thorough understanding will also help you do your job faster and better in the future, finding even better solutions to even tougher challenges, all the while reducing the chances of making mistakes.
2. Do the Job Properly
Every time you do a job the goal has to be to leave the system more robust, maintainable, faster and more error-free than when you found it, and by the largest degree possible.
For example, when writing code, including good exception-handling, inline documentation, and instrumentation (debug messages). Write your code so that it is reusable in other systems. Furthermore, load the code into source control, and put good comments when you check it in. Conduct a code review with your team when you're done. There are any number of ways to improve the code, these are just suggestions (and here are a few more tricks to improve your PL/SQL code).
Speaking of suggestions, when tracking down a performance issue, set up automated performance test procedures. Evaluate performance-testing software and get one set up in your development environment, which pre-loaded tests that can run at the touch of a button. Work with other teams or customers that support similar technologies and create a shared standard.
When designing or deploying a system, think about security and scalability, not just the checklist of functionality requirements. Make sure your system is appropriately designed and sized for future growth, and maintenance - build a development, testing and production environment.
When troubleshooting, doing the job properly means getting to the root cause of the problem, not just the quick fix. There are even ways to add value when writing SQL queries. These are the types of value-added activities that make everything you do in your career progressively easier, and your systems progressively more robust and maintainable.
There are lots of possibilities (here are a few more). These are the various differences between doing a job properly and just doing a job fast. It's the difference between professionals who love their job and do it well, and those who just do their time and wonder why they don't get anywhere.
At least have the conversation with yourself. Have you found the root cause, is there something you can do to make future development easier, are there other performance issues you can track down while you're in there, or ways to help find them in the future?
3. Document
Part of doing a job properly is documenting your work. Document the requirements before you begin, document your designs, deployment plans, tests, how to maintain it - whatever is appropriate.
They secret to writing good documentation is pretending you're someone else, and you're reading this in the future. Maybe you're someone maintaining the work, or someone doing something similar somewhere else, or maybe it broke and you're someone fixing it.
Anticipate what this individual is looking for. If they're doing something similar, what are the exact steps needed to do it from scratch, including explanations behind each step? If something broke, what do you think it would be, what tests would prove it, and how would they fix it?
Be as thorough as that future individual (which might be you!) would need you to be, including the requirements of the job, names and dates, the tests and their results.
4. Test
If the three rules of real estate are location, location, location then the three rules of our profession is test, test, test.
Years ago we termed the phrase deprayment to refer to any deployment that wasn't tested. Rather than deploy-and-test, we had one individual on our team that would unfortunately deploy-and-pray (thus "deprayment"). Don't do this, because it generally hurts others more than it does you, especially since there are many effective ways to test practically anything.
Test it yourself, have someone on your team test, have the vendor test it, have those who support systems that touch yours test it, and have the user test it. Everyone is testing it from their own perspective.
Record your tests, include steps, results, dates, and names. If something breaks later, even something unrelated to what you're doing, knowing what tests yielded which results on which day can be very useful troubleshooting information indeed.
It also shows a tremendous amount of respect for your system, the people that use it, and those systems that depend on it, or upon which it depends. It reinforces the message that your system is important, and will generally result in high level of cooperation with all of them. A level of commitment that will help you do your job more successfully, and at much lower risk since so many people have knowledge about what's going on and who's involved.
Testing is also a great way to deal with any issues that come up. Despite your best efforts sometimes things happen, but if you have evidence that your solution was properly tested it will not only help you fix this new problem but also demonstrate to your customers and managers that it wasn't likely caused by incompetence or neglect.
That may sound harsh, but how else do customers and managers know if you're doing your job properly or not? Good documentation, testing and above all the communication it entails is the best way for them to know that there's a difference between you and a non-professional.
5. Communicate
I've very rarely ran into a situation where someone was accused of over-communication. Miscommunication, sure, but not over-communication. If in doubt simply ask those with whom you have relationships what level of communication they would like (and document it).
One important type of communication is within your team. Get your peers to review your requirements, your deployment plan, your tests, your results, and so on. Not only can they find your mistakes, or give you ideas to improve, but it will also give them the knowledge they need to quickly hop in and help if something goes wrong - even in your absence.
It also makes your job more fun being reminded that people actually care what you did and how you did it.
Bottom Line
Though it's not always easy for customer and managers to identify the difference between quality work and non-quality work, it's real, and it's big.
The difference between professionals who love their job, do it well, and get better and better every year while their systems become more and more stable, is that they're more interested in doing their job completely than in doing it fast.
They work to understanding the technology thoroughly, do the job properly to leave a system better off every time they touch it, document everything they do thoroughly, test it completely, and communicate regularly with users, peers, vendors and other technology groups.
A lot of this may indeed sound like common sense for someone who's already looking at a site like this, but if someone forwarded you this link perhaps they're trying to tell you something. In the long run you'll enjoy your work a lot more and get further ahead if you understand the difference between doing a job completely and doing it fast.
Thursday, March 15, 2012
Book Giveaway Update
There are still a few books available - anything unclaimed by the end of next week will be recycled. Pay only for shipping (and optional tip).
Saturday, March 03, 2012
Oracle Book Giveaway
I've got seven Oracle books available for giveaway. Any unclaimed books will get recycled (assuming the library doesn't want them).
If you'd like one or more, just send an email to my gmail account (my account id is just my last name, Vollman). Since I don't want to be out of pocket, I'll ask that you arrange to pay for the shipping, please.
In the unlikely event that there is high demand for one of these books, it'll simply go to whoever made the best offer. I'm not looking to make money, that just seems like the fairest way to get the book into the hands of whoever would enjoy it most, and I doubt offers will be very high at all. I spent hundreds on just these books (not to mention my others), and would probably just spend whatever money I do get on buying more.
Here's a brief write-up on each one.
Oracle 9i Performance Tuning, Tip and Techniques
Richard J. Niemiec - McGraw Hill Oracle Press
This book is in perfect shape except my name on the 2nd page. Obviously there are very few Oracle 9i installations out there, but there is nevertheless a wealth of knowledge that is still valid in the latest versions of Oracle.
Optimizing Oracle Performance
Cary Millsap, O'Reilly Press
Cary Millsap is, of course, a legend in this field. I reviewed this book here. This book is also in great shape save for my name on the 2nd page. Most of this book is still valid today.
Oracle Insights, Tales of the Oak Table
Several authors, OakTable Press
A timeless masterpiece that I reviewed here. Authors include Mogens Norgaard, Jonathan Lewis, Connor McDonald, Cary Millsap and many others. A highly enjoyable read, in great shape except (once again) my name on the 2nd page.
An Introduction to Database Systems (Sixth Edition)
Chris Date, Addison Wesley
Chris Date, the undisputed master of relational databases. An absolutely indispensable book for understanding both the theory and practice of relational databases. Though it was my textbook back in University, it's a hard cover and still in fantastic shape.
Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning
Richmond Shee (et al), Oracle Press
This was written for Oracle 10g, but I'd say the majority still applies today. It's in great shape, excepting for my name on the inside cover.
Oracle 9i PL/SQL: A Developer's Guide
Bulusu Lakshman, Apress Expert's Voice
I got this in a bargain bin for $10. PL/SQL hasn't change a great deal since Oracle 9, so it'll serve as a useful reference and great learning material.
Oracle Database 10g PL/SQL Programming
Scott Urman, McGraw-Hill Oracle Press
Other than Feuerstein's, this is my favourite PL/SQL Programming book. A fantastic reference, lots of code, great examples for both beginners and the advanced, and virtually everything still applies today. Highly recommended.
That's it!
Spread the word, and send the email if you'd like any of these books. I don't want to waste these books if there's someone out there who wants them.
Don't feel you need to offer me any money (other than shipping costs) but if you do, I won't accept anything higher than whatever the 2nd highest offer was.
Tuesday, September 07, 2010
Using Oracle SQL Developer with MS SQL
Having chosen Oracle SQL Developer as your preferred Oracle database tool, do you have to install and learn a new technology for supporting your MS SQL databases? Nope! It's easy to connect SQL Developer to MS SQL databases, and I'll show you how.
Background
For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases they'd only give me command-line SQLPlus access. Consequently I never really chose a preferred Oracle client, nor became particularly proficient with anything other than command-line SQLPlus.
Eventually I found myself working directly with a client, and could finally choose whichever tool I liked best, and like many of you I chose Raptor - now known as Oracle SQL Developer. Explaining my choice is outside the scope of this article, but suffice it to say that it's a powerful time-saver at best, and a big step up on command-line SQLPLus at worst.
Connecting to MS SQL
Not all the database applications we support are going to be Oracle-based, and the most popular of the alternatives is Sybase's curious nephew MS-SQL. Fortunately there's no need to find another application for your MS-SQL instances, you can still use SQL Developer, and here's how.
Step 1. Get the JDBC driver for MS SQL
If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember where.
Step 2. Load the JDBC driver
As explained in Oracle's tutorial, under Preferences, scroll down to Database and Third Party JDBC Drivers. Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection (You get Oracle and Access by default).
Step 3. Set up your new database connection
Now you can create a new MS SQL database connection the same you would an Oracle connection. The only difference is that you need to select the SQLServer tab instead of Oracle. If you don't see that tab, then something went wrong - either you got the wrong jar file or it didn't load correctly.
If you're brand new to Oracle SQL Developer, Anders Andreasen has an example with a few extra screenshots.
Use the Test button to make sure you can connect, and if you can't the error message ought to be descriptive enough to give you a starting point to troubleshoot.
Troubleshooting
Needless to say, technologies as complex as these will occasionally present you with unexpected difficulties.
For instance, you may find that you need to copy the ntlmauth.dll file from the SSO directory to SQL Developer's jdk/jre/bin directory.
Or, you may need to check the Configuration Manager on your SQL Server database to make sure you have TCP/IP communication enabled, and over default port 1433.
If you used the driver I recommended, they have a FAQ that can help you track down some types of errors.
Recap
If you support multiple databases, there may not be a need to install multiple tools. Oracle SQL Developer can be used to connect to different flavours of databases, including MS SQL. Just download and load in the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.
Background
For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases they'd only give me command-line SQLPlus access. Consequently I never really chose a preferred Oracle client, nor became particularly proficient with anything other than command-line SQLPlus.
Eventually I found myself working directly with a client, and could finally choose whichever tool I liked best, and like many of you I chose Raptor - now known as Oracle SQL Developer. Explaining my choice is outside the scope of this article, but suffice it to say that it's a powerful time-saver at best, and a big step up on command-line SQLPLus at worst.
Connecting to MS SQL
Not all the database applications we support are going to be Oracle-based, and the most popular of the alternatives is Sybase's curious nephew MS-SQL. Fortunately there's no need to find another application for your MS-SQL instances, you can still use SQL Developer, and here's how.
Step 1. Get the JDBC driver for MS SQL
If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember where.
Step 2. Load the JDBC driver
As explained in Oracle's tutorial, under Preferences, scroll down to Database and Third Party JDBC Drivers. Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection (You get Oracle and Access by default).
Step 3. Set up your new database connection
Now you can create a new MS SQL database connection the same you would an Oracle connection. The only difference is that you need to select the SQLServer tab instead of Oracle. If you don't see that tab, then something went wrong - either you got the wrong jar file or it didn't load correctly.
If you're brand new to Oracle SQL Developer, Anders Andreasen has an example with a few extra screenshots.
Use the Test button to make sure you can connect, and if you can't the error message ought to be descriptive enough to give you a starting point to troubleshoot.
Troubleshooting
Needless to say, technologies as complex as these will occasionally present you with unexpected difficulties.
For instance, you may find that you need to copy the ntlmauth.dll file from the SSO directory to SQL Developer's jdk/jre/bin directory.
Or, you may need to check the Configuration Manager on your SQL Server database to make sure you have TCP/IP communication enabled, and over default port 1433.
If you used the driver I recommended, they have a FAQ that can help you track down some types of errors.
Recap
If you support multiple databases, there may not be a need to install multiple tools. Oracle SQL Developer can be used to connect to different flavours of databases, including MS SQL. Just download and load in the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.
Wednesday, August 11, 2010
FIRST_ROWS vs ALL_ROWS
A colleague asked me some questions about FIRST_ROWS and ALL_ROWS, but I'm hesitant to blog about it because it's already been done so well by others -- the best example would probably be Sachin Arora.
Nevertheless, it never hurts to lend another voice to the Oracle choir, so here's everything I know on the topic.
FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can set it in your init.ora file, or you can set it on a session-by-session basis (using alter session). You can also set it on a query-by-query basis by using hints (although such hints may be ignored in certain cases, like where all rows must be accessed, as in a GROUP BY).
Your basic choices for OPTIMIZER_MODE include CHOOSE by default, RULE, ALL_ROWS, FIRST_ROWS and its variant FIRST_ROWS_N (where N is 1, 10, 100 or 1000). So far I'm not telling you anything that wouldn't be obvious with a glance at the documentation, but let me explain a little bit about the latter two.
ALL_ROWS and FIRST_ROWS have a lot in common. They're the only two settings that force the use of the CBO (Cost-Based Optimizer), and therefore they're collectively referred to as cost-based, since the concept of cost is directly related to the CBO.
If your statistics are up to date -- which they should be!! -- then the CHOOSE option will generally use the CBO as well. Possibly always, but I'd rather not use the word "always" unless I were certain. If you turn tracing on, you can see the optimizer setting that was used, and if you see "Cost=" then you know the CBO came into play.
The most important thing to keep in mind when using CHOOSE or a cost-based optimizer setting is that your statistics are up to date, otherwise it might default to RULE (if it's set to the default value of CHOOSE), or will use default statistics.
"The CBO without stats is like a morning without coffee. A really bad idea."
- Tom Kyte (though I'm sure Jon Emmons would agree).
The difference between FIRST_ROWS and ALL_ROWS is exactly what you'd think - the former instructs the CBO to get the first rows as quickly as possible even if the overall query takes longer, whereas the latter simply gets you all the data as quickly as possible.
As you would probably guess, FIRST_ROWS will likely be better in an interactive client/server situation where there is a benefit to getting some of the results before the rest. FIRST_ROWS is also found to use index scans instead of full table scans more often than otherwise - but for more detail on its peculiarities, check Sachin's article or search the Oracle forums.
As far as I know, the only real difference between ALL_ROWS and CHOOSE is that ALL_ROWS will use the CBO regardless of whether you have up-to-date statistics. Basically ALL_ROWS doesn't make as much sense to me, because if you care about performance to the point where you're modifying optimizer settings, then you probably care enough to have up-to-date statistics, in which case you'd leave it on the default value of CHOOSE.
That's about all I can really say about these two settings. If you've read this far and you'd like to know more, there are two obvious things you can do:
- You can Ask Tom, and/or
- You can read the Oracle Documentation
Each of those sources has pretty much everything I've said, plus more. If you've got more to ask or to offer on this topic, I invite you to leave a comment.
Nevertheless, it never hurts to lend another voice to the Oracle choir, so here's everything I know on the topic.
FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can set it in your init.ora file, or you can set it on a session-by-session basis (using alter session). You can also set it on a query-by-query basis by using hints (although such hints may be ignored in certain cases, like where all rows must be accessed, as in a GROUP BY).
Your basic choices for OPTIMIZER_MODE include CHOOSE by default, RULE, ALL_ROWS, FIRST_ROWS and its variant FIRST_ROWS_N (where N is 1, 10, 100 or 1000). So far I'm not telling you anything that wouldn't be obvious with a glance at the documentation, but let me explain a little bit about the latter two.
ALL_ROWS and FIRST_ROWS have a lot in common. They're the only two settings that force the use of the CBO (Cost-Based Optimizer), and therefore they're collectively referred to as cost-based, since the concept of cost is directly related to the CBO.
If your statistics are up to date -- which they should be!! -- then the CHOOSE option will generally use the CBO as well. Possibly always, but I'd rather not use the word "always" unless I were certain. If you turn tracing on, you can see the optimizer setting that was used, and if you see "Cost=" then you know the CBO came into play.
The most important thing to keep in mind when using CHOOSE or a cost-based optimizer setting is that your statistics are up to date, otherwise it might default to RULE (if it's set to the default value of CHOOSE), or will use default statistics.
"The CBO without stats is like a morning without coffee. A really bad idea."
- Tom Kyte (though I'm sure Jon Emmons would agree).
The difference between FIRST_ROWS and ALL_ROWS is exactly what you'd think - the former instructs the CBO to get the first rows as quickly as possible even if the overall query takes longer, whereas the latter simply gets you all the data as quickly as possible.
As you would probably guess, FIRST_ROWS will likely be better in an interactive client/server situation where there is a benefit to getting some of the results before the rest. FIRST_ROWS is also found to use index scans instead of full table scans more often than otherwise - but for more detail on its peculiarities, check Sachin's article or search the Oracle forums.
As far as I know, the only real difference between ALL_ROWS and CHOOSE is that ALL_ROWS will use the CBO regardless of whether you have up-to-date statistics. Basically ALL_ROWS doesn't make as much sense to me, because if you care about performance to the point where you're modifying optimizer settings, then you probably care enough to have up-to-date statistics, in which case you'd leave it on the default value of CHOOSE.
That's about all I can really say about these two settings. If you've read this far and you'd like to know more, there are two obvious things you can do:
- You can Ask Tom, and/or
- You can read the Oracle Documentation
Each of those sources has pretty much everything I've said, plus more. If you've got more to ask or to offer on this topic, I invite you to leave a comment.
