RE: ora-600 when analyzing IOT

2003-08-14 Thread Igor Neyman 
Does it mean, that if I don't have overflow segment in my IOT, I don't
have to analyze table, just analyze PK?

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L






Igor,

When you gather statistics on an IOT with the ANALYZE command, it will
analyze the primary key and overflow segment so I would think
this is all that is required by CBO.  Hopefully others have more
insight.

Rick


 

  Igor Neyman

  [EMAIL PROTECTED]To:
[EMAIL PROTECTED]

  on.com  cc:
[EMAIL PROTECTED]

   Subject:  RE: ora-600
when analyzing IOT
  08/07/2003 12:13

  PM

 

 





Rick,

Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.

Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L






According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6



 execute

 dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');



 analyze table ipn_measurement estimate statistics ;







HTH
Rick





  Igor Neyman

  [EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
  on.com  cc:

  Sent by: Subject:  ora-600 when
analyzing IOT
  [EMAIL PROTECTED]

  .com





  08/07/2003 12:44

  PM

  Please respond to

  ORACLE-L










Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).

First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:

I'm getting:

ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []

when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.

Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.

Any ideas?

Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:

ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS

because analyze index still runs with no errors.

Oracle version is 8.1.5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see

RE: ora-600 when analyzing IOT

2003-08-14 Thread Igor Neyman 
Rick,

Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.

Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L






According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6

 

 execute

 dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');

 

 analyze table ipn_measurement estimate statistics ;

 





HTH
Rick



 

  Igor Neyman

  [EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]   
  on.com  cc:

  Sent by: Subject:  ora-600 when
analyzing IOT
  [EMAIL PROTECTED]

  .com

 

 

  08/07/2003 12:44

  PM

  Please respond to

  ORACLE-L

 

 






Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).

First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:

I'm getting:

ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []

when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.

Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.

Any ideas?

Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:

ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS

because analyze index still runs with no errors.

Oracle version is 8.1.5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

ora-600 when analyzing IOT

2003-08-14 Thread Igor Neyman 








First, sorry for asking this list before searching Metalink,
but I dont have access to it right now.

So, here it is:



Im getting:



ORA-00600: internal error code, arguments: [15163], [333],
[17424], [16191], [], [], [], []



when analyzing one of the index-organized tables:

ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.



Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked
fine yesterday, and the table didnt grew too much since yesterday.



Any ideas?



Another question, do I really have to run analyze
table on IOT for cost-based optimizer, or analyzing index would be
enough:



ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS



because analyze index still runs with no
errors.



Igor Neyman, OCP DBA

[EMAIL PROTECTED]

RE: ora-600 when analyzing IOT

2003-08-14 Thread Rick_Cale 





Igor,

When you gather statistics on an IOT with the ANALYZE command, it will
analyze the primary key and overflow segment so I would think
this is all that is required by CBO.  Hopefully others have more insight.

Rick


   

  Igor Neyman

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]  
  
  on.com  cc:   [EMAIL PROTECTED]   
 
   Subject:  RE: ora-600 when analyzing 
IOT
  08/07/2003 12:13 

  PM   

   

   





Rick,

Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.

Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L






According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6



 execute

 dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');



 analyze table ipn_measurement estimate statistics ;







HTH
Rick





  Igor Neyman

  [EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
  on.com  cc:

  Sent by: Subject:  ora-600 when
analyzing IOT
  [EMAIL PROTECTED]

  .com





  08/07/2003 12:44

  PM

  Please respond to

  ORACLE-L










Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).

First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:

I'm getting:

ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []

when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.

Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.

Any ideas?

Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:

ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS

because analyze index still runs with no errors.

Oracle version is 8.1.5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051

Re: ora-600 when analyzing IOT

2003-08-14 Thread Rick_Cale 





According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6

   
  
 execute   
  
 dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');   
  
   
  
 analyze table ipn_measurement estimate statistics ;   
  
   
  




HTH
Rick



   

  Igor Neyman

  [EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]   
  on.com  cc: 

  Sent by: Subject:  ora-600 when analyzing IOT

  [EMAIL PROTECTED]

  .com 

   

   

  08/07/2003 12:44 

  PM   

  Please respond to

  ORACLE-L 

   

   






Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).

First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:

I'm getting:

ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []

when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.

Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.

Any ideas?

Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:

ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS

because analyze index still runs with no errors.

Oracle version is 8.1.5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT

ora-600 when analyzing IOT

2003-08-10 Thread Igor Neyman 

Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).

First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:

I'm getting:

ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []

when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.

Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.

Any ideas?

Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:

ANALYZE INDEX pk_ipn_measurement ESTIMATE STATISTICS

because analyze index still runs with no errors.

Oracle version is 8.1.5.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: ora-600 when analyzing IOT

2003-08-07 Thread Alexander . Feinstein 
Title: RE: ora-600 when analyzing IOT





Only missing data in *_TABLES is avg_row_len which, probably, can be used for estimating hash area for hash join.
Also, columns statistics will not be generated, probably more important.


Alex.


-Original Message-
Does it mean, that if I don't have overflow segment in my IOT, I don't have to analyze table, just analyze PK?


Igor Neyman, OCP DBA
[EMAIL PROTECTED]

RE: ora-600 when analyzing IOT

2003-08-07 Thread Rick_Cale 





I am not sure but based on the definition of an IOT I would think you would
have to ANALYZE TABLE since the primary key data is not duplicated in
a separate index.

Rick


   

  Igor Neyman

  [EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]   
  on.com  cc: 

  Sent by: Subject:  RE: ora-600 when analyzing 
IOT
  [EMAIL PROTECTED]

  .com 

   

   

  08/07/2003 02:14 

  PM   

  Please respond to

  ORACLE-L 

   

   





Does it mean, that if I don't have overflow segment in my IOT, I don't
have to analyze table, just analyze PK?

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L






Igor,

When you gather statistics on an IOT with the ANALYZE command, it will
analyze the primary key and overflow segment so I would think
this is all that is required by CBO.  Hopefully others have more
insight.

Rick




  Igor Neyman

  [EMAIL PROTECTED]To:
[EMAIL PROTECTED]

  on.com  cc:
[EMAIL PROTECTED]

   Subject:  RE: ora-600
when analyzing IOT
  08/07/2003 12:13

  PM









Rick,

Thanks for prompt reply.
I tried it (deleting statistics before analyzing table) and it worked.

Any ideas, whether cost-based optimizer need statistics on IOT or having
statistics on PK index of IOT would be enough?


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L






According to MetaLink you need to delete statistics and re-analyze
I do not know your database version but this is supposely fixed in 8.1.6



 execute

 dbms_stats.delete_table_stats('owner','IPN_MEASUREMENT');



 analyze table ipn_measurement estimate statistics ;







HTH
Rick





  Igor Neyman

  [EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L [EMAIL PROTECTED]
  on.com  cc:

  Sent by: Subject:  ora-600 when
analyzing IOT
  [EMAIL PROTECTED]

  .com





  08/07/2003 12:44

  PM

  Please respond to

  ORACLE-L










Ok, since my original message still hasn't arrived (sent couple hours
ago), here it goes again (sorry, if you get duplicate).

First, sorry for asking this list before searching Metalink, but I don't
have access to it right now.
So, here it is:

I'm getting:

ORA-00600: internal error code, arguments: [15163], [333], [17424],
[16191], [], [], [], []

when analyzing one of the index-organized tables:
ANALYZE TABLE ipn_measurement ESTIMATE STATISTICS.

Now, similar statement analyzing other IOTs works fine.

Also, on the same table (ipn_measurement) analyze worked fine yesterday,
and the table didn't grew too much since yesterday.

Any ideas?

Another question, do I really have to run analyze table on IOT for
cost-based optimizer, or analyzing index would be enough:

ANALYZE INDEX