Enabling INTRA_PARALLEL config - does it help?

[maricel]

Is there any advantage in terms of performance when activating
intra-parallelism

INTRA_PARALLEL = yes
DFT_DEGREE = -1

for a Windows 2K, sigle (1) CPU server. The database is used for a small
datamart system with approximately 10-20GB in size. The database is mainly
used for queries except when refreshing the data on a scheduled basis.

Any input would be highly appreciated.

Regards,
JE

[Mark A]

"maricel" <ma*****@xtra.co.nz> wrote in message
news:LL********************@news.xtra.co.nz...

Is there any advantage in terms of performance when activating
intra-parallelism

INTRA_PARALLEL = yes
DFT_DEGREE = -1

for a Windows 2K, sigle (1) CPU server. The database is used for a small
datamart system with approximately 10-20GB in size. The database is mainly
used for queries except when refreshing the data on a scheduled basis.

Any input would be highly appreciated.

Regards,
JE

If you stripe the containers across multiple drives (by defining each
container within a particular DMS tablespace on a separate physical drive),
and do the extent and prefetch configuration properly, it could help with
some queries that scan large parts of the tables and/or indexes. You are to
some extent (no pun intended) limited by your 1 CPU, but intra-partition
parallelism could help depending on how fast the CPU was.

[Ian]

Mark A wrote:

"maricel" <ma*****@xtra.co.nz> wrote in message
news:LL********************@news.xtra.co.nz...

Is there any advantage in terms of performance when activating
intra-parallelism

INTRA_PARALLEL = yes
DFT_DEGREE = -1

for a Windows 2K, sigle (1) CPU server. The database is used for a small
datamart system with approximately 10-20GB in size. The database is mainly
used for queries except when refreshing the data on a scheduled basis.

Any input would be highly appreciated.

Regards,
JE

If you stripe the containers across multiple drives (by defining each
container within a particular DMS tablespace on a separate physical drive),
and do the extent and prefetch configuration properly, it could help with
some queries that scan large parts of the tables and/or indexes. You are to
some extent (no pun intended) limited by your 1 CPU, but intra-partition
parallelism could help depending on how fast the CPU was.

INTRA_PARALLEL does not control IO parallelism -- that is controlled by the
number of containers in your tablespace and the NUM_IOSERVERS parameter
(regardless of the setting for INTRAL_PARALLEL).

INTRA_PARALLEL lets a DB2 divide a query into multiple subsections that
can be executed in parallel with each other. If you leave DFT_DEGREE set
to -1, you may find that DB2 avoids using a runtime degree > 1 since you
have just 1 CPU.

This is probably not a good idea -- there are a number of other processes
that are running to support the database manager (io servers/cleaners, log
readers/writers, etc.). Especially if you are supporting more than 1
concurrent user.

This does raise an interesting question as to how DB2 interacts with the
(kernel) scheduler, and if setting CURRENT DEGREE = 2 or more could improve
performance. You could experiment with this, but I wouldn't do it on your
production system :-)
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----