Home » SQL & PL/SQL » SQL & PL/SQL » Need some help understanding parallels (oracle 12c, Red Hat Linux)
Need some help understanding parallels [message #673701] Wed, 05 December 2018 04:42 Go to next message
sureshonly@hotmail.com
Messages: 5
Registered: December 2018
Junior Member
Hi Guys,

I need some help in understanding how parallels work in different situations.

I got table A, B with parallel 10 degrees each.

When I run the below DML how it works and how many processors the DB look for

Create Table C PARALLEL (DEGREE 10) AS
SELECT A.* FROM A
JOIN B ON (A.COL1=B.COL1)

My server got 50 processors.

Can someone tell me what will be the performance of the query and what parallels I can use to tune it.

Regards,
Suresh

--moderator edit: added [code] tags, please do so yourself in future

[Updated on: Sat, 08 December 2018 02:34] by Moderator

Report message to a moderator

Re: Need some help understanding parallels [message #673703 is a reply to message #673701] Wed, 05 December 2018 06:21 Go to previous messageGo to next message
John Watson
Messages: 7699
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

No-one can tell you what the performance will be. You will have to run the query, and time it. Immediately after running it, if you run this
select dfo_number,tq_id,server_type,process,num_rows,instance from v$pq_tqstat
order by instance,tq_id,process;
you will see how the parallelism worked.
Re: Need some help understanding parallels [message #673704 is a reply to message #673703] Wed, 05 December 2018 08:54 Go to previous messageGo to next message
sureshonly@hotmail.com
Messages: 5
Registered: December 2018
Junior Member
Thanks It helps a lot.
Re: Need some help understanding parallels [message #673705 is a reply to message #673704] Wed, 05 December 2018 08:59 Go to previous messageGo to next message
John Watson
Messages: 7699
Registered: January 2010
Location: Global Village
Senior Member
Don't stop there,man! Forums are meant to be two-way process: give as well as receive. If you show and explain what you did, that may help someone else in future.
Re: Need some help understanding parallels [message #673706 is a reply to message #673705] Wed, 05 December 2018 09:25 Go to previous messageGo to next message
sureshonly@hotmail.com
Messages: 5
Registered: December 2018
Junior Member
I checked the above table after executing a sample query. and it gives the list of parallels its used for the query and also about the bytes of data returned. I will have to check some big queries. Will give more output soon.
Re: Need some help understanding parallels [message #673738 is a reply to message #673706] Fri, 07 December 2018 04:34 Go to previous messageGo to next message
sureshonly@hotmail.com
Messages: 5
Registered: December 2018
Junior Member
I have 60M records in table A, and 120M records in the second table. So query is running very slow, It took around 8 hours with out any parallels, and with parallel 10 on each table, its been running for more than 12 hours. Very strange, and sad as its running slow with parallels than no parallel.

In the v$pq_tqstat results, I found p000 to p015 for PROCESS field. I had to abort the query as its in production and need to give away for other jobs.
I found 3 consumers and 1 producer for each process eg. P000

Consumer 1 - 18K records 4274 waits
Consumer 2 - 7M records 2062 waits
Consumer 3 - 11M records 2221 waits
Producer 1 - 550 records 4279 waits

Regards
Suresh
Re: Need some help understanding parallels [message #673739 is a reply to message #673738] Fri, 07 December 2018 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Parallel is harmful if you have only 1 disk or disk controller behind.

Re: Need some help understanding parallels [message #673740 is a reply to message #673739] Fri, 07 December 2018 05:55 Go to previous messageGo to next message
sureshonly@hotmail.com
Messages: 5
Registered: December 2018
Junior Member
Shall I assume its only using 15 Parallels for my above query?

Regards
Suresh
Re: Need some help understanding parallels [message #673755 is a reply to message #673740] Sat, 08 December 2018 02:37 Go to previous messageGo to next message
John Watson
Messages: 7699
Registered: January 2010
Location: Global Village
Senior Member
I have no idea what you should assume, because you have no provided enough information. You need to show the results of the query you are running and the execution plan. Only by considering these together can one get any idea of what is happening.

If you do decide to post this information, please remember to enclose your copy/pastes within [code] tags, so that they are readable.
Re: Need some help understanding parallels [message #673756 is a reply to message #673740] Sat, 08 December 2018 08:33 Go to previous message
BlackSwan
Messages: 26323
Registered: January 2009
Location: SoCal
Senior Member
sureshonly@hotmail.com wrote on Fri, 07 December 2018 03:55
Shall I assume its only using 15 Parallels for my above query?

Regards
Suresh

You have seen for yourself that 10 Parallel is slower than single thread processing.
So explain why you propose increasing parallel to 15 will be faster than either 10 or 1.
Previous Topic: RETURNING BULK COLLECT INTO COLLECTION
Next Topic: Problem with the product user profile
Goto Forum:
  


Current Time: Thu Dec 13 04:32:11 CST 2018