RE: stupid question on FTS
Date: Fri, 24 Aug 2012 14:53:06 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01EB457A_at_WIN02.hotsos.com>
The big question is of course is the full table scan (or full partition scan) really bad? Does it really perform worse than the index scan? As in does it do more Logical IOs then the index scan? You say " I know it doesn't make sense for my App to ever do a FTS on it..." Why is that? Thru testing is the index scan really better than the full scan?
Of course there are ways to do this, like setting "OPTIMIZER_INDEX_COST_ADJ" to something very low like 10 for any query touching this table with the OPT_PARAM hint. Have stored outlines for queries hitting this table. Use rule based optimization, which will always use an index if it's there (but since it's a partitioned table Rule wouldn't work anyway).
But these are just like putting a Band-Aid on a sucking chest wound. It might help for a moment but it's unlikely to save the patient. What is really going on? What is it about the stats or the query that is making the optimizer thing the Full Table Scan is a good idea? If it's multiple queries that are different in nature but all touch the same table then it's likely something wrong with the stats. My instinct tells me it's the CLUSTER_FACTOR that might be out of whack. But very hard to tell.
Keep in mind that it's NOT about how many ROWS are being selected out of the table but how many BLOCKS those rows are in that matters. The CLUSTER_FACTOR is the way the optimizer has a clue as to how densely or sparsely packed that data is. The more sparse it is (the higher the CLUSTER_FACTOR) the less likely it will use the index. Setting this stat lower will make the index look "better" for any range scan like action, because the optimizer will think the data is densely pack, as in it's in fewer blocks in the table. (Use DBMS_STATS.SET_INDEX_STATS to set the stat.) A good CLUSTERING_FACTOR is a value close to the number of blocks in the table, a bad one is close to the number of rows in the table. Manually setting the CLUSTER_FACTOR might help however it will of course be overwritten at the next collection. But setting it lower and seeing the right activity will at least give you a clue that it very well may be the issue.
Ric Van Dyke
Education Director
Hotsos Enterprises LTD.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vasu
Sent: Thursday, August 23, 2012 9:12 PM
To: oracle-l_at_freelists.org
Subject: Re: stupid question on FTS
Its on 11.2.0.3 , and being tested in load-test env.
Thanks
On Thu, Aug 23, 2012 at 7:34 PM, Vasu <vasudevanr_at_gmail.com> wrote:
> I have a huge table in my OLTP DB, that has 100 million+ rows (pls
> don't ask why), and is partitioned.
>
> I know it doesn't make sense for my App to ever do a FTS on it.. But
> Oracle at times picks up FTS as the best access path (and our STATS is
> not at it best yet).
>
> It just wished for a setting..that "I never want to have a FTS on my
> table..Unless otherwise explicitly told thru a Hint" .
>
> yes, a Hint/setting can't compensate for lack of STATS.. but just that
> my desparate situation/laziness forced me to think that way.
>
> Any thoughts?
>
> Thanks,
> Vasu
>
>
-- -Vasu -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 24 2012 - 14:53:06 CDT