Skip to Main Content
  • Questions
  • Is it possible to avoide full table scan at all.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vinod.

Asked: March 28, 2005 - 11:13 pm UTC

Last updated: April 01, 2005 - 8:58 am UTC

Version: 9.0.1.3.0

Viewed 1000+ times

You Asked

Hi Tom,

When we write a SQL query to join many tables ( lets say 7-8 tables ) used with joins and group by clause, i have seen that full tables scan takes place in atleast one,two tables depending on the nature of query.
Is it possible to avoid full table scan at all ? I have also seen that even after creating indexes on proper column sometimes oracle doesn't hits the indexes and as a result full table scan takes place. What could be possible reasons for that ?

Thanking you in anticipation
Regards,
Vinod Shettigar.

and Tom said...

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>

1) because full scans are NOT EVIL
2) and indexes are NOT ALL GOODNESS
3) goto 1 until you believe it




Rating

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

*

Invisible, March 29, 2005 - 10:57 am UTC

And Tom Rules the world! >:-D

Seriously... maybe AskTom should have a VFAQ section? Or just a very frequent answers section:

* Full scans can be FASTER than indexes. (Sometimes.)
* The optimiser is better at deciding than you are. [If you give it enough data!]
* Joins aren't as slow as you might think.
* The database can do joins faster than you can.
* The database can find things faster than you can.
* Adding COMMIT statements will NOT make you database go faster.
* Adding COMMIT statements WILL mess up your data, eventually.
* Not backing up your rollabck segments will NOT save time. ;-)

[Any others you feel like adding?]

* mode=fast is not a valid init.ora parameter. ;-)


Tom Kyte
March 29, 2005 - 11:13 am UTC

Thanks, but I do not rule the world. I make as many mistakes as anyone, I just try to leave enough evidence behind to be caught :)

You just described Effective Oracle by Design ......

*****

A reader, March 29, 2005 - 11:23 am UTC


Full table scan

Vinod Shettigar, March 29, 2005 - 11:26 pm UTC

Tom

you are really great. You changed my way of thinking. I always use to think that full table scan always leads to slow retrival of data & Indexes are always helpful.

Thanks a million.
Regards,
Vinod



Ok then ;-)

Invisible, March 30, 2005 - 4:23 am UTC

Man, if I ever earn enough money, I'm probably going to buy one of your books...

IMO, Oracle is a very cool bit of software. My only real problem with it is the rather clunky interface - it's rather hard to know what's going on inside unless you know the exact v$ view to query and how to interpret the (typically cryptic) results. And creating a new database is really difficult.

[On the other hand, I suppose something as important as a database should only be operated by experts. And creating a database isn't something you need to do that often. Still, for testing things out, would be nice if it weren't so hard to build a test setup...]

I have an Oracle book at work which explains most things. Even then, there are a number of confusing aspects. [e.g., password files.] I think it would sure be interesting to read one of your books... (if I ever have the cash!)

Here's another things that's surely an FAQ... You've written several different books about Oracle. Are the later ones just newer than the earlier ones, or are they aimed at covering different subject areas?

Keep up the good work. ;-)


Tom Kyte
March 30, 2005 - 7:26 am UTC

$ dbca

that is really difficult?


I've only written two fully and 1 in partnership.

o Beginning Oracle Programming -- a collaboration
o Expert one on one Oracle (to be renamed Expert Oracle 10g Edition)
o Effective Oracle by Design

Expert One on One was 1/3 about "how oracle works", 1/3 about how "specific things works (anayltics, partitioning, etc), 1/3 about how "specific built in packages work"....

Effective Oracle by Design is best practices Oriented.

Well

Invisible, March 30, 2005 - 7:52 am UTC

Call me dim (no, go on - most people call me worse!), but what does "$ dbca" do? (".)

Anyway, will definitely look out for your work next time I'm on Amazon. :-) [I probably don't NEED it, but I'm always interested to learn more about Oracle.]

Tom Kyte
March 30, 2005 - 9:02 am UTC

dbca the database configuration assistant

$ dbca

in Linux/Unix fires it off. Guess in windows it would be

Start -> something -> something -> something -> Database Configuration Assistant

dave, March 30, 2005 - 7:56 am UTC

dbca is the program name to create / configure options in / delete a database via a gui

$ is the shell prompt in unix / linux


Keep up the good work! but, for how long?!

Rajesh, March 30, 2005 - 7:59 am UTC

Tom!
Since one reader commented "Keep up the good work", this question popped up on my mind.

How long do you think, you can continue running this one man show? Do you not feel, this is becoming overhead day by day? (Answering the same set of questions, following up the reviews, Keeping your cool when some stupid questions are asked).

Do you think, you justify the role of being a vice president of a company of this size/reach? I mean, if you stop answeing questions, you can save a lot of time, which can be spent for "Better Oracle" in coming days!

I honestly believe, there is a breaking point for everything! Do you foresee the breaking point for "AskTom" in near future?!

You have full rights to skip/delete this post. Just came in my mind.

I have no doubts whatsoever that you are doing a GREAT job for the Oracle Community.

Thanks
Rajesh S

I see

Invisible, March 30, 2005 - 10:45 am UTC

I'll have to go look for that. I was aware there's a tool which creates a "default" database which you can't alter the configuration of [until it's been created]. So my process for creating a database is usually something along the lines of

* Open notepad, create an init.ora file with 80-odd settings in it.
* Run ORADIM to create an instance. (I still have no idea WTH all those parameters do!)
* Use svrmgrl to log in to the newly created idle instance and run a 25-line CREATE DATABASE command.
* Run the half-dozen scripts it says to run to initialise all the PLSQL stuff. (Which never seems to work right.)
* Go through the process of creating tablespaces, rollback segments, and more user accounts.
* Do whatever I was going to do with this test database I just created...

Oh well! It's not something I need to do TOO often... Hopefully I don't need to test things much now.

(Actually, I have a question that's kind of related to the default database the installer wants to create... uh... mmm... but I'll ask in another thread. ;-)

...which leads on to...

Tom is one real knowledgable bloke. :-D If only there was more than one of him! :-(


Tom Kyte
March 30, 2005 - 10:47 am UTC

dbca lets you quickly create a "starter" database or a custom one.

should be "next, next, next, next, finish"

Next steps for a experienced DBA

Luis, March 30, 2005 - 3:58 pm UTC

Hello, Tom.

First of all, thank you very much for guiding (even don't knowing me) my way on learning effective Oracle. What and where I am now are a outcome of what people like you, Mogens and Millsap have shared with us, 'human kind' people.

Now that I:
- know everything about restore;
- know everything about restore;
- know everything about restore; (these are the 3 most important thing for DBAs in your opinion, isn't it? :-P )
- know everything about backup;
- read almost all HOTSOS papers;
- focus tuning on LIOs;
- look to OWI before ratios when facing a bottleneck;
- know what is and how to avoid ORA-1555;
- understand the optimizer and statistics;
on what do you suggest me to focus my effort on to learn more about Oracle technology?

Thank you again and best regards,
Luis

Tom Kyte
March 30, 2005 - 5:21 pm UTC

bind variables! and the arguments you need to have under your belt to convince the developers to use them :)

seriously though, describe your "job", what do you do day to day (each dba job is a little different...)

I would make sure I knew the business (not technical perhaps but important) for sure. How can I become more relevant to my company, a contributor -- not just an employee.

Interact with others (any user groups where you are? think about organizing one? build a community)



Tuning

Mark Wooldridge, March 30, 2005 - 11:14 pm UTC

I have seen full table scans in the past when a few of the DB parameteres are not configured correctly for the application and hardware. Specifically the db_file_multiblock_read_count, optimizer_index_caching, optimizer_index_cost_adj parameters. All is described in detail in Effective Oracle by Design chapter 6. There is a query you can run which suggests values for these parameters which looks at the db sequential read and db scattered read values etc.

Query I found on the net. Hopefully Tom can explain more about the queries.

col a1 head "avg. wait time|(db file sequential read)"
col a2 head "avg. wait time|(db file scattered read)"
col a3 head "new setting for|optimizer_index_cost_adj"

select a.average_wait a1,
b.average_wait a2,
round( ((a.average_wait/b.average_wait)*100) ) a3
from v$system_event a,
v$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';



Example

Some results I have obtained from various combinations of hardware platform and IO sub-system.

avg. wait time avg. wait time new setting for
(db file sequential read) (db file scattered read) optimizer_index_cost_adj
------------------------- ------------------------ ------------------------
.171659257 3.33033582 5
.13254 1.12365 12
.017605522 .104148241 17
1.29639067 2.06954043 63
.535133533 .397919802 134
.940889054 .509830001 185
.537904057 .145183814 370



9i releases, use this query instead:

select a.average_wait a1,
b.average_wait a2,
round( ((a.average_wait/b.average_wait)*100) ) a3
from
(select d.kslednam EVENT,
s.kslestim / (10000 * s.ksleswts) AVERAGE_WAIT
from x$kslei s, x$ksled d
where s.ksleswts != 0 and s.indx = d.indx) a,
(select d.kslednam EVENT,
s.kslestim / (10000 * s.ksleswts) AVERAGE_WAIT
from x$kslei s, x$ksled d
where s.ksleswts != 0 and s.indx = d.indx) b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';




Tom Kyte
March 31, 2005 - 7:35 am UTC

I'd ask the author of the query myself.

<quote effective oracle>
If you think of the OPTIMIZER_INDEX_CACHING parameter as being used to tell Oracle the percentage of an index that is cached, you can think of the OPTIMIZER_INDEX_COST_ADJ parameter as telling Oracle how much of the table data will be cached. The lower the number, the less costly single-block table accesses become; conversely, the higher this number, the more costly. A way to think of this would be that this number reflects the cost of performing multiblock I/O (associated with full-table scans, for example) versus the cost of performing single-block I/O (associated with index reads). If you leave this parameter at the default setting of 100, these operations are costed the same. Setting this value to 50 causes the optimizer to consider a single-block table access as half as expensive as multiblock I/O would be, effectively cutting in half the cost of table access (50/100).
</quote>


Looking at the numbers above and the suggested index cost adjust, it seems to be saying

"if you are waiting for multiblock reads (scattered reads) more than single block reads -- make single block reads cheaper by setting this low..

if you are waiting about the same amount of time for each, leave it at 100.

if you are waiting for single block reads (sequential reads) more than multiblock io -- make the cost of single block reads more expensive to sway the optimizer towards plans that do multiblock io."





9i releases - put the magic aside

Vladimir Andreev, March 31, 2005 - 3:30 am UTC

Mark,

I'd say, in "9i releases" and up - let dbms_stats.gather_system_stats do this calculation (and many others) for you.

Cheers,
Flado

About that

Invisible, March 31, 2005 - 4:51 am UTC

Yes - about that "starter database". It creates a couple of tablespaces - USERS, TOOLS, et al. Anyway, we have a couple of DBs for an application we use here. To install the server portion, you create an empty DB and run a couple of scripts which create all the schema objects.

Anyway, these scripts create two new tablespaces, and (AFAIK) put all the schema objects into those. Now, I should emphasise, I wasn't around when the DBs were set up. But it looks to me like they were set up using the automatic tool - they have a tablespace called USERS, one called TOOLS, and so forth.

My question: is there any way to find out what (if anything) is in a certain tablespace? I mean, I'm sitting here backing them all up, but I rather suspect some of them might be completely EMPTY. And - if that's the case - I should probably remove them.

Related - is there a way to find out "how full" a given tablespace is? (So I can see how much of the allocated space is in use - do I need more? Is there too much as it is? etc.) Can you do that on a single datafile, or only on a while tablespace?

As always - thanks for your [understandably limited!] time. :-)


Tom Kyte
March 31, 2005 - 7:55 am UTC

  1  select owner, segment_name, segment_type
  2   from dba_segments
  3* where tablespace_name = 'USERS'
ops$tkyte@ORA9IR2> /
 
OWNER                          SEGMENT_NAME
------------------------------ ------------------------------
SEGMENT_TYPE
------------------
SCOTT                          EMP
TABLE
 
SCOTT                          DEPT
TABLE
 
SCOTT                          BONUS
TABLE
 
SCOTT                          SALGRADE
TABLE
 
SCOTT                          DUMMY
TABLE
........



http://asktom.oracle.com/Misc/free.html

for a freespace report.


 

Next steps for a experienced DBA - part II

Luis, March 31, 2005 - 7:20 am UTC

Nice shot, Tom: bind variables. But, Oracle always translates a parameter in a predicate to a bind variable, doesn't it? The exception is when we use "execute immediate", right? In this last case, we need to use "execute immediate ... into ... using..." right?

Regarding my day to day job, I developed a mod_plsql app which colects information on all of my 20 production databases twice a day and it reports me things like: tbs with less than 15% of free space, segments which next extent are bigger than the max free contiguous space (unfortunatelly I have DMT tbs because of a 24x7 app), segments with more than 32 extents (SAFE algorithm rules), queries doing a lot of PIO (they generally mean a poor explain plan - here where I am the unique DBA and I have 100 Oracle "professionals" writing "fantastic" queries, I can say that PIO means poor statement). So, my 1st hour is spent correcting what is wrong on the report. The 2nd hour is spent tuning those queries in report. Other 3 hours in the day are spent supporting the developers (such a boring thing - maxextents reached, ORA-1555, ...) and deploying applications. Then I spend 2 hour talking with the developers to understand their needs for new projects, and whatever related to the BUSINESS (this is my 1st job and I've been in this company for 4 years, I know where any piece of information related to the BUSINESS is, but I'll move next week to a even bigger company and will need to learn again all this). The last hour is spent with technical "refresh": papers from everywhere (ixora, asktom, metalink, hotsos,...). This is MY day. Would you suggest me changes in my schedule?

Regarding interaction with others, I participate on Brazilian forums, but 95% of the threads are dummy questions regarding those boring things of always: maxextents reached, tnsnames config, etc. Do you recomend an expert forum or community?

Thanks a lot!

Luis

Tom Kyte
March 31, 2005 - 8:16 am UTC

<quote>
But, Oracle always translates a parameter in a
predicate to a bind variable, doesn't it?
</quote>

on the fact of it, that is wrong, but looking at your next statement:

<quote>
The exception is when we use "execute
immediate", right? In this last case, we need to use "execute immediate ... into
... using..." right?
</quote>

I have a feeling you are thinking exclusively about PLSQL. In PLSQL -- it is very very very HARD not to use bind variables 100% correctly. If you do 100% static sql in plsql, I can say absolutely that "you have done the best job you can with binding". For in PLSQL (this is the magic of PLSQL) references to program variables in static SQL are automagically bound.

in PLSQL the time you have to think about it is when you use dynamic sql (dbms_sql or native dynamic sql). There you have to be careful to bind correctly, manually.

Most every other language -- java/jdbc, C with OCI, C++ with OCI, VB -- they all use dynamic sql exclusively. You have to manually bind properly. Unless the developers use plsql exclusively, you have to make sure they are aware of this fact.


segments with next extents.... use LMTs and stop running that query forever.

SAFE? 3200 extents are perfectly OK. Having many extents is perfectly OK.

maxextents reached? use LMTs, maxextents doesn't exist in them!

Sounds like you might want to research the features that start with A (auto) and see what applies to you -- sounds like many of the things you do could be "not done".

oracle-l is a fairly high signal to noise ratio list, lots of signal, less noise. </code> http://www.freelists.org/archives/oracle-l/ <code> you can review it yourself.



Thanks!

Invisible, March 31, 2005 - 8:45 am UTC

Well...

It *is* all about knowing which views to query! ;-)

I had a go at a cut and paste of the webpage. Looks like a real useful query. (And possibly the most confusing looking SQL I've ever seen! But then, if I sat and pretty printed it, it's probably not actually that complex...)

Unfortunately, it doesn't work for me. :-(

Connected to:
Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production

SQL> @C:\FreeSpace.sql
               decode(segment_space_management,'AUTO','a ','m ')
                      *
ERROR at line 2:
ORA-00904: invalid column name



Oh well!

Anyway, I had a go at modifying the query in your reply:

SQL> SELECT tablespace_name, sum(bytes)
  2  FROM dba_segments
  3  GROUP BY tablespace_name;

TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
RBS                              29360128
SYSTEM                           56483840
APP_DATA                        595320832
APP_INDEX                       249430016


Out of the 7 tablespaces we have, only 4 appear in the listing. Can I take that as meaning it's safe to remove the others?

Interestingly, in the *production* database, the TOOLS tablespace isn't empty. (It is on the various test DBs.) It contains a single segment named PLAN_TABLE. Interesting...

The production instance also has two tablespaces - one called TEMP, the other called TEMPORARY_DATA. Both appear empty - but then, if they really *are* temporary, then they should do. (?) How do I check whether they really _are_ temporary, or whether the guy who built this just thought that giving them a special name would magically MAKE them temporary?
 

Tom Kyte
March 31, 2005 - 9:31 am UTC

goto bottom of page free.html, updated....


plan_table is good -- it is for explain plans. the databases that don't have it are suspect to me!! that means no one is investigating performance as they develop!


read about the DBA_TABLESPACES view, it'll tell you about tablespaces.

Nice

Invisible, March 31, 2005 - 10:05 am UTC

Last century! LOL. Makes it sound so long ago... ;-)

Very pretty output BTW. (Once I changed the linesize.)
                                                                
Tablespace Name           KBytes         Used         Free   Used
------------------- ------------ ------------ ------------ ------
 RBS                     153,600       32,776      120,824   21.3
 SYSTEM                   59,392       55,296        4,096   93.1
 TEMP                     51,200            8       51,192     .0
 TEMPORARY_DATA          102,400            8      102,392     .0
 TOOLS                    12,288           40       12,248     .3
 USERS                    20,480            8       20,472     .0
 APP_DATA              1,048,576      645,336      403,240   61.5
 APP_INDEX               245,760      240,800        4,960   98.0
                    ------------ ------------ ------------
sum                    1,693,696      974,272      719,424


ALL tablespaces have at least 8 bytes used - I presume that's some kind of overhead. Interestingly - the "largest" column matches the "free" on most of the empty tablespaces. Except on the TEMP one, where it's 2,240 bytes smaller...

My my, looks like that index tablespace is a tad full. (Is that "used" space, or just "allocated" space? Indexes can shrink and grow after all...) SYSTEM looks fullish too - but that's unlikely to grow *ever* - unless we change the schema. Or so I believe...

As I say, plan_table exists in the production DB, just not the test ones.

Just curiose now - is this table created the first time you use explain plan or something? Or is it supposed to be generated when you create a new DB?

As for DBA_TABLESPACES...

SQL> SELECT tablespace_name, contents, logging FROM dba_tablespaces;

TABLESPACE_NAME                CONTENTS  LOGGING
------------------------------ --------- ---------
SYSTEM                         PERMANENT LOGGING
RBS                            PERMANENT LOGGING
USERS                          PERMANENT LOGGING
TEMP                           TEMPORARY LOGGING
TOOLS                          PERMANENT LOGGING
TEMPORARY_DATA                 PERMANENT LOGGING
APP_INDEX                      PERMANENT LOGGING
APP_DATA                       PERMANENT LOGGING

8 rows selected.

So that (empty) TEMPORARY_DATA tablespace really *is* doing NOTHING. (...unless the application code creates something in there and deletes it afterwards. Seems unlikely tho. But since we didn't write the application...)

Anyway, you've been most helpful. Thanks for your time.
 

Tom Kyte
March 31, 2005 - 10:28 am UTC

that 8 is the 'overhead' yes (will vary)


temp has adjacent free extents that have not been colesced (not necessary in locally managed tablespace, but coalesceing has to take place in dictionary managed tables -- you can alter the tablespace to coalesce it if you like)


the index tablespace is "full" but you'd want to look at the autoextend on the datafiles, it might well be able to grow


You create the plan table by running $ORACLE_HOME/rdbms/admin/utlxplan.sql, some tools will create it as well (tkprof for example) as needed.


temporary_data looks like a scratch tablespace that could actually be used by the application -- it is a "hunch" however, they might create tables in there, batch something and drop them later.

Cool

Invisible, March 31, 2005 - 10:52 am UTC

Thanks for your assessment. :-)

Yes, TEMP has freespace in chunks. That's no problem - I was thinking more along the lines of "does that mean there WAS something in this tablespace once?" Sure, it's empty now - but I was wondering if the lower largest_free was an indication that there was an object in there that has subsequently been deleted. (But then, this is a true temp tablespace anyway - so it would be empty if nobody is using it! ;-)

As for autoextend... the "max poss Kbytes" on all of the tablespaces is *huge* - 33GB or something stupid. (I presume that's the figure to look at anyway...)

I suppose if I really cared about the other "temp" tablespace there's probably some auditing feature somewhere that I could turn on that would tell me if anything has happened to it - but then, how do I tell if it's unused, or just not been used this particular week. ;-) I think I'll leave that one alone for now...

[I suppose I could try checking if the app user even has permission to access it tho...]

Interesting info about plan_table. I'll make a note of that...


One final question before I go... If I delete data out of a table, will the "used" figure for the index tablespace go down? I know Oracle counts some things as freed immediately, and others just get "reused" next time they're needed, but still show up as "used"...


Thanks for all your great work! :-D

(BTW... does anyone else think that AskTom should have a little PayPal button that says "click here if you think Tom is fantastic" on it? ;-)

Hmm... this text box is really tiny... I wonder if that's Tom's hint to peeps to keep the questions short? ;-)


Tom Kyte
March 31, 2005 - 10:57 am UTC

it is an indication for sure. part of the hunch.


once space is given to a segment, that segment owns if. If you fill up a table with 1,000,000,000 rows and it consumes 100gig of storage and you delete all of the rows -- it'll still consume 100gig of storage (the table will)

However, if you truncate and let it release the storage, it will give it back.

Or if you "reorg" it (rebuild it) you can get it "back"

Or if you have 10g and you shrink/compact it, you can get it "back"

and so on....

IGMP

Invisible, April 01, 2005 - 4:37 am UTC

I presume from the use of "segment" that indexes have the same behaviour then?

So... the "used" figure just means that 98% of the tablespace *was* in use at some point in time... More of a high water mark kind of figure...

(Of course, if I *really* cared, I could ask Oracle how many rows are in each of the tables, and compute some kind of estimate like that... or I could just accept that when the thing fills up, the OS file will physically get bigger! BTW, does Oracle log a trace message when that happens?)

Being curiose... Is changing the size of a segment an expensive operation? In other words, if I make a table shrink down now, and in a few hours it needs to grow again, will that give a noticable performance hit?

(I remember reading here somewhere that rebuilding indexes tends to be a daft move - unless you just made some radical change to the underlying data. But a table is NOT an index. ;-)

Right, well, anyway... You've been a great help. Now I just need to sit down and have a careful look at my DBs, and decide exactly what I'm going to do...

Tom Kyte
April 01, 2005 - 8:40 am UTC

segment is a segment

ops$tkyte@ORA9IR2> select distinct segment_type from dba_segments;
 
SEGMENT_TYPE
------------------
CACHE
CLUSTER
INDEX
INDEX PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TABLE SUBPARTITION
TYPE2 UNDO
 
12 rows selected.


indexes are included in that.


Autoextends are not "audited", you would have to keep a history of the file sizes if you were so inclined.


As for the growing and shrinking -- well, it is a long story.  It could be expensive (an index getting back to its steady state, splitting, expanding).  It could be very cheap (a table grabs a next extent and doubles in size in milliseconds).


It is not that rebuilding indexes is daft, You want to understand why you are rebuilding an index on a recurring basis -- you want to understand that it is doing something useful, understand any negative side effects and see if there isn't a long term solution to the entire problem! 

Right

Invisible, April 01, 2005 - 8:58 am UTC

I rephrase - rebuilding indexes just for the fun of it is daft. ;-)