Re: Performance impact of MONITORING and GATHER_STALE

On 03/15/2005 08:32:55 PM, Leng Kaing wrote:
> Hi Mladen,

>=20

Well, if the table we're talking about is an active OLTP table and rollback= =20
of transactions is something that occurs regularly, the numbers in your=20 DBA_TAB_MODIFICATIONS are going to be grossly overestimated. Niall has show= n
that even if an insert is rolled back, t is still recorded as an insert. As=  you
know, DBMS_STATS.GATHER_<SCHEMA|DATABASE>_STATS with options=3D'GATHER AUTO= ' will
decide to collect statistics if there are >10% modified rows. You will have statistics gathered automatically on your most active tables every night.=20 Wolfgang Breitling had an article in which he was advocating a 2-pass stati= stics
collection, based on the need. The need would be decided manually, based on=  the=20
cardinality (I apologize to Wolfgang if I misunderstood him). The two passe= s should
have method_opt =3D> 'FOR TABLE' (collecting for tables) and method_opt = =3D> 'FOR COLUMNS SIZE SKEWONLY'
(collecting histograms). I believe it was that article in which he said som= ething like:
It is only safe to collect statistics when it makes no sense to do so.=20 Essentially, the problem is that if you resort to the periodic statistics c= ollection, you will
never know what your plans will look like. The only time when nothing will = change is when the
relative numbers and distributions are unchanged, in which case there wasn'= t any need to collect
statistics in the first place.
I like using GATHER AUTO, because it selects histograms based on the column=  predicates.=20
It is a little known fact that CBO maintains table SYS.COL_USAGE$ in which = all predicates=20
are recorded. What I am objecting to is the practice of regular statistics = collection which=20
introduces an element of randomness in the DBA life. You never know what ar= e your execution
plans going to be the next week.=20
One thing where Wolfgang and I differ are OPTIMIZER_INDEX* parameters. On a= n OLTP system,
I simply love those parameters because they give me what I want: a supporte= d RBO. RBO had
a very simple philosophy: if there is index, use it. CBO then added somethi= ng like: "Well=20
then, it's not that simple. Let's take a look at the distribution of your r= ows.". It's all
nice and kosher, unless I don't want full table scan, hash join, merge join=  and fast full=20
index scan ever to take place. I have an interactive application and I want=  all my table=20
accesses to be done by using an index. In that case, my statistics has to r= ecord existence=20
of an index and my OPTIMIZER_INDEX* parameters will turn CBO into RBO on st= eroids. I even
went so far to update PVAL1 in AUX_STATS$ and set MBRC to a small number, l= ike 4 to make
full table scans even less probable. Analyzing occurs only when a new table=  is added. This=20
is done because Oracle will desupport RBO some time soon. OPTIMIZER_INDEX* = parameters are
a way of getting RBO without RBO.

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l