When should one rebuild an index?
shweta....@googlemail.com
We can use
ANALYZE INDEX <index> VALIDATE STRUCTURE
command on the affected indexes - each invocation of this command
creates a single row in the INDEX_STATS view.
Thus determining the indexes which are good candidates for rebuilding.
BUT my doubt is that if,
we use dbms_stats to gather the statstistics for CBO using cascade
option. Then if
ANALYZE INDEX <index> VALIDATE STRUCTURE is ran , that what will be
fate of the stattistics gathered using dbms_stat for index( cascade
option).
Also, analyze command is deprecated now. So is there any other way to
find out the indexes which needs rebuilding.?
Thanks.
Charles Hooper
Take a look at the following links, which should help you find the
answer to the question of when one should rebuild indexes:
http://richardfoote.wordpress.com/category/index-rebuild/ (several
very helpful blog entries)
http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/
http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Bob Jones
<shweta....@googlemail.com> wrote in message
news:3bddc9d2-d04f-4525...@h20g2000yqn.googlegroups.com...
INDEX_STATS is populated by ANALYZE only. Your CBO data collected by
DBMS_STATS are safe.
DA Morgan
I can think of a few worse criteria but you are reasonably close. As
does Charles Hooper I highly recommend you read Richard Foote's notes.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
DA Morgan
The CBO data is safe but what you are suggesting as criteria irrelevant.
If you truly want to know when to rebuild indexes read Richard's
comments. Read Jonathan Lewis' comments. And learn wield SYS_OP_LBID.
Tom Kyte finally managed to make an impact with respect to bind
variables after carrying the torch for it seems like a decade. Can
anyone put this index rebuild nonsense into the rubbish bin where
it belongs? Please!
Robert Klemme
> If you truly want to know when to rebuild indexes read Richard's
> comments. Read Jonathan Lewis' comments. And learn wield SYS_OP_LBID.
Maybe we need something like BAARF for index rebuilding - or even
rebrand BAARF to "battle against any rebuild flapdoodle".
> Tom Kyte finally managed to make an impact with respect to bind
> variables after carrying the torch for it seems like a decade.
That's the bad side of internet not forgetting anything, especially if
someone seems to be more proficient in search engine tuning of his pages
than in dealing with Oracle on a scientific basis. :-)
> Can
> anyone put this index rebuild nonsense into the rubbish bin where
> it belongs? Please!
But don't forget "PURGE DBA_RECYCLEBIN" afterwards! ;-)
Cheers
robert
Charles Hooper
> Maybe we need something like BAARF for index rebuilding - or even
> rebrand BAARF to "battle against any rebuild flapdoodle".
>
> someone seems to be more proficient in search engine tuning of his pages
> than in dealing with Oracle on a scientific basis. :-)
>
>
> robert
It seems that the links I pointed to in my original post in this
thread missed one of the reasons for rebuilding indexes, so that the
indexes can be moved from one tablespace to another, for instance if
someone wanted to move their indexes from a tablespace with a 2KB
block size to a tablespace with a 32KB block size, with the database
instance running on a Windows server.
http://www.freelists.org/post/oracle-l/Setting-db-32k-cache-size-breaks-11107,6
For thoughts on rebuilding indexes in larger block size tablespaces,
see the following link:
http://richardfoote.wordpress.com/category/index-block-size/
----
Search engine tuning of pages... that reminds me of a couple threads
on Oracle's OTN forums. Ever wonder how the redo log switches are
controlled by the size of the log_buffer?:
http://forums.oracle.com/forums/thread.jspa?threadID=837258
For those willing to perform searches, the effects of efforts toward
tuning search engine results to return one's pages first by repeatedly
posting multiple links to one's sites may quite easily backfire.
Ignore the first couple posts (a legitimate question) and the last
couple posts (a completely different topic) in the following thread:
http://forums.oracle.com/forums/thread.jspa?threadID=837914&tstart=0
The above thread was created as a result of this Statspack thread,
which was removed from the Oracle OTN forums, but is still in the
Google cache:
http://74.125.113.132/search?q=cache:URVx_XbrD74J:forums.oracle.com/forums/thread.jspa%3FmessageID%3D3159904%26tstart
To the OP, sorry for taking the thread a bit off topic. It is
important to not only understand when an index should be rebuilt, but
also to understand the potential problems with rebuilding. Just using
ANALYZE INDEX VALIDATE STRUCTURE against an index generates locks on
the underlying table which may cause problems:
http://forums.oracle.com/forums/thread.jspa?threadID=576921
Matthias Hoys
"Robert Klemme" <short...@googlemail.com> wrote in message
news:6rmkd3F...@mid.individual.net...
I believe that there are still valid reasons for rebuilding indexes, for
example when you remove a large amount of rows from a table, and you are
sure that table will never grow to the same size anymore (failed batch load
caused by programming errors etc.). Or not ?
Robert Klemme
> I believe that there are still valid reasons for rebuilding indexes,
That's exactly what Richard Foote says. Please read his excellent blog
(links have been posted already). It's only that the reasons and
situations are far less common than usually (i.e. in the myth adhering
camp) thought.
> for
> example when you remove a large amount of rows from a table, and you are
> sure that table will never grow to the same size anymore (failed batch load
> caused by programming errors etc.). Or not ?
Maybe, maybe not. You'd better check whether this index benefits from
rebuilding - and whether it's worthwhile (a few wasted blocks might be
ok compared to the hassle of rebuilding an index which is under heavy use).
Cheers
robert
--
remember.guy do |as, often| as.you_can - without end
Bob Jones
"DA Morgan" <damo...@psoug.org> wrote in message
news:12303572...@bubbleator.drizzle.com...
> Bob Jones wrote:
>> <shweta....@googlemail.com> wrote in message
>> news:3bddc9d2-d04f-4525...@h20g2000yqn.googlegroups.com...
>>> Hi
>>>
>>> We can use
>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>> command on the affected indexes - each invocation of this command
>>> creates a single row in the INDEX_STATS view.
>>> Thus determining the indexes which are good candidates for rebuilding.
>>>
>>> BUT my doubt is that if,
>>> we use dbms_stats to gather the statstistics for CBO using cascade
>>> option. Then if
>>> ANALYZE INDEX <index> VALIDATE STRUCTURE is ran , that what will be
>>> fate of the stattistics gathered using dbms_stat for index( cascade
>>> option).
>>>
>>> Also, analyze command is deprecated now. So is there any other way to
>>> find out the indexes which needs rebuilding.?
>>>
>>> Thanks.
>>>
>>
>> INDEX_STATS is populated by ANALYZE only. Your CBO data collected by
>> DBMS_STATS are safe.
>
> The CBO data is safe but what you are suggesting as criteria irrelevant.
>
What criteria?
DA Morgan
I will state it clearly.
The data collected by ANALYZE does not lead to a conclusion as to
whether an index should be rebuilt. In other words ... the fact that
CBO data is not destroyed is irrelevant. Time is being spent, CPU and
I/O wasted, producing a metric that is not a relevant criteria.
This is not different from the same mythologist promoting Buffer Cache
Hit Ratios. Yes you can generate a value. A numeric value. But valid
conclusions can not be drawn from that numeric value. Only, perhaps,
about the person that suggested making a decision based upon it.
Bob Jones
It doesn't look like OP has reached any conclusion yet. He was merely asking
a question. I think my response was relevant to his question. Whether he
should rebuild indexes based on the stats and other factors is not my call.
> This is not different from the same mythologist promoting Buffer Cache Hit
> Ratios. Yes you can generate a value. A numeric value. But valid
> conclusions can not be drawn from that numeric value. Only, perhaps,
> about the person that suggested making a decision based upon it.
Not different in the way BCHR is also irrelevant to performance and it is
just a number? That's fascinating.
Perhaps, instead of looking at stats, we just need an Ace Director to
orchestrate DBAs what to use or not to use in all situations.
Robert Klemme
> "DA Morgan" <damo...@psoug.org> wrote in message
> news:12304050...@bubbleator.drizzle.com...
>> Bob Jones wrote:
>>> "DA Morgan" <damo...@psoug.org> wrote in message
>>> news:12303572...@bubbleator.drizzle.com...
>>>> Bob Jones wrote:
>>>>> <shweta....@googlemail.com> wrote in message
>>>>> news:3bddc9d2-d04f-4525...@h20g2000yqn.googlegroups.com...
>>>>>> Hi
>>>>>>
>>>>>> We can use
>>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>>>>> command on the affected indexes - each invocation of this command
>>>>>> creates a single row in the INDEX_STATS view.
>>>>>> Thus determining the indexes which are good candidates for rebuilding.
>>>>>>
>>>>>> BUT my doubt is that if,
>>>>>> we use dbms_stats to gather the statstistics for CBO using cascade
>>>>>> option. Then if
>>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE is ran , that what will be
>>>>>> fate of the stattistics gathered using dbms_stat for index( cascade
>>>>>> option).
>>>>>>
>>>>>> Also, analyze command is deprecated now. So is there any other way to
>>>>>> find out the indexes which needs rebuilding.?
>>>>>>
>>>>> DBMS_STATS are safe.
>>>> The CBO data is safe but what you are suggesting as criteria irrelevant.
>>>>
>>> What criteria?
>> I will state it clearly.
>>
>> The data collected by ANALYZE does not lead to a conclusion as to
>> whether an index should be rebuilt. In other words ... the fact that
>> CBO data is not destroyed is irrelevant. Time is being spent, CPU and
>> I/O wasted, producing a metric that is not a relevant criteria.
>
> It doesn't look like OP has reached any conclusion yet. He was merely asking
> a question. I think my response was relevant to his question. Whether he
> should rebuild indexes based on the stats and other factors is not my call.
IMHO your notion of relevance here is a bit too formal - while part of
the OP's question was indeed whether DBMS_STATS data is safe when doing
ANALYZE the more important question he asked was how to determine
whether an index is eligible for rebuilding. If the answer is "not by
using ANALYZE" it is completely irrelevant whether or not ANALYZE
destroys DBMS_STATS data.
>> This is not different from the same mythologist promoting Buffer Cache Hit
>> Ratios. Yes you can generate a value. A numeric value. But valid
>> conclusions can not be drawn from that numeric value. Only, perhaps,
>> about the person that suggested making a decision based upon it.
>
> Not different in the way BCHR is also irrelevant to performance and it is
> just a number? That's fascinating.
Well, I guess Daniel means that the output of ANALYZE (which is
deprecated btw., this has also been mentioned already) is meaningless
without further information much the same way as a high or low BCHR is
meaningless without further info. A high BCHR can indicate that your
application is doing too much useless work (wasting CPU cycles doing
table scans in buffer cache) and a low BHCR can indicate that your app
does not have proper indexes (and thusly needs to do FTS for objects
larger than BC). BCHR of course is not irrelevant for performance
because a change most likely correlates with changed performance - but
it does not help you find out whether performance is ok or not and
what's wrong in case not. So it's relevant but useless.
Whatever numbers you get from ANALYZE - they are only a snapshot of the
current index structure. Without further information about the data,
change nature and frequency it is pretty useless because your perfectly
rebuilt index might be back to before in less time than you needed for
the rebuild rendering rebuilding of this index useless to harmful.
> Perhaps, instead of looking at stats, we just need an Ace Director to
> orchestrate DBAs what to use or not to use in all situations.
Well, delegation is a valid approach - there are even people getting
paid for doing consultancy... ;-)
Cheers
robert
DA Morgan
> Perhaps, instead of looking at stats, we just need an Ace Director to
> orchestrate DBAs what to use or not to use in all situations.
Cute Bob. Really cute. The advice you are hearing from Oakies, from
Aces, from internal Oracle resources is the same. If you wish to ignore
it that is certainly your choice.
None of us make a dollar if you take our advice. None of us lose a wink
of sleep if you ignore it. The simple fact is that sooner or later your
employer will contact one of us to bail the bilge. Whether you will
still be there when that happens is irrelevant tous. But a cautionary
note to you and others who think attitude trumps aptitude. There is a
reason why Oracle is being so successful selling the "you need fewer
DBAs" mantra. CIOs and CTOs see too many dollars wasted on people that
don't read. Too many dollars wasted on people that go to classes and
still keep doing things the old way. Too many dollars wasted on people
that think they know better than Tom Kyte (still not using bind
variables), Richard Foote (still rebuilding indexes), Jonathan Lewis
(still tuning based on BHCR), etc.
What I am telling you in simple terms is that you can run ANALYZE
INDEX from now until you are a member of the gray-hair-no-hair crowd
like me and you will not once see anything that will clearly indicate
that your database will benefit from an index rebuild. It is the wrong
tool. You can start figuring that out for yourself here if you wish:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4005.htm#SQLRF01105.
If you think I'm wrong write up a White Paper and I will be happy to
provide you an opportunity to present it at a conference. If you just
want to stand on the sidelines and throw stones the line forms to the
right.
Bob Jones
> IMHO your notion of relevance here is a bit too formal - while part of the
> OP's question was indeed whether DBMS_STATS data is safe when doing
> ANALYZE the more important question he asked was how to determine whether
> an index is eligible for rebuilding. If the answer is "not by using
> ANALYZE" it is completely irrelevant whether or not ANALYZE destroys
> DBMS_STATS data.
>
Hmmm, let me review OP's message again. I still don't see the part he asked
about whether an index is eligible for rebuilding. You are welcome to quote
his question here.
>>
>> Not different in the way BCHR is also irrelevant to performance and it is
>> just a number? That's fascinating.
>
> Well, I guess Daniel means that the output of ANALYZE (which is deprecated
> btw., this has also been mentioned already) is meaningless without further
> information much the same way as a high or low BCHR is meaningless without
> further info.
ANALYZE...VALIDATE is deprecaded? By whom and in what version? I don't know
what your definition of "meaningless" is here. How could something be
"meaningless" yet provides a specific piece of information.
> A high BCHR can indicate that your application is doing too much useless
> work (wasting CPU cycles doing table scans in buffer cache) and a low BHCR
> can indicate that your app does not have proper indexes (and thusly needs
> to do FTS for objects larger than BC). BCHR of course is not irrelevant
> for performance because a change most likely correlates with changed
> performance - but it does not help you find out whether performance is ok
> or not and what's wrong in case not. So it's relevant but useless.
>
It seems we have been through this before. How could something be relevant
yet useless? There is not a single system stat alone that can tell if
performance is ok and what's wrong with it, nor are they meant to. Are they
all useless?
> Whatever numbers you get from ANALYZE - they are only a snapshot of the
> current index structure. Without further information about the data,
> change nature and frequency it is pretty useless because your perfectly
> rebuilt index might be back to before in less time than you needed for the
> rebuild rendering rebuilding of this index useless to harmful.
>
No one is saying that cannot be the case - a special case I might add.
Bob Jones
> Bob Jones wrote:
>
>> Perhaps, instead of looking at stats, we just need an Ace Director to
>> orchestrate DBAs what to use or not to use in all situations.
>
> Cute Bob. Really cute. The advice you are hearing from Oakies, from
> Aces, from internal Oracle resources is the same. If you wish to ignore
> it that is certainly your choice.
>
> None of us make a dollar if you take our advice. None of us lose a wink
> of sleep if you ignore it. The simple fact is that sooner or later your
> employer will contact one of us to bail the bilge. Whether you will still
> be there when that happens is irrelevant tous. But a cautionary
> note to you and others who think attitude trumps aptitude. There is a
> reason why Oracle is being so successful selling the "you need fewer
> DBAs" mantra. CIOs and CTOs see too many dollars wasted on people that
> don't read. Too many dollars wasted on people that go to classes and
> still keep doing things the old way. Too many dollars wasted on people
> that think they know better than Tom Kyte (still not using bind
> variables), Richard Foote (still rebuilding indexes), Jonathan Lewis
> (still tuning based on BHCR), etc.
>
Hmmm, I have been here well over 10 years and still happily employed. In
fact, our company is still doing very well in this down economy.
There is nothing wrong with showing attitude or aptitude, just not by
throwing people's names around though.
> What I am telling you in simple terms is that you can run ANALYZE
> INDEX from now until you are a member of the gray-hair-no-hair crowd
> like me and you will not once see anything that will clearly indicate
> that your database will benefit from an index rebuild. It is the wrong
> tool. You can start figuring that out for yourself here if you wish:
> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4005.htm#SQLRF01105.
>
I see no part of this doc suggesting ANALYZE being useless in determining
index rebuild. Where?
Yes, it may take some time to make sense of the stats, but no longer than
finding the NEED_REBUILD column in the index views.
> If you think I'm wrong write up a White Paper and I will be happy to
> provide you an opportunity to present it at a conference. If you just
> want to stand on the sidelines and throw stones the line forms to the
> right.
> --
Actually someone already did that at the last OpenWorld. ANALYZE and index
rebuilds were repeatedly used together to illustrate his points. Oracle
might still have a copy of it.
Robert Klemme
> I don't know
> what your definition of "meaningless" is here. How could something be
> "meaningless" yet provides a specific piece of information.
It provides a bit of data, whether that is information depends on the
usefulness. If you cannot do anything with it, it's just data.
http://en.wikipedia.org/wiki/Data#Meaning_of_data.2C_information_and_knowledge
>> Whatever numbers you get from ANALYZE - they are only a snapshot of the
>> current index structure. Without further information about the data,
>> change nature and frequency it is pretty useless because your perfectly
>> rebuilt index might be back to before in less time than you needed for the
>> rebuild rendering rebuilding of this index useless to harmful.
>
> No one is saying that cannot be the case - a special case I might add.
From what I read in Richard's blog it is a much more common case than
usually assumed.
Cheers
robert
Bob Jones
> On 28.12.2008 02:41, Bob Jones wrote:
>
>> I don't know what your definition of "meaningless" is here. How could
>> something be "meaningless" yet provides a specific piece of information.
>
> It provides a bit of data, whether that is information depends on the
> usefulness. If you cannot do anything with it, it's just data.
>
> http://en.wikipedia.org/wiki/Data#Meaning_of_data.2C_information_and_knowledge
>
Hmmm, so it provides meaningless data about index structures and space
usage. I wonder why Oracle went through all that trouble.
>>> Whatever numbers you get from ANALYZE - they are only a snapshot of the
>>> current index structure. Without further information about the data,
>>> change nature and frequency it is pretty useless because your perfectly
>>> rebuilt index might be back to before in less time than you needed for
>>> the rebuild rendering rebuilding of this index useless to harmful.
>>
>> No one is saying that cannot be the case - a special case I might add.
>
> From what I read in Richard's blog it is a much more common case than
> usually assumed.
>
Common case for you may not be for OP or others. I for one would not make
any assumption of OP's case and dismiss what he was doing.
DA Morgan
Thakn you Robert ... very well said.
DA Morgan
You also see no part suggesting it is useful either do you? Are you
able to acknowledge that fact?
But here: Knock yourself out explaining whether this index should or
should not be rebuilt based on the information generated by ANALYZE.
SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks,
btree_space, used_space
2 FROM index_stats
3 WHERE name = 'IX_TEST';
no rows selected
SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks,
btree_space, used_space
2 FROM index_stats
3 WHERE name = 'IX_TEST';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ---------- ----------
----------- ----------
2 128 20863 97 96 1
784032 768604
SQL> delete from test;
20863 rows deleted.
SQL> commit;
SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks,
btree_space, used_space
2 FROM index_stats
3 WHERE name = 'IX_TEST';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ---------- ----------
----------- ----------
2 128 20863 97 96 1
784032 768604
SQL>
As you can not the obvious inference is that ANALYZE does not, in and
of itself, produce information of value in determining whether there
will be a benefit from an index rebuild.
Now lets try it my way. I rebuild and repopulate that table.
SQL> SELECT rows_per_block, count(*) blocks
2 FROM (
3 SELECT /*+ cursor_sharing_exact
4 dynamic_sampling(0)
5 no_monitoring
6 no_expand
7 index_ffs(test,ix_test)
8 noparallel_index(test,ix_test)
9 */
10 sys_op_lbid(90508, 'L', test.rowid) block_id,
11 COUNT(*) rows_per_block
12 FROM test
13 WHERE object_name IS NOT NULL
14 GROUP BY sys_op_lbid(90508, 'L', test.rowid))
15 GROUP BY rows_per_block;
ROWS_PER_BLOCK BLOCKS
-------------- ----------
244 1
245 1
227 2
198 1
194 3
222 2
213 3
217 2
235 2
209 1
207 2
218 2
211 3
224 1
205 1
210 3
223 1
229 3
253 1
230 2
215 3
201 2
314 1
193 1
232 1
206 2
226 1
248 1
231 2
241 1
199 3
254 2
228 1
243 3
200 1
233 1
188 1
221 4
203 3
236 1
225 1
195 2
202 4
216 2
7 1
237 1
197 6
219 1
204 4
234 2
208 1
51 rows selected.
SQL> delete from test;
20863 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT rows_per_block, count(*) blocks
2 FROM (
3 SELECT /*+ cursor_sharing_exact
4 dynamic_sampling(0)
5 no_monitoring
6 no_expand
7 index_ffs(test,ix_test)
8 noparallel_index(test,ix_test)
9 */
10 sys_op_lbid(90508, 'L', test.rowid) block_id,
11 COUNT(*) rows_per_block
12 FROM test
13 WHERE object_name IS NOT NULL
14 GROUP BY sys_op_lbid(90508, 'L', test.rowid))
15 GROUP BY rows_per_block;
no rows selected
SQL>
There are valid and invalid means of determining whether an index will
benefit from a rebuild. The above demo of SYS_OP_LBID produces one
useful data point. It too, by itself, is insufficient in most cases.
But at least the numbers it generates are relevant.
DA Morgan
> "Robert Klemme" <short...@googlemail.com> wrote in message
> news:6rp8o6F...@mid.individual.net...
>> On 28.12.2008 02:41, Bob Jones wrote:
>>
>>> I don't know what your definition of "meaningless" is here. How could
>>> something be "meaningless" yet provides a specific piece of information.
>> It provides a bit of data, whether that is information depends on the
>> usefulness. If you cannot do anything with it, it's just data.
>>
>> http://en.wikipedia.org/wiki/Data#Meaning_of_data.2C_information_and_knowledge
>>
>
> Hmmm, so it provides meaningless data about index structures and space
> usage. I wonder why Oracle went through all that trouble.
Not meaningless with respect to index structures and space usage.
Meaningless within the context of the topic being discussed. Meaningless
as a criteria for rebuilding indexes. Not everything that is not white
is black. There are shades of gray and a rainbow's worth of colors in
between.
Bob Jones
> Bob Jones wrote:
>> "Robert Klemme" <short...@googlemail.com> wrote in message
>> news:6rp8o6F...@mid.individual.net...
>>> On 28.12.2008 02:41, Bob Jones wrote:
>>>
>>>> I don't know what your definition of "meaningless" is here. How could
>>>> something be "meaningless" yet provides a specific piece of
>>>> information.
>>> It provides a bit of data, whether that is information depends on the
>>> usefulness. If you cannot do anything with it, it's just data.
>>>
>>> http://en.wikipedia.org/wiki/Data#Meaning_of_data.2C_information_and_knowledge
>>>
>>
>> Hmmm, so it provides meaningless data about index structures and space
>> usage. I wonder why Oracle went through all that trouble.
>
> Not meaningless with respect to index structures and space usage.
> Meaningless within the context of the topic being discussed. Meaningless
> as a criteria for rebuilding indexes. Not everything that is not white
> is black. There are shades of gray and a rainbow's worth of colors in
> between.
Index structure and space usage are meaningless in determining index
rebuild? So in your opinion what is meaningful?
Bob Jones
So why did you point out the doc if it does not say neither?
Only less than half of the columns here but that's ok.
> As you can not the obvious inference is that ANALYZE does not, in and
> of itself, produce information of value in determining whether there
> will be a benefit from an index rebuild.
>
I am not surprised you reached this conclusion based on only the example
above.
DA Morgan
You were given a very good list of links earlier in this thread. Did you
follow them? If not then I would suggest you start there.
There is no question in my mind that two best non-Oracle employee
experts on the subject are Richard Foote and Jonathan Lewis. It would
be far more valuable for you to read their opinions than mine.
What I hope you pay special attention to when you read their comments is
that the value in rebuilding an index has something to do with the
efficiency of storage but that is only one consideration out of many.
Also critically important is whether the index, by the nature of how it
is being used (or misused), will return to that same inefficient state
almost immediately. If it will then an index rebuild is going to be
worthless no matter the metrics. And this critical piece of information
can not be found within any one tool or technique.
Part of being an Ace is knowing that you don't know everything but,
hopefully, knowing enough experts that you can find out what you don't
know when questions arise. The most valuable part of being an Ace to me
is my relationship with other Aces and the many Oakies I've met over
the years. Just this year, here in Seattle, we have had visits from Tom
Kyte, Mogens Norgaard, Cary Millsap, Kevin Closson, Jeremiah Wilton,
Kyle Hailey, Richard Foote, Karen Morton, Hans Forbrich, Tim Tow, and
Ed Roske. No single person is "that" smart. Everyone working together
smart enough. We all have things we can learn from others if we are
receptive.
hpuxrac
snip
> Index structure and space usage are meaningless in determining index
> rebuild? So in your opinion what is meaningful?
Bob it is "getting to be" fairly well accepted in oracle dba land that
for the most part the need to rebuild indexes on a regular scheduled
ongoing basis is way oversold.
That being said, many shops have available cpu and time and schedules
that allow scheduled rebuilds to not interfere with business
priorities.
One can build test cases which either demonstrate that "little to no
gains" to applications ( and important business activities ) are
gained when doing certain rebuilds or "significant gains" are made.
Cary Millsap has a good definition that is relevant to meaningful if
you want to read his book on Optimizing Oracle Performance. To badly
paraphrase it the basic concept is when the net gain to the business
of performing the performance tuning ( which an index rebuild is one
potential type of a performance tuning method ( or at least an attempt
at one )) is not much more than the cost of doing the performance
tuning ... that's when to stop.
The guy you are posting back and forth with just loves to keep posting
and will try to make strange points unrelated to your replies.
hpuxrac
> Part of being an Ace is knowing that you don't know everything but,
> hopefully, knowing enough experts that you can find out what you don't
> know when questions arise.
Yikes ... feeling sick ...
Any real professional in any occupation knows this.
The Ace thing is a sign that people have spent a large significant
amount of time promoting themselves in one way or another. Not that
anyone posting here does that much.
sybr...@hccnet.nl
<johnb...@sbcglobal.net> wrote:
>The Ace thing is a sign that people have spent a large significant
>amount of time promoting themselves in one way or another. Not that
>anyone posting here does that much.
>
So why is Burleson not an ACE?
Hpuxrac, your behavior with respect to Daniel Morgan is very annoying.
So far, you have done everything to ridicule him, at the same time
boasting you know everything better, without bringing up anything
substantial.
I suggest for a New Years resolution you consider to stop your little
vendetta, take it offline, or GET LOST
here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Sybrand Bakker
Senior Oracle DBA
sybr...@hccnet.nl
<johnb...@sbcglobal.net> wrote:
>
>The guy you are posting back and forth with just loves to keep posting
>and will try to make strange points unrelated to your replies.
The guy who is responding now is on a personal vendetta with that
other guy, and will bring just up anything to ridicule him.
He is not a grain better than the guy he is ridiculing.
Noons
and he doesn't identify himself ever,
which is not the case with DA Morgan.
Anyone who insists on personal vendettas
on the Usenet without full identification is
immediately defined as a low-life troll with
no credibility whatsoever.
And in case there is any doubt,
my name is Nuno Souto.
Mladen Gogala
> Hpuxrac, your behavior with respect to Daniel Morgan is very annoying.
> So far, you have done everything to ridicule him, at the same time
> boasting you know everything better, without bringing up anything
> substantial.
That's the matter of taste. I've been annoyed by Dan and even had public
quarrels with Dan before. I've also been annoyed by hpuxrac before. As
inconceivable as it may seem, perhaps one could even find a few people
annoyed by me.
> I suggest for a New Years resolution you consider to stop your little
> vendetta, take it offline, or GET LOST
> here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
And what gives you right to tell anyone to "get lost"? This forum is
un-moderated so you are just being rude, not for the 1st time. You're
probably just unsuccessfully trying to be funny.
--
Mladen Gogala
http://mgogala.freehostia.com
Robert Klemme
> Now lets try it my way. I rebuild and repopulate that table.
>
> SQL> SELECT rows_per_block, count(*) blocks
> 2 FROM (
> 3 SELECT /*+ cursor_sharing_exact
> 4 dynamic_sampling(0)
> 5 no_monitoring
> 6 no_expand
> 7 index_ffs(test,ix_test)
> 8 noparallel_index(test,ix_test)
> 9 */
> 10 sys_op_lbid(90508, 'L', test.rowid) block_id,
> 11 COUNT(*) rows_per_block
> 12 FROM test
> 13 WHERE object_name IS NOT NULL
> 14 GROUP BY sys_op_lbid(90508, 'L', test.rowid))
> 15 GROUP BY rows_per_block;
Daniel, is there any particular reason why you did not include any
sorting (presumably on ROWS_PER_BLOCK ASC or maybe on BLOCKS DESC) here?
It seems to me that the result is then easier to digest. You do not
have it on http://www.psoug.org/reference/undocumented.html#uosl so I am
assuming there is a reason for this.
Unfortunately I don't have an Oracle database at hand right now so I
cannot experiment myself. However, I wonder whether there is a way
using documented features to get at the number of index entries per
block. Using a query on DBA_EXTENDS it should be possible to generate a
list of all (FILE_ID, BLOCK_ID)s of a database object. Now we would
only need a function that would return the number of entries given
(FILE_ID, BLOCK_ID).
If one could generate a list of all ROWIDs of all index entries (which I
doubt is possible because index blocks look very different from data
blocks) one could use functions in DBMS_ROWID to extract block addresses
and use that for grouping.
An alternative might be to use DBMS_SPACE.SPACE_USAGE to get at least a
rough idea how evenly rows are distributed across index blocks and
DBMS_SPACE.UNUSED_SPACE to see how much space is "wasted".
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#CACBDJIH
Kind regards
robert
DA Morgan
> I am not surprised you reached this conclusion based on only the example
> above.
Rerun my demo and include any and all columns you wish. Then, using
those columns and the data they contain, make your case as to how it
constitutes sufficient information to determine an index should be
rebuilt.
DA Morgan
> On 28.12.2008 22:50, DA Morgan wrote:
>
>> Now lets try it my way. I rebuild and repopulate that table.
>>
>> SQL> SELECT rows_per_block, count(*) blocks
>> 2 FROM (
>> 3 SELECT /*+ cursor_sharing_exact
>> 4 dynamic_sampling(0)
>> 5 no_monitoring
>> 6 no_expand
>> 7 index_ffs(test,ix_test)
>> 8 noparallel_index(test,ix_test)
>> 9 */
>> 10 sys_op_lbid(90508, 'L', test.rowid) block_id,
>> 11 COUNT(*) rows_per_block
>> 12 FROM test
>> 13 WHERE object_name IS NOT NULL
>> 14 GROUP BY sys_op_lbid(90508, 'L', test.rowid))
>> 15 GROUP BY rows_per_block;
>
> Daniel, is there any particular reason why you did not include any
> sorting (presumably on ROWS_PER_BLOCK ASC or maybe on BLOCKS DESC) here?
> It seems to me that the result is then easier to digest. You do not
> have it on http://www.psoug.org/reference/undocumented.html#uosl so I am
> assuming there is a reason for this.
No good reason and you are absolutely correct that sorting would help.
Also helpful would be buckets with counts so one could quickly grasp
the number of blocks in one container as compared with another.
If I get some free time I may rewrite though if anyone else would like
to I will be happy to post their work and their name.
> Unfortunately I don't have an Oracle database at hand right now so I
> cannot experiment myself. However, I wonder whether there is a way
> using documented features to get at the number of index entries per
> block. Using a query on DBA_EXTENDS it should be possible to generate a
> list of all (FILE_ID, BLOCK_ID)s of a database object. Now we would
> only need a function that would return the number of entries given
> (FILE_ID, BLOCK_ID).
> If one could generate a list of all ROWIDs of all index entries (which I
> doubt is possible because index blocks look very different from data
> blocks) one could use functions in DBMS_ROWID to extract block addresses
> and use that for grouping.
>
> An alternative might be to use DBMS_SPACE.SPACE_USAGE to get at least a
> rough idea how evenly rows are distributed across index blocks and
> DBMS_SPACE.UNUSED_SPACE to see how much space is "wasted".
One quick solution is to use DBMS_SPACE.CREATE_INDEX_COST to get a good
idea of how big an index should be: Then compare it with the actual
index. http://www.psoug.org/reference/dbms_space.html
Bob Jones
"hpuxrac" <johnb...@sbcglobal.net> wrote in message
news:ff677f90-7ac5-463a...@s9g2000prg.googlegroups.com...
On Dec 28, 7:08 pm, "Bob Jones" <em...@me.not> wrote:
snip
>> Index structure and space usage are meaningless in determining index
>> rebuild? So in your opinion what is meaningful?
>Bob it is "getting to be" fairly well accepted in oracle dba land that
>for the most part the need to rebuild indexes on a regular scheduled
>ongoing basis is way oversold.
True, but that's not what we are debating here. We are talking about whether
INDEX_STATS is useful in determining index rebuild.
>That being said, many shops have available cpu and time and schedules
>that allow scheduled rebuilds to not interfere with business
>priorities.
>One can build test cases which either demonstrate that "little to no
>gains" to applications ( and important business activities ) are
>gained when doing certain rebuilds or "significant gains" are made.
Yes, it is very application dependent. Index rebuilds often yield different
results in different situations.
Back in the days when I was a developer, INDEX_STATS and other info were
routinely used to find potential index rebuilds. There were disagreements on
when to rebuild, but none thought INDEX_STATS was useless.
>Cary Millsap has a good definition that is relevant to meaningful if
>you want to read his book on Optimizing Oracle Performance. To badly
>paraphrase it the basic concept is when the net gain to the business
>of performing the performance tuning ( which an index rebuild is one
>potential type of a performance tuning method ( or at least an attempt
>at one )) is not much more than the cost of doing the performance
>tuning ... that's when to stop.
>The guy you are posting back and forth with just loves to keep posting
>and will try to make strange points unrelated to your replies.
I know.
Bob Jones
"Noons" <wizo...@yahoo.com.au> wrote in message
news:gja0p7$r1v$2...@news.motzarella.org...
I have never heard anything quite as absurd. It appears you share Captain
Morgan's believe - your credibility in this group is based on your name and
not what you post here.
> And in case there is any doubt,
> my name is Nuno Souto.
Why would it matter? I am not expecting someone here to reach me or trying
to sell anything.
Bob Jones
> Bob Jones wrote:
>
>> I am not surprised you reached this conclusion based on only the example
>> above.
>
> Rerun my demo and include any and all columns you wish. Then, using
> those columns and the data they contain, make your case as to how it
> constitutes sufficient information to determine an index should be
> rebuilt.
Sufficient information? Are we changing the topic again?
What a simplistic approach to tuning. If I can make a conclusion that
INDEX_STATS is useless just base on a Mickey Mouse example without any other
data, life would be easy. Anyone has a crystal ball to lend?
Noons
> > and he doesn't identify himself ever,
> > which is not the case with DA Morgan.
> > Anyone who insists on personal vendettas
> > on the Usenet without full identification is
> > immediately defined as a low-life troll with
> > no credibility whatsoever.
>
> I have never heard anything quite as absurd.
Apparently, your hearing is bad. If you'd care
to READ instead, which is what people do on Usenet,
you might find it more gratifying? It's not hard, just
go back above and carry on.
> It appears you share Captain
> Morgan's believe - your credibility in this group is based on your name and
> not what you post here.
I don't share ANYTHING with Daniel: we have disagreed many
times before, here and elsewhere, it's on public record.
And anyway, you don't know ANYTHING about me to claim I
share anything with anyone, so stop trying to be childish:
it borders on boring and it's demeaning to you.
> Why would it matter?
Because someone might not be aware that I have
the courage of putting my name where my writing
is. Something sorely lacking on many cowards in the
Usenet who use anonimity as a way to hide while
stating their lies.
> I am not expecting someone here to reach me or trying
> to sell anything.
Good. Excellent, even!
And what the heck has that got to do with anything?
Mladen Gogala
> Index structure and space usage are meaningless in determining index
> rebuild? So in your opinion what is meaningful?
Nothing. Indexes do not need to be rebuilt on regular basis, period.
Please, read this:
http://www.oracle.com/global/nl/education/specials/pdfs/0806_richard.pdf
Mladen Gogala
> Yes, it is very application dependent. Index rebuilds often yield
> different results in different situations.
And index rebuilds can hit a bug and yield "invalid rowid" error, as
reported by J. Lewis.
shweta....@googlemail.com
for discussing this topic at length.
Jonathan Lewis
"Mladen Gogala" <gogala...@gmail.com> wrote in message
news:gjdbrc$ivs$1...@solani.org...
> On Mon, 29 Dec 2008 20:43:52 -0600, Bob Jones wrote:
>
>> Yes, it is very application dependent. Index rebuilds often yield
>> different results in different situations.
>
> And index rebuilds can hit a bug and yield "invalid rowid" error, as
> reported by J. Lewis.
>
It's not so much a bug as a design defect that could be quite hard to
code around given it's connection with cross-DDL read-consistency -
see http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/
for anyone who hasn't seen the problem before.
And there are cases where indexes fairly obviously need some sort
of housekeeping - and often you don't need to run any query
against them to know that they are going to run into trouble, e.g.
http://jonathanlewis.wordpress.com/2008/09/26/index-analysis/
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
DA Morgan
> "DA Morgan" <damo...@psoug.org> wrote in message
> news:12305653...@bubbleator.drizzle.com...
>> Bob Jones wrote:
>>
>>> I am not surprised you reached this conclusion based on only the example
>>> above.
>> Rerun my demo and include any and all columns you wish. Then, using
>> those columns and the data they contain, make your case as to how it
>> constitutes sufficient information to determine an index should be
>> rebuilt.
>
> Sufficient information? Are we changing the topic again?
You may be ... I haven't wavered an angstrom.
> What a simplistic approach to tuning. If I can make a conclusion that
> INDEX_STATS is useless just base on a Mickey Mouse example without any other
> data, life would be easy. Anyone has a crystal ball to lend?
Perhaps you should reread this thread from the beginning.
To be honest, and I should be, I intentionally let this thread mislead
the conversation just to see if anyone had actually used ANALYZE INDEX
to make these decisions.
Given that I introduced a flagrant and obvious error the result to that
question is clear.
The functionality that might be used is ANALYZE INDEX <index_name>
COMPUTE STATISTICS. That no one noticed I used VALIDATE STRUCTURE
says what needs to be said.
But, having run a full battery of tests using COMPUTE STATISTICS I will
state, here and for the record, that it too provides a metrics that are
not a reliable source of information as to whether an index would
benefit from a rebuild.
Bob Jones
"Noons" <wizo...@gmail.com> wrote in message
news:94e1a6f6-ac55-4a46...@v5g2000prm.googlegroups.com...
> Bob Jones wrote:
>
>> > and he doesn't identify himself ever,
>> > which is not the case with DA Morgan.
>> > Anyone who insists on personal vendettas
>> > on the Usenet without full identification is
>> > immediately defined as a low-life troll with
>> > no credibility whatsoever.
>>
>> I have never heard anything quite as absurd.
>
> Apparently, your hearing is bad. If you'd care
> to READ instead, which is what people do on Usenet,
> you might find it more gratifying? It's not hard, just
> go back above and carry on.
>
No, I don't find reading your comments gratifying at all. It is actually
very boring.
>> It appears you share Captain
>> Morgan's believe - your credibility in this group is based on your name
>> and
>> not what you post here.
>
> I don't share ANYTHING with Daniel: we have disagreed many
> times before, here and elsewhere, it's on public record.
> And anyway, you don't know ANYTHING about me to claim I
> share anything with anyone, so stop trying to be childish:
> it borders on boring and it's demeaning to you.
>
It shows just the opposite in this thread. You also like to go off the topic
and to other threads.
>> Why would it matter?
>
> Because someone might not be aware that I have
> the courage of putting my name where my writing
> is.
> Something sorely lacking on many cowards in the
> Usenet who use anonimity as a way to hide while
> stating their lies.
>
Wow, having a little too much Captain Morgan in you?
>> I am not expecting someone here to reach me or trying
>> to sell anything.
>
> Good. Excellent, even!
> And what the heck has that got to do with anything?
That means using your "real name" doesn't do any good here, especially when
it is reflected poorly.
Bob Jones
> On Sun, 28 Dec 2008 18:08:46 -0600, Bob Jones wrote:
>
>
>> Index structure and space usage are meaningless in determining index
>> rebuild? So in your opinion what is meaningful?
>
> Nothing. Indexes do not need to be rebuilt on regular basis, period.
> Please, read this:
> http://www.oracle.com/global/nl/education/specials/pdfs/0806_richard.pdf
>
Who said they need to be rebuilt regularly? Please read this thread first.
Bob Jones
> Bob Jones wrote:
>> "DA Morgan" <damo...@psoug.org> wrote in message
>> news:12305653...@bubbleator.drizzle.com...
>>> Bob Jones wrote:
>>>
>>>> I am not surprised you reached this conclusion based on only the
>>>> example above.
>>> Rerun my demo and include any and all columns you wish. Then, using
>>> those columns and the data they contain, make your case as to how it
>>> constitutes sufficient information to determine an index should be
>>> rebuilt.
>>
>> Sufficient information? Are we changing the topic again?
>
> You may be ... I haven't wavered an angstrom.
>
Really? From irrelevance to sufficient information seems to be quite a shift
to me.
>> What a simplistic approach to tuning. If I can make a conclusion that
>> INDEX_STATS is useless just base on a Mickey Mouse example without any
>> other data, life would be easy. Anyone has a crystal ball to lend?
>
> Perhaps you should reread this thread from the beginning.
>
> To be honest, and I should be, I intentionally let this thread mislead
> the conversation just to see if anyone had actually used ANALYZE INDEX
> to make these decisions.
>
> Given that I introduced a flagrant and obvious error the result to that
> question is clear.
>
> The functionality that might be used is ANALYZE INDEX <index_name>
> COMPUTE STATISTICS. That no one noticed I used VALIDATE STRUCTURE
> says what needs to be said.
>
> But, having run a full battery of tests using COMPUTE STATISTICS I will
> state, here and for the record, that it too provides a metrics that are
> not a reliable source of information as to whether an index would
> benefit from a rebuild.
Neither of us need to reread the thread. I think it is very clear to
everyone now.
Noons
>
> No, I don't find reading your comments gratifying at all. It is actually
> very boring.
Well, the solution for anyone with half a brain
is to stop reading them. Because if you are
expecting me to stop because of your boringness,
you are going to ahve to wait a looooong time...
> It shows just the opposite in this thread. You also like to go off the topic
> and to other threads.
Oh. Ad-homine now?
Here is a clue, troll:
PISS OFF!
*plonk*
Shakespeare
Shakespeare
Bob Jones
Bob Jones
> Bob Jones wrote,on my timestamp of 31/12/2008 3:00 PM:
>
>>
>> No, I don't find reading your comments gratifying at all. It is actually
>> very boring.
>
> Well, the solution for anyone with half a brain
> is to stop reading them. Because if you are
> expecting me to stop because of your boringness,
> you are going to ahve to wait a looooong time...
>
>
Please stop spamming here. Nothing, absolutely nothing you posted here is
relevant to this thread or even Oracle.
Anyone with a brain at all would stop posting this kind of nonsense.
Mladen Gogala
>>
> Please stop spamming here. Nothing, absolutely nothing you posted here
> is relevant to this thread or even Oracle. Anyone with a brain at all
> would stop posting this kind of nonsense.
Pal, you're talking to Nuno Suoto, one of the most respected long term
members of this group. This is it, I am putting you back to my kill file
where you have been before, because of the shameless trolling about the
BCHR. Plonk. I advise the other regulars to do the same.
hpuxrac
snip
> Pal, you're talking to Nuno Suoto, one of the most respected long term
> members of this group.
At one time Nuno did actively participate here.
In the past couple of years well not so much.
Noons
> At one time Nuno did actively participate here.
>
> In the past couple of years well not so much.
Any wonder why?
Noons
> where you have been before, because of the shameless trolling about the
> BCHR. Plonk. I advise the other regulars to do the same.
Don't waste much time with this one, Mladen.
Another example of the "shock" brigade:
<let's abuse everyone from a position of "superiority">
Well known tactic of the imbecile or incompetent.
Mladen Gogala
>> In the past couple of years well not so much.
>
> Any wonder why?
No, I toned down my activity, too. I had a good laughter when I took
a look at Steve Adams' politically correct oracle list. Juan Pacheco
Reyes, an old "friend" of mine, just advised the list to look at some
"astral projection" book. I wonder whether astral projections could help
him with diagnosing the performance problems.
Sad conclusion is that all oracle lists have become meeting place for
bozos and rude newbies, not willing to invest a minimal effort into
learning. All things being said, this is still my favorite place because
there is no censorship. I would advise B.J. to go to Steve's list. It's
useless anyway but Steve will duly protect his feelings from being hurt.
Noons
> a look at Steve Adams' politically correct oracle list. Juan Pacheco
> Reyes, an old "friend" of mine, just advised the list to look at some
> "astral projection" book. I wonder whether astral projections could help
> him with diagnosing the performance problems.
Yeah. Funny enough, I actually contribute financially
to that list being up. Few do. But I get slammed for
inadvertently posting a "ping" and God forbid I dare post
more than 80% of the previous post.
Let's see what "Juanito" gets?...
> learning. All things being said, this is still my favorite place because
> there is no censorship.
Indeed: the main motivation for coming back from time to time.
Most other moderated lists including Oracle's own seem to
welcome mainly conformists, "line followers" or "seekers
of the truth about dual"...
I'd gladly have dizwell's forum back: at least there one
could argue serious subjects and truly learn through peer
discussion with only the occasional slap from HJR. I did offer
to help the financial side of the forum if it was too much of
a hurdle but he decided to shut it down...
Ah well, it was his site: he was entitled to run it whichever
way he wanted - and I'm too busy to run sites. A pity, though.
Noons
> learning. All things being said, this is still my favorite place because
> there is no censorship.
Only reason I still come back from time to time.
I'd rather put up with bozos like BJ than dull
arse-kissers and rude newbies desperately looking
for any notoriety or the definition of dual.
And that's unfortunately what populats most other
lists.
Ah well, a new year is ahead. Let's hope idiocracy
is delayed for at least another 12 months.
Palooka
> I'd gladly have dizwell's forum back: at least there one
> could argue serious subjects and truly learn through peer
> discussion with only the occasional slap from HJR. I did offer
> to help the financial side of the forum if it was too much of
> a hurdle but he decided to shut it down...
> Ah well, it was his site: he was entitled to run it whichever
> way he wanted - and I'm too busy to run sites. A pity, though.
Curmudgeonly is the word which springs to mind. Nevertheless I am with
you 100% on this one. HJR's site and contributions are sorely missed here.
Have you seen his DORIS script for easing Linux Oracle installations,
btw? It's a beaut, as our antipodean friends would say.
Cheers,
Palooka (please forgive the anonymity; I have reasons)
Noons
>
> Curmudgeonly is the word which springs to mind. Nevertheless I am with
> you 100% on this one. HJR's site and contributions are sorely missed here.
Not just here. The forum was an excellent source of
all sorts of good info, OS and Oracle related...
> Have you seen his DORIS script for easing Linux Oracle installations,
> btw? It's a beaut, as our antipodean friends would say.
Haven't seen it, have heard of it. I've been away from
the Linux world for nearly two years now, apart from Suse
on my laptop.
> Palooka (please forgive the anonymity; I have reasons)
NP, fine with me provided it stays away
from personal attacks.
sybr...@hccnet.nl
wrote:
>It's a beaut, as our antipodean friends would say.
HEY, YOU are the Antipodeans!!!
--
Noons
> On Fri, 02 Jan 2009 20:00:31 +0000, Palooka <nob...@nowhere.com>
> wrote:
>
>> It's a beaut, as our antipodean friends would say.
>
> HEY, YOU are the Antipodeans!!!
>
LOL! Sheeesh...
That's what they get with all
that blood up around their ears...
Bob Jones
"Mladen Gogala" <mgo...@yahoo.com> wrote in message
news:gjj3g0$2g8$1...@ss408.t-com.hr...
So far you have posted nothing meaningful in this thread. Please for once
use your own judgement. Stop being an ass-kisser. It serves no purpose here.
Btw, you are not obligated to respond here at all.
Bob Jones
> a look at Steve Adams' politically correct oracle list. Juan Pacheco
> Reyes, an old "friend" of mine, just advised the list to look at some
> "astral projection" book. I wonder whether astral projections could help
> him with diagnosing the performance problems.
>
> Sad conclusion is that all oracle lists have become meeting place for
> bozos and rude newbies, not willing to invest a minimal effort into
> learning. All things being said, this is still my favorite place because
> there is no censorship. I would advise B.J. to go to Steve's list. It's
> useless anyway but Steve will duly protect his feelings from being hurt.
>
Actually I have been protecting your feeling all along by not calling you a
moron.
Bob Jones
> Mladen Gogala wrote,on my timestamp of 2/01/2009 8:03 PM:
>
>> learning. All things being said, this is still my favorite place because
>> there is no censorship.
>
Only reason I still come back from time to time.
arse-kissers and rude newbies like MG.
Bob Jones
Please stop kissing your own ass and each others. It is gettnig to be too
noisy.
> Well known tactic of the imbecile or incompetent.
You call ass kissing a tatic?
joel garry
> "Noons" <wizofo...@yahoo.com.au> wrote in message
I must say, I respect those guys because of the informative postings
they've made over a long period of time (and I mean decades in the
case of Noons).
Can't say as I've seen anything really useful from you. Care to point
towards something you've done that would improve my opinion? Nothing
wrong with lurking, nothing wrong with opinions, but it does help
everyone if people contribute positively when they can, and no one
wants to see plain negativity.
jg
--
@home.com is bogus.
The Al Franken Decade!
Bob Jones
"joel garry" <joel-...@home.com> wrote in message
news:506a42b2-cee6-4064...@z28g2000prd.googlegroups.com...
On Jan 5, 4:54 pm, "Bob Jones" <em...@me.not> wrote:
> "Noons" <wizofo...@yahoo.com.au> wrote in message
>
> news:gjl3g1$vdr$1...@news.motzarella.org...> Mladen Gogala wrote,on my
> timestamp of 2/01/2009 8:03 PM:
>
> >> learning. All things being said, this is still my favorite place
> >> because
> >> there is no censorship.
>
> Only reason I still come back from time to time.
> I'd rather put up with bozos like NS than dull
> arse-kissers and rude newbies like MG.
>I must say, I respect those guys because of the informative postings
> they've made over a long period of time (and I mean decades in the
>case of Noons).
As informative as this thread? I see.
>Can't say as I've seen anything really useful from you. Care to point
>towards something you've done that would improve my opinion?
Care to read the thread before inserting comments?
>Nothing
>wrong with lurking, nothing wrong with opinions, but it does help
>everyone if people contribute positively when they can, and no one
>wants to see plain negativity.
The negativity started when someone cut in the middle of this thread with
some rude nonsense.
If all you saw was negativity, then unbiased negativity is still better than
biased positivity (ass-kissing). Wouldn't you agree?
Mladen Gogala
>> Only reason I still come back from time to time. I'd rather put up with
>> bozos like NS than dull arse-kissers and rude newbies like MG.
>
> I must say, I respect those guys because of the informative postings
> they've made over a long period of time (and I mean decades in the case
> of Noons).
I haven't been called a "newbie" in a long, long time. This
is certainly refreshing, especially having in mind that I
will celebrate my 0x30-th birthday on Saturday, January the 10.
joel garry
> "joel garry" <joel-ga...@home.com> wrote in message
>
> news:506a42b2-cee6-4064...@z28g2000prd.googlegroups.com...
> On Jan 5, 4:54 pm, "Bob Jones" <em...@me.not> wrote:
>
> > "Noons" <wizofo...@yahoo.com.au> wrote in message
>
> >news:gjl3g1$vdr$1...@news.motzarella.org...> Mladen Gogala wrote,on my
> > timestamp of 2/01/2009 8:03 PM:
>
> > >> learning. All things being said, this is still my favorite place
> > >> because
> > >> there is no censorship.
>
> > Only reason I still come back from time to time.
> > I'd rather put up with bozos like NS than dull
> > arse-kissers and rude newbies like MG.
> >I must say, I respect those guys because of the informative postings
> > they've made over a long period of time (and I mean decades in the
> >case of Noons).
>
> As informative as this thread? I see.
>
> >Can't say as I've seen anything really useful from you. Care to point
> >towards something you've done that would improve my opinion?
>
> Care to read the thread before inserting comments?
I've followed it from the beginning, the technical issues were so
predictable I didn't bother to comment.
The OP said:
> Thus determining the indexes which are good candidates for rebuilding.
You said:
> It doesn't look like OP has reached any conclusion yet.
It looks to me Dan Morgan hit the nail early on, and you made yourself
seem as if you haven't read or purposefully ignored the excellent
Richard Foote notes on the subject.
>
> >Nothing
> >wrong with lurking, nothing wrong with opinions, but it does help
> >everyone if people contribute positively when they can, and no one
> >wants to see plain negativity.
>
> The negativity started when someone cut in the middle of this thread with
> some rude nonsense.
>
> If all you saw was negativity, then unbiased negativity is still better than
> biased positivity (ass-kissing). Wouldn't you agree?
Doesn't look unbiased to me, otherwise I might agree. Calling MG an
arse-kisser and newbie... I think some of us got a big laugh out of
that. At your expense.
Happy Birthday Mladen! I'll be only 1.0833333 times your age
Saturday. My birthday was 2 weeks ago Wednesday. Yep, that day every
year <sigh>.
jg
--
@home.com is bogus.
No more free beer.
http://www3.signonsandiego.com/stories/2009/jan/06/1b6beer214252-free-beer-ends-seaworld-busch-parks/?uniontrib
Ah, memories of unlimited Michelob as a teenager at the Busch Bird
Sanctuary... http://articles.latimes.com/2008/jun/13/business/fi-busch13
Noons
>
> I haven't been called a "newbie" in a long, long time. This
> is certainly refreshing, especially having in mind that I
> will celebrate my 0x30-th birthday on Saturday, January the 10.
You are merely a baby, Mladen!
Try 0x37 on the 18th next month, for size...
Bob Jones
> As informative as this thread? I see.
>
> >Can't say as I've seen anything really useful from you. Care to point
> >towards something you've done that would improve my opinion?
>
> Care to read the thread before inserting comments?
> I've followed it from the beginning, the technical issues were so
> predictable I didn't bother to comment.
> The OP said:
> Thus determining the indexes which are good candidates for rebuilding.
> You said:
> It doesn't look like OP has reached any conclusion yet.
> It looks to me Dan Morgan hit the nail early on
Congratulations, you have kissed the ass right on the spot.
>
> >Nothing
> >wrong with lurking, nothing wrong with opinions, but it does help
> >everyone if people contribute positively when they can, and no one
> >wants to see plain negativity.
>
> The negativity started when someone cut in the middle of this thread with
> some rude nonsense.
>
> If all you saw was negativity, then unbiased negativity is still better
> than
> biased positivity (ass-kissing). Wouldn't you agree?
> Doesn't look unbiased to me, otherwise I might agree. Calling MG an
> arse-kisser and newbie... I think some of us got a big laugh out of
> that. At your expense.
Really? Selective quoting is not only bias, it shows no class and no
character. Yes, ass-kissers are despicable and laughable.
Bob Jones
Good time to start learning the netiquette.
Shakespeare
>
> Good time to start learning the netiquette.
>
>
Then just do it!
Shakespeare
Bob Jones
> Bob Jones schreef:
>
>>
>> Good time to start learning the netiquette.
>
> Then just do it!
>
Yup, start by not talking out of your butt. It doesn't sound Shakespeare.
raja
I have a doubt regarding rebuilding indexes on max partition.
Please explain me / tel me how can we proceed regarding the following
scenarios :
------------------------------------------------------------------------------------------------------------------------
1. rebuilding indexes on a max partition with data
2. rebuilding indexes on a max partition with data on local indexes
3. rebuilding indexes on a max partition with data on global indexes
4. rebuilding indexes on a max partition without data
5. rebuilding indexes on a max partition without data on local indexes
6. rebuilding indexes on a max partition without data on global
indexes
Table partition details are as follows :
-----------------------------------------------------------------------
partitioning_type subpartitioning_type
list none
range hash
Index partition details are as follows :
-----------------------------------------------------------------------
partitioning_type subpartitioning_type
range hash
If someone explain with examples or provide URLs having examples, i
would be very much thankful.
Please help on this by detailed explanation.
With Regards,
Raja.
Mladen Gogala
> If someone explain with examples or provide URLs having examples, i
> would be very much thankful.
> Please help on this by detailed explanation.
Raja, Oracle indexes usually do not need to be rebuilt. If you have
many empty blocks, you may want to try coalescing the index, rather
then rebuilding it. Jonathan Lewis has a script utilizing an undocumented
function sys_op_lbid which measures the distribution of the rows
throughout the leaves blocks of an index. The script is not generic and
there is some editing required. You should consider coalescing the index
if you have a significant percentage of empty or near-empty blocks.
However, this should be done only for exceptionally large indexes. Having
a range scan in an index with level 3 read 2 more blocks on average is not
a valid reason for rebuilding an index. On the other hand, if you have a
full index scan which goes through all the leaves, and empty index blocks
comprise 20% of the index, then your full scan will have 20% more work to
do.
Can you please let me know what motivated you to start inquiring about
rebuilding indexes? Rebuilding is a solution in a very few cases and is
usually done on the recommendation of the dinosaurs who are used to such
things as "defragmenting" and "rebuilding indexes". On the other hand,
"rebulding indexes" has recently become a favorite cause for some people
to start flame wars.
Also, can you please tell us the version of your database. There are
some bugs in 8i (Note: 75216.1) and in 9i (mainly with coalescing
function-based indexes).
joel garry
> Hi,
>
> I have a doubt regarding rebuilding indexes on max partition.
>
> Please explain me / tel me how can we proceed regarding the following
> scenarios :
> 1. rebuilding indexes on a max partition with data
> 2. rebuilding indexes on a max partition with data on local indexes
> 3. rebuilding indexes on a max partition with data on global indexes
> 4. rebuilding indexes on a max partition without data
> 5. rebuilding indexes on a max partition without data on local indexes
> 6. rebuilding indexes on a max partition without data on global
> indexes
>
> Table partition details are as follows :
> -----------------------------------------------------------------------
> partitioning_type subpartitioning_type
> list none
> range hash
>
> Index partition details are as follows :
> -----------------------------------------------------------------------
> partitioning_type subpartitioning_type
> range hash
>
> If someone explain with examples or provide URLs having examples, i
> would be very much thankful.
> Please help on this by detailed explanation.
>
> With Regards,
> Raja.
http://www.google.com/search?hl=en&q=index+unusable+geist+site%3Aforums.oracle.com&btnG=Search
jg
--
@home.com is bogus.
Amazon New Yorkers on the Moon.
http://www3.signonsandiego.com/stories/2009/jan/13/1b13webtax01460-states-seek-sales-tax-online-purch/?uniontrib
raja
Thanks for your immediate response.
I have a Oracle 10gR1 database used for datawarehousing.
I already have the database partitions for every month...
Now i have to extend the partition. ( In these partitions some have
data in max partition and some dont have data in max partition )
to extend the partition, we have to disable the constraints and enable
after partitioning and similarly we have to drop the indexes and
create the indexes after partitioning.
Instead of dropping and creating the indexes again, i though of
rebuilding the indexes. i felt that this would save time.
so to rebuild the indexes on which partition was done ( now the
indexes should be in UNUSABLE state ), i felt that i should check
whether the following scenarios would work out properly (i.e, i wanted
to be sure that rebuilding the indexes should be a problem under the
scenarios that i have mentioned you already ).
a. first i want to know, how to check whether there is data in the max
partition ?
b. second, how to find whether an index is a local index or a global
index ?
I would like to tel the scenarios again :
1. rebuilding indexes on a max partition with data
2. rebuilding indexes on a max partition with data on local indexes
3. rebuilding indexes on a max partition with data on global indexes
4. rebuilding indexes on a max partition without data
5. rebuilding indexes on a max partition without data on local
indexes
6. rebuilding indexes on a max partition without data on global
indexes
Table partition details are as follows :
-----------------------------------------------------------------------
partitioning_type subpartitioning_type
list none
range hash
Index partition details are as follows :
-----------------------------------------------------------------------
partitioning_type subpartitioning_type
range hash
index type details are as follows :
-----------------------------------------------------------------------
NORMAL
IOT - TOP
Please explain me the above doubts on index rebuilding...
With Regards,
Raja.
Mladen Gogala
> a. first i want to know, how to check whether there is data in the max
> partition ?
You can try with "alter table truncate <partition name>". If anybody
starts screaming, there was some data in the partition.