Skip to Main Content
  • Questions
  • Blocks read using index vs. full table scan

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Robert.

Asked: June 27, 2002 - 12:26 pm UTC

Last updated: December 05, 2019 - 9:23 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I have a 3 part question about performance...

1) The first question is sort of a introductory summary question.
2) Then I have an example ending with a question.
3) Finaly I have a very specific 'important' question.

Why is it sometimes faster to do a full table scan on a large table vs. using an index ?

For example, say you have a large table which has an index that has 4 distinct values, each making up 25% of the index. Now let's say you run a query on this table which is going after one of the distinct values in the index...

select *
from big_table
where indexed_col1 = 'ONE_FOURTH_OF_TABLE';

My understanding is that Oracle might typically choose to do a full table scan on the table instead of using the index.

Why is this ? It would appear to me that Oracle would have to at least twice as many reads (in this example) to do the FTS vs. using the index.

IMPORTANT QUESTION: Does Oracle do a separate read for each BLEVEL in the index it is accessing ?

Thanks!

Robert.

and Tom said...

Well, I'm going to recommend a book -- "expert one on one Oracle" written by me. I have quite a few examples that show why this is true (and it takes a couple of pages). If you read the reviews on amazon, I think you'll find that people generally like the book overall.

Here is a short answer:

A full tablescan reads the table using large sequential reads -- many blocks at a time. Sequential reads are the fastest type of IO you can do on a disk in general.

An index read will do single block, random IO's. These are generally the slowest you can do.

If you are going to read 25% of the table via an index you will probably hit MOST if not all of the blocks in the table. You will hit some blocks many times probably as well.

Ok, if you take the above as "fact" we can move on... Lets say the table is 100 blocks, with 1000 values and your buffer cache can hold 50 blocks. Let's say you will read this via an index. We will read 250 rows.

The index might tell us "goto block 1, get row 1", "goto block 2, get row 1" ... "goto block N get row 1".... "goto block 100 get row 1"

So, so far, the index has had us read each block once and get the first row on each block. The index processes the data in sorted order, not in the order it physically resided on the disk in the table. Hence, we did 100 random IO's to read 100 blocks in. Not only that -- but blocks 1-50 are no longer in the buffer cache, blocks 51-100 are. Now, resuming our query (we have 150 more rows to get!) the index tells us: "goto block 1, get row 2", "goto block 2, get row 2" ... "goto block N get row 2".... "goto block 100 get row 2"

Oh no -- we just got told to RE-READ the blocks we already read!!! Again!!! from Disk!! So, assuming the table had no blocks in the cache, we've now just done 200 random physical IO's against that table -- AND we are not done yet!!!

So, picking up where we left off -- the index now says "goto block 1, get row 3", "goto block 3, get row 1" ... "goto block N get row 3".... "goto block 50 get row 3". Bummer, 50 more random physical IO's.


Now, the full table scan comes in. Suppose our db_file_multiblock_read_count is set to 20. We do 5 sequential IO's of 20 blocks each and answer the query. Done. Period. Much faster.

Here, a full table scan does a whole lot less IO more efficiently.


For the important question: We do single block IO on indexes generally, so yes, it would be a separate read for each block to traverse the tree.




Rating

  (82 ratings)

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

Comments

Good explanation

Robert, June 27, 2002 - 2:18 pm UTC

So far so good; now, if we can reduce the BLEVEL on the index by one then we will, by definition, reduce the random I/O read for that index by one as well (unless two blevels existed on the same block, etc.) ?

Thanks,

P.S.
I have your book. It was your chapter on TKPROF and your digging into the raw trace files that made me say "This is just the Oracle information I've been looking for" And by a guy with a sense of humor too! (now I just need to read the book, huh?!).



Tom Kyte
June 27, 2002 - 2:30 pm UTC

yes, but most indexes are going to hang out at 3 levels in the real world and levels 1 and 2 are typically cached so it isn't really worth the effort.


If you have the book, read pages 277-285. It has the examples I was refering to (colocated and disorganized tables)

Concerning db_file_multiblock_read_count

Richard, June 27, 2002 - 5:46 pm UTC

I was under the impression that the amount of performance
improvement you can get by setting the
db_file_multiblock_read_count parameter is limited by the
OS's IO buffer size. In short, the Oracle block size (in K)
multiplied by the value of db_file_multiblock_read_count
should be equal to the OS's IO buffer (in K).

So, if you have a block size of 8K and your OS's IO buffer
is 64K, setting the db_file_multiblock_read_count to
anything greater than 8 is useless.

Correct? Or no?



Tom Kyte
June 27, 2002 - 8:09 pm UTC

That would be pretty much correct -- however, on many OS's today the OS IO buffer is in the multiple- hundreds of K even into the megabyte range. Depends on the OS (and we limit it to that, you can set it artificially high, we'll bring it back down).

I was using numbers that made the math really really easy for illustrative purposes.

Oracle internal IO limitation

A P Clarke, June 28, 2002 - 6:07 am UTC

Tom

I read somewhere that Oracle itself limits I/O through the SSTIOMAX kernel constant. My info is now a couple of years old, but at the time the value of SSTIOMAX was said to be 512K. What is its current (9i) value? How could I find the value for myself? The only place it's mentioned is in the error messages (ORA-27067).

Also, would setting db_file_multiblock_read_count to an unfeasibly high number have an impact on optimization?

Thanks, APC

Tom Kyte
June 28, 2002 - 7:23 am UTC

One method  is to do a big full scan and see what the wait events tell us.  For example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t storage ( initial 100m next 100m ) tablespace xxx  as select * from all_objects;
Table created.

<b>create a table with a pretty large initial extent. We never will read more then an extent at a time (eg: I initially did this test in a locally managed tablespace with 512k extents -- I read 64 8k blocks at a time, then realized "oh yeah, thats my extent size"...)</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
22858 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
45716 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
91432 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
182864 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set db_file_multiblock_read_count = 1024;
Session altered.

<b>now, set this paramter to something really really high...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set events '10046 trace name context forever, level 12';

Session altered.

<b>enable tracing with wait events capture on..</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ FULL(t) */ count(*) from t;

  COUNT(*)
----------
    365728

<b>and full scan.  Now, look at the trace file:</b>

PARSING IN CURSOR #1 len=38 dep=0 uid=439 oct=3 lid=439 tim=3742056778 hv=194957013 ad='86989890'
select /*+ FULL(t) */ count(*) from t
END OF STMT
PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3742056778
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3742056778
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file scattered read' ela= 14 p1=10 p2=2514 p3=128
WAIT #1: nam='db file scattered read' ela= 12 p1=10 p2=2642 p3=128
WAIT #1: nam='db file scattered read' ela= 10 p1=10 p2=2770 p3=128
WAIT #1: nam='db file scattered read' ela= 10 p1=10 p2=2898 p3=128
WAIT #1: nam='db file scattered read' ela= 12 p1=10 p2=3026 p3=128
WAIT #1: nam='db file scattered read' ela= 12 p1=10 p2=3154 p3=128
WAIT #1: nam='db file scattered read' ela= 10 p1=10 p2=3282 p3=128
WAIT #1: nam='db file scattered read' ela= 9 p1=10 p2=3410 p3=128


<b>db file scattered read is our wait event -- p3 is the number of blocks we tried to read.  Apparently on my OS+filesystem+db version, I can read 1m at a time (128 * 8K block)...</b>


Yes, setting db_file_multiblock_read_count to an unfeasibly high number can unduly affect the optimizer.  If you can never obtain that read rate, and it thinks you will, it could lead it down the incorrect path. 

Excellent.....

Ashok, June 28, 2002 - 6:08 am UTC


Excellent.

Leo, June 28, 2002 - 10:25 am UTC


OS IO buffer

Nitin, March 05, 2003 - 8:42 am UTC

I am unable to understand what is meant by OS IO buffer.
How do we find it .

Is it the same as read write cache on sun solaris .
Well if it is , then if i have understood it correctly we can have the value of db_file_multiblock_io up to
1073741824/8172=130000(approx) as we have 1gb of read write cache and using db_block_size=8k.



Tom Kyte
March 05, 2003 - 10:54 am UTC

it is the max amount the OS will read in a read request.

it was 128 blocks on my system as per the example. It is the MAX io size the os will do, regardless of what we ask for


Question

sankar, March 05, 2003 - 9:59 am UTC

I created a table that spans across 5 extents and each extent is 260 blocks . I did the alters and part of the trace file is below . It is on 8.1.7.2 / hp-ux 11.0

Select extent_id ,blocks from dba_extents where segment_name='T';

EXTENT_ID BLOCKS
---------- ----------
0 260
1 260
2 260
3 260
4 260

PARSING IN CURSOR #1 len=70 dep=0 uid=163 oct=42 lid=163 tim=1608319387 hv=347037164 ad='85c9e8a0'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #1:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1608319387
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 807 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=163 oct=3 lid=163 tim=1608320195 hv=194957013 ad='85caffd0'
select /*+ FULL(t) */ count(*) from t
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1608320195
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1608320195
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 5 p1=3 p2=867 p3=128
WAIT #1: nam='db file scattered read' ela= 1 p1=3 p2=995 p3=47
WAIT #1: nam='db file scattered read' ela= 4 p1=3 p2=1302 p3=128
WAIT #1: nam='db file scattered read' ela= 3 p1=3 p2=1430 p3=128
WAIT #1: nam='db file scattered read' ela= 0 p1=3 p2=1558 p3=4
WAIT #1: nam='db file scattered read' ela= 4 p1=3 p2=2082 p3=128
WAIT #1: nam='db file scattered read' ela= 1 p1=3 p2=2210 p3=39
FETCH #1:c=34,e=43,p=602,cr=1210,cu=12,mis=0,r=1,dep=0,og=4,tim=1608320238
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1608320238
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 462 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=99200 pid=1 pos=1 obj=5187 op='TABLE ACCESS FULL T '
XCTEND rlbk=0, rd_only=1

I expected to see in the trace file that oracle tried to
read 128 blocks at a time twice and 4 blocks next time for each extent . My question is why do i see as below .

WAIT #1: nam='db file scattered read' ela= 5 p1=3 p2=867 p3=128
WAIT #1: nam='db file scattered read' ela= 1 p1=3 p2=995 p3=47
Also i see a p3=39 once and i dont see this for all extents .




Tom Kyte
March 05, 2003 - 11:10 am UTC

the table isn't full. we only read what we need -- and some of it can be in the buffer cache (hence we won't read that necessarily).


It "looks" like you are using 3 of the 5 extents to hold data (this is probably, not 100%, caching can affect this).

extent 1: blocks 867 .. 1042 (128+47)
extent 2: blocks 1302 .. 1562 (128+128+4)
extent 3: blocks 2082 .. 2249 (128+39)

the other two extents have nothing, two of the three that do have data are not full.

db_file_multiblock_read_count IN 9i VS 8i

Saradha Bavanandam, April 17, 2003 - 5:19 pm UTC

Tom,

We are currently upgrading our databases to Oracle 9i one by one. Currently we have two development databases one running 8i version and other running 9i version. These two databases exactly same in size, table structures, data and parameters. We have same application running under 8i and 9i database. The performance in 8i database is good and the performance on 9i database is very bad.

After so much of investigation, we have modified db_file_multiblock_read_count parameter to 8 in the Oracle9i database. Initially it was 32. After reducing this parameter to 8 the performance in 9i database is better.
But this parameter is in 8i database is set to 32 and the performance is fine.

Do the behaviour or influence of this parameter in Oracle9i
different from 8i database.

What we should do when we upgrade our production databases.
Currently the production (8i) database is set to 32.

Production databases and development database are in two different unix boxes.

Please advice.

Thanks
Saradha

Tom Kyte
April 17, 2003 - 5:54 pm UTC

have you considered comparing query plans -- statspack -- anything "database wise" to see what might be different in the two.

the parameter you changed affects the costing of full scans. The bigger the number, the lower the cost. that, combined with a totally different cluster factor and a host of other things -- could affect the plans. Rather then tweak random knobs and dials -- you should figure out "whats different" between the two and diagnose it from that perspective.

(hint, look at optimizer_* init parameters even more then the one you tweaked already -- see what it does to the costing of index plans)

explain plan

Saradha Bavanandam, April 21, 2003 - 3:42 pm UTC

Tom,

I have printed the execution plan of the same query in 8i
and 9i. The plan is different for the same query where the
database setup, size, structure are exactly the same in 8i and 9i.

SELECT
    A.ID,
    CV.CURRENCY_RATE,
    I.BID_DRIVER_ID,
    CRA.ACTIVITY_ID,
    NVL(I.RANGE_DRIVER_ID, -1),
    NVL(MAX_DRIVER_VALUE, 0),
    NVL(MIN_DRIVER_VALUE, 0) ,
    I.COMP_VALUE,
    I.BILL_TYPE_ID
  FROM COMP_AGREEMENT_LANES L,
    COMP_AGREEMENT A,
    CURRENCY_VALUES CV,
    COMP_AGREEMENT_SET CAS,
    MSP_GROUP_ITEMS MGI,
    COMP_RATE_ACTIVITY CRA,
    COMP_AGREEMENT_ITEMS I,
    USER_DATA_SET UDS
  WHERE ( L.ORIG_LOCATION_ID = :oraAgentLocationId
    OR L.DEST_LOCATION_ID = :oraAgentLocationId)
    AND A.IMP_EXP = :oraAgentImpExp
    AND MGI.MSP_ID = :oraAgentMSPId
    AND L.MSP_COLLECTION_ID = MGI.MSP_GROUP_ID
    AND L.ORIG_PC_LOW IS NULL
    AND L.ORIG_PC_HIGH IS NULL
    AND L.DEST_PC_LOW IS NULL
    AND L.DEST_PC_HIGH IS NULL
    AND L.COMP_AGREEMENT_ID = A.ID
    AND L.COMP_AGREEMENT_SET_ID = CAS.ID
    AND UDS.CURRENCY_SET_ID = CV.CURRENCY_SET_ID
    AND UDS.id = :oraUserDataSetId
    AND CV.CURRENCY_ID = A.CURRENCY_ID
    AND EXISTS (
      SELECT * FROM COMP_AGREEMENT_SET_DEFN D
      WHERE A.ID = D.COMP_AGREEMENT_ID
      AND D.COMP_AGREEMENT_SET_ID = :oraCompAgreementSetId )
    AND I.COMP_AGREEMENT_ID = A.ID
    AND CRA.BID_DRIVER_ID = I.BID_DRIVER_ID
  ORDER BY I.BILL_TYPE_ID DESC;


SQL> show parameter optim

NAME                                 TYPE    VALUE
------------------------------------ ------- ------
optimizer_features_enable            string  8.1.7
optimizer_index_caching              integer 0
optimizer_index_cost_adj             integer 100
optimizer_max_permutations           integer 80000
optimizer_mode                       string  CHOOSE
optimizer_percent_parallel           integer 0
db_file_multiblock_read_count        integer 8

8i:SQL> set autotrace on exp
8i:SQL> @iq
no rows selected.

   1    0   SORT (ORDER BY) (Cost=133 Card=16 Bytes=1504)
   2    1     CONCATENATION
   3    2       HASH JOIN (Cost=37 Card=3 Bytes=264)
   4    3         NESTED LOOPS (Cost=34 Card=3 Bytes=246)
   5    4           HASH JOIN (Cost=22 Card=3 Bytes=189)
   6    5             NESTED LOOPS (Cost=19 Card=3 Bytes=153)
   7    6               NESTED LOOPS (Cost=37 Card=4 Bytes=376)
   8    7                 NESTED LOOPS (Cost=13 Card=3 Bytes=126)
   9    8                   NESTED LOOPS (Cost=13 Card=3 Bytes=117)
  10    9                     TABLE ACCESS (BY INDEX ROWID) OF 'USER_DATA_SET' (Cost=1 Card=1 Bytes=6)
  11   10                       INDEX (UNIQUE SCAN) OF 'PK_USER_DATA_SET' (UNIQUE)
  12    9                     TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_LANES' (Cost=12 Card=3 Bytes=99)
  13   12                       INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES_IMP' (NON-UNIQUE) (Cost=11 Card=3)
  14    8                   INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET' (UNIQUE)
  15    7                 INDEX (UNIQUE SCAN) OF 'PK_MSP_GROUP_ITEMS' (UNIQUE)
  16    6               TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT' (Cost=2 Card=20488 Bytes=184392)
  17   16                 INDEX (UNIQUE SCAN) OF 'IDX_CA_IMP_EXP' (UNIQUE) (Cost=1 Card=20488)
  18   17                   INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET_DEFN' (UNIQUE) (Cost=2 Card=1 Bytes=8)
  19    5             TABLE ACCESS (FULL) OF 'CURRENCY_VALUES' (Cost=1  Card=215 Bytes=2580)
  20    4           TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_ITEMS' (Cost=4 Card=819508 Bytes=15570652)
  21   20             INDEX (RANGE SCAN) OF 'CAI_NONUNIQUE' (NON-UNIQUE) (Cost=3 Card=819508)
  22    3         INDEX (FULL SCAN) OF 'PK_COMP_RATE_ACTIVITY' (UNIQUE) (Cost=1 Card=7 Bytes=42)
  23    2       NESTED LOOPS (Cost=37 Card=4 Bytes=376)
  24   23         NESTED LOOPS (Cost=37 Card=3 Bytes=264)
  25   24           NESTED LOOPS (Cost=34 Card=3 Bytes=246)
  26   25             NESTED LOOPS (Cost=22 Card=3 Bytes=189)
  27   26               NESTED LOOPS (Cost=19 Card=3 Bytes=153)
  28   27                 NESTED LOOPS (Cost=13 Card=3 Bytes=126)
  29   28                   NESTED LOOPS (Cost=13 Card=3 Bytes=117)
  30   29                     TABLE ACCESS (BY INDEX ROWID) OF 'USER_DATA_SET' (Cost=1 Card=1 Bytes=6)
  31   30                       INDEX (UNIQUE SCAN) OF 'PK_USER_DATA_SET' (UNIQUE)
  32   29                     TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_LANES' (Cost=12 Card=3 Bytes=99)
  33   32                       INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES' (NON-UNIQUE) (Cost=11 Card=3)
  34   28                   INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET' (UNIQUE)
  35   27                 TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT' (Cost=2 Card=20488 Bytes=184392)
  36   35                   INDEX (UNIQUE SCAN) OF 'IDX_CA_IMP_EXP' (UNIQUE) (Cost=1 Card=20488)
  37   26               TABLE ACCESS (BY INDEX ROWID) OF 'CURRENCY_VALUES' (Cost=1 Card=215 Bytes=2580)
  38   37                 INDEX (UNIQUE SCAN) OF 'PK_CURRENCY_VALUES'    (UNIQUE)
  39   25             TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_ITEMS' (Cost=4 Card=819508 Bytes=15570652)
  40   39               INDEX (RANGE SCAN) OF 'CAI_NONUNIQUE' (NON-UNIQUE) (Cost=3 Card=819508)
  41   24           INDEX (RANGE SCAN) OF 'PK_COMP_RATE_ACTIVITY' (UNIQUE) (Cost=1 Card=7 Bytes=42)
  42   23         INDEX (UNIQUE SCAN) OF 'PK_MSP_GROUP_ITEMS' (UNIQUE)




In Oracle9i
9i:SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------timizer_dynamic_sampling           integer     1
optimizer_features_enable            string      9.2.0
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_max_permutations           integer     2000
optimizer_mode                       string      CHOOSE
db_file_multiblock_read_count        integer     32

9i:SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
9i:SQL> set autotrace on exp
9i:SQL> @iq.sql
no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=491 Card=34 Bytes=3468)
   1    0   SORT (ORDER BY) (Cost=491 Card=34 Bytes=3468)
   2    1     HASH JOIN (Cost=489 Card=34 Bytes=3468)
   3    2       HASH JOIN (Cost=487 Card=34 Bytes=3264)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_ITEMS' (Cost=3 Card=1 Bytes=19)
   5    4           NESTED LOOPS (Cost=484 Card=34 Bytes=2856)
   6    5             NESTED LOOPS (SEMI) (Cost=382 Card=34 Bytes=2210)
   7    6               NESTED LOOPS (Cost=348 Card=34 Bytes=1938)
   8    7                 NESTED LOOPS (Cost=280 Card=34 Bytes=1632)
   9    8                   NESTED LOOPS (Cost=280 Card=34 Bytes=1530)
  10    9                     NESTED LOOPS (Cost=280 Card=222 Bytes=8658)
  11   10                       TABLE ACCESS (BY INDEX ROWID) OF 'USER_DATA_SET' (Cost=1 Card=1 Bytes=6)
  12   11                         INDEX (UNIQUE SCAN) OF 'PK_USER_DATA_SET' (UNIQUE)
  13   10                       TABLE ACCESS (FULL) OF 'COMP_AGREEMENT_LANES' (Cost=279 Card=222 Bytes=7326)
  14    9                     INDEX (UNIQUE SCAN) OF 'PK_MSP_GROUP_ITEMS' (UNIQUE)
  15    8                   INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET' (UNIQUE)
  16    7                 TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT' (Cost=2 Card=409754 Bytes=3687786)
  17   16                   INDEX (UNIQUE SCAN) OF 'IDX_CA_IMP_EXP' (UNIQUE) (Cost=1 Card=2)
  18    6               INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET_DEFN' (UNIQUE) (Cost=1 Card=819508 Bytes=6556064)
  19    5             INDEX (RANGE SCAN) OF 'CAI_NONUNIQUE' (NON-UNIQUE) (Cost=2 Card=1)
  20    3         TABLE ACCESS (FULL) OF 'CURRENCY_VALUES' (Cost=2 Card=215 Bytes=2580)
  21    2       INDEX (FULL SCAN) OF 'PK_COMP_RATE_ACTIVITY' (UNIQUE) (Cost=1 Card=7 Bytes=42)


Now I changed the db_file_multiblock_read_count=8 and ran
the same query.

9i:SQL> alter session set db_file_multiblock_read_count=8;
Session altered.
9i:SQL> @iq.sql
no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=541 Card=34 Bytes=34
          68)
   1    0   SORT (ORDER BY) (Cost=541 Card=34 Bytes=3468)
   2    1     HASH JOIN (Cost=538 Card=34 Bytes=3468)
   3    2       HASH JOIN (Cost=536 Card=34 Bytes=3264)
   4    3         NESTED LOOPS (SEMI) (Cost=533 Card=34 Bytes=2856)
   5    4           NESTED LOOPS (Cost=499 Card=34 Bytes=2584)
   6    5             NESTED LOOPS (Cost=397 Card=34 Bytes=1938)
   7    6               NESTED LOOPS (Cost=329 Card=34 Bytes=1632)
   8    7                 HASH JOIN (Cost=329 Card=222 Bytes=9324)
   9    8                   NESTED LOOPS (Cost=2 Card=1 Bytes=9)
  10    9                     TABLE ACCESS (BY INDEX ROWID) OF 'USER_DATA_SET' (Cost=1 Card=1 Bytes=6)
  11   10                       INDEX (UNIQUE SCAN) OF 'PK_USER_DATA_SET' (UNIQUE)
  12    9                     INDEX (FULL SCAN) OF 'PK_COMP_AGREEMENT_SET' (UNIQUE) (Cost=1 Card=1 Bytes=3)
  13    8                   TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_LANES' (Cost=325 Card=222 Bytes=7326)
  14   13                     BITMAP CONVERSION (TO ROWIDS)
  15   14                       BITMAP OR
  16   15                         BITMAP CONVERSION (FROM ROWIDS)
  17   16                           SORT (ORDER BY)
  18   17                             INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES' (NON-UNIQUE) (Cost=7)
  19   15                         BITMAP CONVERSION (FROM ROWIDS)
  20   19                           SORT (ORDER BY)
  21   20                             INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES_IMP' (NON-UNIQUE) (Cost=6)
  22    7                 INDEX (UNIQUE SCAN) OF 'PK_MSP_GROUP_ITEMS'   (UNIQUE)
  23    6               TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT' (Cost=2 Card=1 Bytes=9)
  24   23                 INDEX (UNIQUE SCAN) OF 'IDX_CA_IMP_EXP' (UNIQUE) (Cost=1 Card=2)
  25    5             TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_ITEMS' (Cost=3 Card=1 Bytes=19)
  26   25               INDEX (RANGE SCAN) OF 'CAI_NONUNIQUE' (NON-UNIQUE) (Cost=2 Card=1)
  27    4           INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET_DEFN' (UNIQUE) (Cost=1 Card=819508 Bytes=6556064)
  28    3         TABLE ACCESS (FULL) OF 'CURRENCY_VALUES' (Cost=2 Card=215 Bytes=2580)
  29    2       INDEX (FULL SCAN) OF 'PK_COMP_RATE_ACTIVITY' (UNIQUE) (Cost=1 Card=7 Bytes=42)


Specifically, When the db_file_multiblock_read_count=32, the COMP_AGREEMENT_LANES goes for a FULL TABLE SCAN,
and when the db_file_multiblock_read_count=8, it uses the INDEX SCAN. But in Oracle8i, when db_file_multiblock_read_count=32, it does the INDEX SCAN.
Basically the INDEX SCAN is faster in this case.

This is the query in particular causing the performance problem. The query is doing a index scan and the performance is good in 8i even though the db_file_multiblock_read_count=32.


To determine the optimal value, I did the following test. 

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select /*+ FULL(t) */ count(*) from dba_source t;

  COUNT(*)
----------
    787035

WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1787 p3=25
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1851 p3=25
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1931 p3=25
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=2027 p3=25

I have again ran the same query by setting the db_file_multiblock_read_count=25 and the performance still bad and the plan is same as db_file_multiblock_read_count=32.

Please advice. Should we try to change the query or should
we lower the db_file_multiblock_read_count to 8.

Thanks
Saradha
 

What is BITMAP INDEX CONVERSION

Saradha Bavanandam, April 21, 2003 - 5:32 pm UTC

Tom,

If you notice the EXECUTION PLAN, it say BITMAP CONVERSION
and can you please explain me in what situation this happens. We do not have any BITMAP INDEX at all.

13 8 TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_LANES' (Cost=325 Card=222 Bytes=7326)
14 13 BITMAP CONVERSION (TO ROWIDS)
15 14 BITMAP OR
16 15 BITMAP CONVERSION (FROM ROWIDS)
17 16 SORT (ORDER BY)
18 17 INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES' (NON-UNIQUE) (Cost=7)
19 15 BITMAP CONVERSION (FROM ROWIDS)
20 19 SORT (ORDER BY)
21 20 INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES_IMP' (NON-UNIQUE) (Cost=6)

Thanks
Saradha


Tom Kyte
April 21, 2003 - 10:42 pm UTC

you do not need a bitmap, it is a valid plan -- it is creating a bitmap index on the fly...


what are the time differences here do

alter session set the multi-block read count to 8
alter session set sql_trace=true;
run query
exit

do again but with 32 and post just the relevant portions of the tkprof

touch count in 9i R2

A reader, April 22, 2003 - 12:41 pm UTC

Tom, Your wonderful book explained the touch count concept. If I tried it in 9iR2, the x$bh has different columns. Is there a way that you can give the touch count demo in 9iR2? Thanks as always for your time.

Tom Kyte
April 22, 2003 - 9:32 pm UTC

works for me? what problem are you hitting exactly

Works!

A reader, April 23, 2003 - 4:24 pm UTC

Tom, I tried again. It works. Sorry for the confusion.

Just one clarification. Does it mean that if the block is not touched for some time, and if the space is needed for other blocks, this "untouched" block will be removed from the list, can i say, the same LRU list. Does Oracle still use the LRU list alogrithm in 9i and touch count is the way it is managed? Thanks. Your book is the best in the market. I have read it two times already.

Tom Kyte
April 23, 2003 - 8:14 pm UTC

correct, the less often the block is "touched" the more probable it will be removed over time from the cache itself.

Yes, the touch count algorithm is still in effect.

why the execution plan is different in 8i and 9i

Saradha Bavanandam, April 24, 2003 - 2:26 pm UTC

Tom,

I have taken the trace in 9i and in 8i where the two instanace are the same in everything except the oracle version. I ran this query but currently no matching data
available in both the instances and I ran the query with 
"no rows selected". In both the instances the 
db_file_multiblock_read_count=32.

My primary question is, the execution plan is totally look
different in 9i as compared to 8i. The performance in 8i was good for this query and it is bad in 9i instance.
Because in Oracle9i, for a table (COMP_AGREEMENT_LANES), the optimizer chooses the FULL TABLE SCAN but in 8i the same
query the optimizer choosess the INDEX SCAN.

I would like to verify first why the plan is so different
in 9i and 8i when the matching parameters in both the database are the same.

SELECT
    A.ID,
    CV.CURRENCY_RATE,
    I.BID_DRIVER_ID,
    CRA.ACTIVITY_ID,
    NVL(I.RANGE_DRIVER_ID, -1),
    NVL(MAX_DRIVER_VALUE, 0),
    NVL(MIN_DRIVER_VALUE, 0) ,
    I.COMP_VALUE,
    I.BILL_TYPE_ID
FROM COMP_AGREEMENT_LANES L,
    COMP_AGREEMENT A,
    CURRENCY_VALUES CV,
    COMP_AGREEMENT_SET CAS,
    MSP_GROUP_ITEMS MGI,
    COMP_RATE_ACTIVITY CRA,
    COMP_AGREEMENT_ITEMS I,
    USER_DATA_SET UDS
WHERE ( L.ORIG_LOCATION_ID = 3596
    OR L.DEST_LOCATION_ID = 3596)
    AND A.IMP_EXP = 'E'
    AND MGI.MSP_ID = 123
    AND L.MSP_COLLECTION_ID = MGI.MSP_GROUP_ID
    AND L.ORIG_PC_LOW IS NULL
    AND L.ORIG_PC_HIGH IS NULL
    AND L.DEST_PC_LOW IS NULL
    AND L.DEST_PC_HIGH IS NULL
    AND L.COMP_AGREEMENT_ID = A.ID
    AND L.COMP_AGREEMENT_SET_ID = CAS.ID
    AND UDS.CURRENCY_SET_ID = CV.CURRENCY_SET_ID
    AND UDS.id = 564
    AND CV.CURRENCY_ID = A.CURRENCY_ID
    AND EXISTS (
      SELECT * FROM COMP_AGREEMENT_SET_DEFN D
      WHERE A.ID = D.COMP_AGREEMENT_ID
      AND D.COMP_AGREEMENT_SET_ID = 377 )
    AND I.COMP_AGREEMENT_ID = A.ID
    AND CRA.BID_DRIVER_ID = I.BID_DRIVER_ID
ORDER BY I.BILL_TYPE_ID DESC

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      2.58       2.53          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.49       1.65       4467       4561          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      4.07       4.19       4467       4561          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=4561 r=4467 w=0 time=1659641 us)
      0   HASH JOIN  (cr=4561 r=4467 w=0 time=1659556 us)
      0    HASH JOIN  (cr=4561 r=4467 w=0 time=1658996 us)
      0     TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_ITEMS (cr=4561 r=4467 w=0 time=1658502 us)
      1      NESTED LOOPS  (cr=4561 r=4467 w=0 time=1658472 us)
      0       NESTED LOOPS SEMI (cr=4561 r=4467 w=0 time=1658450 us)
      0        NESTED LOOPS  (cr=4561 r=4467 w=0 time=1658442 us)
      0         NESTED LOOPS  (cr=4561 r=4467 w=0 time=1658436 us)
      0          NESTED LOOPS  (cr=4561 r=4467 w=0 time=1658428 us)
      0           NESTED LOOPS  (cr=4561 r=4467 w=0 time=1658421 us)
      1            TABLE ACCESS BY INDEX ROWID USER_DATA_SET (cr=2 r=0 w=0 time=223 us)
      1             INDEX UNIQUE SCAN PK_USER_DATA_SET (cr=1 r=0 w=0 time=123 us)(object id 139307)
      0            TABLE ACCESS FULL COMP_AGREEMENT_LANES (cr=4559 r=4467 w=0 time=1658175 us)
      0           INDEX UNIQUE SCAN PK_MSP_GROUP_ITEMS (cr=0 r=0 w=0 time=0 us)(object id 139300)
      0          INDEX UNIQUE SCAN PK_COMP_AGREEMENT_SET (cr=0 r=0 w=0 time=0 us)(object id 139243)
      0         TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT (cr=0 r=0 w=0 time=0 us)
      0          INDEX UNIQUE SCAN IDX_CA_IMP_EXP (cr=0 r=0 w=0 time=0 us)(object id 143072)
      0        INDEX UNIQUE SCAN PK_COMP_AGREEMENT_SET_DEFN (cr=0 r=0 w=0 time=0 us)(object id 139247)
      0       INDEX RANGE SCAN CAI_NONUNIQUE (cr=0 r=0 w=0 time=0 us)(object id 143071)
      0     TABLE ACCESS FULL CURRENCY_VALUES (cr=0 r=0 w=0 time=0 us)
      0    INDEX FULL SCAN PK_COMP_RATE_ACTIVITY (cr=0 r=0 w=0 time=0 us)(object id 139230)

********************************************************************************



In Oracle8i:
----------------------------------------
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.57       0.57          0          0          0           0
Execute      1      0.04       0.04          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.61       0.61          0          0          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 78

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY
      0   CONCATENATION
      0    HASH JOIN
      0     NESTED LOOPS
      0      HASH JOIN
      0       NESTED LOOPS
      0        NESTED LOOPS
      0         NESTED LOOPS
      0          NESTED LOOPS
      0           TABLE ACCESS BY INDEX ROWID USER_DATA_SET
      0            INDEX UNIQUE SCAN (object id 659595)
      0           TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_LANES
      0            INDEX RANGE SCAN (object id 661403)
      0          INDEX UNIQUE SCAN (object id 659554)
      0         INDEX UNIQUE SCAN (object id 659585)
      0        TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT
      0         INDEX UNIQUE SCAN (object id 661411)
      0       TABLE ACCESS FULL CURRENCY_VALUES
      0      TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_ITEMS
      0       INDEX RANGE SCAN (object id 661410)
      0     INDEX FULL SCAN (object id 659517)
      0    NESTED LOOPS
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS
      0        NESTED LOOPS
      0         NESTED LOOPS
      0          NESTED LOOPS
      0           TABLE ACCESS BY INDEX ROWID USER_DATA_SET
      0            INDEX UNIQUE SCAN (object id 659595)
      0           TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_LANES
      0            INDEX RANGE SCAN (object id 661405)
      0          INDEX UNIQUE SCAN (object id 659554)
      0         TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT
      0          INDEX UNIQUE SCAN (object id 661411)
      0        TABLE ACCESS BY INDEX ROWID CURRENCY_VALUES
      0         INDEX UNIQUE SCAN (object id 659538)
      0       TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_ITEMS
      0        INDEX RANGE SCAN (object id 661410)
      0      INDEX RANGE SCAN (object id 659517)
      0     INDEX UNIQUE SCAN (object id 659585)

This table (COMP_AGREEMENT_LANE) is joined with many other tables in the query. But if I take the portion of the query for this table alone and run in both 9i and 8i, the execution plan is the same.

For example,
SQL> select MSP_COLLECTION_ID, COMP_AGREEMENT_SET_ID, COMP_AGREEMENT_ID
  2  from COMP_AGREEMENT_LANES xx, COMP_AGREEMENT_SET yy
  3  where (xx.DEST_LOCATION_ID = 2430 OR xx.ORIG_LOCATION_ID = 2430)
  4  and xx.ORIG_PC_LOW IS NULL
  5  and xx.ORIG_PC_HIGH IS NULL
  6  and xx.DEST_PC_LOW IS NULL
  7  and xx.DEST_PC_HIGH IS NULL
  8* and xx.COMP_AGREEMENT_SET_ID = yy.ID

----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=60 Bytes=216
          0)

   1    0   CONCATENATION
   2    1     NESTED LOOPS (Cost=17 Card=3 Bytes=108)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_LANES
          ' (Cost=17 Card=3 Bytes=99)

   4    3         INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES' (NON
          -UNIQUE) (Cost=8 Card=3)

   5    2       INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET' (UNIQUE
          )

   6    1     NESTED LOOPS (Cost=17 Card=3 Bytes=108)
   7    6       TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENT_LANES
          ' (Cost=17 Card=3 Bytes=99)

   8    7         INDEX (RANGE SCAN) OF 'IX_COMP_AGREEMENT_LANES_IMP'
          (NON-UNIQUE) (Cost=8 Card=3)

   9    6       INDEX (UNIQUE SCAN) OF 'PK_COMP_AGREEMENT_SET' (UNIQUE
          )

I get the same execution plan in 8i as well as in 9i.
But in the actual query, this table's access path is totally different.

Thanks
Saradha 

Tom Kyte
April 24, 2003 - 3:09 pm UTC

I do not see any fetch calls in 8i -- why not?

Saradha Bavanadam, April 24, 2003 - 3:52 pm UTC

Tom,
I ran once again but by replacing one of the bind value to
A different one and you can see fetch calls in 8i as well as 9i but the plan is different.

In Oracle9i:
------------------------------
SELECT
A.ID,
CV.CURRENCY_RATE,
I.BID_DRIVER_ID,
CRA.ACTIVITY_ID,
NVL(I.RANGE_DRIVER_ID, -1),
NVL(MAX_DRIVER_VALUE, 0),
NVL(MIN_DRIVER_VALUE, 0) ,
I.COMP_VALUE,
I.BILL_TYPE_ID
FROM COMP_AGREEMENT_LANES L,
COMP_AGREEMENT A,
CURRENCY_VALUES CV,
COMP_AGREEMENT_SET CAS,
MSP_GROUP_ITEMS MGI,
COMP_RATE_ACTIVITY CRA,
COMP_AGREEMENT_ITEMS I,
USER_DATA_SET UDS
WHERE ( L.ORIG_LOCATION_ID = 2430
OR L.DEST_LOCATION_ID = 2430)
AND A.IMP_EXP = 'E'
AND MGI.MSP_ID = 123
AND L.MSP_COLLECTION_ID = MGI.MSP_GROUP_ID
AND L.ORIG_PC_LOW IS NULL
AND L.ORIG_PC_HIGH IS NULL
AND L.DEST_PC_LOW IS NULL
AND L.DEST_PC_HIGH IS NULL
AND L.COMP_AGREEMENT_ID = A.ID
AND L.COMP_AGREEMENT_SET_ID = CAS.ID
AND UDS.CURRENCY_SET_ID = CV.CURRENCY_SET_ID
AND UDS.id = 564
AND CV.CURRENCY_ID = A.CURRENCY_ID
AND EXISTS (
SELECT * FROM COMP_AGREEMENT_SET_DEFN D
WHERE A.ID = D.COMP_AGREEMENT_ID
AND D.COMP_AGREEMENT_SET_ID = 377 )
AND I.COMP_AGREEMENT_ID = A.ID
AND CRA.BID_DRIVER_ID = I.BID_DRIVER_ID
ORDER BY I.BILL_TYPE_ID DESC

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 2.71 2.68 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.72 2.68 0 12 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=12 r=0 w=0 time=4044 us)
0 HASH JOIN (cr=12 r=0 w=0 time=3995 us)
0 HASH JOIN (cr=12 r=0 w=0 time=3504 us)
0 NESTED LOOPS SEMI (cr=12 r=0 w=0 time=3079 us)
0 NESTED LOOPS (cr=12 r=0 w=0 time=3071 us)
0 NESTED LOOPS (cr=12 r=0 w=0 time=3068 us)
0 NESTED LOOPS (cr=12 r=0 w=0 time=3065 us)
18 HASH JOIN (cr=10 r=0 w=0 time=2900 us)
1 NESTED LOOPS (cr=3 r=0 w=0 time=393 us)
1 TABLE ACCESS BY INDEX ROWID USER_DATA_SET (cr=2 r=0 w=0 time=234 us)
1 INDEX UNIQUE SCAN PK_USER_DATA_SET (cr=1 r=0 w=0 time=123 us)(object id 139307)
1 INDEX FULL SCAN PK_COMP_AGREEMENT_SET (cr=1 r=0 w=0 time=119 us)(object id 139243)
18 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_LANES (cr=7 r=0 w=0 time=890 us)
18 BITMAP CONVERSION TO ROWIDS (cr=6 r=0 w=0 time=696 us)
1 BITMAP OR (cr=6 r=0 w=0 time=649 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=3 r=0 w=0 time=423 us)
12 SORT ORDER BY (cr=3 r=0 w=0 time=340 us)
12 INDEX RANGE SCAN IX_COMP_AGREEMENT_LANES (cr=3 r=0 w=0 time=141 us)(object id 143075)
1 BITMAP CONVERSION FROM ROWIDS (cr=3 r=0 w=0 time=136 us)
6 SORT ORDER BY (cr=3 r=0 w=0 time=122 us)
6 INDEX RANGE SCAN IX_COMP_AGREEMENT_LANES_IMP (cr=3 r=0 w=0 time=73 us)(object id 143073)
0 INDEX UNIQUE SCAN PK_MSP_GROUP_ITEMS (cr=2 r=0 w=0 time=82 us)(object id 139300)
0 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN IDX_CA_IMP_EXP (cr=0 r=0 w=0 time=0 us)(object id 143072)
0 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_ITEMS (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN CAI_NONUNIQUE (cr=0 r=0 w=0 time=0 us)(object id 143071)
0 INDEX UNIQUE SCAN PK_COMP_AGREEMENT_SET_DEFN (cr=0 r=0 w=0 time=0 us)(object id 139247)
0 TABLE ACCESS FULL CURRENCY_VALUES (cr=0 r=0 w=0 time=0 us)
0 INDEX FULL SCAN PK_COMP_RATE_ACTIVITY (cr=0 r=0 w=0 time=0 us)(object id 139230)

********************************************************************************



In Oracle8i:
---------------------
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.61 0.64 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 148 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.62 0.64 0 148 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 78

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 CONCATENATION
0 HASH JOIN
0 NESTED LOOPS
1 HASH JOIN
0 NESTED LOOPS
1 NESTED LOOPS
7 NESTED LOOPS
7 NESTED LOOPS
2 TABLE ACCESS BY INDEX ROWID USER_DATA_SET
2 INDEX UNIQUE SCAN (object id 659595)
7 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_LANES
7 INDEX RANGE SCAN (object id 661403)
12 INDEX UNIQUE SCAN (object id 659554)
6 INDEX UNIQUE SCAN (object id 659585)
0 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT
0 INDEX UNIQUE SCAN (object id 661411)
0 TABLE ACCESS FULL CURRENCY_VALUES
0 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_ITEMS
0 INDEX RANGE SCAN (object id 661410)
0 INDEX FULL SCAN (object id 659517)
0 NESTED LOOPS
13 NESTED LOOPS
13 NESTED LOOPS
13 NESTED LOOPS
13 NESTED LOOPS
13 NESTED LOOPS
13 NESTED LOOPS
2 TABLE ACCESS BY INDEX ROWID USER_DATA_SET
2 INDEX UNIQUE SCAN (object id 659595)
13 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_LANES
13 INDEX RANGE SCAN (object id 661405)
24 INDEX UNIQUE SCAN (object id 659554)
24 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT
24 INDEX UNIQUE SCAN (object id 661411)
24 TABLE ACCESS BY INDEX ROWID CURRENCY_VALUES
24 INDEX UNIQUE SCAN (object id 659538)
24 TABLE ACCESS BY INDEX ROWID COMP_AGREEMENT_ITEMS
24 INDEX RANGE SCAN (object id 661410)
24 INDEX RANGE SCAN (object id 659517)
0 INDEX UNIQUE SCAN (object id 659585)
12 INDEX UNIQUE SCAN (object id 659526)

********************************************************************************
The count of rows in both the databases are the exactly same.

Thanks
Saradha

Tom Kyte
April 24, 2003 - 4:20 pm UTC

and the 8i database used the wrong plan - now you are confusing me.

The 9i plan above is "correct" and much better then the 8i plan was. The parse took longer -- but it was a hard parse. run it again so it is soft and see.

12 logical IO's 9i.
148 logical IO's 8i.

9i wins.


What else

Saradha Bavanandam, April 24, 2003 - 5:30 pm UTC

Tom,

We have tested this query in another 9i instance
where we have these same set of tables with the same
number of rows and interestingly the execution plan is
much much similar to the one in 8i.

So, it comes down to only this 9i instance that is behaving differently. The tables, indexes are created, populated and analyzed exactly the same way in both the instance.
The parameters are all the same.

What else I should check in this 9i instance which is causing this performance problem.

Thanks
Saradha

Tom Kyte
April 24, 2003 - 6:20 pm UTC

umm, it seems to be running faster?

consistent gets

saradha bavanandam, April 24, 2003 - 6:05 pm UTC

Tom,

The consistent gets is different in 9i and 8i instances for
a table that has exactly the same number of rows (actuall it is exactly the same data) I repeated
the following for a number of time it comes out the same. This the table I am actually having problem with where the access path is FULL TABLE in 9i and INDEX SCAN in 8i. Can
you please read the output and lead me anything from there.

In Oracle8i:
SQL> SELECT COUNT(*) FROM comp_agreement_lanes;
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       4520  consistent gets
          0  physical reads
          0  redo size
        478  bytes sent via SQL*Net to client
        438  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

In Oracle9i instance:
SQL> SELECT COUNT(*) FROM comp_agreement_lanes;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2744  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

May be is it something causing the execution plan to be different in 9i versus 8i.. Should I drop the table in both
the instance and recreate it and test the query to see if the plan is the same. 

Tom Kyte
April 24, 2003 - 6:22 pm UTC

I am curious why you want 9i to slow down? to do more work?



Comment on first explain plan

Johan Snyman, April 25, 2003 - 3:40 am UTC

The first explain plan comparison between 8i and 9i included the following parameters:

For 8i:
optimizer_features_enable string 8.1.7
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 80000 <- **
optimizer_mode string CHOOSE
optimizer_percent_parallel integer 0
db_file_multiblock_read_count integer 8

For 9i:
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000 <- **
optimizer_mode string CHOOSE
db_file_multiblock_read_count integer 32


Shurely the large difference in optimizer_max_permutations could affect the capability of 9i to consider enough options to find an optimal plan ? Maybe this is what makes them different ?

Tom Kyte
April 25, 2003 - 7:48 am UTC

as well the multiblock read count which at 32 makes 9i cost a full scan cheaper then 8i with a session of 8.

optimizer_max_permutations=80000

Saradha Bavanandam, April 25, 2003 - 9:40 am UTC

Tom,

Actually the db_file_multiblock_read_count parameter was set to 32 only in 8i instance. I have mistyped in my first posting as 8 in 8i. But it is actually 32 and that's why the whole concern is. Both the instance is set to 32 and why
the plan is different. With the FULL TABLE SCAN the perfromance is bad in 9i instance.

Also I have tried to set the optimizer_max_permutations=80000
and tried again but the plan did not change.

Any other direction or clue would be greatly appreciated.
Thanks
Saradha

Tom Kyte
April 25, 2003 - 11:19 am UTC

sigh, i must be totally missing something somewhere - unless you are changing all of the cuts and pastes -- the ONLY thing I've seen so far shows "9i's plan is better then 8i's"

Tell you what - if you can get it all together, provide the correct, accurate, concise information in the form of the init.oras and the tkprofs that demonstrate the issue AND i'm taking questions -- feel free to post it as a question. this is getting too far off topic here and at this point -- I don't trust the information above.

"Touch Count"

Robert, April 25, 2003 - 10:10 am UTC

[If I may...] Discussed in Tom's book on pages 78 and 79.

OS IO Buffer Size

Jack Mulhollan, April 25, 2003 - 3:54 pm UTC

I tried you test to try to determine the OS IO buffer size.  I couldn't set db_file_multiblock_read_count higher than 32.


SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16


SQL> alter session set db_file_multiblock_read_count = 1024;

Session altered.


SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     32


My db_block_size is 32k.


SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     32768


I did the 10046 trace and saw that Oracle was waiting to read 32 blocks (which makes sense).

=====================
PARSING IN CURSOR #3 len=49 dep=0 uid=21 oct=3 lid=21 tim=1051298646121978 hv=73
3395062 ad='8a617f00'
select /*+ full(a) */ count(*)
from temp_01_tb a
END OF STMT
PARSE #3:c=0,e=1004,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1051298646121969
BINDS #3:
EXEC #3:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1051298646122262
WAIT #3: nam='SQL*Net message to client' ela= 4 p1=1650815232 p2=1 p3=0
WAIT #3: nam='db file scattered read' ela= 26274 p1=113 p2=278406 p3=32
WAIT #3: nam='db file scattered read' ela= 19549 p1=113 p2=278438 p3=32
WAIT #3: nam='db file scattered read' ela= 18979 p1=113 p2=278470 p3=32

...

My questions:

1) Should I conclude that my OS IO Buffer Size is 1m (32 x 32K) or is there some other reason that I'm not able to set db_file_multiblock_read_count higher? 

2) I do mostly full scans of multi-gig tables (large batch environment).  Should I consider increasing my db_file_multiblock_read_count from 16 to 32?  

Thanks.
 

Tom Kyte
April 25, 2003 - 4:45 pm UTC

1) conclude away....
2) sure

execution plan 8i vs 9i

Saradha Bavanandam, April 28, 2003 - 6:27 pm UTC

Tom,

Sorry If I misled you on this question. But my question remains the same.

Why do the execution plan for a query drastically change in a 9i instance as compared to the 8i instance. The performance for this particular query is bad in 9i.

I have simplified the query from my original posting, in order to explain you better.

SQL executed in 9i and in 8i instance:
---------------------------------------
SQL> SELECT L.AMT_CALC_ID
  2    FROM AMT_CALC_OPR L
  3    WHERE ( L.GO_CAT_ID = 2430 OR L.DT_CAT_ID = 2430)
  4    AND L.GO_PACT_MIN IS NULL
  5    AND L.GO_PACT_MAX IS NULL
  6    AND L.DT_PACT_MIN IS NULL
  7    AND L.DT_PACT_MAX IS NULL
  8   AND EXISTS (  SELECT 1 FROM AMT_CALC_DESC D
  9                 WHERE L.AMT_CALC_ID = D.AMT_CALC_ID
 10*                AND D.AMT_CALC_SET_ID = 377 );

Execution plan in 8i instance:
-----------------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=4 Bytes=108)
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=10 Card=1 Bytes=27)
   3    2       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_IMP' (NON-UNIQUE) (Cost=9 Card=1)
   4    3         INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=2 Card=1 Bytes=8)
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=10 Card=1 Bytes=27)
   6    5       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_EXP' (NON-UNIQUE) (Cost=9 Card=1)


Execution plan in 9i instance:
------------------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=501 Card=222 Bytes=7770)
   1    0   NESTED LOOPS (SEMI) (Cost=501 Card=222 Bytes=7770)
   2    1     TABLE ACCESS (FULL) OF 'AMT_CALC_OPR' (Cost=279  Card=222 Bytes=5994)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=819508 Bytes=6556064)


So the access path in 8i is better and it is faster but in 9i it chooses the FULL TABLE SCAN on AMT_CALC_OPR table and the performance is very bad.

Now, I have modify the same query and take out the EXISTS correlated sub query from the main query and run this in 8i and in 9i instance. This time the ACCESS PATH is exactly the same. 

SQL Executed in 8i and in 9i instance:
--------------------------------------
SQL> SELECT L.AMT_CALC_ID
  2    FROM AMT_CALC_OPR L
  3    WHERE ( L.GO_CAT_ID = 2430 OR L.DT_CAT_ID = 2430)
  4    AND L.GO_PACT_MIN IS NULL
  5    AND L.GO_PACT_MAX IS NULL
  6    AND L.DT_PACT_MIN IS NULL
  7    AND L.DT_PACT_MAX IS NULL;

Execution plan in 8i:
---------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=58 Bytes=1566)
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=11 Card=3 Bytes=81)
   3    2       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_IMP' (NON-UNIQUE) (Cost=11 Card=3)
   4    1     INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR' (NON-UNIQUE) (Cost=11 Card=3 Bytes=81)


Execution plan in 9i:
----------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=57 Bytes=1539)
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=7 Card=3 Bytes=81)
   3    2       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_IMP' (NON-UNIQUE) (Cost=7 Card=3)
   4    1     INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR' (NON-UNIQUE) (Cost=7 Card=3 Bytes=81)


Again, I modify the main query and replace the EXISTS correlated sub query with a simple join and execute in 9i instance. This time it chooses the INDEX SCAN and the performance is good in 9i.

9:SQL>  SELECT L.AMT_CALC_ID
  2      FROM AMT_CALC_OPR L, AMT_CALC_DESC D
  3      WHERE ( L.GO_CAT_ID = 2430  OR  L.DT_CAT_ID = 2430)
  4      AND L.GO_PACT_MIN IS NULL
  5      AND L.GO_PACT_MAX IS NULL
  6      AND L.DT_PACT_MIN IS NULL
  7      AND L.DT_PACT_MAX IS NULL
  8      AND L.AMT_CALC_ID = D.AMT_CALC_ID
  9     AND D.AMT_CALC_SET_ID = 377;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=57 Bytes=1995)
   1    0   CONCATENATION
   2    1     NESTED LOOPS (Cost=10 Card=3 Bytes=105)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=7 Card=3 Bytes=81)
   4    3         INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_IMP'   (NON-UNIQUE) (Cost=7 Card=3)
   5    2       INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=1 Bytes=8)
   6    1     NESTED LOOPS (Cost=10 Card=3 Bytes=105)
   7    6       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR' (NON-UNIQUE) (Cost=7 Card=3 Bytes=81)
   8    6       INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=1 Bytes=8)


SQL> desc AMT_CALC_OPR
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 AMT_CALC_SET_ID                     NOT NULL NUMBER(9)
 GO_CAT_ID                                    NUMBER(9)
 GO_PACT_MIN                                  VARCHAR2(10)
 GO_PACT_MAX                                  VARCHAR2(10)
 DT_CAT_ID                                    NUMBER(9)
 DT_PACT_MIN                                  VARCHAR2(10)
 DT_PACT_MAX                                  VARCHAR2(10)
 PCOL_ID                             NOT NULL NUMBER(9)
 AMT_CALC_ID                         NOT NULL NUMBER(9)


SQL> desc AMT_CALC_DESC
 Name                                      Null?    Type
 ----------------------------------------- -------- ------
 AMT_CALC_SET_ID                     NOT NULL NUMBER(9)
 AMT_CALC_ID                         NOT NULL NUMBER(9)

SQL> SELECT index_name, uniqueness from dba_indexes where table_name='AMT_CALC_OPR';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
IX_AMT_CALC_OPR_IMP            NONUNIQUE
IX_AMT_CALC_OPR_EXP            NONUNIQUE
IX_AMT_CALC_OPR                NONUNIQUE

SQL> select column_name from dba_ind_columns where index_name='IX_AMT_CALC_OPR' order by column_position
COLUMN_NAME
-----------
GO_CAT_ID
DT_CAT_ID
PCOL_ID
GO_PACT_MIN
GO_PACT_MAX
AMT_CALC_ID
DT_PACT_MIN
DT_PACT_MAX


SQL> select column_name from dba_ind_columns where index_name='IX_AMT_CALC_OPR_EXP' order by column_position
COLUMN_NAME
-----------
GO_CAT_ID
PCOL_ID
GO_PACT_MIN
GO_PACT_MAX
AMT_CALC_ID

SQL> select column_name from dba_ind_columns where index_name='IX_AMT_CALC_OPR_IMP' order by column_position
  2  /
COLUMN_NAME
------------
DT_CAT_ID
PCOL_ID
DT_PACT_MIN
DT_PACT_MAX
AMT_CALC_ID

SQL> select index_name, uniqueness from dba_indexes where table_name='AMT_CALC_DESC'
  2  /

INDEX_NAME                     UNIQUENES
------------------------------ ---------
XIF973AMT_CALC_DESC            NONUNIQUE
PK_AMT_CALC_DESC               UNIQUE

SQL> select column_name from dba_ind_columns where index_name='PK_AMT_CALC_DESC' order by column_position
  2  /

COLUMN_NAME
-----------------
AMT_CALC_SET_ID
AMT_CALC_ID

SQL> SELECT COUNT(*) FROM AMT_CALC_OPR
/
  COUNT(*)
----------
    819508

SQL> SELECT COUNT(*) FROM AMT_CALC_DESC
/
  COUNT(*)
----------
    819508

SQL> SELECT COUNT(*) FROM AMT_CALC_OPR 
2 WHERE DT_CAT_ID IS NULL
/
  COUNT(*)
----------
    426258

SQL> SELECT COUNT(*) FROM AMT_CALC_OPR 
2 WHERE GO_CAT_ID IS NULL
/

  COUNT(*)
----------
    393250


One more time, I made a simple adjustment to the query. Here I take out the OR condition in the query and run the same query in 9i and in 8i. Each chooses a different INDEX to solve this query.

SQL executed in 9i and in 8i database:
--------------------------------------- 
SQL> SELECT L.AMT_CALC_ID
  2    FROM AMT_CALC_OPR L
  3    WHERE ( L.GO_CAT_ID = 2430 )
  4    AND L.GO_PACT_MIN IS NULL
  5    AND L.GO_PACT_MAX IS NULL
  6    AND L.DT_PACT_MIN IS NULL
  7    AND L.DT_PACT_MAX IS NULL
  8   AND EXISTS (  SELECT 1 FROM AMT_CALC_DESC D
  9                 WHERE L.AMT_CALC_ID = D.AMT_CALC_ID
 10*                AND D.AMT_CALC_SET_ID = 377 );


Execution plan in 8i:
---------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=3 Bytes=72)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR_EXP' (Cost=10 Card=3 Bytes=72)
   2    1     INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_EXP' (NON-UNIQUE) (Cost=9 Card=3)
   3    2       INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=2 Card=1 Bytes=8)


Execution plan in 9i:
---------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=57 Bytes=1824)
   1    0   NESTED LOOPS (SEMI) (Cost=64 Card=57 Bytes=1824)
   2    1     INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR' (NON-UNIQUE) (Cost=7 Card=57 Bytes=1368)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=819508 Bytes=6556064)

So, the 8i optimizer had chosen IX_AMT_CALC_OPR_EXP index
and the 9i optimizer had chosen IX_AMT_CALC_OPR index scan.

The data on both the instance is exactly the same. The common initilization parameters in both the instances are  same.

Finally, why the execution plan and the performace are different and bad in 9i instance.

SQL> SELECT L.AMT_CALC_ID
  2    FROM AMT_CALC_OPR L
  3    WHERE ( L.GO_CAT_ID = 2430 OR L.DT_CAT_ID = 2430)
  4    AND L.GO_PACT_MIN IS NULL
  5    AND L.GO_PACT_MAX IS NULL
  6    AND L.DT_PACT_MIN IS NULL
  7    AND L.DT_PACT_MAX IS NULL
  8   AND EXISTS (  SELECT 1 FROM AMT_CALC_DESC D
  9                 WHERE L.AMT_CALC_ID = D.AMT_CALC_ID
 10*                AND D.AMT_CALC_SET_ID = 377 );


Thanks
Saradha
 

Tom Kyte
April 28, 2003 - 9:14 pm UTC

tell you what, when I'm taking questions -- feel free. this is too hard here to follow.

execution plan in 8i vs 9i

Saradha Bavanandam, April 29, 2003 - 11:17 am UTC

Tom,

The performance of the following particular query is very bad in Oracle9i instance. The same query works fine in Oracle8i instnace. The oracle9i and the oracle8i instances are very very similar.

SQL> SELECT L.AMT_CALC_ID
  2    FROM AMT_CALC_OPR L
  3    WHERE ( L.GO_CAT_ID = 2430 OR L.DT_CAT_ID = 2430)
  4    AND L.GO_PACT_MIN IS NULL
  5    AND L.GO_PACT_MAX IS NULL
  6    AND L.DT_PACT_MIN IS NULL
  7    AND L.DT_PACT_MAX IS NULL
  8   AND EXISTS (  SELECT 1 FROM AMT_CALC_DESC D
  9                 WHERE L.AMT_CALC_ID = D.AMT_CALC_ID
 10*                AND D.AMT_CALC_SET_ID = 377 );

8i-SQL > SET AUTOTRACE ON EXP
8i-SQL > @qry1
...
18 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=4 Bytes=108)
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=10 Card=1 Bytes=27)
   3    2       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_IMP' (NON-UNIQUE) (Cost=9 Card=1)
   4    3         INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=2 Card=1 Bytes=8)
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=10 Card=1 Bytes=27)
   6    5       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_EXP' (NON-UNIQUE) (Cost=9 Card=1)


9i-SQL > SET AUTOTRACE ON EXP
9i-SQL > @qry1
...
18 rows selected.

9i:SQL>

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=501 Card=222 Bytes=7770)
   1    0   NESTED LOOPS (SEMI) (Cost=501 Card=222 Bytes=7770)
   2    1     TABLE ACCESS (FULL) OF 'AMT_CALC_OPR' (Cost=279  Card=222 Bytes=5994)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=819508 Bytes=6556064)


Somehow, the combination of OR and EXISTS caluse in the query is what causing this performance problem in Oracle9i instance. To prove my argument if I take out the EXISTS caluse and make it as a regular join, then I get a good performance in 9i. Or if I split the query with UNION ALL instead of OR condition then also I get a good performance.

9i-SQL> get qry1_modified
  1  SELECT
  2     L.AMT_CALC_ID
  3  FROM AMT_CALC_OPR L, AMT_CALC_DESC D
  4  WHERE ( L.GO_CAT_ID = 2430
  5      OR L.DT_CAT_ID = 2430)
  6      AND L.GO_PACT_MIN IS NULL
  7      AND L.GO_PACT_MAX IS NULL
  8      AND L.DT_PACT_MIN IS NULL
  9      AND L.DT_PACT_MAX IS NULL
 10      AND L.AMT_CALC_ID = D.AMT_CALC_ID
 11*     AND D.AMT_CALC_SET_ID = 377
SQL> /

AMT_CALC_ID
-----------------

18 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=57 Bytes=1995)
   1    0   CONCATENATION
   2    1     NESTED LOOPS (Cost=10 Card=3 Bytes=105)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'AMT_CALC_OPR' (Cost=7 Card=3 Bytes=81)
   4    3         INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR_IMP'   (NON-UNIQUE) (Cost=7 Card=3)
   5    2       INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=1 Bytes=8)
   6    1     NESTED LOOPS (Cost=10 Card=3 Bytes=105)
   7    6       INDEX (RANGE SCAN) OF 'IX_AMT_CALC_OPR' (NON-UNIQUE) (Cost=7 Card=3 Bytes=81)
   8    6       INDEX (UNIQUE SCAN) OF 'PK_AMT_CALC_DESC' (UNIQUE) (Cost=1 Card=1 Bytes=8)

Is there any change in Oracle9i optimizer in respect to 
the combination of "OR" and "EXISTS" clause in the query.

Thanks
Saradha 

Tom Kyte
April 29, 2003 - 12:01 pm UTC

I'll try again:

tell you what, when I'm taking questions -- feel free. this is too hard here to
follow.

optimizer_max_permutations.

Kashif, June 10, 2003 - 6:18 pm UTC

Hi Tom,

Curious, why would there be such a drastic change from 8.1.7. to 9.2.0 of this parameter, i.e. from 80000 to 2000? Surely this, along with the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters is a parameter whose default value makes mostly no sense on OLTP type applications? What would be an optimal setting for this parameter? Thanks.

Kashif

Tom Kyte
June 10, 2003 - 8:07 pm UTC

why would this "surely not" be suitable for OLTP?

Well...

Kashif, June 10, 2003 - 10:00 pm UTC

There's the chance that the optimal plan might not be created in the 2000 permutations...

Kashif

P.S. I knew the "surely" word sounded a little too corny...

Tom Kyte
June 11, 2003 - 6:35 am UTC

or 80,0000

or 1 billion.

or 5.

but, especially in a OLTP system - characterized by queries not nearly as complex as say a data warehouse, 2,000 has been tested and deemed more then sufficient. It can help to cut down on parse time as well -- which in a poorly coded OLTP system may well account for more time then the actual execute phase of the query itself.



Dave, June 11, 2003 - 5:07 pm UTC

Would you raise it back up from 2000 for a DW? Up to 80,000, for example? Is there a way ofchecking how many permutations were actually tried in order to determine the optimum plan?

Tom Kyte
June 12, 2003 - 8:27 am UTC

I would not raise it unless told to by support in response to a bug actually.

you can do a 10053 trace if you like but for something doing 2000 plans, the trace file would be, well, "large"

Experiencing the same problem

Nunzio Consiglio, July 24, 2003 - 3:46 pm UTC

I have review this thread and we are experiencing something similar. With a bit of a twist. We have moved from Enterprise 9.0.1 to Standard 9.2.0.3. The explain plan indicates that the script should in fact run much more effecient, but is brutialy slow and does not return. On the Enterprise edition the script begins to return rows within seconds.
I have investigate most if not all the areas indicated in this thread and have not resolved this issue.
I have set up a new server ( we are running window 2000) and installed the Standard version 9.2.0.1 from scratch with the same results.
I will say at this point I am a little frustrated.

Tom Kyte
July 24, 2003 - 4:33 pm UTC

have you tried /*+ FIRST_ROWS */ if what you want is "first rows" fast?



more information about full scan

Juancarlosreyesp@yahoo.com, August 26, 2003 - 12:07 pm UTC

Hi Tom,
In theory if you have all the table in memory, I think is better to use an index, because even in that situation it will read less blocks in memory.

But when you have a string that can be searched in any place
for example searchs like '%XX%'.
In that situation is better to kill al indexes to avoid a not optimal execution plan?

How does it really works, I ask how Oracle searches, it starts from the left corner an search the first XX,when Oracle finds it, then goes to the next record.
Which is the logic behind the '%' searches, to know how that searches could be optimized.
There is a way for example if I know the string is at the right to say oracle start from the right corner, or something like that.
:) Thank you

Tom Kyte
August 26, 2003 - 1:35 pm UTC

theory is wrong.

if Oracle were a single user database...
if there were no need for latches....
if blocks could be accessed instantly (eg: instead of rowids, indexes had pointers in ram)

maybe what you say is true.


it is expensive to access a block in memory. accessing many many blocks via an index typically involves accessing that block many many times -- instead of just ONCE.

goto hotsos.com and read the paper "are you still using cache hit ratios"

;) Thanks

A reader, August 26, 2003 - 3:19 pm UTC


are the index's block's retrieved to the buffer cache as well?

Ravi, October 04, 2003 - 7:27 am UTC

Is the data in the indexes stored as Blocks of memory as well?

2) In that case are they retrieved and placed in the MRU (most recently used) are of the Buffer Cache as well?

3) If so are they available for other processes for re-use as well?

4) Like packages, can we not have a process with which we can Load the indexes to the Buffer cache periodically so that the most frequently used are available in the Buffer Cache?



Tom Kyte
October 04, 2003 - 9:57 am UTC

yes, all data is treated "in blocks"

all data is created "equal", index blocks are treated no differently then table, rollback, etc.


the beauty of a buffer cache is that STUFF YOU USE stays there, STUFF YOU DON"T goes away (or never gets there in the first place!)

Hence, you do not *need* nor do you *want* to "load" things in there.

Keep pool

reader, October 04, 2003 - 11:38 am UTC

<quote> the beauty of a buffer cache is that STUFF YOU USE stays there, STUFF YOU DON"T
goes away (or never gets there in the first place!)

Hence, you do not *need* nor do you *want* to "load" things in there. <quote>

Tom, do you recommend a keep pool to be configured? Looking at your response above, you don't recommend it. Thanks.

Tom Kyte
October 04, 2003 - 1:54 pm UTC

it is a knob that can be used for super fine tuning

after everything else is done.

if you are doing this -- this means you've verified your architecture is correct and "best", that your code is written properly, that you don't have SQL to 100's or 1000's of LIOs that should be doing 1's or 10's of LIO's.


I don't really see where i said anything negative about a keep pool -- i was basically saying "don't try to second guess the system, let it work the way it works, you might be surprised"


Possible Index-ing causing Performance Issues

Denise, December 03, 2003 - 11:43 am UTC

Tom

We recently upgraded our application software to a newer version.

We are experiencing severe performance issues with one of the functionalities..specifically the Mailing Module.

We do large Mailing campaigns twice monthly and each campaign solicits using different criteria.

When we do not specify criteria from the Interest Table
the Mailing Process performs with no problems.

When we assign exclusion codes based on Interest Code 
criteria the Mailing process just "hangs out there" and
never completes..I have to terminate the job in the job_queue then kill the session.

I have attached the code that gets generated through the
software module..I then wrote a SQL statement querying a
sample data of what the module code is supposed to do.
I set the autotrace traceonly explain on and produced the
following results.

Can you decipher anything from the results?

thanks
************************************************************
 insert into NATIONAL.mailing (timestamp,idnumber,first,
     middle,last,cfirst,cmiddle,clast,title,salutation,suffix,
     recordtype,ctitle,csalutation,csuffix,familiar,cfamiliar,
     line1,line2,line3,city,state,zip,carrte,delivery_point,
     check_digit, company_name, usertext1, country_name,
     row_number, userid_add, fullname, email, random_no)
  select sysdate,prospect.idnumber,prospect.first,prospect.middle,prospect.last,prospect.cfirst,
     prospect.cmiddle,prospect.clast,t.title,s.salutation,prospect.suffix,
     prospect.recordtype,t1.title,s1.salutation,prospect.csuffix,prospect.familiar,
     prospect.cfamiliar, address.line1, address.line2, address.line3, address.city, address.state, address.zip,
     address.carrte, address.delivery_point, address.check_digit,
     address.company_name, substr(address.usertext1,1,60), c.name, rownum + 0,'NATIONAL',
     DECODE(prospect.recordtype, 'O', 
     SUBSTR(DECODE(s1.salutation, NULL, NULL, s1.salutation || ' ')||
     DECODE(prospect.cfirst, NULL, NULL, prospect.cfirst || ' ')||
     DECODE(prospect.cmiddle, NULL, NULL, prospect.cmiddle || ' ')||
     prospect.clast || prospect.csuffix, 1, 180), 
     SUBSTR(DECODE(s.salutation, NULL, NULL, s.salutation || ' ')||
     DECODE(prospect.first, NULL, NULL, prospect.first || ' ')||
     DECODE(prospect.middle, NULL, NULL, prospect.middle || ' ')||
     prospect.last || prospect.suffix, 1, 180)) FULLNAME, email.email, 
     dbms_utility.get_hash_value(to_char(dbms_utility.get_time)||prospect.last, 2, 1048576) 
  from prospect,address,email,salutations s,titles t,salutations s1,titles t1,countries c 
  where prospect.mailflag in ('Y') 
     and prospect.salutcode = s.salutcode(+) 
     and prospect.titlecode = t.titlecode(+) 
     and prospect.csalutcode = s1.salutcode(+) 
     and prospect.ctitlecode = t1.titlecode(+) 
     and prospect.idnumber = email.idnumber(+) 
     and (email.preference = 'Y' or email.preference IS NULL) 
     and prospect.idnumber = address.idnumber(+) 
     and (address.preference = 'Y' or address.preference IS NULL) 
     and address.country = c.code(+) 
     and prospect.mailflag = 'Y'
     and prospect.solicit = 'Y'
     and prospect.usercode1 in ( 'ACLJ')
     and prospect.usercode7 in ( 'A')
     and not exists(select i.idnumber 
from interest i where prospect.idnumber = i.idnumber and i.intcode in ('201' , '400' , '995' , '996' , '997' , '998' , 'DECD' , 'ATL' , 'EML' , 'NOLAST' , 'OFC', 'UDAD' , 'SF'))
     and not exists (select m2.idnumber from NATIONAL.mailing m2 where m2.idnumber = prospect.idnumber)
************************************************************

SQL> set autotrace traceonly explain

  1  select idnumber from prospect where
  2  NOT EXISTS(select i.idnumber from interest i where i.intcode in('995','996','DECD') and
  3* idnumber=i.idnumber)
SQL> /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'PROSPECT'
   3    1     CONCATENATION
   4    3       TABLE ACCESS (BY INDEX ROWID) OF 'INTEREST'
   5    4         INDEX (RANGE SCAN) OF 'INTEREST_INTCODE_INDEX' (NON-
          UNIQUE)

   6    3       TABLE ACCESS (BY INDEX ROWID) OF 'INTEREST'
   7    6         INDEX (RANGE SCAN) OF 'INTEREST_INTCODE_INDEX' (NON-
          UNIQUE)

   8    3       TABLE ACCESS (BY INDEX ROWID) OF 'INTEREST'
   9    8         INDEX (RANGE SCAN) OF 'INTEREST_INTCODE_INDEX' (NON-
          UNIQUE)













 

Tom Kyte
December 03, 2003 - 4:44 pm UTC

nope.

not even sure why we are looking at the query we are looking at ;)



I Think I just answered my own question......

Denise, December 03, 2003 - 12:03 pm UTC

Tom

I ran the sql trace on the server that contains the previous software version before the upgrade.
see the results:

  1  select idnumber from prospect where
  2  NOT EXISTS(select i.idnumber from interest i where i.intcode in('995','996','DECD') and
  3* idnumber=i.idnumber)
SQL> /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'PROSPECT'
   3    1     TABLE ACCESS (FULL) OF 'INTEREST'

************************************************************

I can't recall if it was here or in Chapter 7 of your book
where you explain the Block IO on Full Scans versus
Index Scans by individual blocks...etc where Full Scans
can be faster and better depending on the query criteria.

Please add your own feedback if I am on the correct path
here. I will need to pass this on to the software developers(with much trepidation!)

tata




 

Tom Kyte
December 03, 2003 - 4:59 pm UTC

well, look see if that index exists in the older release.

i went all of the way back to 7.1.6 and the RBO always used the index

did you identify that index use as being the issue?

Thanks Tom!

Denise, December 03, 2003 - 2:33 pm UTC

for letting me use your forum to figure out the indexing issue.

I read pages 277-278 in your book and the "light bulb"
upstairs switched on.

I got rid of the index and 'lo and behold' the Mailing
Module with exclude Interest Codes was completed in 1.25
minutes.

My boss and I plan to keep the 'dirty little secret'
to ourselves and not tell the software vendor that we
exterminated one of their stupid indexes.

;~)







Clarification...

Denise, December 04, 2003 - 1:17 pm UTC

sorry Tom....didn't mean an earlier version of Oracle.
We have Fundraising Application Software that runs on
Oracle. I meant the Application Software upgrade(where
the developers saw fit to fix things that weren't 'broken'
and we now have a host of problems with the newer version..
i.e. creating new indexes where not needed.)

I looked at the older version(that worked!) and then looked
at the newer version(that doesn't work!) and deleted an
index that was not necessary...and now everything WORKS!

I found the solution reading your book and reading the
previous posts on this thread...

the learning continues.....thanks





DBA_TABLES.USED_BLOCKS Vs # of. Blocks in Buffer

Sami, January 15, 2004 - 11:02 am UTC

Dear Tom,
SQL> l
  1  select table_name,blocks,
    empty_blocks,avg_row_len,num_rows,
    LAST_ANALYZED
  2  from dba_tables
  3* where table_name in ('COUNTRIES','LANGUAGES','USERSTATUS','USERTIERS','USERTYPES')
SQL> /
 
TABLE_NAME  BLOCKS EMPTY_BLOCKS AVG_ROW_LEN   NUM_ROWS LAST_ANALYZED
------------------ ------------ ----------- ---------- -----------------
COUNTRIES        5          509          32        242 10/17/03 18:00:40
LANGUAGES        4          510          39        141 01/04/04 12:25:44
USERSTATUS       2          512          74         24 01/04/04 13:15:28
USERTYPES        1          513          51          3 01/04/04 13:15:29
USERTIERS        1          513          52          6 01/04/04 13:15:29
COUNTRIES       10          504          46        242 01/04/04 12:07:29
LANGUAGES
 
7 rows selected.
 



The following query is from "Database Performance Tuning Guide and Reference" manual.(14-15)
under "Determining Which Segments Have Many Buffers in the Pool"

COLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999

  1  SELECT o.object_name, COUNT(1) number_of_blocks
  2  FROM DBA_OBJECTS o, V$BH bh
  3  WHERE o.object_id = bh.objd
  4  AND o.owner != 'SYS'
  5  AND o.object_name in ('COUNTRIES','LANGUAGES','USERSTATUS','USERTIERS','USERTYPES')
  6  GROUP BY o.object_name
  7* ORDER BY count(1)
SQL> /
 
OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
USERTIERS                                               2
USERTYPES                                               2
USERSTATUS                                              3
LANGUAGES                                              22
COUNTRIES                                              53


SQL> select count(*) from languages;
 
  COUNT(*)
----------
       141
 
SQL> select count(*) from countries;
 
  COUNT(*)
----------
       242
 


Dear Tom,

I have only 5 USED BLOCKS for countries table, 4 USED BLOCKS for languages table.But in buffer cache  22 BLOCKS for LANGUAGES and 53 BLOCKS for COUNTRIES.

Even if FTS happens it should have have max of 5 BLOCKS(for countries) and 4 BLOCKS(languages), 
Isn't it? Or Am i missing something here?

There no DML activity on languages and countries table after ANALYZE.

Kindly need your advise.
 

Tom Kyte
January 15, 2004 - 12:08 pm UTC

funny, their query is wrong -- oh well (should use data_object_id, ask the authors "hey, what happens if we move or truncate that table??)

but.... why don't you actually investigate this?  aren't you curious?  you could sort of look at the v$bh entries yourself and see what/why they are no?

you'll see different behaviors depending on whether you use assm, non-assm, this that and the other thing.  but -- if you just take a peek -- you might be able to see what you see and figure it out (i cannot make every possible guess here!)

ops$tkyte@ORA9IR2> create table abc ( x int ) tablespace assm;
 
Table created.
 
ops$tkyte@ORA9IR2> insert into abc values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> analyze table abc compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from abc;
 
         X
----------
         1
 
ops$tkyte@ORA9IR2> column ext format a15
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> SELECT o.data_object_id,
  2         (select blocks from user_tables where table_name = 'ABC' ) blocks,
  3             (select file_id || ',' || block_id || ',' || (block_id+blocks-1) from dba_extents where segment_name = 'ABC' and owner = user ) ext,
  4              bh.FILE# ,    bh.BLOCK#,bh.CLASS#, bh.STATUs
  5    FROM (select * from user_objects where object_name = 'ABC'  and rownum > 0) o,
  6             V$BH bh
  7   WHERE o.data_object_id = bh.objd
  8   order by file#, block#
  9  /
 
DATA_OBJECT_ID     BLOCKS EXT                  FILE#     BLOCK#     CLASS# STATU
-------------- ---------- --------------- ---------- ---------- ---------- -----
         34115          5 9,3217,3224              9       3217          8 xcur
         34115          5 9,3217,3224              9       3218          9 xcur
         34115          5 9,3217,3224              9       3219          4 xcur
         34115          5 9,3217,3224              9       3220          1 xcur
         34115          5 9,3217,3224              9       3221          1 xcur
         34115          5 9,3217,3224              9       3222          1 xcur
         34115          5 9,3217,3224              9       3223          1 xcur
         34115          5 9,3217,3224              9       3224          1 xcur
 
8 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table abc;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table abc ( x int ) tablespace non_assm;
 
Table created.
 
ops$tkyte@ORA9IR2> insert into abc values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> analyze table abc compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from abc;
 
         X
----------
         1
 
ops$tkyte@ORA9IR2> column ext format a15
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> SELECT o.data_object_id,
  2         (select blocks from user_tables where table_name = 'ABC' ) blocks,
  3             (select file_id || ',' || block_id || ',' || (block_id+blocks-1) from dba_extents where segment_name = 'ABC' and owner = user ) ext,
  4              bh.FILE# ,    bh.BLOCK#,bh.CLASS#, bh.STATUs
  5    FROM (select * from user_objects where object_name = 'ABC'  and rownum > 0) o,
  6             V$BH bh
  7   WHERE o.data_object_id = bh.objd
  8   order by file#, block#
  9  /
 
DATA_OBJECT_ID     BLOCKS EXT                  FILE#     BLOCK#     CLASS# STATU
-------------- ---------- --------------- ---------- ---------- ---------- -----
         34116          1 1,53473,53480            1      53473          4 xcur
         34116          1 1,53473,53480            1      53474          1 xcur
 
 

More info, since two users have same table names (languages,countries)

Sami, January 15, 2004 - 11:24 am UTC

Deat Tom,
Included OWNER name in the above query for more clarity.

SQL> SELECT o.owner,o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh
WHERE o.object_id = bh.objd
AND o.owner != 'SYS'
AND o.object_name in ('COUNTRIES','LANGUAGES','USERSTATUS','USERTIERS','USERTYPES')
GROUP BY o.owner,o.object_name
ORDER BY count(1)
  2    3    4    5    6    7    8  /
 
OWNER   OBJECT_NAME    NUMBER_OF_BLOCKS
------- -------------- ----------------
USER1   LANGUAGES                     2
TEST    USERTIERS                     2
TEST    USERTYPES                     2
TEST    USERSTATUS                    3
USER1   COUNTRIES                     6
TEST    LANGUAGES                    20
TEST    COUNTRIES                    47
 
7 rows selected.
 
SQL> 




SQL> l
  1  select owner,table_name,blocks,empty_blocks,avg_row_len,num_rows,LAST_ANALYZED
  2    from dba_tables
  3*   where table_name in ('COUNTRIES','LANGUAGES','USERSTATUS','USERTIERS','USERTYPES')
SQL> /
 
OWNER   TABLE_NAME  BLOCKS EMPTY_BLOCKS AVG_ROW_LEN   NUM_ROWS LAST_ANALYZED
------- ------------------ ------------ ----------- ---------- -----------------
USER1   COUNTRIES        5          509          32        242 10/17/03 18:00:40
TEST    LANGUAGES        4          510          39        141 01/04/04 12:25:44
TEST    USERSTATUS       2          512          74         24 01/04/04 13:15:28
TEST    USERTYPES        1          513          51          3 01/04/04 13:15:29
TEST    USERTIERS        1          513          52          6 01/04/04 13:15:29
TEST    COUNTRIES       10          504          46        242 01/04/04 12:07:29
USER1   LANGUAGES
 
7 rows selected.
 
SQL>  

Tom Kyte
January 15, 2004 - 12:10 pm UTC

(use data object id)


Evaluation of indexes

Sanjaya Balasuriya, March 10, 2004 - 7:56 am UTC

Hi Tom,

How can we evaluate the performence of a query with and without indexes ?
I know I can use 'set autotrace traceonly' to check a statement without actually running it ?

Is there a way that I can say run this statement without using indexes and run this one using indexes ?

Thanks in advance.



Tom Kyte
March 10, 2004 - 9:23 am UTC

HINTs are excellent for testing the performance of different plans.

max_io_size

Nadya, March 11, 2004 - 7:49 am UTC

"We never will read more then an extent at a time (eg: I initially did this test in a locally managed tablespace with 512k extents -- I read 64 8k blocks at a time, then realized..."
Tom, does it means that if, for example,
- db_block_size = 8K
- max_io_size=1M
- locally managed tablespace uniform extent 64K,
then in theory I can set db_file_multiblock_read_count=128,
but in practic I can never read more than 64K in a time?
If it is true, then must we choose extent equal or more than max_io_size?
Thanks.

Tom Kyte
March 11, 2004 - 1:25 pm UTC

correct -- if you have 64k extents, that is the biggest multi-block read.


You do NOT have to (nor want to) make your extents 1m just because that is the max io size.

Index access = no multi-block reads
Small tables = we would not read 1m anyway even in a full scan.

what is the max io if tablespace is autoallocate

Rob, May 05, 2004 - 10:15 pm UTC

Tom:

What is the max io size if the tablespace is autoallocate rather than uniform extent size?

Rob

Tom Kyte
May 06, 2004 - 7:27 am UTC

max IO size is 100% os dependent and doesn't have anything to do with autoallocate/uniform really.

SYSTEM_STATS and MBRC

Adrian Billington, July 06, 2004 - 9:00 am UTC

I've been playing with system statistics to get an approximation of achievable MBRC as follows:-

scott@test10g SQL> create table t
  2  as
  3     select * from dba_source;

Table created.

scott@test10g SQL>
scott@test10g SQL> analyze table t estimate statistics;

Table analyzed.

scott@test10g SQL>
scott@test10g SQL> DECLARE
  2     n1 NUMBER;
  3     n2 NUMBER := &1;
  4     n3 NUMBER;
  5     s1 VARCHAR2(30);
  6     d1 DATE;
  7     d2 DATE;
  8
  9  BEGIN
 10
 11     EXECUTE IMMEDIATE 'alter session set db_file_multiblock_read_count = ' || n2;
 12
 13     DBMS_STATS.GATHER_SYSTEM_STATS( 'Start' );
 14
 15     SELECT COUNT(*) INTO n1 FROM ( SELECT /*+ full (t) */ * FROM t );
 16
 17     DBMS_STATS.GATHER_SYSTEM_STATS( 'Stop' );
 18
 19     DBMS_STATS.GET_SYSTEM_STATS (
 20        status => s1,
 21        dstart => d1,
 22        dstop  => d2,
 23        pname  => 'MBRC',
 24        pvalue => n3
 25        );
 26
 27     DBMS_OUTPUT.PUT_LINE( 'Requested MBRC setting of ' || n2 ||
 28                           ' achieved actual MBRC of ' || n3 || ' blocks.' );
 29
 30  END;
 31  /
Enter value for 1: 8
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 8;
Requested MBRC setting of 8 achieved actual MBRC of 8 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL>
scott@test10g SQL>
scott@test10g SQL> /
Enter value for 1: 16
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 16;
Requested MBRC setting of 16 achieved actual MBRC of 15 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL> /
Enter value for 1: 32
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 32;
Requested MBRC setting of 32 achieved actual MBRC of 26 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL> /
Enter value for 1: 64
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 64;
Requested MBRC setting of 64 achieved actual MBRC of 40 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL> /
Enter value for 1: 64
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 64;
Requested MBRC setting of 64 achieved actual MBRC of 40 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL> /
Enter value for 1: 128
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 128;
Requested MBRC setting of 128 achieved actual MBRC of 57 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL> /
Enter value for 1: 128
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 128;
Requested MBRC setting of 128 achieved actual MBRC of 56 blocks.

PL/SQL procedure successfully completed.

scott@test10g SQL> /
Enter value for 1: 1024
old   3:    n2 NUMBER := &1;
new   3:    n2 NUMBER := 1024;
Requested MBRC setting of 1024 achieved actual MBRC of 55 blocks.

PL/SQL procedure successfully completed.

Regards
Adrian 

Tom Kyte
July 06, 2004 - 9:04 am UTC

is there a question? comment?

No, just a response

Adrian, July 07, 2004 - 4:03 am UTC

No question - just more of a comment to follow up some of the discussion on maximum achievable MBRCs. System stats just seem to be a simple method for getting a rough idea of MBRC without delving into trace files.

Looking back at my post, however, it does seem as though I cut myself off before actually making a point...

Regards
Adrian

Tom Kyte
July 07, 2004 - 8:25 am UTC

you'd have to or want to flush the buffer cache.

what you are seeing from that is the MBRC actually done, given the current state of the cache.


ops$tkyte@ORA9IR2> create or replace procedure p( n2 in number )
  2  authid current_user
  3  as
  4     n1 NUMBER;
  5     n3 NUMBER;
  6     s1 VARCHAR2(30);
  7     d1 DATE;
  8     d2 DATE;
  9
 10  BEGIN
 11
 12     EXECUTE IMMEDIATE 'alter session set db_file_multiblock_read_count = ' || n2;
 13
 14     DBMS_STATS.GATHER_SYSTEM_STATS( 'Start' );
 15
 16     SELECT COUNT(*) INTO n1 FROM ( SELECT /*+ full (t) */ * FROM t );
 17
 18     DBMS_STATS.GATHER_SYSTEM_STATS( 'Stop' );
 19
 20     DBMS_STATS.GET_SYSTEM_STATS (
 21        status => s1,
 22        dstart => d1,
 23        dstop  => d2,
 24        pname  => 'MBRC',
 25        pvalue => n3
 26        );
 27
 28     DBMS_OUTPUT.PUT_LINE( 'Requested MBRC setting of ' || n2 ||
 29                           ' achieved actual MBRC of ' || n3 || ' blocks.' );
 30
 31          dbms_stats.delete_system_stats;
 32  END;
 33  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p( 64 )
Requested MBRC setting of 64 achieved actual MBRC of -1 blocks.
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter tablespace users offline;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> alter tablespace users online;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> exec p( 64 )
Requested MBRC setting of 64 achieved actual MBRC of 60 blocks.
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p( 64 )
Requested MBRC setting of 64 achieved actual MBRC of -1 blocks.
 
PL/SQL procedure successfully completed.
 
 

Puzzled

A reader, July 07, 2004 - 8:56 am UTC

Tom

Your results display somewhat predictable characteristics whereas mine do not display the same behaviour at all. My test *appears* to behaving the way I originally thought it should to determine a roughly maximum MBRC, but your test clearly demonstrates the cache's involvement in the stats. How can this be explained ? How large is your T table ?

Regards
Adrian

Tom Kyte
July 07, 2004 - 9:05 am UTC

it was 

ops$tkyte@ORA9IR2> select blocks from user_tables where table_name = 'T';
 
    BLOCKS
----------
     16089


I just setup a "larger than usual" buffer cache on my test machine and got it to the point where the full scan would be satisfied from the buffer cache.

Keep pools, recycle pools, alter table cache, the size of the cache, what else is going on in the system (who is using the cache) -- all of these will affect that number.

Not saying "bad trick, don't do it", just caveating that "your mileage will vary" due to influences outside of your control.  You'll see the same thing with my trace file trick!  if we are satisfied via cache reads -- there will be no p3's to view.  And the p3 values will be different sizes based on how much data is cached. 

That explains it !

Adrian, July 07, 2004 - 10:36 am UTC

Tom

I suspected as much. Thanks. I was always under the (false, probably) impression that you couldn't cache an entire table, else one FTS on a huge table would "flood" it.

Regards
Adrian

Tom Kyte
July 07, 2004 - 11:58 am UTC

depends on the pool it is it (keep)
depends on the settings it has (cache)
depends on the small table threshold (2% of the blocks in the buffer cache)

Single Block 1/O

Reader, July 22, 2004 - 10:45 am UTC

I understand that when Oracle does a fast full scan on an Index it is using MBRC .When doing Full scan then single block I/o but sorted output .

What happens in Range scan and Skip scan ?Is it single or Multi block IO ?

What effect does Clustering factor have on these scans (Range and Skip scan ) ?

Thanks

Tom Kyte
July 23, 2004 - 7:51 am UTC

range and skip have to be single block io -- range is just a mini "full scan" that stops.

clustering factor affects whether an index will be used to access the rows or whether a full scan of the table will be used.

Clustering Factor

reader, July 23, 2004 - 2:32 pm UTC

If the Clustering factor of the an index is good then even with a single block IO Oracle can get more than one row from a single block --TRUE/FALSE ?

If you don't mind can you please show it with an example .

Thanks

Tom Kyte
July 23, 2004 - 4:59 pm UTC

of course -- if there is more than one row on a single block -- of course (it should be obvious?) we can get more than one row from that block.

the clustering factor has nothing to do with the number of rows per block???

ops$tkyte@ORA9IR2> create table t ( x int primary key, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 0 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 2, 0 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 3, 0 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> declare
  2          type array is table of int index by binary_integer;
  3          l_x array;
  4          l_y array;
  5  begin
  6          select x, y bulk collect into l_x, l_y from t where x > 0;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.



SELECT x, y
from
 t where x > 0
                                                                                                                 
                                                                                                                 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           3
                                                                                                                 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 155     (recursive depth: 1)
                                                                                                                 
Rows     Row Source Operation
-------  ---------------------------------------------------
      3  TABLE ACCESS BY INDEX ROWID T (cr=2 r=0 w=0 time=66 us)
      3   INDEX RANGE SCAN SYS_C005887 (cr=1 r=0 w=0 time=43 us)(object id 39604)


3 rows...
cr=total of 1 for index range scan
cr=total of 2 once you add in table access by index rowid

2 LIOs, 3 rows.

 

Many doubts ...

Reader, July 26, 2004 - 12:32 pm UTC

1."range and skip have to be single block io -- range is just a mini "full scan" that stops "

When will range stop --is it index_stats.blks_gets_per_access ??
A full scan will not stop ?
Then this could be expensive if it is being read from the beginning .

2. "the clustering factor has nothing to do with the number of rows per block??? "
If the clustering factor is bad then oracle may have to read more number of blocks from the table to satisfy the condition --TRUE/FALSE.

3.Why do you have a pl/sql procedure in the above example ?

ops$tkyte@ORA9IR2> declare
2 type array is table of int index by binary_integer;
3 l_x array;
4 l_y array;
5 begin
6 select x, y bulk collect into l_x, l_y from t where x > 0;
7 end;
8 /

PL/SQL procedure successfully completed.

4. If I do tkprof < trace File > output sys=no
it is not giving me the row source operation at all .

Any ideas ?

Thanks



Tom Kyte
July 26, 2004 - 1:27 pm UTC

1) range stops when ranges can stop:

select * from t where x between 1 and 100;

will stop range scanning before

select * from t where x between 1 and 100000;

will... A range scan searches the branch blocks to find the first leaf block with the "low value of the range" and range scans leaf blocks one by one till it hits the "hi value of the range"

2) correct, cluster factor is a measure of how many LIO's it will be taking -- how expensive the range scan would be.

3) i wanted a single fetch call to the database to demonstrate the LIO's involved. sqlplus always "over fetches by one" and would incurr more work than needed.

4) you need to exit sqlplus before running tkprof, to get the stat records all written out.

over fetches by one

Reader, July 27, 2004 - 9:18 am UTC

"i wanted a single fetch call to the database to demonstrate the LIO's involved. sqlplus always "over fetches by one" and would incurr more work than
needed."

1.Why does sqlplus do an extra fetch ?

2.Can we see this in tkprof ?

3.why does pl/sql do in a in single fetch ?

Thanks again !


Tom Kyte
July 27, 2004 - 9:30 am UTC

1) because it does, no big deal.

2) yes, that is why I used plsql!

3) because i only fetched once. I told it what to do.

db_file_multiblock_read_count value

Florin, February 20, 2005 - 4:51 am UTC

Hi Tom,

During a huge conversion I've changed the parameter db_file_multiblock_read_count from 16 to 128 and I didn't noticed any improvement. Is this because we need to change also some parameters in EMC and operation system (HP)to allow to read 1M ?
What are these parameters?

Tom Kyte
February 20, 2005 - 9:54 am UTC

well, during this "huge" conversation were you doing tons of really big full scans from disk?

and did you have a really huge conversion timing from the same hardware/set of data with which to compare?


What you want to do to see if this will affect you is:

alter session set db file multiblock read count "low"
do some full scans of data you KNOW to be unbuffered (eg: even the OS buffer isn't buffering them, best on RAW or forcedirectio mounted file systems or ASM managed storage.


alter the read count "high" and repeat


now compare.


but again, unless you were doing massive full scans, one would not expect any change from this and most people for whatever reason, during a 'conversion' take the slow by slow approach and have lots of "for x in select loop process, lookup, convert, update end loop" -- they might have a full scan but it is so overwhelmed by the slow by slow processing on the inside that making the full scan go infinitely fast (0.00 seconds) would have no measured impact on overall run time (the time is in the loop, not the loop itself)

Quick follow-up on the OPTIMIZER_MAX_PERMUTATIONS parm

Gary Wicke, March 07, 2005 - 12:29 pm UTC

Hi Tom

I have a quick follow-up question on the optimizer_max_permutations parameter.

I am working in a DW environment on test and production servers both running Oracle 9.2.0.4 on AIX.

I have a pretty substantial query that ran in less than a minute on the test server but blew out the TEMP tablespace on the production server even after I created a new TEMP table and gave it over twice as much room as was on the test server (I actually upped it to 50GB just to see if it would finish, it didn't).

THe two servers produced different execution plans as well (as one might expect).

We looked at the system parameters and the only one we found that was different was OPTIMIZER_MAX_PERMUTATIONS. The test server was set to 2000 and the production server was set to 80000 (unlimited).

When we altered the session on the production server and set the parm to 2000, the query ran in less than a minute.

I am very curious as to why we saw the drastic drop in performance (it filled the TEMP tablespace and died) with the 80000 value in that parm. I would have expected a 'better' plan with the optimizer given that many more iterations to work with.


Such was not the case.

So my question, finally I know, is what has been oyur experience with that parameter and why would you guess it had such a drastic negative impact on the execution plan?

Many thanks for your tireless efforts to educate us.

I look forward to the 2nd edition of Expert...

Gary

Tom Kyte
March 07, 2005 - 7:56 pm UTC

do you have Effective Oracle by Design -- I cover the 'magic' of this parameter and the difference 1 value can make - 79,999 vs 80,000

(anything below 80k invokes a new join order optimization-- 80k the old routine, 9i defaults to the new one)

Help on overhead from indexes

Sanjaya Balasuriya, March 15, 2005 - 4:21 am UTC

Hi Tom,

I have a bunch of developers who are strictly against indexes.
They actually ask ways that they can tune their SQL statements without using any indexes.
Their argument is that "indexes slows down insert and update operations".

I know that even though every indexes has a negative impact on inserts and updates, selects are hugely benefited from indexes.

Can you show me some examples to use to convince the developers; "indexes are not evil" ?

Thanks.

Tom Kyte
March 15, 2005 - 8:15 am UTC

oh my.

do they know that sql databases are slower than a flat file too? (don't tell them)

and that by not using assembler, they are impacting the maximum performance of their code.

and that by running a gui, their machine is many times slower than if they just used a character mode telnet style window.

and that by using any editor other then sed, they are consuming unneccessary resources.

and they better NEVER use the index in the back of the book -- because by doing so they are promoting the use of indexes in books and indexing a book slows down the book authoring process (i know, it can add litterally weeks to the end of the book writing process, those pesky indexes)

give me a break, are they serious? this has to be a fake question. How to convince them? don't convince them -- take control. seriously. how to convince someone of the obvious...

so, how are they doing primary/unique constraints?


load up a table, with a primary key, and access a record with and without an index. it should be "obvious"

MBRC - Multi Block read Count & Block Size

A reader, June 28, 2005 - 4:21 pm UTC

Tom,
I am on HP UX. OS IO Buffer size is 1m. Block Size is 16K. We have set MBRC at 32. I know we can go to 64.
Would having a Huge table with 32K Block size help us, as against setting MBRC to 64. Will they not have same effect? Any input on having multi block objects? And any input on 32K block table?
Thanks.

Tom Kyte
June 28, 2005 - 5:18 pm UTC

do you full scan a lot?

are you experience large db file scattered read waits?

A reader, June 28, 2005 - 6:36 pm UTC

This being more of a DSS, we do scatterd read. But still Index reads predominate.(25:1)
Thanks.

Tom Kyte
June 28, 2005 - 8:21 pm UTC

which doesn't answer either of my questions....

A reader, June 28, 2005 - 10:03 pm UTC

We do not do full scan a lot. We do 1 Full scan for about 25 index scans. We do not see large db file scattered read waits.
I do not know how to find how many rows we get per scans.

Tom Kyte
June 29, 2005 - 7:56 am UTC

so, why bother tuning things based upon making full scan performance different?

You don't full scan
You aren't waiting on full scan related waits

so why are you tuning based on full scans?

Tuning a full tablescan

Aru, September 08, 2005 - 7:30 pm UTC

Hi Tom,

please can you explain, that when one goes
select count(*) from emp;
What exactly does oracle do to get you the answer to the query? What exactly is it doing to count the number of rows and how does it get the information? i.e- internally.
Regards,
Aru

Tom Kyte
September 08, 2005 - 7:37 pm UTC

run an explain plan.

using the RBO, it will read all blocks that were under the high water mark of the table segment. If the table had 1000000 rows in it, but has 0 now -- it will take about as long to find out "0" as it did 1000000


using the CBO, if any index exists on a column that is defined NOT NULL, you will see an index fast full scan likely and all index blocks will be read instead of the table.

Count(*)

Aru, September 08, 2005 - 9:39 pm UTC

Hi Tom,
Thanks for that,

We have a table :-
SQL> desc attachments_blob;
 Name                              Null?    Type
 -----------------                 -------  -----------
 ATTACHMENTS_DBID                            NUMBER(10)
 ENTITY_DBID                                 NUMBER(10)
 ENTITY_FIELDDEF_ID                          NUMBER(10)
 DATA                                         LONG RAW

There is a UNIQUE INDEX ATTACHMENTS_BLOB ON ATTACHMENTS_BLOB(ATTACHMENTS_DBID)

There are 12000 rows in the tables.

The query takes about 200 seconds to execute. May be because of the blob. 
Explain plan shows that it does a full tablescan.
Does not use the unique index (perhaps a result of the column not being NOT NULL).

Please can you suggest where I should start tuning the
query?

Thanks,
Regards,
Aru.
 

Tom Kyte
September 09, 2005 - 6:41 am UTC

that is NOT a blob, that is a long raw and is stored inline in the table itself (all of the long raw data is stored in the table, with a blob it would not be - things over 4000 bytes would definitely be stored out of line)


I start tuning count(*) queries by *removing them*, they are hardly necessary.


but in your case, just put a primary key on. it'll count the index then (assuming the use of the CBO that is)

Does adding index help here

A reader, September 21, 2005 - 8:44 pm UTC

Tom,

The table in question has around 10,000 records in 2 8K blocks. Only one row will be fetched by the query accessing this table (Or fair to say <= 2 rows)

Does adding index will help in this tiny table.


Tom Kyte
September 22, 2005 - 1:21 pm UTC

how do you get 10k records in 16k?

A reader, September 22, 2005 - 6:04 pm UTC

Tom,

Sorry for the typo.

Its 100 rows in 2 8K blocks.

Does adding index will help here.

Output returned by the query will be atleast 1 and atmost 2 rows.

Tom Kyte
September 22, 2005 - 9:44 pm UTC

sure can:

ops$tkyte@ORA10GR1> /*
ops$tkyte@ORA10GR1> drop table t;
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create table t ( x int, y int );
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t select rownum, rownum from all_objects where rownum <=50;
ops$tkyte@ORA10GR1> alter table t minimize records_per_block;
ops$tkyte@ORA10GR1> insert into t select rownum, rownum from all_objects where rownum <=50;
ops$tkyte@ORA10GR1> create index t_idx on t(x);
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
ops$tkyte@ORA10GR1> */
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> set autotrace traceonly
ops$tkyte@ORA10GR1> select /*+ full(t) */ * from t where x = 42;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=12)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=2 Bytes=12)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

ops$tkyte@ORA10GR1> select * from t where x = 42;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=12)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=2 Bytes=12)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=2)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        512  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

ops$tkyte@ORA10GR1> set autotrace off



ops$tkyte@ORA10GR1> select count(distinct dbms_rowid.rowid_block_number(rowid) ) from t;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                  2



(5 for full scan, 4 for index on manual segment space management - in this WORST case where both rows are fetched and they are on different blocks -- above was using ASSM) 

Thanks

A reader, September 22, 2005 - 11:54 pm UTC


PK, Index or IOT

John Gilmore, September 23, 2005 - 6:42 am UTC

Is there any point having an index on a small table if it contains no more than one physical block of data?

For example, the table:

dept (
dept_no varchar2 ( 3) not null,
dept_desc varchar2 (30) not null));

stores details of the codes and descriptions of departments. The column dept_no would be an indexed foreign key column in several other tables.

As the entire table would be available in one block, is there any performance advantage in having a PK constraint or index on the column dept.dept_no? In this case would anything be gained by implementing dept as an IOT?

Tom Kyte
September 23, 2005 - 9:29 am UTC

(hint, we showed how to see this......)


using ASSM:

ops$tkyte@ORA10GR1> /*
ops$tkyte@ORA10GR1> drop table t;
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create table t ( x int, y int );
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t  values ( 42, 0 );
ops$tkyte@ORA10GR1> create index t_idx on t(x);
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
ops$tkyte@ORA10GR1> */
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> set autotrace traceonly
ops$tkyte@ORA10GR1> select /*+ full(t) */ * from t where x = 42;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=5)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=1 Bytes=5)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        466  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA10GR1> select /*+ index( t t_idx ) */ * from t where x = 42;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        470  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA10GR1> set autotrace off


Using manual segement space management:

ops$tkyte@ORA10GR1> set autotrace traceonly
ops$tkyte@ORA10GR1> select /*+ full(t) */ * from t where x = 42;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=5)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        466  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA10GR1> select /*+ index( t t_idx ) */ * from t where x = 42;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        470  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA10GR1> set autotrace off


<b>so, yes, it can - it can do less LIO...</b> 

Touch Count not showing up for DUAL

Thiru, October 07, 2005 - 2:39 pm UTC

Here is the query I ran to display touch counts:

SELECT TCH,FILE#,DBABLK,dummy
from sys.x$bh, (select dummy from dual)
where obj=(select data_object_id from dba_objects where object_name='DUAL'
and data_object_id is not null)
/

no rows selected

I wait for a few seconds and try but still does not show up the touch count.
exec dbms_lock.sleep(3.2);
/
no rows selected

As such there is no other activity on the database.
How do I go about getting a result?

Thanks


Tom Kyte
October 08, 2005 - 8:46 am UTC

You'll want to figure out why the first query isn't returning anything at all - it should. is the subquery not returning anything?

just ran on 10gr2, 10gr1, 9ir2 - similar results.

sys@ORA10GR2> select tch, file#, dbablk, DUMMY
2 from x$bh, (select dummy from dual)
3 where obj = (select data_object_id
4 from dba_objects
5 where object_name = 'DUAL'
6 and data_object_id is not null)
7 /

TCH FILE# DBABLK D
---------- ---------- ---------- -
10 1 2081 X
10 1 2082 X

sys@ORA10GR2>
sys@ORA10GR2> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.

sys@ORA10GR2> /

TCH FILE# DBABLK D
---------- ---------- ---------- -
11 1 2081 X
11 1 2082 X

sys@ORA10GR2> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.

sys@ORA10GR2> /

TCH FILE# DBABLK D
---------- ---------- ---------- -
12 1 2081 X
12 1 2082 X

sys@ORA10GR2> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.

sys@ORA10GR2> /

TCH FILE# DBABLK D
---------- ---------- ---------- -
13 1 2081 X
13 1 2082 X

sys@ORA10GR2>


Works in 9i and not in 10g

Thiru, October 11, 2005 - 10:06 am UTC

I was able to get the results in 9i. But when I tried it on 10g, no rows selected:

select data_object_id
from dba_objects
where object_name = 'DUAL'
and data_object_id is not null
/

DATA_OBJECT_ID
--------------
222

select dummy from dual
/

D
-
X

select tch, file#, dbablk, DUMMY
from x$bh, (select dummy from dual)
where obj = (select data_object_id
from dba_objects
where object_name = 'DUAL'
and data_object_id is not null)
/

no rows selected

select obj from x$bh where obj=222
/

no rows selected

select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production



Tom Kyte
October 11, 2005 - 3:37 pm UTC

sorry - I ran my tests on 10gr1, 10gr2

use something other than dual, see what you see then.


if you select DUMMY from dual - you should have blocks up there.

Worked after upgrading to 10gR2

Thiru, October 12, 2005 - 10:22 am UTC

sys@SURJAM> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


sys@SURJAM> select tch, file#, dbablk, DUMMY
2 from x$bh, (select dummy from dual)
3 where obj = (select data_object_id
4 from dba_objects
5 where object_name = 'DUAL'
6 and data_object_id is not null)
7 /

TCH FILE# DBABLK D
---------- ---------- ---------- -
1 1 2081 X
1 1 2082 X

sys@SURJAM> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.

sys@SURJAM> /

TCH FILE# DBABLK D
---------- ---------- ---------- -
2 1 2081 X
2 1 2082 X

I was not able to get the touch counts for any query before this upgrade.



How to get total blocks updated in a datafile

Manish Sharma, August 24, 2006 - 10:19 am UTC

Hi Tom,
For some reasons we are implementing OS level of block replication in some oracle database application, which would replicate only the changed oracle blocks.

We want to know how many oracle blocks gets updated/ changed in certain amount of time. This would help us in determining the amount of data being replicated over.

Is there any Oracle table or V$ views, which can tell us about this.

Thanks
Manish

Tom Kyte
August 27, 2006 - 7:53 pm UTC

that is NOT going to work - what are you using here. How do you plan on doing this.

How to get total blocks updated in a datafile

Manish Sharma, August 30, 2006 - 9:04 am UTC

hi Tom,
We are using some third party software Kaasha, which is going to read all the packets from the switch which is just before the SAN and it uses some alogrithms to check if more than one copy of same block & file is coming it would only use the latest one. Then it would send to remote SAN for writing.

This way we are not putting extra read load on SAN, like in case of SAN replication.

This we are doing it for DR site.

To get an idea of how much traffic each database would be generating, we are using blocks update information coming in file i/o stats in stats pack report * with block_size + redo size * 512 * no of log members + archive logs.

Please let me know your feedback on this or reasons why this won't work .

Thanks
Manish




Tom Kyte
August 30, 2006 - 9:17 am UTC

if you want the lowest bandwidth, most disk friendly solution - you would be using data guard.

When you insert a row, what happens?

we modify a database block (say 8k)

we modify say three index leaf blocks for three indexes on this table (3x8k). Assume no block splits (which would add another 3-4 modifications to the equation per index)

we modify undo block(s) (say just one)

we generate redo for the modifications
we generate redo for the modifications (duplicated, you multi-plex redo)

we archive that redo
(maybe twice)


You'll have to repeat all of that stuff.

Use dataguard - when you commit, we'll transmit the redo and make all of those modifications happen again.



You can look at v$filestat to see IO operations, but if you are doing this to "take it easy" on something, you are actually taking the "let us do more work than we need, many many times more work" path.

Just a minor clarification..

Satya, August 30, 2006 - 5:31 pm UTC

I agree with Tom. Especially as Data Guard comes bundled with Enterprise Edition.

I believe the poster (Manish Sharma) was referring to "Kashya" (www.kashya.com). They have a product/appliance, the KBX5000, that's a Fabric-based (out-of-band) replication solution. It ties in to various databases (Oracle, SQL Server etc..) and they have a module called CDP that specifically works with Oracle.


Oracle 9i _SMALL_TABLE_THRESHOLD

A reader, December 07, 2006 - 6:13 am UTC

Hi Tom,
Thank you for giving this great support.
i am confused about the _SMALL_TABLE_THRESHOLD parameter

after some search i found the following

in Oracle9i : up to this value Oracle will put the blocks in the buffer cache
when doing full table scan , and any tables with larger size Oracle will do direct path read

1)am i correct ?

2) what does "direct path read" mean ? is it the normal direct path read that we use or a specail
one ?

Thank you very much

Tom Kyte
December 07, 2006 - 12:41 pm UTC

don't be confused about undocumented parameters.

ignore them.



direct io is io that doesn't use the buffer cache, it just does direct reads.


no, you are not correct on your guess about the parameter, it controls the caching of the blocks (whether they are cached like a single block IO or cached as we cache full scan blocks) - not the method of IO.

Ravi, February 07, 2007 - 11:15 am UTC

Tom

We have a table with the following charactersitics on dba_segments(94720 blocks)

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
----------- ------------ ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
----------- ----------- ----------- ------------ ---------- ---------------
RELATIVE_FNO BUFFER_
------------ -------
ACME_DBA
ENTITLEMENT_INTERESTS
TABLE PACMEEDATA01
98 18242 775946240 94720 74 10485760
10485760 1 2147483645 0 1 1
545 DEFAULT


1 row selected.


It takes about 50 seconds to do a FTS, like 'Select count(*) from entitlement_interests'.
Looks like it reads about 50MB / second ((94720/50) * 8192)!
Now is that appear slow to you?
We have a 8192 bytes on db_block_size and 32 on db_file_multiblock_read_count.
Tom Kyte
February 07, 2007 - 6:48 pm UTC

that is pretty unreadable.

tkprof and and tracing are always very useful to see what you are waiting on....

A reader, February 07, 2007 - 11:27 am UTC

Please append to my last post

To add more info, this table has frequent inserts, but could also be updated, its a transaction table, it is never/rarely deleted.

Its PCTFREE is 10 and PCTUSED is 40 and chain_cnt = 595821, avg_row_ln = 68 and empty_blocks = 790.

Sequential reads vs. Scattered reads

Jens, February 08, 2007 - 8:36 am UTC

Tom,
In the HotSos document http://www.hotsos.com/e-library/abstract.php?id=16 "Why are Oracle¿s Read Events Named Backwards?", Jeff Holt Feb 2000 claims that the events "db file scattered read" and "db file sequential read" does not match to how the data is read from disk, but rather as to how the blocks read from disk are stored in memory.

db file scattered read - blocks are read into random memory areas
db file sequential read - blocks are read into contigous memory

He further says that both reads a file sequentially from disk.

Is this correct, or am I mixing things completely up here?

(Regarding the hotsos library: The documents are protected, so you need to register (freely) to get access to the documents, but there's a lot of good info, so its worth it.)

Jens
Tom Kyte
February 08, 2007 - 11:11 am UTC

I use this example in my talks frequently.

Sometimes what you know just ain't so.

it seems "obvious" that db file sequential read would be for "large multi- block reads" and db file scattered read would be "single block IO"

but it just ain't so.

db file scattered read - the wait event when we do multi-block IO. Why is it call scattered read then? Because the person that named it did the buffer cache part of the call. They got a bunch of blocks from disk (multi-block IO) and then SCATTERED THEM in the buffer cache.

db file sequential read - the wait event for single block IO, typically caused by "read index, read table, read index, read table".... Why sequential then? Because we sequentially read from index to table to index to table.....


multiblock read count

David, July 08, 2007 - 3:34 pm UTC

Following is From metalink note: 223117.1

Starting with Oracle10g Release 2 the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value
when this parameter is not set explicitly. This default value corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks, it will be set to a value that
is equal to the maximum I/O size that can be performed efficiently divided by the standard block size.

-- what os command oracle uses to find max i/o size? If you know could you share. Thanks.

A pretty nice example and explanation on index vs full table scan

Adam Fries, November 18, 2008 - 2:44 pm UTC

A pretty nice example and explanation on index vs full table scan.

http://ravivedala.blogspot.com/2008/11/how-oracle-decides-whether-to-use-index.html

Tom Kyte
November 18, 2008 - 6:27 pm UTC

Scott/Dave/Ravi/A reader/ and now even "Adam"

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

Please do not use this as a means to drive hits....

Index scan vs FTS

Arindam, September 25, 2010 - 2:16 pm UTC

Hi Tom,

I am new to this field. I wants to know the difference between Index scan and FTS. So my questions are:

1)
select * from test
with or without where clause can be a FTS. Isn't it?
2) Generally when we issue the query
select * from test
we can see FTS happening, even if we create index upon the table. Why is it so? Can we impose Index scan during issuing select * from test?
3) I assume index scan is better for a certain/small range of selection/insertion/update. Because it has to issue small I/Os for traversing from root to leaf. But during full scan it has to traverse from root to leaf many times which increases lots of I/O. That's why FTS is necessary during full scan. Please correct my understanding is incorrect.

Tom Kyte
September 27, 2010 - 11:44 am UTC

1) yes, a full table scan is always a possibility - regardless.

2) why would you want to read index, read table, read index, read table, read index, read table over and over and over again - when you could just:

read table (and be done with it)


why would you WANT an index - why do you think an index would be "a good thing"

3) not really sure what you are trying to convey there. why does it (what is "it") have to traverse from root to leaf many times?

That's why FTS is necessary during full scan

that is very circular of you. "why is a full scan necessary during a full scan"....



FTS vs Index scan

Arindam, September 27, 2010 - 12:07 pm UTC

Hi Tom,

Sorry, I might not be so good sometimes in expressing my problem. I was just trying to figure out what if I don't take index scan (can i call it as Table scan only?) while doing a full table scan. I wanted to find the cause of it.

Suppose I have a table of millions of rows which has index on it. So, I will perform index scan for a certain/small range of selection/insertion/update. Because it has to issue small I/Os for traversing from root to leaf. But during full scan it has to traverse from root to leaf many times which increases lots of I/O. That's why we don't use index scan while doing full table scan, we only waits table to do the scanning itself. Please correct my understanding is incorrect.

Tom Kyte
September 27, 2010 - 12:41 pm UTC

we don't traverse from root to leaf over and over - we would read the root/branch/leaf block to find the first rowid and then go to the table to retrieve that row and then READ THE SAME leaf block to get the next one and so on.

Once we get to the leaf, we don't go back up the tree again - we just read across the leaf blocks on the bottom - they are stored as a doubly linked list (hopefully they still teach that in computer science :) one day - they will not...) at the leaf level. So once we get to the start key, we can range scan to the stop key of the range scan without going back up the tree ever.

FTS vs Index scan

Arindam, September 27, 2010 - 12:55 pm UTC

Hi Tom,

Thanks for correcting it. So we take a horizontal travel through leaf blocks to get all the rows of the table. Then what is the harm of not using Index during a full table scan? Is it because it reads table data and its index entry over and over and thus increases I/O?
Tom Kyte
September 27, 2010 - 1:24 pm UTC

say you want to read an entire table.

should you:


a) read the table using multi-block IO getting say 64 blocks at a time from disk and processing them


b)
for x in (select rowid from index) 
loop
   read that block pointed to by that rowid
   output row
end loop


Imagine if you did (b), how many times might you have to read and re-read each database block from disk or from the cache? You might easily have to read each block N times where N is the number of rows on the block!


Full scanning of the table is just so incredibly more efficient. You read each block ONCE (not up to once per row) using nice big juicy multi-block IO (not a block at a slow block at a time).

FTS vs Index scan

Arindam, September 27, 2010 - 1:56 pm UTC

Hi Tom,

It seems I got hold of this.
So what you are saying is, if we read the entire table via index then the index will tell us to get block1.row1,block2.row1 and so on since index reads in sorted order. Therefore if our DB cache cannot hold all the blocks of the table then to find the other rows in the same block(like block1.row2,block1.row3), we might have to read that block1 again and again. same scenario for other blocks.

But if we set db_file_multiblock_read_count=64(say) then we can use a single I/0 to read all the rows residing in those 64 blocks only once. And then head towards other block. In that case we need not to read every block again and again.

Am I finally correct?
Tom Kyte
September 27, 2010 - 2:46 pm UTC

even if the cache holds all of the blocks, you have to read and re-read them from the cache. And reading from the cache is very expensive, you don't want to do it more than you have to. The cache is "not free", it is very costly.

if you full scan the table, you'll process ALL of the rows on a given block at once rather than over and over and over again as you would via the index, yes.

FTS vs Index scan

Arindam, September 28, 2010 - 5:47 am UTC

Hi Tom,

Thanks. I can understand it now. But still have a little query.

I guess Index reads in sorted order of Key column value. Can there be any possiblity for an index to read all the rows from one block at a time? i.e. retrieval of Block1.row1,Block1.row2 and so on for Block1 only once, so that index cannot have to re-read that block.
Tom Kyte
September 28, 2010 - 9:06 am UTC

... I guess Index reads in sorted order of Key column value ...

IF we do an index range scan or index full scan - yes. IF we do an index fast full scan - no, not true.


... Can there be any possiblity for an index to read all the rows from one block at a time? i.e. retrieval of Block1.row1,Block1.row2 and so on for Block1 only once, so that index cannot have to re-read that block. ...

sure - if the data in the table just happens to be organized in the same sort order as the data in the index. for example:


ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t
ops$tkyte%ORA11GR2> as
ops$tkyte%ORA11GR2> select rownum id1, dbms_random.random id2, a.*
ops$tkyte%ORA11GR2>   from all_objects a
ops$tkyte%ORA11GR2> /
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t modify id1 not null;
ops$tkyte%ORA11GR2> alter table t modify id2 not null;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index id1_idx on t(id1);
ops$tkyte%ORA11GR2> create index id2_idx on t(id2);
ops$tkyte%ORA11GR2> */

<b>data in the table is accidentally sorted by id1 - due to the way I loaded the data.  It is NOT sorted by id2 (unless I'm very very very unlucky :) )</b>

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace on
ops$tkyte%ORA11GR2> select count(subobject_name) from t;

COUNT(SUBOBJECT_NAME)
---------------------
                  535


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |   320   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 59902 |   994K|   320   (1)| 00:00:04 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1149  consistent gets
          0  physical reads
          0  redo size
        436  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

<b>so, a full scan with that count takes about 1149 IO's to complete - the table is about 1150 blocks in size therefore</b>

ops$tkyte%ORA11GR2> select /*+ index( t, id1_idx ) */ count(subobject_name) from t;

COUNT(SUBOBJECT_NAME)
---------------------
                  535


Execution Plan
----------------------------------------------------------
Plan hash value: 1281067298

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    17 |  1305   (1)| 00:00:16 |
|   1 |  SORT AGGREGATE              |         |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T       | 59902 |   994K|  1305   (1)| 00:00:16 |
|   3 |    INDEX FULL SCAN           | ID1_IDX | 59902 |       |   160   (0)| 00:00:02 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1304  consistent gets
          0  physical reads
          0  redo size
        436  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

<b>force it to use the index on the "sorted table column" and you see the IO's do not go up that much - just by about the size of the index on the column.  We read the table in "order of the table" - since the index was sorted in the same fashion as the table - and the IO's against the table did not go up really..

However....</b>

ops$tkyte%ORA11GR2> select /*+ index( t, id2_idx ) */ count(subobject_name) from t;

COUNT(SUBOBJECT_NAME)
---------------------
                  535


Execution Plan
----------------------------------------------------------
Plan hash value: 4143500907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    17 | 71776   (1)| 00:14:22 |
|   1 |  SORT AGGREGATE              |         |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T       | 59902 |   994K| 71776   (1)| 00:14:22 |
|   3 |    INDEX FULL SCAN           | ID2_IDX | 59902 |       |   186   (0)| 00:00:03 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      71759  consistent gets
          0  physical reads
          0  redo size
        436  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> set autotrace off

<b>Using that other index - the one that causes the index to skip all over the table - the IO's go up to the number of rows in the table (my all_objects has a little over 71,000 rows...</b>

Alexander, January 30, 2013 - 1:18 pm UTC

Hi,

I recently read some information from Oracle support and doing some goggling, that confused my understanding of how a full table scan effects the buffer cache. I was under the impression that it could not flush out the buffer cache because the slots that it reads into memory are sequentially overwritten as to not age out other blocks that may be hot. Other information I read, including some in the documentation stated that it just places it at the end of the LRU chain so it is less likely to blow out the buffer cache, but still could. Could you clarify how this works and what is possible and what isn't? Thank you.
Tom Kyte
January 31, 2013 - 2:18 pm UTC

basicallly - a full scan will be done in a way that the recently read in full scan blocks will be overwritten by newly read full scan blocks so that a full scan of a big table will not render the buffer cache useless.

whether it is "sequential", "LRU", "touch count based", "purple" - it doesn't really matter. The logic is to read the blocks in such a way that the recently read ones will purposely be overwritten instead of flushing out older blocks.

if you use the CACHE hint or alter table cache - that changes this algorithm, causing the blocks to be treated like single block IO's would.

Alexander, January 31, 2013 - 2:40 pm UTC

Ok, but what you explained just covers blocks already in cache that need to be read again and overwritten right? What about blocks not in cache that need to be processed for a full scan? Will all those be read into the buffer cache and effect whats in there? How are those blocks evaluated?
Tom Kyte
January 31, 2013 - 2:59 pm UTC

no, that wasn't what I was talking about.


say there is NOTHING in the cache for this table, then we'll read a set of blocks, process them, and when we read the next set of blocks - we'll tend to overwrite the first set we read in.


say there are some blocks in the cache for this table, then we'll figure out how many blocks we need to read for the first multi-block IO, put them in the cache, process them and then tend to overwrite them with the next read we do.

regardless, we'll tend to recycle the buffer cache blocks that we just used. conceptually it is like this - it is a lot more complex because the buffer cache itself is complex, it is lists of lists of lists (a block goes onto a specific list based on its hashed database block address - so the blocks we read are scattered all over the buffer cache with each multiblock read...)

Index read

Satheesh, May 24, 2013 - 2:00 pm UTC

Hi Tom

I have 2 options to design an application.

1) Continuous uniform queries which query less rows (with possible repitions of queries). Around 30 queries per minute fetching an indexed single row.
2) Batch query executed in fixed intervals (say 15mins) which query more rows. Single query fetching around 25k indexed rows.

Please let me know which option is more resource intensive in DB
Tom Kyte
May 29, 2013 - 4:52 pm UTC

neither
both
#1 is
#2 is

it depends.


30 queries per minute fetching a single row is nothing, it won't even really register. That query execution time should be measured in milliseconds.


1 query every 15 minutes getting 25k rows (this math is whacky, how can you either get 450 rows or 25,000 rows and do the same thing?????) would hit the server hard every 15 minutes - giving you a possible disturbance in the ability to do the other stuff you are doing.


I don't see how these two are comparable. One appears to be doing more than 2 orders of magnitude more work than the other.

index on small tables

boris, November 08, 2018 - 4:39 am UTC

The Oracle documentation states that small table does not require indexes. Is that true ?

https://docs.oracle.com/html/E25494_01/indexes002.htm


I hope the index on many lookup tables that are accessed frequently via the join can potentially save significant resources.

Please explain. Thanks for your service to the oracle community.

Connor McDonald
November 09, 2018 - 2:17 am UTC

"Small tables do not require indexes."

is an incorrect statement and I will file a documentation bug.

An index on *every* table is decided on by the same method, no matter what the size of the table is, and that is:

Does the benefit outweigh the cost.

index on small tables

Boris, November 09, 2018 - 7:20 pm UTC

Thanks Connor for your short and sweet answer. Much appreciated.
Chris Saxon
November 13, 2018 - 5:00 pm UTC

Glad this helped.

Jess, December 03, 2019 - 3:58 pm UTC

Dear Oracle Masters,

I'm looking at some legacy code that has numerous queries each of which selects some data from a table and joins it to a 'base' view (~350K records, most unique on the join column, but not all). There are full table scans everywhere, as for this view, as well as the tables it joins to (and tables it underpins) we bring back about 60-80% of the records.

However, for all but the base view, it's only an equijoin, so no data is necessary other than the join column. These columns were never indexed because we expect most of the table rows to be returned... But if we had an index, we wouldn't need to go to disk (since all data we need would be in the index). Shouldn't that be different (and possibly better) than an FTS? I've tried it both ways, but not seeing much difference performance-wise in the plan...

What's a good "rule of thumb" if you're going to match 60-80% of the rows, but aren't planning to read additional column values from disk?

Thank you!
Chris Saxon
December 05, 2019 - 9:23 am UTC

If you're ONLY selecting join columns, then I'd expect an index to be useful. No matter what fraction of the rows you return. Because this enables the database to do index-only scans.

Indexes are (usually) smaller data structures than the table they're on. So there's just less data to read. And thus more efficient. Even if you read all the data!

create table t1 (
  c1 int, 
  c2 varchar2(1000)
    default lpad ( 'x', 1000, 'x' )
);

create table t2 (
  c1 int, 
  c2 varchar2(1000)
    default lpad ( 'x', 1000, 'x' )
);

insert into t1 values ( 1, default );
insert into t1 values ( 2, default );

insert into t2 values ( 1, default );

commit;

set serveroutput off
alter session set statistics_level = all;
select t1.c1, t2.c1 
from   t1
join   t2
on     t1.c1 = t2.c1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

-------------------------------------------------------------------------------------    
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
-------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      14 |    
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.01 |      14 |    
|   2 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |       7 |    
|   3 |   TABLE ACCESS FULL| T1   |      1 |      2 |      2 |00:00:00.01 |       7 |    
------------------------------------------------------------------------------------- 


create index i1 
  on t1 ( c1 );
create index i2 
  on t2 ( c1 );

select t1.c1, t2.c1 
from   t1
join   t2
on     t1.c1 = t2.c1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

------------------------------------------------------------------------------------    
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |    
|   1 |  NESTED LOOPS     |      |      1 |      1 |      1 |00:00:00.01 |       2 |    
|   2 |   INDEX FULL SCAN | I2   |      1 |      1 |      1 |00:00:00.01 |       1 |    
|*  3 |   INDEX RANGE SCAN| I1   |      1 |      1 |      1 |00:00:00.01 |       1 |    
------------------------------------------------------------------------------------