Need help in tuning the SQL

[abhaysk]

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

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.

[guru_heaven]

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

[pando]

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

[abhaysk]

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.

[slimdave]

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.

[abhaysk]

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..

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.

[abhaysk]

Anand :

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

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.

[slimdave]

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.

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

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.

[abhaysk]

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.

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.

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.

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.

[slimdave]

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.

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.

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?