1 2 3 Previous Next 31 Replies Latest reply: May 5, 2008 3:33 AM by Jonathan Lewis Go to original post RSS
      • 15. Re: Full Table Scan
        311441
        Hi David

        I can very easily give you an example where an index would be the best option to query 99% of data. I can very easily give you an example where a FTS is the best option to query 1% of data.

        7% is meaningless in this context ...

        And I taught for OU for many years ;)

        Cheers

        Richard Foote
        http://richardfoote.wordpress.com/
        • 16. Re: Full Table Scan
          636947
          Hi,

          Of course, no one is saying otherwise. All I am saying is that the CBO textbook overview from Oracle says that when you are about to query more than 7% of table data, the optimizer will try and do an FTS. Not always, the CBO takes into account many many other parameters and the behavior can change quite a bit (not to mention the fact that many of the text written about Oracle's CBO, even in Oracle's own books, is sometimes not entirely correct) - usually the amount of L-I/O and P-I/O between an index-scan and an FTS.

          It's always nice to see a fellow OU instructor here. :)

          Best Regards,
          David Yahalom
          http://www.authoritybase.com
          • 17. Re: Full Table Scan
            546612
            "Queries that retrieve less than 7 percent of the table data blocks
            should use an index range scan. Conversely, queries
            that read more than 7 percent of the table data
            blocks will probably be faster with a full-table
            scan. "
            ...
            I've read this in an Oracle University Press book
            seven or eight years ago and it has stuck with me
            ever since.
            David,

            A bit more detail about which book (or author) would be appreciated, if at all possible. And a clarification ... was that Oracle Press or Oracle University

            Oracle University provides course texts but (afaik) does not have an 'Oracle University Press'
            whereas
            Oracle Press is independent of Oracle, and even though it happens to have Oracle's official support, in the late 90s and early 00s Oracle Press books were known to have many opportunities for correction.
            • 18. Re: Full Table Scan
              636947
              "Queries that retrieve less than 7 percent of the
              table data blocks
              should use an index range scan. Conversely,
              queries
              that read more than 7 percent of the table data
              blocks will probably be faster with a full-table
              scan. "
              ...
              I've read this in an Oracle University Press book
              seven or eight years ago and it has stuck with me
              ever since.
              David,

              A bit more detail about which book (or author) would
              be appreciated, if at all possible. And a
              clarification ... was that Oracle Press or Oracle
              University

              Oracle University provides course texts but (afaik)
              does not have an 'Oracle University Press'
              whereas
              Oracle Press is independent of Oracle, and even
              though it happens to have Oracle's official support,
              in the late 90s and early 00s Oracle Press books were
              known to have many opportunities for correction.
              Hi,

              Oracle University. I've got the book home. I'll post a scan this weekend. :)

              David Yahalom
              http://www.authoritybase.com
              • 19. Re: Full Table Scan
                585319
                I agree, there are times that FTS is the best action plan when we're going to retrieve large amount of data.

                I just wonder if OP issue is related to a scenario like that... isn't it?

                Regards
                Ignacio

                http://oracledisect.blogspot.com
                • 20. Re: Full Table Scan
                  Mark Reichman
                  Tom has a good piece on full table scans and Cost Based Optimizer

                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968

                  This part always make me chuckle every time I read it...

                  and we said...
                  first of all -- just keep saying to yourself over and over

                  "full scans are not evil, indexes are not good"
                  "full scans are not evil, indexes are not good"
                  "full scans are not evil, indexes are not good"
                  "full scans are not evil, indexes are not good"
                  "full scans are not evil, indexes are not good"
                  "full scans are not evil, indexes are not good"

                  over and over and over...

                  Then, look at your query -- tell me, how can a full scan be avoided?

                  tell me -- using the EXISTING data structures you have -- what plan could YOU yourself as
                  a human come up with that does not involve a full table or index scan? Given your
                  existing structures, the INDEXES are actually deadly here -- the CBO would be smarter and
                  stop using them.
                  • 21. Re: Full Table Scan
                    jgarry
                    Hi,

                    We are runing in 10g (10.2.0.3), when using full
                    table scan, the perfromance is very slow. Is a bug in
                    ASM or SQL program problem? How can I vertify the
                    problem come from? I have runing health check in
                    oracle but found nothing.
                    Not disputing what the others have said, but adding:

                    Is there some particular reason you think it is slow?
                    What exactly are you doing for a "health check?"
                    Was it faster in the past?
                    Has something changed in the meantime?
                    Is this a new query?
                    Have you done any mass deletes or mass loads?
                    Have you checked what exactly Oracle is waiting on?
                    Is the table sparse?
                    Is this on every table?
                    Are there messages in the alert log?
                    What kind of hardware are you using?
                    Have you run a statspack?
                    Have you run an explain plan?
                    Is this just for one person or for many people?
                    Questions, questions, questions!
                    • 22. Re: Full Table Scan
                      585321
                      Hi Mr Richard ,

                      For a table with a single row and single column better to access the table directly than access it through an index coz' then you access two parts of the database instead of one .
                      I can very easily give you an example where an index would be the best option to query 99% of data.
                      I can very easily give you an example where a FTS is the best option to query 1% of data.
                      Can you please elaborate on this with examples .

                      Thank you!

                      null
                      • 23. Re: Full Table Scan
                        Jonathan Lewis
                        It's not really relevant to the OP's question - but funnily enough, when you access through the index, you will hit the root block of the index one, and the table block once for a total of 2 LIOs. If you do a tablescan you will access the segment header block twice (9i onwards) and the table block once for a total of 3 LIOs.

                        For the OP -
                        I think Charles Hooper pointed out that you need to explain what you mean by slow - and how you can investigate where the time goes. Just one idle thought, though: what have you done with your db_file_multiblock_read_count. I only ask because I've seen some people set it to zero for 10g, thinking that this means Oracle will use the default, when in fact it makes Oracle switches to the value one.


                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk
                        • 24. Re: Full Table Scan
                          585321
                          Hi Jonathan ,

                          Thats left me even more curious . I am still a student trying to learn oracle . !!!!!
                          When you access through the index, you will hit the root block of the index one, and the table block once for a total of 2 LIOs. If you do a tablescan you will access the segment header block twice (9i onwards) and the table block once for a total of 3 LIOs.
                          Can you please elaborate on the above para you mentioned or provide appropriate LINKS please.
                          LIO stands for L??? input output I guess . Whats the abbreviation please?

                          Thank you!!!!
                          • 25. Re: Full Table Scan
                            108476
                            Hi,

                            I'm not sure about all of the "noise" posts earlier, but I'll take your question at face value. Here is your core observation:
                            when using full table scan, the perfromance is very slow.
                            Full-table scans, by definition, read all of the data blocks up to the HWM for the table, and time takes time.

                            The only way to improve the end-to-end performance of a full-table scan is Oracle parallel query (OPQ). Here are my notes:

                            http://www.dba-oracle.com/art_builder_opq_cpu.htm

                            --------------------

                            Hope this helps. . .

                            Donald K. Burleson
                            Oracle Press author
                            Author of "Oracle Tuning: The Definitive Reference":
                            http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
                            • 26. Re: Full Table Scan
                              Jonathan Lewis
                              >
                              I'm not sure about all of the "noise" posts earlier,
                              ...
                              >
                              >
                              The only way to improve the end-to-end performance of
                              a full-table scan is Oracle parallel query (OPQ).
                              No it's not - first, as Charles Hooper suggested in his post earlier, you need to find out why the tablescan is slow (and what the OP means by slow, anyway).

                              Here's an example of improving end to end tablescan performance without throwing parallel query at it. Cut and paste from a session running a tablescan under 10.2.0.1, with some output deleted (marked by "...").
                              SQL> set autotrace traceonly statistics
                              SQL> l
                                1* select n1 from t1
                              SQL> /
                              Elapsed: 00:00:01.73

                              Statistics
                              ----------------------------------------------------------
                                        0  recursive calls
                              ...
                                    40000  rows processed

                              SQL> /
                              Elapsed: 00:00:01.73

                              Statistics
                              ----------------------------------------------------------
                                        0  recursive calls
                              ...
                                    40000  rows processed

                              SQL> set arraysize 1024
                              SQL> /
                              Elapsed: 00:00:00.06

                              Statistics
                              ----------------------------------------------------------
                                        0  recursive calls
                              ...
                                    40000  rows processed

                              SQL> /
                              Elapsed: 00:00:00.06

                              Statistics
                              ----------------------------------------------------------
                                        0  recursive calls
                              ...
                                    40000  rows processed

                              SQL> spool off
                              That's improved the tablescan by a factor of nearly 30 simply by changing the array fetch size, and doing nothing else. Controlling the db_file_multiblock_read_count could also have an effect (as I mentioned in my earlier post).

                              Other, more subtle, possibilities include: making sure that multiblock reads weren't being broken up by the various layers between Oracle and the disks; making sure the discs hadn't been striped in an inappropriate way; checking the way the disks have been exposed to the operating system as logical devices.

                              There are far too many unknowns to uncover before suggesting that parallel query is the only way to speed things up.

                              Regards
                              Jonathan Lewis
                              http://jonathanlewis.wordpress.com
                              http://www.jlcomp.demon.co.uk
                              • 27. Re: Full Table Scan
                                jgarry
                                >
                                Can you please elaborate on the above para you<p>
                                mentioned or provide appropriate LINKS please.<p>
                                LIO stands for L??? input output I guess . Whats the<p>
                                abbreviation please?<p>
                                <p>
                                It stands for Logical Input/Output, as opposed to Physical Input/Output. LIO is basically Oracle looking at its own buffers, while PIO means calls to the OS. Oracle may not always know whether it is really having to get something from the physical disk (for example, it may actually be coming from an OS buffer cache or a SAN cache or... who knows?). So when tuning Oracle, it makes sense to focus on the logical I/O that Oracle can know something about, and let the PIO be reduced as a consequence - this usually means fixing inefficient SQL and optimization. See these google links.
                                • 28. Re: Full Table Scan
                                  Charles Hooper
                                  I'm not sure about all of the "noise" posts earlier,
                                  but I'll take your question at face value. Here is
                                  your core observation:
                                  when using full table scan, the perfromance is
                                  very slow.

                                  Full-table scans, by definition, read all of the data
                                  blocks up to the HWM for the table, and time takes
                                  time.

                                  The only way to improve the end-to-end performance of
                                  a full-table scan is Oracle parallel query (OPQ).
                                  Here are my notes:

                                  http://www.dba-oracle.com/art_builder_opq_cpu.htm

                                  --------------------

                                  Hope this helps. . .

                                  Donald K. Burleson
                                  Oracle Press author
                                  Author of "Oracle Tuning: The Definitive Reference":
                                  http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
                                  Mr. Burleson,

                                  Thanks for joining this conversation. However, it concerns me that you consider one, several, or all of the posts prior to your post in this thread as noise. Prior to your post I suggested that the original poster use a 10046 trace at level 8 or 12 as "the wait events and CPU/elapsed time present in the trace files provide a significant amount of detail to determine the source of the performance problem." Do you feel that this suggestion represents noise in this thread? A couple days ago, I followed a link that you posted in the Oracle forums to one of the threads on your discussion forum. While looking at that thread, I noticed a thread there titled "Trace Files for Tunning" and a response that you posted in that thread. From:
                                  http://dba.ipbhost.com/index.php?showtopic=11342&pid=35029&mode=threaded&show=&st=&#entry35029
                                  "Trace files are RARELY helpful in diagnising Oracle SQL, as a standard execution plan and execution stats are plenty for 99.9% of SQL diagnostics:"

                                  That comment leads me to believe that you find my suggestion to use a 10046 trace as contributing to the noise in this thread.

                                  Reading "Optimizing Oracle Performance" several years ago changed the way I approach performance tuning. A 10046 trace file really is able to provide a great deal of very useful information that is difficult to obtain by other means. Rather than seeing averages, as would be the case for a Statspack report, it is possible to see the statistics change as Oracle encountered a particular problem that caused a performance delay. When odd statistics show in other types of performance collection methods (Statspack, DBMS_XPLAN, TKPROF, system-wide wait events, BCHR, etc.), a 10046 trace shows why the problem occurred - and whether or not there was an actual problem. For example, take a look at this thread where a TKPROF report showed a maximum 4179 second wait on "SQL*Net message from client" and a maximum 4064 second wait on a single block read.

                                  Re: query tuning

                                  The 10046 trace file in that thread showed that the 4179 second wait on "SQL*Net message from client" could not have happened. By the same observation method, it would be clear that the 4064 second wait on a single block read could not have happened. Also, by examining the 10046 trace file, it was determined that the single block reads where not for index reads, or even full tablescans truncated to a single block read due to pre-existing blocks in the buffer cache. Rather, the single block reads were in the undo tablespace, as identified by the information in the raw 10046 trace file.

                                  A quick example setup for a full tablescan:
                                  CREATE TABLE T1(
                                    C1 NUMBER(10),
                                    C2 VARCHAR2(255),
                                    C3 VARCHAR2(255),
                                    C4 VARCHAR2(255));

                                  INSERT INTO
                                    T1
                                  SELECT
                                    ROWNUM RN,
                                    LPAD('A',255,'A'),
                                    LPAD('B',255,'B'),
                                    LPAD('C',255,'C') 
                                  FROM
                                    DUAL
                                  CONNECT BY
                                    LEVEL<=500000;

                                  COMMIT;
                                  Now, we have a table with 500,000 rows with possibly 10 rows per 8KB block (AVG_ROW_LEN is 772). Now, flush the buffer cache to force physical reads:
                                  ALTER SYSTEM FLUSH BUFFER_CACHE;
                                  ALTER SYSTEM FLUSH BUFFER_CACHE;
                                  Now, let's force a full table scan (a 10046 trace is enabled at level 8, and a DBMS_XPLAN is generated):
                                  SELECT
                                    *
                                  FROM
                                    T1
                                  WHERE
                                    ROWNUM<=1000;

                                  DBMS_XPLAN (partial)
                                  ----------------------------------------------------------------------------------------------
                                  | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
                                  ----------------------------------------------------------------------------------------------
                                  |*  1 |  COUNT STOPKEY     |      |      1 |        |   1000 |00:00:00.10 |     123 |    176 |
                                  |   2 |   TABLE ACCESS FULL| T1   |      1 |    500K|   1000 |00:00:00.08 |     123 |    176 |
                                  ----------------------------------------------------------------------------------------------
                                  Did the TABLE ACCESS FULL (full table scan) in the plan indicate that Oracle read all blocks up to the high water mark? Oracle did NOT read all blocks up to the high water mark, regardless of what the plan shows. The proof is in the 10046 trace file:
                                  =====================
                                  PARSING IN CURSOR #11 len=54 dep=0 uid=63 oct=3 lid=63 tim=1044938463796 hv=4195490999 ad='1c501fdc' sqlid='g386bagx1475r'
                                  SELECT
                                    *
                                  FROM
                                    T1
                                  WHERE
                                    ROWNUM<=1000
                                  END OF STMT
                                  PARSE #11:c=0,e=2282,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1044938463790
                                  EXEC #11:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1044938464457
                                  WAIT #11: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938464504
                                  WAIT #11: nam='db file sequential read' ela= 40853 file#=1 block#=84361 blocks=1 obj#=72319 tim=1044938505437
                                  WAIT #11: nam='reliable message' ela= 232 channel context=563314104 channel handle=563285032 broadcast message=564250208 obj#=72319 tim=1044938506251
                                  WAIT #11: nam='enq: KO - fast object checkpoint' ela= 163 name|mode=1263468550 2=65558 0=1 obj#=72319 tim=1044938506489
                                  WAIT #11: nam='direct path read' ela= 41814 file number=1 first dba=84362 block cnt=7 obj#=72319 tim=1044938586589
                                  WAIT #11: nam='direct path read' ela= 19323 file number=1 first dba=87305 block cnt=48 obj#=72319 tim=1044938606571
                                  FETCH #11:c=0,e=142335,p=86,cr=15,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938606885
                                  WAIT #11: nam='SQL*Net message from client' ela= 1258 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938608214
                                  WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938608305
                                  FETCH #11:c=0,e=690,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938608971
                                  WAIT #11: nam='SQL*Net message from client' ela= 656 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938609666
                                  WAIT #11: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938609725
                                  FETCH #11:c=0,e=687,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938610396
                                  WAIT #11: nam='SQL*Net message from client' ela= 579 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938611012
                                  WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938611072
                                  FETCH #11:c=0,e=681,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938611734
                                  WAIT #11: nam='SQL*Net message from client' ela= 742 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938612512
                                  WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938612575
                                  WAIT #11: nam='direct path read' ela= 4898 file number=1 first dba=87361 block cnt=30 obj#=72319 tim=1044938618207
                                  FETCH #11:c=0,e=5901,p=42,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938618458
                                  WAIT #11: nam='SQL*Net message from client' ela= 631 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938619135
                                  WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938619197
                                  FETCH #11:c=0,e=681,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938619860
                                  WAIT #11: nam='SQL*Net message from client' ela= 581 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938620476
                                  WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938620532
                                  FETCH #11:c=0,e=683,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938621199
                                  WAIT #11: nam='SQL*Net message from client' ela= 920 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938622154
                                  WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938622219
                                  WAIT #11: nam='direct path read' ela= 8072 file number=1 first dba=87391 block cnt=42 obj#=72319 tim=1044938630900
                                  FETCH #11:c=0,e=8966,p=48,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938631167
                                  WAIT #11: nam='SQL*Net message from client' ela= 613 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938631822
                                  WAIT #11: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938631880
                                  FETCH #11:c=0,e=686,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938632550
                                  WAIT #11: nam='SQL*Net message from client' ela= 583 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938633168
                                  WAIT #11: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938633271
                                  FETCH #11:c=0,e=675,p=0,cr=12,cu=0,mis=0,r=100,dep=0,og=4,tim=1044938633928
                                  WAIT #11: nam='SQL*Net message from client' ela= 602 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938634565
                                  WAIT #11: nam='direct path read' ela= 22151 file number=1 first dba=86793 block cnt=48 obj#=72319 tim=1044938656764
                                  FETCH #11:c=0,e=22249,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1044938656854
                                  STAT #11 id=1 cnt=1000 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=123 pr=176 pw=176 time=37561 us)'
                                  STAT #11 id=2 cnt=1000 pid=1 pos=1 obj=72319 op='TABLE ACCESS FULL T1 (cr=123 pr=176 pw=176 time=34756 us cost=8433 size=386000000 card=500000)'
                                  WAIT #11: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938656998
                                  WAIT #11: nam='SQL*Net message from client' ela= 132538 driver id=1413697536 #bytes=1 p3=0 obj#=72319 tim=1044938789567
                                  =====================
                                  From the trace file we see many interesting details, including the absence of the typical db file scattered reads commonly associated with full table scans. From the trace it is also possible to see that 100 rows were read at a time, with a fairly consistent delay between requests for each set of the 100 rows. What else might we see in the trace file that would help us identify the source of a performance problem?

                                  Please feel free to comment on whether or not my suggestion to capture a 10046 trace file contributed to the noise in this thread. If it did contribute to the noise, what other method would you employ beside parallel query to correct the problem?

                                  Thanks,
                                  Charles Hooper
                                  IT Manager/Oracle DBA
                                  K&M Machine-Fabricating, Inc.
                                  • 29. Re: Full Table Scan
                                    311441
                                    Hi User 321

                                    Jonathan Lewis answered this question for you in this other thread:

                                    Full Table Scans / Index Scans /Index Organized Tables...

                                    The point being there is no magical number (be it 7% or any other number) from which Oracle starts considering using a FTS. It's dependant totally on the associated costs of each option and these costs are based on numerous different variables such a number of leaf blocks, number of table blocks, average rows per block, clustering factor of index, effective multiblock read, etc. etc.

                                    Cheers

                                    Richard Foote
                                    http://richardfoote.wordpress.com/