ORA-00600 Error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: ORA-00600 Error

  1. #1
    Join Date
    Apr 2009
    Posts
    5

    ORA-00600 Error

    Hello

    I was executing this query and encountered an ORA-00600 Error. When googled I found it to raised from the kernel code of the Oracle RDBMS software when an unexpected condition is met.

    This is what the error looked like

    Code:
    ORA-00600: internal error code, arguments: [kkestGetIdxKey: no key for idx!],
    [], [], [], [], [], [], []
    I raised a Ticket with the Oracle support and this is what they have to say:

    There is one problem raised for this error, which is fixed in 11.1.0.7 which is generated when the same table is referenced in 2 query blocks and table is part of a cluster

    This seems to be a variant to the bug # xxxx which has been mentioned earlier. That bug deals with hashing operations on a clustered table. It seems that the inline view you're using is treated the same way as a cluster internally, causing the same behavioour to occur.
    and those guys suggested to alter a system parameter

    alter system set "_optimizer_enable_extended_stats" = false

    which once altered is set for all database sessions and restarts.

    I dont want to do that and unable to find what part of my sql is causing the error. BTW I am on 11.1.0.6 and this is the query

    Code:
    SELECT       mu_id, inv_id, stgr_id, heat_id 
    	 FROM material_unit 
    	 WHERE mu_id in 
    		 (SELECT      mu_id FROM 
    			 (SELECT   
    				 mu.mu_id, mu.inv_id,
    				 mu.stgr_id, mu.heat_id 
    				 FROM
    				 material_unit mu,
    				 plant_configuration_item pci,
    				 production_resource pr,
    				 oper_res ors,
    				 route_operation ro	WHERE mu.inv_id	= pci.inv_id
    				 AND pci.pcfg_id	= 22
    				 AND pci.res_id	= pr.res_id
    				 AND ors.agres_id = pr.agres_id
    				 AND ors.oper_id = ro.oper_id
    				 AND ro.route_id = 'R1'
    				 AND ro.step_number = 1
    			 )  )  
    		 AND 	 0.010000  < (SELECT to_number(chr_value) FROM material_unit_characteristic WHERE mu_id='SL18' AND mu_set = 1
    				 AND chr_id = 'AVWGHT')

    I am really struck with that and unable to move forward. Can you please suggest something.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Oracle has already suggested two alternative solutions.

    As far as I can see your query includes a bunch of inline views, if you want to troubleshoot it I would suggest to test each subquery from the most inner one working your way out until reaching the main one.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2009
    Posts
    5
    I have raised a ticket with the oracle and they suggest to change the system parameter. And one more thing which i forgot to mention, the query works fine on 10g and earlier perfectly with the same dataset. Problem seen only in 11g. I could not make out whats wrong with the query.!!

    I checked the inner queries and work perfectly fine, is my query very unusual way of forming a sql???

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Read the bug documentation.

    Look at listed affected platforms?... does it says bug was introduced on Ora11g?

    In the affirmative case problem is the bug, not the query so your options are: 1) Do what Oracle support suggested or, 2) Rework the query so you do the same stuff with a different coding.

    By the way, why did you choose to hide the bug number?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2009
    Posts
    5
    >>By the way, why did you choose to hide the bug number?

    :-) I am not aware whether I can post the bug number on the net.
    The bug number is # 7209891 and it is not documented.

    >>In the affirmative case problem is the bug, not the query so your options are: 1) Do what Oracle support suggested or, 2) Rework the query so you do the same stuff with a different coding.


    1.I cannot go with the Oracle support work around as it is setting a system parameter related to optimization. I may not convince this to my customer

    2. Rewrite the query!!! I dont know what to re write.

    I am kinda struck here!!!!

  6. #6
    Join Date
    Apr 2009
    Posts
    10
    What I assume from the ORA-600 error pasted is that there some problem with the index. This error is propping up when it trying to fetch the index key. You can go through the trace file to get the exact condition/scenario when this error is propping up.

  7. #7
    Join Date
    Apr 2009
    Posts
    5
    Quote Originally Posted by B.B View Post
    What I assume from the ORA-600 error pasted is that there some problem with the index. This error is propping up when it trying to fetch the index key. You can go through the trace file to get the exact condition/scenario when this error is propping up.
    Its above my head.!!!!!!!!............Do you see any possibility of changes in the way I am creating the query.

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    I see three things that can be done to simplify the query:
    1) The inline view at the first level is probably needless
    2) The inline view at the second level is probably needless as well
    3) You can pre-select the value for last condition (e.g. in a variable in pl/sql)

    I think the points 1) and 2) are enough.

    Code:
    SELECT mu.mu_id, mu.inv_id, mu.stgr_id, mu.heat_id 
       FROM
          material_unit mu,
          plant_configuration_item pci,
          production_resource pr,
          oper_res ors,
          route_operation ro 
       WHERE mu.inv_id = pci.inv_id
         AND pci.pcfg_id = 22
         AND pci.res_id = pr.res_id
         AND ors.agres_id = pr.agres_id
         AND ors.oper_id = ro.oper_id
         AND ro.route_id = 'R1'
         AND ro.step_number = 1
         AND 0.010000 < :PRE_SELECTED_VALUE
    It's just a guess but give it a try.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  9. #9
    Join Date
    Apr 2009
    Posts
    5

    Thanks

    thanks a lot ales

    I will try that. Actually I posted a simple query just to show the problem. The actual query is runs almost a page and is randomly generated.

    Meanwhile a got a patch from the oracle guys and its 11.1.0.7. Applied and executed the same query on the same data successfully.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by raghuveerd View Post
    Meanwhile a got a patch from the oracle guys and its 11.1.0.7. Applied and executed the same query on the same data successfully.
    So your problem is solved, isn't it?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width