Parallelism in Oracle

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server and other platforms » Oracle » Parallelism in Oracle

Parallelism in Oracle

Rate Topic

Display Mode

Topic Options

Author

Message

Lynn Pettis

Posted Wednesday, October 20, 2010 10:02 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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)

Post #1007856

PaulB-TheOneAndOnly

Posted Wednesday, October 20, 2010 12:48 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:08 PM
Points: 2,982, Visits: 4,396

Lynn Pettis (10/20/2010)

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

Lynn Pettis

Posted Thursday, October 21, 2010 8:17 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1008516

PaulB-TheOneAndOnly

Posted Thursday, October 21, 2010 8:50 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:08 PM
Points: 2,982, Visits: 4,396

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 Wink

_____________________________________
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

Lynn Pettis

Posted Thursday, October 21, 2010 2:34 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1008820

spaghettidba

Posted Friday, October 22, 2010 1:20 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

If you have LOTS of time to spend answering awfully off-topic questions, you can open an SR on support.oracle.com. Tongue

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1008986

gints.plivna

Posted Friday, October 22, 2010 2:18 AM

Valued Member

Group: General Forum Members
Last Login: Monday, October 08, 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

PaulB-TheOneAndOnly

Posted Friday, October 22, 2010 6:52 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:08 PM
Points: 2,982, Visits: 4,396

Lynn Pettis (10/21/2010)

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 »

Permissions