index_stats no populating
bal...@comcast.net
populate. I'm running 9.2.0.5 with the compatible parameter set to
8.1.0, I don't think that should matter though.
I run
analyze index [index_name] validate structure;
that returns Index analyzed, but the table does not populate.
I've also tried running
analyze index [index_name] compute statistics
and then running the validate structure but still nothing.
anyone know why?
Thanks,
fitzj...@cox.net
bal...@comcast.net wrote:
> I'm new to this DBA stuff and I can't seem to get index_stats to
> populate. I'm running 9.2.0.5 with the compatible parameter set to
> 8.1.0, I don't think that should matter though.
Really? Disabling all of the 9i and 9iR2 features in a 9.2.0.5
database doesn't matter? I would think otherwise.
> I run
> analyze index [index_name] validate structure;
Which doesn't populate anything.
> that returns Index analyzed, but the table does not populate.
See above.
> I've also tried running
> analyze index [index_name] compute statistics
Why are you still using the deprecated 'analyze index' command? You
should be using dbms_stats.gather_index_stats() to generate index
statistics. You should also be using dbms_stats.gather_table_stats()
in place of 'analyze table', and should you use CASCADE=TRUE in the
parameter list to gather_table_stats() you'll also generate your index
statistics.
> and then running the validate structure but still nothing.
Why do you insist upon executing 'analyze index .. validate
structure;'? What do you think this accomplishes other than validating
an already valid index?
> anyone know why?
The question is why are you *not* using the dbms_stats package?
> Thanks,
David Fitzjarrell
Anurag Varma
analyze index ... validate structure;
?
Also note: the table will be populated only for the latest analyze ....
statement run.
Running analyze index ... compute statistics ... has nothing to do
with your issue.
Anurag
yon...@yahoo.com
Most likely Anurag's message solves your problem. If not, there's a
little possibility you're hitting Bug 4533389, although the bug report
only says 9.2.0.6.
(For David, analyze index validate structure does not collect optimizer
statistics. This is one of the few uses of analyze that dbms_stats
cannot replace.)
Yong Huang
Ben
I did say, I'm new to this, right?? I think I did.
So, I could be wrong, but no, the compatibility setting shouldn't have
any bearing on my problem at hand with index_stats not populating. It
was available in 8.1 and later.
The instance and schema that I am running this in uses DMTs, so yes, I
need to analyze index validate structure, to look at an index that I
think needs to be coalesced.
And if running alter index validate structure doesn't populate
anything, then why do all of the manuals say that it does?
The compatible setting is a whole nother issue that I need to find out
the ramifications for bumping it up in our system. I am aware that
there are quite a few features with 9i that I can't use due to this.
Anurag,
I did query the table in the same session that I analyzed the index in.
All the documentation that I could find in the manuals did at least say
that index_stats only holds one record while that session is still
active. I've tried all that I know what to do, but I can't seem to get
it to populate. I didn't know if there was maybe a package I had to run
first before it would work, or an ini parameter that allows this. Thank
you for the non-condescending reply, unlike others here.
Ben
Chuck
> Comments embedded.
> bal...@comcast.net wrote:
>> I'm new to this DBA stuff and I can't seem to get index_stats to
>> populate. I'm running 9.2.0.5 with the compatible parameter set to
>> 8.1.0, I don't think that should matter though.
>
> Really? Disabling all of the 9i and 9iR2 features in a 9.2.0.5
> database doesn't matter? I would think otherwise.
>
>> I run
>> analyze index [index_name] validate structure;
>
> Which doesn't populate anything.
Really? It does every time I use it.
Anurag Varma
Then its possible you might be hitting some bug as Yong suggested.
You would have to open an iTar for it....
One last thing I wanted to point out:
analyze index .... validate structure ONLINE;
will not populate index_stats at least until 10.1.0.2
(although you do not state using this option .. I thought I'd point
this out).
while analyze index ... validate structure;
should populate index_stats .. or fail with an error if
the index structure is not valid (corrupt).
Anurag
fitzj...@cox.net
David Fitzjarrell