Need help in tuning the SQL

Hi Friends,
We are using oracle 8.1.7, solaris 2.8.

can u please help me in tuning this SQL.

INDEX is present on both date_added and ad_id column

select count(distinct l.com_id),l.date_added, a.source
from tabs a,lis l where
(to_char(l.date_added,'DD/MON/YYYY') >= '01/JAN/2002' and
to_char(l.date_added,'DD/MON/YYYY') <= '31/DEC/2002') and
l.ad_id = a.ad_id
group by l.date_added,a.source

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=279116 Card=32505 By
tes=942645)

1 0 SORT* (GROUP BY) (Cost=279116 Card=32505 Bytes=942645) :Q142035
001

2 1 NESTED LOOPS* (Cost=278935 Card=32505 Bytes=942645) :Q142035
000

3 2 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card=33 :Q142035
515 Bytes=670300) 000

4 2 TABLE ACCESS* (BY INDEX ROWID) OF 'TABS' (Cost=2 Card=74 :Q142035
92600 Bytes=67433400) 000

5 4 INDEX* (UNIQUE SCAN) OF 'PKS_KEYS' (UNIQUE) (Cost= :Q142035
1 Card=7492600) 000



1 PARALLEL_TO_SERIAL SELECT A1.C0,A1.C1,COUNT(DISTINCT A1.C2) FRO
M :Q142035000 A1 GROUP BY A1.C0,A1.C

2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "PKS_KEYS") */ A1.C1 C0,A2."S

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT

U can see there is a full table scan happening on LIS table


regards
anandkl

The following statement is wrong because you compare
strings and not dates:

(to_char(l.date_added,'DD/MON/YYYY') >= '01/JAN/2002' and
to_char(l.date_added,'DD/MON/YYYY') <= '31/DEC/2002')

A string like '01/DEC/2003' will be smaler than '31/JAN/2003', I guess it's not soemthing you realy want.


Try the follwing statement and check if it's faster

select count(distinct l.com_id),l.date_added, a.source
from tabs a,lis l
where
l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and
l.ad_id = a.ad_id
group by l.date_added,a.source

Hi Mike,
Its still doing FULL TABLESCAN


select count(distinct l.com_id),l.date_added, a.source
from tabs a,lis l
where
l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and
l.ad_id = a.ad_id
group by l.date_added,a.source 2 3 4 5 6 7
8 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=602938 Card=5108360
Bytes=148142440)

1 0 SORT* (GROUP BY) (Cost=602938 Card=5108360 Bytes=148142440 :Q142066
) 003

2 1 MERGE JOIN* (Cost=559467 Card=5108360 Bytes=148142440) :Q142066
002

3 2 SORT* (JOIN) (Cost=234139 Card=5267012 Bytes=105340240 :Q142066
) 002

4 3 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card= :Q142066
5267012 Bytes=105340240) 001

5 2 SORT* (JOIN) (Cost=325328 Card=7492600 Bytes=67433400) :Q142066
002

6 5 TABLE ACCESS* (FULL) OF 'TABS' (Cost=305557 Card=74926 :Q142066
00 Bytes=67433400) 000



1 PARALLEL_TO_SERIAL SELECT A1.C0,A1.C1,COUNT(DISTINCT A1.C2) FRO
M :Q142066002 A1 GROUP BY A1.C0,A1.C

2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A1.C2 C0,A2.C1 C1,A1.C1 C2 FROM :Q

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_TO_PARALLEL SELECT /*+ Q142066001 NO_EXPAND ROWID(A1) */
A1."AD_ID" C0,A1."COMP_ID" C1,A1

5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_FROM_SERIAL

What is the % of recs being selected?.

Its close to 5 - 6%

regards
anandkl

Which % of rows of the table lis satisfay the follwoing condition:

l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and

Is it only 5-6% ?

Yes..it 5 - 6%

regards
anandkl

wht type of inx is it?

wat is the card of the cols indexed?

was the no of recs in the table 'lis'

last thing that you may like to do is reorg table/index & analyze

It's not the % of rows that matters, it's the % of blocks that they represent.

What is the clustering factor of the index on date_added?

How many blocks are there in the LIS table below the high water mark?

Hi,
Its a normal bit map index, the total no of recoreds in lis is
13368818 records

I have analyzed the tables, as well as the index

regards
anandkl

The total no of blocks in lis table is 1408000

regards
anandkl

tables Partitioned?
I got this site abt optimizer...

http://download-west.oracle.com/docs...3/optimops.htm

HTH

Quote:

---

Originally posted by slimdave
It's not the % of rows that matters, it's the % of blocks that they represent.

---

Ohh com on dave, tell me how will oracle estimate (Estimator phase) the number of blocks be fetched ( before execution phase )?
Will it directly estimate no of blocks that needs to be fetched?

Ofcourse its the blocks that matters, but to calculate/estimate number of blocks oracle will take the help of Stats (column/table) correlates/applies with QUERY in question and estimate, right?.

So, dont you think oracle will first evaluate/estimate %of recs that it needs to fetch. if otherwise why do you think Histograms are necessary?

Anand :

You still didnt answer wass the Card of both cols...

BTW, are histograms taken since you used Bit Map Type I wud guess the card of the cols is too low & taking histograms will help you.

Abhay.

Quote:

---

Originally posted by abhaysk
Ohh com on dave, tell me how will oracle estimate (Estimator phase) the number of blocks be fetched ( before execution phase )?
Will it directly estimate no of blocks that needs to be fetched?

Ofcourse its the blocks that matters, but to calculate/estimate number of blocks oracle will take the help of Stats (column/table) correlates/applies with QUERY in question and estimate, right?.

So, dont you think oracle will first evaluate/estimate %of recs that it needs to fetch. if otherwise why do you think Histograms are necessary?

---

Estimating the number of rows is just a step towards estimating the number of blocks, based on the index clustering factor (I suggest a search ofdocumentation for the phrase "clustering factor"). Once Oracle has estimated how many blocks it will require from the table, the number of rows becomes an irrelevance in deciding whether to use FTS or index, except as far as it affects later stages of the execution plan.

Sure, it estimates the number of rows required from the table, but that could mean returning 1% of the blocks or all of the blocks. That is why it is the % of blocks that matters, and why the idea that Oracle will use an index for "% of rows less than n" is yet another myth.

Quote:

---

Originally posted by slimdave
Estimating the number of rows is just a step towards estimating the number of blocks, based on the index clustering factor (I suggest a search ofdocumentation for the phrase "clustering factor").

---

It might sound just a step to you, but to me its significant & this is what i was trying to point/emphaisze in my previous post.

Quote:

---

Originally posted by slimdave
Once Oracle has estimated how many blocks it will require from the table, the number of rows becomes an irrelevance in deciding whether to use FTS or index, except as far as it affects later stages of the execution plan.

---

Ok, in my earlier post I shud have been presize of using % of Blocks rather than % of rows...

Quote:

---

Originally posted by slimdave
That is why it is the % of blocks that matters,

---

yes, but i think you didnt read the post completely

Quote:

---

Ofcourse its the blocks that matters, but to calculate/estimate number of blocks oracle will take the help of Stats (column/table) correlates/applies with QUERY in question and estimate, right?.

---

Please note am not telling "% rows" is all that matters, but i meant its significant, as more often than not you get a range of values in a block/blocks over spreading into all/most of the blocks in the table.

Quote:

---

Originally posted by slimdave
and why the idea that Oracle will use an index for "% of rows less
than n" is yet another myth.

---

I aggree, but i think my post of

Quote:

---

What is the % of recs being selected?.

---

is driving you crazy ;)

Well, all i can say its a common practice to use recs over blocks as one would assume it(recs) to be presnt in small no(%) of blocks & cant imagine for n values(recs) to spread accross n blocks ( Ofcourse there is a chance of being so, but rare ).

Abhay.

Quote:

---

Originally posted by abhaysk
Anand :

You still didnt answer wass the Card of both cols...

---

This is important, I think.
If the cardinality is high, bitmap indexes aren't useful.
There's no reason to assume the l.date_add column has low cardinality, as well as the l.ad_id column.

Try to create b-tree indexes instead of the bitmap ones.

Blocks is what matters, it is the smallest logical unit Oracle manages NOT rows. It´s very common people think Oracle manages rows

It depends on how you configure your storage parameters, you can alter your cluster factor with those parameters. Having small amount of rows in tons of blocks is not rare, just how you configure PCTUSED, PCTFREE, INITRANS, MAXTRANS

So I do agree with Slimdave

Code:

---


create table a_low_storage pctfree 90 pctused 5 nologging as
select
      OWNER,
      TABLE_NAME,
      COLUMN_NAME,
      DATA_TYPE,
      DATA_TYPE_MOD,
      DATA_TYPE_OWNER,
      DATA_LENGTH,
      DATA_PRECISION,
      DATA_SCALE,
      NULLABLE,
      COLUMN_ID,
      DEFAULT_LENGTH,
      NUM_DISTINCT,
      DENSITY,
      NUM_NULLS,
      NUM_BUCKETS,
      LAST_ANALYZED,
      SAMPLE_SIZE,
      CHARACTER_SET_NAME,
      CHAR_COL_DECL_LENGTH,
      GLOBAL_STATS,
      USER_STATS,
      AVG_COL_LEN
  from dba_tab_columns;

create table a_high_storage pctfree 1 pctused 90 nologging as
select
      OWNER,
      TABLE_NAME,
      COLUMN_NAME,
      DATA_TYPE,
      DATA_TYPE_MOD,
      DATA_TYPE_OWNER,
      DATA_LENGTH,
      DATA_PRECISION,
      DATA_SCALE,
      NULLABLE,
      COLUMN_ID,
      DEFAULT_LENGTH,
      NUM_DISTINCT,
      DENSITY,
      NUM_NULLS,
      NUM_BUCKETS,
      LAST_ANALYZED,
      SAMPLE_SIZE,
      CHARACTER_SET_NAME,
      CHAR_COL_DECL_LENGTH,
      GLOBAL_STATS,
      USER_STATS,
      AVG_COL_LEN
  from dba_tab_columns;

create unique index a_low_storage_idx1 on a_low_storage(owner, table_name, column_name);

create unique index a_high_storage_idx1 on a_high_storage(owner, table_name, column_name);

analyze table a_low_storage compute statistics;

analyze table a_high_storage compute statistics;

set autotrace traceonly exp stat

select * from a_high_storage where owner = 'SYS' and (table_name like 'A%' or
                                                      table_name like 'B%' or
                                                      table_name like 'C%' or
                                                      table_name like 'D%');

3278 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=437 Bytes=30153)
  1    0  TABLE ACCESS (FULL) OF 'A_HIGH_STORAGE' (Cost=20 Card=437 Bytes=30153)

Statistics
----------------------------------------------------------
          0  recursive calls
        12  db block gets
        350  consistent gets
          0  physical reads
          0  redo size
    261332  bytes sent via SQL*Net to client
      24623  bytes received via SQL*Net from client
        220  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      3278  rows processed

select * from a_low_storage where owner = 'SYS' and (table_name like 'A%' or
                                                      table_name like 'B%' or
                                                      table_name like 'C%' or
                                                      table_name like 'D%'); 

3278 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=132 Bytes=9108)
  1    0  CONCATENATION
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
  3    2      INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
  4    1    TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
  5    4      INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
  6    1    TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
  7    6      INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)
  8    1    TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=3 Card=1 Bytes=69)
  9    8      INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=2 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      1911  consistent gets
          0  physical reads
          0  redo size
    274348  bytes sent via SQL*Net to client
      24623  bytes received via SQL*Net from client
        220  SQL*Net roundtrips to/from client
        13  sorts (memory)
          0  sorts (disk)
      3278  rows processed

---

Same number of rows different number of blocks, different excution plan

Number of rows would be used in JOINs needed to reduce the result set as small as possible

Quote:

---

Originally posted by guru_heaven
Blocks is what matters, it is the smallest logical unit Oracle manages NOT rows. It´s very common people think Oracle manages rows

It depends on how you configure your storage parameters, you can alter your cluster factor with those parameters. Having small amount of rows in tons of blocks is not rare, just how you configure PCTUSED, PCTFREE, INITRANS, MAXTRANS

So I do agree with Slimdave

Same number of rows different number of blocks, different excution plan

Number of rows would be used in JOINs needed to reduce the result set as small as possible

---

Ok, I would say having PCTUSED 5 is a dumb idea, but any how letme try to show you how Number Of Rows play
a significant role. I dont analyze index, but i just take table/column stats, now see the behaviour.

Code:

---

TEST:SYS> analyze table A_high_STORAGE compute statistics for table for all columns size 254 /* note no index analyzed */;

Table analyzed.

TEST:SYS> analyze table A_low_STORAGE compute statistics for table for all columns size 254 /* note no index analyzed */;

Table analyzed.

TEST:SYS> alter session set optimizer_mode=all_rows;

Session altered.

TEST:SYS> set autotrace traceonly;
TEST:SYS> select * from A_high_STORAGE where owner='ABHAY' and table_name like 'T%';

1049 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=61 Bytes=43
          31)

  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'A_HIGH_STORAGE' (Cost=4
          Card=61 Bytes=4331)

  2    1    INDEX (RANGE SCAN) OF 'A_HIGH_STORAGE_IDX1' (UNIQUE) (Co
          st=2 Card=61)

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

TEST:SYS>
TEST:SYS> select * from A_low_STORAGE where owner='ABHAY' and table_name like 'T%';

1049 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=59 Bytes=41
          89)

  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=4 C
          ard=59 Bytes=4189)

  2    1    INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cos
          t=2 Card=59)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        423  consistent gets
          5  physical reads
          0  redo size
      28619  bytes sent via SQL*Net to client
      1258  bytes received via SQL*Net from client
        71  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      1049  rows processed

---

Well From that time i am telling you guys '% ROWS' is not all it matters, but play significant role in calculating number
of blocks based on Cluster Factor as Slimdave said. Now i have not taken index stats and oracle thinks index scan is
better over FTS because it simply unable to estimate the number of blocks.

Guru i dont think in any type if environment PCTUSED will be less than 40....

Abhay.

In the test case PCTFREE is what matters not PCTUSED, what are you talking about PCTUSED? I made PCTUSED low because PCTUSED + PCTFREE cannot be > 100

And who on earth would analyze the table and not the index if you are gonna use CBO?

just had a closer look at your test, try the one below, at least make the test same not with your *own* tables

Code:

---


analyze table a_low_storage delete statistics;

Table analyzed.

analyze table a_high_storage delete statistics

Table analyzed.

analyze table A_low_STORAGE compute statistics for table for all columns size 254;

Table analyzed.

analyze table A_high_STORAGE compute statistics for table for all columns size 254;

Table analyzed.

select * from a_low_storage where owner = 'SYS' and (table_name like 'A%');

1379 rows selected.

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=1421 Bytes=98049)
  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'A_LOW_STORAGE' (Cost=83 Card=1421 Bytes=98049)
  2    1    INDEX (RANGE SCAN) OF 'A_LOW_STORAGE_IDX1' (UNIQUE) (Cost=4 Card=1421)

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

select * from a_high_storage where owner = 'SYS' and (table_name like 'A%');

1379 rows selected.

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1443 Bytes=99567)
  1    0  TABLE ACCESS (FULL) OF 'A_HIGH_STORAGE' (Cost=20 Card=1443 Bytes=99567)

Statistics
----------------------------------------------------------
          0  recursive calls
        12  db block gets
        224  consistent gets
          0  physical reads
          0  redo size
    110505  bytes sent via SQL*Net to client
      10526  bytes received via SQL*Net from client
        93  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      1379  rows processed

---

Try this query, and you will know why i used Owner different from SYS

Code:

---

select owner, count(*) from A_HIGH_STORAGE group by owner

Or

select owner, count(*) from A_LOW_STORAGE group by owner

---

More over original poster told he had 5 to 6 % recs, so i had exactly that % recs in owner ABHAY.

BTW, the link you suggested in other post, you may want look again ( regarding Histograms )

Also, regarding PCTUSED was not ref to your tests, but i wondered if in any real case environment existed.

Please also note how many blocks does A_HIGH_STORAGE have & you will know why it went to FTS. More Over your results are exactly opposite to what was expected, because in a A_LOW_STORAGE the recs are very scattered and still going for index scan

Abhay.

Quote:

---

Originally posted by abhaysk

Also, regarding PCTUSED was not ref to your tests, but i wondered if in any real case environment existed.

Please also note how many blocks does A_HIGH_STORAGE have & you will know why it went to FTS. More Over your results are exactly opposite to what was expected, because in a A_LOW_STORAGE the recs are very scattered and still going for index scan

Abhay.

---

You get things wrong, in HIGH stll orage the rows are tightly packed, Oracle knows because the number of rows per block is HIGH that´s why it goes for FTS because it knows it´s cheaper to read all blocks

In LOW storage the rows are scattered that´s why it uses INDEX SCAN becase it *MIGHT* be cheaper

The access path determines the number of units of work required to get data from a basesicall table. The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation. Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to be scanned and the multiblock read count value. The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.

Basically:

Number of rows is only used when rowid index fetch is used

Rest, blocks

Quote:

---

Originally posted by guru_heaven
You get things wrong, in HIGH stll orage the rows are tightly packed, Oracle knows because the number of rows per block is HIGH that´s why it goes for FTS because it knows it´s cheaper to read all blocks

In LOW storage the rows are scattered that´s why it uses INDEX SCAN becase it *MIGHT* be cheaper

---

I will giv up...
All i can say is try deleting your histograms and check wass the result, then you will know why.

Quote:

---

Originally posted by abhaysk
Well, all i can say its a common practice to use recs over blocks as one would assume it(recs) to be presnt in small no(%) of blocks & cant imagine for n values(recs) to spread accross n blocks ( Ofcourse there is a chance of being so, but rare ).

---

Sure, it's a common practice -- so is separating indexes and tables into different tablespaces. Still it's wrong to do so, and by asking what the % of rows was you reinforced the mythology.

Why would you assume that the rows are present in a small number of blocks? Oracle doesn't, don't see why anyone should. It's very likely that rows of a particular value will be scattered throughout the table.

In the example given, the optimizer plan line ...

3 2 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card=33 :Q142035
515 Bytes=670300) 000

.. tells you that Oracle is expecting to retrieve 670,300 bytes in order to retrieve 33 rows -- that's about 2kb per row. Oracle believes that the target rowsare scatterred throughout the table, it seems.

Quote:

---

Originally posted by slimdave
Sure, it's a common practice -- so is separating indexes and tables into different tablespaces. Still it's wrong to do so, and by asking what the % of rows was you reinforced the mythology.

Why would you assume that the rows are present in a small number of blocks? Oracle doesn't, don't see why anyone should. It's very likely that rows of a particular value will be scattered throughout the table.

---

You cant say its very likely, but it depends on so many factors..the design of the structure on how it will hold recs and so..

Quote:

---

Originally posted by slimdave
In the example given, the optimizer plan line ...

3 2 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card=33 :Q142035
515 Bytes=670300) 000

.. tells you that Oracle is expecting to retrieve 670,300 bytes in order to retrieve 33 rows -- that's about 2kb per row. Oracle believes that the target rowsare scatterred throughout the table, it seems.

---

This is what Oracle thinks and is doing FTS & morover its an estimated value & it should not necessarly be accurate...

In such cases I would like go some thing like this...

* Reorg Tables/Indexes and analyze without histograms.
* If, no gain than with histograms
* now even if Oracle thinks FTS is better than it is..but even then i would like to see with RULE
* So, I set session Optmizer mode to RULE & check the statistcs or by just deleting the Table/Index/Col stats in the underlying tables of the query ( Mind this is last thing i would do )

Anand I would suggest you to try consider changing you session mode to RULE and see, or also try deleting your COLUMN Stats ( Histograms )
and see the statistics and compare.

Abhay.

Anand :

Also, can you post for below queries, you will know why LIS is going for FTS.

Code:

---

select
  count(distinct substr(l.rowid,1,15)) "Selected Blocks"
from
  tabs a,
  lis l
where
  l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
  l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and
  l.ad_id = a.ad_id
group by
  l.date_added,
  a.source
;

select
  count(distinct substr(l.rowid,1,15)) "Total Blocks"
from
  lis l
;

---

Abhay.

Quote:

---

You cant say its very likely, but it depends on so many factors..the design of the structure on how it will hold recs and so..

---

sure I can -- you said it was very unlikely, and that seems plain wrong.

Quote:

---

This is what Oracle thinks and is doing FTS & morover its an estimated value & it should not necessarly be accurate...

---

Of course it's estimated -- Oracle won't know for sure till it executes the query, but the point the point i am making is that it explains the FTS. "5-6% of rows" translates to this many blocks in Oracle's estimation, based on the clustering factor of the index

Quote:

---

In such cases I would like go some thing like this...

* Reorg Tables/Indexes and analyze without histograms.
* If, no gain than with histograms
* now even if Oracle thinks FTS is better than it is..but even then i would like to see with RULE
* So, I set session Optmizer mode to RULE & check the statistcs or by just deleting the Table/Index/Col stats in the underlying tables of the query ( Mind this is last thing i would do )

---

"Reorg tables and indexes"? Another waste of time - what's that supposed to do?

And you've saved the easiest thing for last -- tweaking the otpimizer to promote the use of the index. Surely that's the simplest, fastest thing to do? But don't change to RBO, just promote the use of the index with an index() hint, and compare performance with the FTS.

Quote:

---

Originally posted by slimdave
sure I can -- you said it was very unlikely, and that seems plain wrong.

---

I would like to see some prof?

In presnt case atleast, there is a range of dates & i belive inserted every day/or month which will be closely packed.

Quote:

---

Originally posted by slimdave
Of course it's estimated -- Oracle won't know for sure till it executes the query, but the point the point i am making is that it explains the FTS. "5-6% of rows" translates to this many blocks in Oracle's estimation, based on the clustering factor of the index.

---

Let him get the results of the specified queries so we will know how exactly the rows are spread.

Quote:

---

Originally posted by slimdave
"Reorg tables and indexes"? Another waste of time - what's that supposed to do?.

---

heavy dml on the tables ( especially deletes frequently will make void space in the blocks which may not be reused due many reasons )

check the number of change in blocks required before and after reorg of table/index, you will know why.

Quote:

---

Originally posted by slimdave
And you've saved the easiest thing for last -- tweaking the otpimizer to promote the use of the index. Surely that's the simplest, fastest thing to do? But don't change to RBO, just promote the use of the index with an index() hint, and compare performance with the FTS.

---

I would use hint in very first place & check the stats ( i would assume he would have tried it ), so keeping in mind hints(and many other workarounds to use index) didnt work i would go as said above

Abhay.

Quote:

---

In presnt case atleast, there is a range of dates & i belive inserted every day/or month which will be closely packed.

---

Maybe so, but it's speculation. In this case Oracle doesn't appear to think they are closely packed.

Quote:

---

Let him get the results of the specified queries so we will know how exactly the rows are spread.

---

Yes -- it will be interesting to compare the results with the clustering factor that Oracle has for the index.

Quote:

---

heavy dml on the tables ( especially deletes frequently will make void space in the blocks which may not be reused due many reasons )

---

But before advising a reorg, why not advise checking on whether there are voids in the tables and indexes? There may indeed be reasons why the spaces might not be reused, but ...

i) The presence of voids doesn't seem likely in this case
ii) The reasons for voids not being resused would be associated with incorrect setting of storage parameters, which would require attention anyway.
iii) Voids are easily detactable with a query.

So at best, just reorganizing the tables and indexes would provide a temporary fix that would hide the real nature of the problem, and at worst it would be a big waste of time. I just advise that before taking the medecine, we diagnose the illness, right?

Clustering Factor

To calculate the clustering factor of an index, Oracle does the following.

For each entry in the index Oracle compares the entry's table rowid block with the block of the previous index entry.

If the block is different, Oracle increments the clustering factor by 1.

The minimum possible clustering factor is equal to the number of blocks identified through the index's list of rowid's -- for an index on a column or columns conatining no nulls, this will be equal to the number of blocks in the table that contain data. The maximum clustering factor is the number of entries in the index.

So this means that Oracle now has a statistic to allow it to estimate how many table blocks would be associated with an index range scan.

If the clustering factor is close to the number of entries in the index, then an index range scan of 1000 index entries may require nearly 1000 blocks to be read from the table.

If the clustering factor is close to the number of blocks in the table, then an index range scan of 1000 index entries may require only 50 blocks to be read from the table.

This can be compared with the cost of reading the entire table up to the high-water mark (using the more efficient multiblock i/o mechanism) to determine whether a full table scan or an index range scan offers the most efficient access mechanism.

Note that where extensive deletes have occurred from the table there may be blocks with no rows in. These will be accounted for in the clustering factor because those blocks will not appear in the index's rowid list. The full table scan will still read all table blocks up to the high water mark, regardless of whether they contain rows or not.

Note also that calculating the clustering factor is done without reference to the table at all -- it is based solely on information contained in the index.

Quote:

---

Originally posted by slimdave
Maybe so, but it's speculation. In this case Oracle doesn't appear to think they are closely packed.

---

It would be gr8, if you perform a test & u will know..

Quote:

---

Originally posted by slimdave
But before advising a reorg, why not advise checking on whether there are voids in the tables and indexes? There may indeed be reasons why the spaces might not be reused, but ...

---

Ok, thas correct, but i assume he would have done that in very first place..

Quote:

---

Originally posted by slimdave
iii) Voids are easily detactable with a query.

---

I have been using Package DBMS_SPACE for this ( to find %free space in blocks ), but quite not sure to do with a query, yes ofcourse you can get the breakup of number of rows in each block, but how would you calculate free space accordingly?

If you could let me know, it would be great.

Abhay.

Quote:

---

I have been using Package DBMS_SPACE for this ( to find %free space in blocks ), but quite not sure to do with a query, yes ofcourse you can get the breakup of number of rows in each block, but how would you calculate free space accordingly?

If you could let me know, it would be great.

---

As you say, the statistics tell you the number of rows, the number of blocks, and also you can read the average row length.

There are many statistics in user_tables that help detect voids -- empty_blocks, avg_space, num_freelist_blocks, avg_space_freelist_blocks.

You can even do something simplistic like taknig the avg row length and multipling by the number of rows, adding a little more to account for block headers, and comparing the result to the spacebelow the high water mark.

Quote:

---

Originally posted by slimdave
As you say, the statistics tell you the number of rows, the number of blocks, and also you can read the average row length.

There are many statistics in user_tables that help detect voids -- empty_blocks, avg_space, num_freelist_blocks, avg_space_freelist_blocks.

You can even do something simplistic like taknig the avg row length and multipling by the number of rows, adding a little more to account for block headers, and comparing the result to the spacebelow the high water mark.

---

Dave :

Ya, i know by all these figures you can estimate/evaluate the avg
number of blocks candidate for reuse and so, but still it will not
give you exact figures, when you said "Voids are easily detactable
with a query." I was wondering if you meant the exact figures..

PS what i was thinking...

Code:

---

TEST:ABHAY> @c:\dba\Free_Space_Blocks
Enter value for owner (RETURN for ALL) > ABHAY
Enter value for SEGMENT name (RETURN for ALL) > check_scatter
**************************************************************************
ABHAY TABLE CHECK_SCATTER
-------------------------------------------------------------------------
Tablespace : SEG_MANAGE_AUTO
Total Unformatted Bytes : 0 (0 blocks)
Total  0-25% Free : 0 (0 blocks)
Total  25-50% Free : 8192 (1 blocks)
Total  50-75% Free : 0 (0 blocks)
Total  75-100% Free : 1957888 (239 blocks)
Total Full : 7839744 (957 blocks)

I used DBMS_SPACE package for above results..i thought if you had
some query to find as such...

Below is what at best i can get, but i said i was unable to calculate
free sapce per block..i could only get breakup/blocks with number of
rows and so...ofcourse by AVG row lenght i can evaluate to almost to
near figures as above, but not exact...


So i wondered if you have designed some query to find so.!!

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  Select
  2    Min(CNT) "Min_Rows_In_Block/s",
  3    Max(CNT) "Max_Rows_In_Block/s",
  4    Round(Sum(CNT)/Count(CNT)) "Avg_Rows_Per_Block"
  5  From
  6    (
  7    Select
  8      Substr(Rowid,1,15) Block,
  9      Count(*) CNT
 10    from
 11      Check_Scatter
 12    Group by
 13      Substr(Rowid,1,15)
 14*  )
 15  /

 Min_Rows_In_Block/s  Max_Rows_In_Block/s  Avg_Rows_Per_Block
-------------------- -------------------- --------------------
                112                  169                  167

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  Select
  2    CNT Number_Of_Rows ,
  3    Count(*) Number_Of_Blocks
  4  From
  5    (
  6    Select
  7      Substr(Rowid,1,15) Block,
  8      Count(*) CNT
  9    from
 10      Check_Scatter
 11    Group by
 12      Substr(Rowid,1,15)
 13    )
 14  Group By
 15*  CNT
TEST:ABHAY> /

      NUMBER_OF_ROWS    NUMBER_OF_BLOCKS
-------------------- --------------------
                112                    1
                163                    1
                165                  304
                166                  168
                167                  39
                168                  438
                169                    7

---