I’ve overheard the SQL Server folks talking about the challenges of support applications where the NOLOCK (or is it NO_LOCK?) hint has been deployed to make transactions and queries go faster.
This probably causes more problems than it solves, and rather trying to rehash what I’ve heard second hand, I suggest you go listen to a MSFT MCM talk about it. Brent Ozar PLF co-founder Kenda Little does an excellent overview on it here.
That got me thinking, what’s the equivalent ‘abused and misused hint’ in the Oracle world? The first thing that came to mind was /*Parallel*/ (Oracle Docs). The documentation is fairly straightforward, with the first line saying
The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation.
So what does a developer think when they read this?
Wait a second here. My query is taking X minutes now. If I use a parallel degree Y hint, I can diving X by Y! The math here being, well if takes one process X minutes, then surely Y processes working together in concert can divide up the work and complete the task much, much faster.
Unfortunately, this is not always the case. That’s really not how it works at all to be honest. Application architects and DBAs take great care to determine what tables should be divided and conquered with parallelism. You’ll see this when a table is created with the PARALLEL option. This means you can write your queries and let the system best decide when to split the work up to multiple worker processes. One could argue then that a business user may NEVER want to use the parallel hint in their ad-hoc queries.
Parallel Can Be Bad
I’ll quote one Oracle master quoting another – in this case Tom Kyte quoting Jonathon Lewis (AskTom)
You might want to get Jonathan Lewis’s book “practical Oracle8i — building efficient databases”. I think he put it beautifully when he described parallel query as a “non scalable operation”. It does not scale up with users (more users, less scalability). It gives you the ability to consume the ENTIRE machine with your one query. It is designed to use EVERYTHING, it is very selfish.
Parallel Can Be Awesome
Greg Rahn (Twitter), a data performance geek for Oracle Corp, gives us an excellent scenario where parallel degree execution makes a lot of sense – data warehouses. (blog). One of my favorite lines, Greg confirms what many of us already know “PX is not, however, “go fast” magic pixi dust for any old operation.” With that out of the way, he goes on to assure folks that we shouldn’t be afraid to use it either though. Greg advocates using this when you’re working with really large datasets in a data warehouse, especially on SELECTs and Create Table As Select (CTAS).
As a matter of fact, Oracle gets very explicit on using parallelism when running CTAS statements.
If you think this is a lame post because I’m just quoting others and sharing web links, then I can’t argue with that. I’ve heard people talk about parallel being a pain to support when folks don’t understand it, but I’ve never had to be that guy supporting it. If you’re a Oracle developer though, please don’t add hints to your query without fully investigating what that hint does. Just reading the first page of the Oracle Docs might get you in trouble.
What Hint Do You See Over or Misused?
/*+ FIRST_ROWS*/ anyone? Or maybe /*+ INDEX*/? That’s right, sometimes Oracle doesn’t want to use an index, and it doesn’t make sense. Also, there are things more important than getting your data base as quickly as possible.
And I know that some of you will chime in that ANY hint is a bad hint. I am happy to have hints available when the occasion calls for one, but I don’t try to solve every problem with a hint either.
