-
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.
-
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.
-
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???
-
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.
-
>>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!!!!
-
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.
-
Originally Posted by B.B
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.
-
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
-
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.
-
Originally Posted by raghuveerd
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|