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).

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

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