cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Parallel Execution

Former Member
0 Kudos

Hi

We are running R/3 4.7 Enterprise (Extn 110) / 10.2.0.4 on Solaris

Recently we had added 4 more CPU's to our central instance. We have been having issues with dialog response time for a while now and I was investigating that when I chanced upon "Oracle Parallel Execution"

My requirement here is

1. Leverage Parellelism into existing SQL queries in Z Programs

2. Parellize new Z programs.

First,

Would parellel execution show a difference in performance in OLTP systems where there is not much of querying unlike BW.

Second,

I was reading through notes 651060 (which has some recommendations regarding parellel Execution) and 806554 and I have some questions

Note 806554 - FAQ: I/O-intensive database operations states that PARALLEL_EXECUTION_MESSAGE_SIZE should be set to at least 16384 however this is set to 2152 in my system

Note 651060 has recommendations for PARALLEL_MAX_SERVERS in OLAP however I would like to find out regarding OLTP systems.

Note 651060 also specifies PARALLEL_THREADS_PER_CPU to be set to 1. This is set to 2 in our environment

The same note also states how parellelism can be mentioned in segment and statement level. I am looking at setting this up in System level.

Now one more issue that I have is that the DEV, QA and SND systems do not have the same number of CPU as PRD and hence I would not be able to test this in another system.

Since we have already a running system going back to change Z programs to use parellel execution may not be a possibility and hence I would like to know if this can be done system level. If I am to do that system level would I need to edit the parameters as per Note 651060? Would that be where I need to start off?

Any thoughts / suggestions / ideas / experiences in implemention this concept would be of great help.

Thanks

Ravi

Accepted Solutions (0)

Answers (2)

Answers (2)

lbreddemann
Active Contributor
0 Kudos

Hello Ravi,

oracle parallel query execution is not an easy feature.

Especially for high concurrent workload scenarios like the classic R/3 scenario where many users are entering, changing and retrieving data at the same time, enabling parallel query might lead to severe performance issues.

The general idea of it is to give one database session/query an overequal share of the resources it would usually get. To cite Tom Kyte "it turns your multiuser database into a single-user database".

Another point is that only specific action are actually parallelized and this boils down to: segment scans.

Whenever you need to scan a segment (index, table), then you might break this up into smaller chunks and do this is parallel.

This is what parallel query does in most situations.

If you think about this, you should immediately see why this is a problem for OLTP scenarios: we don't want scans to happen there!

To tune your queries, it would be much better to actually do this (tune the query: change the indexing, change the search conditions, etc.) than to simply turn on a parallel query option which can be difficult to control.

For BW-scenarios parallel query is usually only employed in so called warehouse-queries. These are typically rather controlled and you have a good chance to avoid resource starvation.

For end-user queries this is not used, as it may happen that many users run queries at the same time and then the database would just come to a grinding halt.

If you do have mass data operations going on in your Z-reports, then carefully check, whether this really is done in one SQL statement (that's where parallel query can help you) or whether you actually pull much data into ABAP, change the data and push it back to the database.

For the latter case, parallelisation on report level might be the better and more controllable approach.

Also check these blogs on this topic:

/people/naresh.pai/blog/2005/06/16/parallel-processing-in-abap

/people/adam.baryla/blog/2011/05/02/parallel-processing-made-easy-with-custom-zab1lparallel-abap-library

regards,

Lars

Former Member
0 Kudos

Hi Ravi,

>> Would parellel execution show a difference in performance in OLTP systems where there is not much of querying unlike BW.

From database point of view, it will show difference in performance on OLTP systems, but you need to use native SQL statements between "exec/endexec" statements in the ABAP program which will avoid to use application server buffers while fetching the data. Under this circumstance, the query performance might be degraded. Because of you will not able to add "PARALLEL" hint into your Open SQL statements, ABAP programs will not be able to use parallelism on OLTP systems.

>> Note 651060 also specifies PARALLEL_THREADS_PER_CPU to be set to 1. This is set to 2 in our environment

The same note also states how parellelism can be mentioned in segment and statement level. I am looking at setting this up in System level.

As per note 651060, it is good to configure this value to "1", in order to avoid overload.

>> Since we have already a running system going back to change Z programs to use parellel execution may not be a possibility and hence I would like to know if this can be done system level.

Please note that because of the Open SQL statements have been translated to the native SQL, by SAP database interface, it is not able to achieve your requirement.

Additionally, even if you set the profile parameters at the Oracle level and set the table attribute to "PARALLEL" by using "ALTER TABLE <table_name> PARALLEL <degree>;" statement, you will not able to use parallelism on Open SQL statements, because of the reasons that I mentioned, above.

I hope that I clarified the issue,

Best regards,

Orkun Gedik

Former Member
0 Kudos

Additionally, at the OLAP point of view, if you do not use PARALLELISM very carefully, the system performance might be effected negatively.

Best regards,

Orkun Gedik

lbreddemann
Active Contributor
0 Kudos

> Additionally, even if you set the profile parameters at the Oracle level and set the table attribute to "PARALLEL" by using "ALTER TABLE <table_name> PARALLEL <degree>;" statement, you will not able to use parallelism on Open SQL statements, because of the reasons that I mentioned, above.

> Orkun Gedik

Hi there.

This is not true - the default parallel degree of tables/indexes will be used if applicable, regardless whether or not the statement comes from OpenSQL or native SQL.

To the CBO it doesn't make any difference and that's where the decision about the parallelism is made.

regards,

Lars

Former Member
0 Kudos

Hi,

>> To the CBO it doesn't make any difference and that's where the decision about the parallelism is made.

Are you sure that the database will use parallelism, even you don't use PARALLEL statement in your SQL statement. I have doubt about it.

I mean for example, parallel attribute has been set on MKPF table. Under this circumstance, even I don't use PARALLEL statement, the system will run parallelism when I execute a query on MKPF table?

As far as I know that the CBO decides full table scan instead of index scan, if you set this attribute which may cause performance loss on the SQL statement.

Best regards,

Orkun Gedik

lbreddemann
Active Contributor
0 Kudos

> >> To the CBO it doesn't make any difference and that's where the decision about the parallelism is made.

>

> Are you sure that the database will use parallelism, even you don't use PARALLEL statement in your SQL statement. I have doubt about it.

Yes, I'm sure. And you can be too.

Just setup the default parallelism for a table and run a ABAP statement that will lead to a full table or index fast full scan.

In fact this automatic parallelism is the reason for switching back the parallel degree every time after an index creation or rebuild.

That way, we prevent unwanted and uncontrolled parallelism happening on the database.

> I mean for example, parallel attribute has been set on MKPF table. Under this circumstance, even I don't use PARALLEL statement, the system will run parallelism when I execute a query on MKPF table?

As I wrote: it totally depends on how you access your table.

If it's a kind of scan, yes, you're going to see parallel query kicking in.

If it's just a index lookup + table access - then there's no way to parallelize this.

> As far as I know that the CBO decides full table scan instead of index scan, if you set this attribute which may cause performance loss on the SQL statement.

No, it's not such a fixed rule.

The CBO tries to figure out, whether or not the parallel execution plan would be faster.

If the math indicate it would, then yes, the CBO might go for the parallel plan including scans instead of using index range/unique scans and that of course could lead to severe performance issues.

That was what I was writing about above...

regards,

Lars

Former Member
0 Kudos

Thank you for this information my friend