Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parallelism in Oracle Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 10:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1007856
Posted Wednesday, October 20, 2010 12:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1007948
Posted Thursday, October 21, 2010 8:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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).




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1008516
Posted Thursday, October 21, 2010 8:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1008553
Posted Thursday, October 21, 2010 2:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1008820
Posted Friday, October 22, 2010 1:20 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 4,656, Visits: 11,131
If you have LOTS of time to spend answering awfully off-topic questions, you can open an SR on support.oracle.com.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1008986
Posted Friday, October 22, 2010 2:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 8, 2012 2:57 AM
Points: 66, Visits: 433
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
Post #1009004
Posted Friday, October 22, 2010 6:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1009143
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse