This forum is currently locked. You can't register or post questions at this time. (read more)

parallelism in table

All posts relating to Oracle database administration.

Moderator: Tim...

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

parallelism in table

Postby mail.madhankumar » Thu Apr 10, 2014 2:52 pm

Hi Tim,
is there any disadvantage if we use parallelism in table level.

one Query having two(A & B) table with inner join. both contains million rows. A table contains filter, now A table uses indexes but B using Full table access. cann't add filter condition in B. in this time how to improve speed of the Query. for this scenario i changed Degree from 1 to 4. earlier that Query was taking 1Min , after changing degree to 4 it takes 5 sec.
i think if we use parallelism, CPU uses more but i am not sure. i have 32CPU in this server , i have allocated only 4. Please suggest me.. shall i use parallelism or need to work in Query level.


Thanks,
Madhan

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: parallelism in table

Postby Tim... » Fri Apr 11, 2014 1:25 pm

Hi.

As always, the answer is "it depends".

- If you are confident the query can't be tuned further and parallel helps, then go with parallel.
- Are you confident that setting parallel at table level is not adversely affecting other workloads?
- Have you tried using parallel hints, so only this query is affected, not the whole of the interactions with the table?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

User avatar
matthew_morris
Member
Posts: 13
Joined: Thu May 23, 2013 12:15 am
Location: Orlando, FL
Contact:

Re: parallelism in table

Postby matthew_morris » Sun Apr 13, 2014 9:36 pm

Changing queries to run in parallel is trading resources for speed. Parallel operations always use more resources than an equivalent serial query. That is specifically what it does -- throw additional resources into processing a given operation.

If your database resources are not fully utilized, then using parallelism is a means of getting more benefit from the hardware capabilities you already have. If the resources of your database are at full or near-full utilization, then parallelism can rob resources from user X (and make their query slower) in order to give them to user Y (to make his faster). In severe cases it can impact the performance of the database as a whole.
Matthew Morris
DBA/Developer/Author
OCP DBA 7.3, 8, 9i, 10g, 11g, 12c
OCP Advanced PL/SQL Developer
My website: http://www.oraclecertificationprep.com
My blog: http://ocprep.blogspot.com/

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: parallelism in table

Postby Tim... » Sun Apr 13, 2014 9:57 pm

Hi.

You've kind-of missed the point of the original post. The OP said that using parallel worked well, reducing the query time from 1 minute to 5 seconds. The point was, should parallel be set at table level.

My point here is, what else is affected by this? If it were just a parallel hint in a query, only this query would be affected by the parallel setting. It it were set at table level, potentially its affect could be more widespread. It is always dangerous to make a change that could potentially affect the wider workload on the server. It's like using a sledgehammer to crack a walnut!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

User avatar
matthew_morris
Member
Posts: 13
Joined: Thu May 23, 2013 12:15 am
Location: Orlando, FL
Contact:

Re: parallelism in table

Postby matthew_morris » Mon Apr 14, 2014 1:22 am

Tim... wrote:You've kind-of missed the point of the original post.


I don't really think I did -- but I was apparently not clear enough. My point was that when using parallelism, DBAs must always take into account that the operation will use more database resources. If the server the database is on has those resources to spare, then parallelism makes better use of what you have (unused CPU cycles, memory and I/O bandwidth benefit nobody). If the server is running low on resources, parallelism can be harmful.

I understand that your reply was focused strictly on setting the table DOP vs using a query. I wasn't trying to imply yours was wrong. Mine was simply referring to the decision on parallelizing operations in general. The OPs first sentence was asking if there was a disadvantage in setting the parallelism at the table level. There certainly *is* if his server is short on resources and parallelizing queries against that table will cause shortages elsewhere.
Matthew Morris
DBA/Developer/Author
OCP DBA 7.3, 8, 9i, 10g, 11g, 12c
OCP Advanced PL/SQL Developer
My website: http://www.oraclecertificationprep.com
My blog: http://ocprep.blogspot.com/

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: parallelism in table

Postby Tim... » Mon Apr 14, 2014 7:11 am

Hi.

I agree, but the implication of the post was that things were working fine for this statement, which suggests the overhead of parallel processing is not a negative thing for this statement.

Where we do agree is the impact on a system basis needs to be judged. This is true for this single statement in isolation, as it may now be hogging resources and slowing down other processes. My point was, by changing the DOP on the table, that issue might be compounded by other statements also doing the same. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 1 guest

cron