How to use : Parallel threads (parallel) when "Reorganizing tables"

[pr_srinivas]

Hi....Experts....

We are doing some reorganizations of few tables.

When we are reorganizing ...

How to use "Parallel threads ".......( referring Step 4) in below screen shot.

-

---

Options for reorganization of tables: SAPP10.XXXX,... (2 tables)

1 * Reorganization action (action) ............ [reorg]

2 ~ New destination tablespace (newts) ........ []

3 ~ Separate index tablespace (indts) ......... []

4 - Parallel threads (parallel) ............... [1]

5 ~ Table/index parallel degree (degree) ...... []

6 - Create DDL statements (ddl) ............... [yes]

7 ~ Category of initial extent size (initial) . []

8 ~ Sort by fields of index (sortind) ......... []

9 # Index for IOT conversion (iotind) ......... [FIRST]

10 - Table reorganization mode (mode) .......... [online]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

---

How to use ...Parallel threads to make faster the reorganizations.

..what is the impact on the system .

Rgds

[volker_borowski2 · Accepted solution]

Hi,

To speed up with a single Instance, you an only use parallel query

by increasing the parallel degree.

It will cost the amount of parallel query processes, and

will do a linear hit on IO activity in your system. So if you reorg

a table with 12million blocks with a degree of 12, you will start

12 processes of which each will roughly do 1Mio reads.

In terms of cpu, PQ is not that costy, but the IO strikes hard,

if you have other activity in the system.

Volker

OOPS: Wrong statement of mine corrected

If you have mutiple tables, "threads" defines how many objects you want to reorg at the same time.

"degree" defines the number of PQ processes per object.

So a reorg of TABA/TABB/TABC with "threads" 2 and "degree" 4

will start

TABA with 4 PQ procs

and

TABB with 4 PQ procs

the first one that finishes will then do

TABC with 4 PQ procs

Sorry

Edited by: Volker Borowski on Nov 23, 2010 7:16 PM

[pr_srinivas · Accepted solution]

VOKER............Thank U Very much for your swift reply.

Can you make me a deep dive understanding "PQ Procs.....really did not understand ...PQ Procs".

I tried with 1 thread it self was so expensive ...

The archive logs were just gettting flooded....so i am sure i will not attmpt any further load on system using multiple "Threads" (or) 'Degrees".

But understanding , my DB is No-Archive Mode...

my host contains ...AIX....P595 Server with 4 CPU / 16 GB RAM..

Threads now clear....is usefull only when i attempt to reorg more than ....1.....

But....degree....did not understand.....

Pl Help.

Rgds

[volker_borowski2 · Accepted solution]

PQ is abbreviated for Parallel Query.

It is a tool to usually speed up full table or full index scans.

For reorgs or exports you usually want to read the entire table.

For read of the entire table a full table scan is in general the best approach

because you will get all blocks with the lowest number of reads.

Now to speed that up, you can read the table with more reader processes.

This requires a bit of overhead on the process coordination side and to manage

the merge of the result set.

Get yourself a table of at least 100000 blocks.

REPOSRC will do will do if you you have nothing else you are familiar with.

EXAMPLE !!!! not intended to be used on a productive system !!!!

====================================================

Get ST04 SQL Session Monitor open and reset the counters.

Get an sqlplus window an try this (not on procduction, it will cost some IO

SET TIMING ON

select /*+ full(T) / count() from sapsr3.REPOSRC T;

Refresh the ST04 session.

Youll see the reads of your session proceeding, but only a single process

You can CTRL-C the query, if you do not like to wait for termination.

Reset the counters in the ST04 Monitor

now run

select /*+ full(T) parallel(T,4) / count() from sapsr3.REPOSRC T;

You will see 5 active sessions now in the session monitor.

One is waiting for the PQ results and 4 procs will show

increasing READ counters with your very same statement.

Notice the process Ids and check the IDs on OS level with "ps -ef"

You should not terminate a PQ query with CTRL-C if possible.

If you like try again with "parallel(T,8)" to compare the speed against

four processes but do not take a too high degree.

You can not use more than "parallel_max_servers" configured in oracle.

WARNING: if you uses ORDER BY / GROUP BY or both,

the required number of PQ procs might increase.

Volker

[former_member524429 · Accepted solution]

Hi,

The PQ Processes counts (using option -e of brspace) represent the internal Oracle Parallel processing feature. It means that the it uses parallel query during Data copy processing request. The count should not be high (do not more than 4). Refer Remark 13 of SAP Note 646681 to get more information.

If you combine Parallel Query option (-e) with combination of Parallel thread option (-p) in BRSAPCE command , then within each one parallel thread the reorganization process will be performed with n parallel queries for the selected table. Sometime it hampers the system performance due to I/O loads , if your system is not so capable with respect to High CPU counts and RAM (specially while reorganizing tables with LOB columns).

Also look at this useful SAP Note 806554 which is talking about other factors which needs to be tuned to achieve Optimized Performance during Table Reorganization activity and other I/O intensive DB level activities.

Regards,

Bhavik G. Shroff