October 20, 2010 at 10:02 am
Having an issue with a stored procedure that appears to be parallelism related.
In stepping through the stored proc we were unable to get the procedure to create a duplicate enter in a table. Problem is this same procedure did exactly that when run from the application in production.
In MS SQL Server I'd use the MOXDOP = 1 hint to force the procedure to be single threaded. Is there something similar in Oracle?
Is there something else I should look for while debugging the procedure?
October 20, 2010 at 12:48 pm
Lynn Pettis (10/20/2010)
Having an issue with a stored procedure that appears to be parallelism related.In stepping through the stored proc we were unable to get the procedure to create a duplicate enter in a table. Problem is this same procedure did exactly that when run from the application in production.
In MS SQL Server I'd use the MOXDOP = 1 hint to force the procedure to be single threaded. Is there something similar in Oracle?
Is there something else I should look for while debugging the procedure?
Yes. NOPARALLEL hint would do it.
For a query like "select * from employee a where state_id = 'XX";"
it should be "select /*+ NOPARALLEL(a) */ from employee a where state_id = 'XX";"
NOPARALLEL hint overrides PARALLEL setting at table level.
_____________________________________
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.October 21, 2010 at 8:17 am
Thank you for the info Paul, but I have a feeling that isn't what I need in this case.
Here is the issue (without providing actual code, can't).
We have a stored procedure that uses numerous functions to return Index values from various tables. If the function does not find a match in a table, it inserts the data into that table and creates a new index value in the process. (Yes, I know that functions in MS SQL Server won't allow this, but Oracle allows functions to update tables.)
Problem we are having is that when there are two (or more) duplicate streams (configuration entries) and the entry does not exist initially we get duplicate entries in the table. What this means is that for the duplicate streams, each has a unique index value instead of using the same index value.
Yes, confusining. Wish I could explain in more detail but hands are tied on that one.
One solution would be to put a contraint on the table to prevent a duplicate entry, unfortunately not possible at this time as we have multiple duplicate entries in the table currently and we need to clean those up first, but it is a time consuming task as each index value must be consecutive (no breaks allowed, and again I really can't explain why).
The problem, when stepping through the stored procedure in debug mode, it works properly. When executed from the application or PL/SQL Developer normally, it fails.
Really looks like parallelism, or something similar.
ANY ideas or suggestions at what to look at or change or something, please let me know.
I am new to Oracle (12+ years SQL Server 6.5 - 2008), and the other developer I am working with is also not an Oracle developer either (SQL Server 4.2).
October 21, 2010 at 8:50 am
Lynn Pettis (10/21/2010)
One solution would be to put a contraint on the table to prevent a duplicate entry, unfortunately not possible at this time as we have multiple duplicate entries in the table currently and we need to clean those up first, but it is a time consuming task as each index value must be consecutive (no breaks allowed, and again I really can't explain why).
Yes you can, Oracle allows for it.
Create unique constraint as NOVALIDATE, ENABLE - that way Oracle would not validate current data (allowing for duplicates in the process) but will prevent new duplicates from getting in 😉
_____________________________________
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.October 21, 2010 at 2:34 pm
Okay, we think we solved the problem, but don't ask me why; especially since we couldn't rebreak it.
Using Oracle's SQL Developer stored procedures and functions are by default compiled for debug.
When we were esearching this problem, we were getting the problem with a duplicate entry being created where one shouldn't, logicaly thinking. Well, this morning we recompliled all the stored procedures and functions in Dev without debug. Problem fixed. Recompiled the stored procedures and functions for debug, still fixed! What?? It didn't break? WT%!?!.
Went into Test, problem existed there. Compiled all procedures and functions without debug, problem fixed.
If anyone with Oracle experience can explain this, I'm all ears and willing to learn something new.
October 22, 2010 at 1:20 am
If you have LOTS of time to spend answering awfully off-topic questions, you can open an SR on support.oracle.com. 😛
-- Gianluca Sartori
October 22, 2010 at 2:18 am
Hmm, not sure I can explain this behaviour but how are you creating (and what it actually is) "index value"?
If it is some number I suggest to use sequence instead of some max+1 or something like that. So you won't get duplicates. Of course enabled novalidated constraint should be used anyway to be absolutely sure that even manually new duplicates cannot be created.
Gints Plivna
http://www.gplivna.eu
October 22, 2010 at 6:52 am
Lynn Pettis (10/21/2010)
Okay, we think we solved the problem, but don't ask me why; especially since we couldn't rebreak it.Using Oracle's SQL Developer stored procedures and functions are by default compiled for debug.
When we were esearching this problem, we were getting the problem with a duplicate entry being created where one shouldn't, logicaly thinking. Well, this morning we recompliled all the stored procedures and functions in Dev without debug. Problem fixed. Recompiled the stored procedures and functions for debug, still fixed! What?? It didn't break? WT%!?!.
Went into Test, problem existed there. Compiled all procedures and functions without debug, problem fixed.
If anyone with Oracle experience can explain this, I'm all ears and willing to learn something new.
Can't explain it but, as a rule of thumbs the closer you are to the database the better - if in doubt apply ddl and compile code via sqlplus.
_____________________________________
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.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply