Skip to Main Content
  • Questions
  • Parallel hint in oracle created more than double Entries in $sql

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mansi.

Asked: January 19, 2023 - 12:11 pm UTC

Last updated: March 23, 2023 - 2:01 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Viewed 1000+ times

You Asked

Hi Team,

I have one query which quite complex and join multiple tables.
Its
Insert into table a
(Select /*+ parallel(S,8) */ column A,column B from Table a,Table b {Business logic})

Now the issue is, when this query is running, I could see there are 17 entries in gv$SQL and gv$Session.

Idelily it should only have 8 parallel queries running in gv$SQL as per my understandig.
How come there are double the entries.

Please note its RAC environment with 2 Instances.

If I go through the real time SQL monitor, I could see PX COORDINATOR has 17 executions. and PX SEND and PX BLOCK operation has 8 execution.

Is anything wrong here, because this query is running too slow.

and Connor said...

PARALLEL(8) can have up to:

- 8 producers (threads reading data and passing the data up stream to ... )
- 8 consumers (threads receiving data from the producers)
- 1 coordinator (your original session overseeing all the parallel workers doing their thing).

So 17 is not unexpected.

If that is overloading your server CPU and/or IO subsytem, then try reducing the parallel degree.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

/*+ parallel(S,8) */ Changed to /*+ parallel(S,4) */

Mansi, March 14, 2023 - 3:35 pm UTC

Thank you Connor for the answer.

I understood that /*+ parallel(S,8) */ is making 17 parallel instances, but I now I reduced it to /*+ parallel(S,4) */ to match the CPU capacity and /*+ parallel(S,8) */ is making query too slow.

Still I see 17 Instances, why it is not taking hint as 4. Is any other paramater I need to check as /*+ parallel(S,8) */ is really making query and system slow and it show heavy IOs.
Chris Saxon
March 21, 2023 - 6:13 pm UTC

Is "S" a table alias in your query? It doesn't appear in the pseudo example in the post

Table Alias

Mansi, March 22, 2023 - 5:06 am UTC

Yes S Is a table Alias used in entire query.
Chris Saxon
March 23, 2023 - 2:01 pm UTC

If there are many tables in the query, they could be accessed with different numbers of parallel processes. The docs have a detailed guide on the ways you can set the parallel level and thus things you can check:

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/degree-parallel.html#GUID-68ED8F5E-DD97-4762-985C-4C3AF85F9629

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.