Oracle 10g and parallel query question

[Former Member]

Hi Oracle on SAP Gurus!

We are currently thinking of activating parallel query for certain segments (large application tables and indexes). We searched in SAPNet and SDN and have also studied the SAP Note 651060. But we did not find a complete answer to the following question which is very important for us:

Which kinds of queries (despite from full table scan and index scan in partitioned indexes) support parallel queries and which ones do not support parallel queries?

This is important for us to find out whether we have candidates for parallel queries or not.

Thanx for any hint!

Regards,

Volker

[markus_doehr2]

That note is very detailed and does well explain what kind accesses support a parallel execution. To completely understand the note you need to have an in-depth knowledge about Oracle and how the database works internally.

To answer you question I could just repeat what is written in the note (and adding nested joins to your statement).

Does the answer to question 2 not answer your question?

Markus

[Former Member]

Hi Markus!

Thanx for your reply. The problem with that note is that it always contains only "for example" phrases and "certain selection components" but there is nowhere a more or less complete list.

Background: We have more or less 0 Full Table Scans but a large amount of Index Range Scans.

Here are some statistical values representing about 3 month:

Table scans & fetches

Short table scans 239.400.877

Long table scans 1.740

Fetch by rowid 1019589936425

by continued row 165.419.796

Regards,

Volker

[Former Member]

Hi Volker

You did not give us a specific example of a query, so i just give you some general information. First please let us know what kind of system you have (OLTP <-> OLAP), they greatly differ regarding database work.

In OLTP like SAP systems index range scans are very common and not something bad in particular. But you cannot speed up index range scans with parallel query (PQ). Generally you should not use PQ at all in OLTP systems. Index range scans speed is affected by table / index size, the index fields, how the index is sorted in relation to the table (called clustering factor).

If you have a specific example, please post it.

Best regards

Michael

[Former Member]

Hi Michael!

Thanx for your very helpful reply. Of course we have an SAP OLTP System and I know that Index Range Scans are very commonly in such a kind of system. But why do you not propose to use parallel query in OLTP systems?

To give you an example. We have a few amount of tables with a size of appr. 100GB which are accessed very frequently via Index Range Scans.

May it make sense to use partitioning of these tables in conjunction with parallel query?

Thanx in advance.

Regards,

Volker

[Former Member]

But why do you not propose to use parallel query in OLTP systems?

If the queries are accessed very frequently you will just run out of cpu and io ressources. OLTP systems are (historical) typically multi user systems. You can off course use PQ for 'single user' activities, like index rebuilds, some batchjobs, but you shouldn't do for frequent user queries.

If you have time look at this interesting Article [Suck It Dry - Tuning Parallel Execution|http://doug.burns.tripod.com/px.html]

It is quite old, and you don't have to read all tech details, but i recommend having a look at the conclusions at the end.

May it make sense to use partitioning of these tables in conjunction with parallel query?

I know some guys, who do partitioning on OLTP systems, even SAP systems. But they don't use PQ then. The use partitioning to work on a smaller set of data. In your case the range scans, would need to scan only one partition, saving buffer cache and effectively speeding up execution. So you don't need PQ to scan all partitions at all, this would be a typical OLAP approach.

Best regards

Michael

[stefan_koehler]

Hello Volker,

> May it make sense to use partitioning of these tables in conjunction with parallel query?

Yeah maybe, but it depends on the data distribution of the table and if you want to use partitioned indexes, too.

It also depends on the ranges that are requested with by your queries.

> Which kinds of queries (despite from full table scan and index scan in partitioned indexes) support parallel queries and which ones do not support parallel queries?

I also got this question some time ago, and i found this: http://www.dbanotes.net/archives/px.pdf

The supported query options are explained there.

Btw. index range scans can also be executed in parallel, if they are used in a nested loop or if the are partitioned for example.

Regards

Stefan

P.S.: I also tested the parallel query option in our ERP 2005 (OLTP) system.. but the result was not very satisfying.

[Former Member]

Hi guys!

Thank you all for your very interesting and helpful answers and hints.

May two last question be allowed?

1. What about partitioniong in SAP OLTP systems (SAP ERP 6.00)?

2. What about index compression in SAP OLTP systems (SAP ERP 6.00)?

Are there any experiences in this community?

Thanx and regards,

volker

[stefan_koehler]

Hello Volker,

> 1. What about partitioniong in SAP OLTP systems (SAP ERP 6.00)?

Yeah this is possible through the ABAP DDIC and you have to decide if it makes sense. In some cases (for example for date ranges in selects) it can be very good.

> 2. What about index compression in SAP OLTP systems (SAP ERP 6.00)?

Yeah i have done this, too. It saves a lot of space in my cases (default rebuid: 671GB <-> compressed: 302 GB).

Just take a look at here for some experiences:

Regards

Stefan

[Former Member]

Hi!

Thanx. This answers my question. By the way: SAP note 1109743 again is invisible.

Regards,

Volker