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.

Wednesday, August 04, 2010

One Million

Today, August 4th, shortly after lunch, ThinkOracle had it's one millionth visitor. Care for a stroll down memory lane?

I started this site May 16, 2005, shortly after starting a new position with a company that made financial software. The idea was to make my own contribution to the growing Oracle community, expand my knowledge, improve my technical writing, and it never hurts to establish a good reputation.

I actually started a sister site for Sybase that same day, wrote for about a year (consistently at first, then sporadically), and it's not even at 42,000 hits. There just isn't as big a community for Sybase as there is for Oracle.

As for the Oracle site, I posted regularly for over two years, when I changed jobs again. Unfortunately my new position didn't involve nearly as much Oracle work, so my contributions started to fade off.

Surprisingly, the number of visitors I was getting didn't fade off, and Google dutifully sent a number of Oracle professionals to my archives. It was always a thrill when someone in the office would swing by my desk and tell me a story about how they were Googling a solution to a problem, and came across my site.

Unfortunately, being a good blogger is a big investment of time. It's not just the writing, you have to keep your knowledge up to date, follow other blogs, participate in forums and other user groups - it's a lot of work, especially if it's not a central part of what you do for a living. I wish I had the time to post more than a few times a year, but I don't.

A lot of my favourite fellow bloggers have come and go in this time, although I'm amazed at those that are still going strong, like Tom, Eddie, Doug, Pete, Mark, Laurent and Tim, to name just a few. I remember all the great bloggers that were there when I started and have since faded just like I have, especially David, Niall and Howard. I also see a brand new generation of Oracle bloggers, and as lucky as we were back then, I think everyone is luckier today.

Thinking back, there have been a lot of highlights over the years (all of which you can find in my archives):

1. Tom Kyte following my blog and commenting regularly to help me get going. This site might not be here without him, and oh what a spike in traffic when he first linked to it.

2. Oracle started including my blog in their aggregator, August, 2005.

3. My first milestone: 60 posts and 10,000 visitors by November, 2005.

4. Computerworld recognising ThinkOracle as one of the best IT blogs, December 2005.

5. Oracle publishing one of my articles on their site, February 2006.

6. Meeting Tom Kyte, June 2006.

7. My next milestone: 107 posts and 100,000 visitors November, 2006.

8. Almost winning Oracle Blogger of the year in 2006 (Damn you Eddie!)

9. Being asked to be Technical Editor on Alice Rischert's SQL book, February-March 2009.

10. One million!

Even before I was a blogger, I would visit Oracle discussion forums, and marvel at how people like Laurent would tirelessly assist anyone in need of Oracle expertise. Once I started blogging, I understood how addictive and contagious it can be.

I have very fond memories of my time as a regular blogger. I'm still here, I haven't given up, and you can definitely expect as much new content as I can muster.

And if you're a fan of my writing and you like hockey, keep your eyes peeled for the 2010-11 Puck Prospectus Annual, which is coming out in September and on which I'm an author. You can also check out my stuff on ESPN.com. If comedy is more your style, my personal blog, which pre-dates this one, is still going strong.

Monday, August 02, 2010

Changing User's Default Schema

Last week I got a question about changing a user's default schema.

My colleague is supporting a typical database application which is configured to use the user/schema that was created for its database. Many queries were written for this application that use that schema owner, but my colleague would like to run those queries with his own account instead - either because he doesn't want to log in as schema owner, or can't.

To make the queries work for his account, he'd have to go through every one and add the schema name. That's tedious! What he'd like to do is change the default schema for his user account. What are his options?

Best Practices

First of all, I'm glad he's not simply using the schema owner account. The schema owner has virtually unlimited privileges, and the more people or applications that use that account, the greater the risk of accidentally making a big mistake. In fact, I prefer even configuring the applications to use their own account, rather than the schema owner.

Secondly, this is hardly a new question, in fact it was probably explained best by Tim Hall at Oracle Base years ago:

"Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far (too) many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects."

Alter Session

When a user connects, it connects to its own schema, the one that was created when the user was created. It can certainly change to another, using alter session. Dan Morgan has a reference on altering sessions, or you check your Oracle documentation.

You can do this in at least three places:
1. When you first connect, either manually or as the first line of your SQL script.
2. As a log-in trigger (as Tim demonstrates)
3. In your glogin.sql file, which executes when you first log in.

Use Views

If we're not talking about a lot of tables, another option is to create views in your own schema of the same name as the tables. As I've written before, this is just one of the many useful purposes of views.

You can restrict access, and also include only those columns in the view as you need. The only trick is that you may need to update those views if the underlying tables ever change.

Synonyms

And again, if you're only selecting from a few tables, your other option is to use synonyms, which in a sense dictates which schema the statements are working against.

You'll have to do this on a table-by-table basis, and the big drawback is that it could confuse things if you deal with other schemas with the same table names. Oracle will use a local object first, then look for any private synonyms before using the public one. Here's Dan Morgan's reference on synonyms.

My Recommendation

As for me, my favourite option is to alter session at the top of my script. All these other options are things that are hidden from the user, and I've always disliked it when my database is set up to do things in the background of which I might not be aware. If I select from a table I don't have, I'd like to know that, rather than have it silently grab it from somewhere else instead.

This is an old problem, so there may be other solutions, too. If you've got one, please leave them in my comments, along with any opinions you have on these approaches.

Also, one other special request. Please scroll down to my stat counter, and if you're my one millionth reader (or very close to it), please leave me a comment! Thanks.

This page is powered by Blogger. Isn't yours?