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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mansi.

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

Last updated: January 23, 2023 - 7:15 am UTC

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

Viewed 100+ 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.

More to Explore

Performance

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