The Why and How to Avoid of Full Table Scans!!!

[engineer2100]

Hi,

I am right now involved in a a lot of Code review actiivities. As a part of this i am also looking into the SQL queries to see if any of these are badly written.

The one common thing that I come across is that the queries do Full Table Scans.

i would like to understand the situations under which the optimizer chooses to do a FTS even when the query uses Indexed columns.

Also how to resolve thee FTSs is another concern I would like some to help me with.

Thanks
Engi

 

[SantaMufasa]

Engi,

The short answer as to why the Oracle optimiser chooses Full Table Scans (FTS) is either:

1) It thinks that a FTS is more economical than an alternative (e.g., it estimates that it will take "longer" to obtain the rows by reading INDEX blocks plus the TABLE blocks than simply reading all of the TABLE blocks.)

2) It must do a FTS because there is a WHERE clause that cannot be resolved with information in any INDEX (or non-FTS alternative). This occurs (typically) when the WHERE clause includes some Oracle function (e.g., SUBSTR(), INSTR(), ADD_MONTHS(), MOD(), et cetera) [Note: Newer versions of Oracle accommodate indexes that contain FUNCTIONs, thus reducing the susceptibility to this issue.]

What I have disclosed here is simply a snowflake on the tip of the "Oracle tuning iceberg". There is so much more to proper tuning that what I have mentioned, above, so I highly recommend your obtaining an Oracle Tuning text/reference at the very minimum. Amazon.com has many titles; one that I like in particular is SQL Tuning, by Dan Tow (and it's not just because my name appears in the Acknowledgements on page xix. <grin>)

Let us know if you have additional specific tuning questions...but we aren't able to post extensive information on Oracle Tuning...That's best done by buying the book.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

You are my rescue again Santa... I am glad.

i am facing a rather complicated (at least to me)issue here. I have three different Databases at my disposal. Now when do explain plan of a query in these three database, the results that i get is different. I get FTS in one and the other two work their way out using Indexes.

Why is this change in behaviour? I heard that CBO provides the same result irrespective of database the query is run on! Am I missing something here?

Thanks
Prashanth Shankhawaram

 

[engineer2100]

Prashanth,

Please post it under my name!! People get confused you see.

Thanks
Engi

 

[jimbopalmer]

One time you want a FTS is any table where all the data fits in one disk fetch. So if the table is small, the optimizer can read it all more cheaply than sorting through it. (this defeats many 'development' databases, they do not really look like production)

Another time FTS is better than indexes is if more than 1/4 of the rows are going to be selected. It is cheaper to read them all sequentially than sort through them and read over 1/4 of them randomly.

I tried to remain child-like, all I acheived was childish.

 

[carp]

As you have found from experience, CBO will NOT generate the same execution plan on different databases. There are many factors involved, but the one that is most likely to apply here is a difference in statistics between the databases. CBO uses the statistics on the table to decide on an execution plan. If the statistics are significantly different between databases, then CBO is liable to select a different access method. For instance, if the statistics on one database indicate the table has only 50 rows, it may choose an FTS. If the same table's statistics indicate 50,000,000 rows on a different database, then CBO may look more closely at using indexes.

 

[engineer2100]

Hi Carp,

This is the problem. the DB's are same in all aspects; I mean we create the DB's using the same scripts. Load the same set of tables.

One is the Dev DB
Second and Third ones are Integration DB's.

It is mandatory that we keep all the three in sync. Thats what prompted me to say that "CBO would produce same result.."

Thanks
-Engi

 

[SantaMufasa]

Yes, Engi, but clearly, Carp's concern is a key issue: Are you, in fact, explicitly re-gathering statistics on all objects on all databases? I'll bet that the statistics on one or more of your otherwise-"identical" databases have not been gathered recently.

Ensure that you re-gather the statistics on all three databases (using the same ANALYZE parameters), and I'll bet that your performance (and execution plans) become identical.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

We had a situation here about two weeks ago. We have three databases, and the DBAs told me they were "identical". Yet one was performing far worse than the others. I did some EXPLAIN PLANS on the one that was dragging, and the database returned the execution plan along with a notation that I might have better performance if I didn't use RBO. I asked the DBA why the database was set to RBO; she told me that it was "impossible to use RBO on 10g; it can't be done!". I checked the optimizer settings on all three: one was set to ALL_ROWS, one was at CHOOSE, and one was RULE. The morals of the story:
1. Don't believe everything Oracle tells you. The DBA went to a class and the instructor told them that RBO was finally dead. Apparently, nobody told our database!
2. Don't always believe what the DBA tells you about the database. Trust but verify - especially if you have more than one DBA!
3. When you reach an impasse, recheck your assumptions.

How do you keep them all in synch - triggers and DB links? If I load 1,000 rows in one DB, how long before they show up in the other two?