Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parallelism in Oracle


Parallelism in Oracle

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
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?

Cool
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)
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
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).

Cool
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)
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
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.

Cool
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)
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5728 Visits: 13305
If you have LOTS of time to spend answering awfully off-topic questions, you can open an SR on support.oracle.com. :-P

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
gints.plivna
gints.plivna
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search