OraFAQ Forum: SQL & PL/SQL » ORA-12801: error signaled in parallel query server PXXX (merged 3)

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12801: error signaled in parallel query server PXXX (merged 3) (oracle ,12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12801: error signaled in parallel query server PXXX (merged 3) [message #679058]

Fri, 31 January 2020 00:23

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

Inside a package we are inserting data into a table using select statement . select statement has parallel hint and DOP is 8 .
We are getting error "ORA-12801: error signaled in parallel query server P006" while running the process.
I removed DOP from parallel hint and rerun the process but still getting error.
I observed "alter session set hash_area_size=948576000 " statement in a package.
Could you please let me know is there any relation between ORA-12801 and hash_area_size.

Report message to a moderator

ORA-12801: error signaled in parallel query server PXXX [message #679059 is a reply to message #679058]

Fri, 31 January 2020 00:24

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

Report message to a moderator

ORA-12801: error signaled in parallel query server PXXX [message #679060 is a reply to message #679058]

Fri, 31 January 2020 00:25

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679062 is a reply to message #679058]

Fri, 31 January 2020 00:36

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-12801: error signaled in parallel query server %s
 *Cause: A parallel query server reached an exception condition.
 *Action: Check the following error message for the cause, and consult
          your error manual for the appropriate action.
 *Comment: This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679064 is a reply to message #679062]

Fri, 31 January 2020 02:41

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

Thanks Michel , Below is the complete error detail :

ERROR at line 1:
ORA-12801: error signaled in parallel query server P004
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "USER.PKG_TEST", line 3035
ORA-06512: at line 1

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679065 is a reply to message #679064]

Fri, 31 January 2020 02:46

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

So what does user.pkg_test line 3035 say?

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679068 is a reply to message #679065]

Fri, 31 January 2020 03:10

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

as i told we are inserting data in a table using select which has parallel hint along with DOP.

below is the sample query with dummy names, not complete query :

INSERT /*+ APPEND */ INTO test_tt
SELECT /*+ materialize ordered full(T1) parallel(T1,8 ) use_nl(T2) use_nl(T3) */ ....

[Updated on: Fri, 31 January 2020 03:12]

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679069 is a reply to message #679068]

Fri, 31 January 2020 03:20

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Lets see the full statement.

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679071 is a reply to message #679069]

Fri, 31 January 2020 03:57

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

/* Formatted on 1/31/2020 3:28:24 PM (QP5 v5.185.11230.41888) */
INSERT /*+ APPEND */
INTO test_usage_facts_tt (characteristics,
usage_bin,
USAGE_BIN_period,
ui_FROM,
ui_to,
season_config_id,
program_id,
cust_class_ind,
day_type,
weather_zone,
rate_group_id,
channel_id,
dummy_location_id,
dummy_account_id,
dummy_co_id,
total_usage,
avg_usage,
total_days,
read_date,
next_read_date)
SELECT characteristics,
usage_bin,
USAGE_BIN_period,
ui_FROM,
ui_to,
season_config_id,
program_id,
cust_class_ind,
day_type,
weather_zone,
rate_group_id,
channel_id,
dummy_location_id,
dummy_account_id,
dummy_co_id,
total_usage,
avg_usage,
total_days,
read_date,
next_read_date
FROM (WITH dt
AS (SELECT /*+ materialize */
billingcycle, read_date, next_read_date
FROM (SELECT billingcycle,
read_date,
LEAD (
read_date)
OVER (PARTITION BY billingcycle
ORDER BY read_date)
next_read_date
FROM test_dates)
WHERE next_read_date BETWEEN TO_DATE (p_entry_date)
- parm_days_for_emr_recovery
AND TO_DATE (p_entry_date) - 1),
dummy_locations
AS (SELECT /*+ materialize ordered full(cum) parallel(cum,8 ) use_nl(md) use_nl(sm) */
cum.entry_date,
md.device_id,
sm.dummy_location_id,
sm.dummy_account_id,
md.readingcycle,
dt.billingcycle,
dt.read_date,
dt.next_read_date
FROM test_vee_override_gateways override,
test_cum_reads cum,
dummy_device md,
dt,
dummy_meter sm
WHERE cum.device_id = md.device_id
AND p_entry_date <> dt.next_read_date
AND cum.entry_date = p_entry_date
AND md.readingcycle = dt.billingcycle
AND md.device_id = sm.device_id
AND TO_DATE (p_entry_date) - 1 / 86400 BETWEEN sm.effective_date
AND sm.end_effective_date
- 1
/ 86400
AND cum.gateway_id = override.gateway_id)
SELECT /*+ ordered index(mra,dummy_usage_facts_daily_N1 ) use_nl(mra) */
characteristics,
usage_bin,
USAGE_BIN_period,
ui_FROM,
ui_to,
day_type,
weather_zone,
rate_group_id,
channel_id,
mra.dummy_location_id,
mra.dummy_account_id,
avg_usage AS total_usage,
avg_usage,
1 total_days,
dummy_locations.read_date,
dummy_locations.next_read_date,
SEASON_CONFIG_ID,
program_id,
cust_class_ind,
mra.dummy_co_id
FROM dummy_locations, dummy_usage_facts_daily mra
WHERE 1 = 1
AND dummy_locations.dummy_location_id =
mra.dummy_location_id
AND dummy_locations.dummy_account_id = mra.dummy_account_id
AND mra.ui_to >= dummy_locations.read_date + 1
AND mra.ui_to <= dummy_locations.next_read_date
AND mra.readcycle = dummy_locations.readingcycle)

/* Note : I have used dummy_names in above query */

[Updated on: Fri, 31 January 2020 03:58]

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679072 is a reply to message #679071]

Fri, 31 January 2020 04:04

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, now repost formatted.

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679073 is a reply to message #679072]

Fri, 31 January 2020 06:01

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

I have attached formatted query.

Attachment: 206438.sql.txt
(Size: 5.01KB, Downloaded 1679 times)

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679074 is a reply to message #679073]

Fri, 31 January 2020 07:34

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Now post it directly in the thread in [code][/code] tags

Report message to a moderator

Re: ORA-12801: error signaled in parallel query server PXXX [message #679085 is a reply to message #679074]

Mon, 03 February 2020 00:19

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

/* Formatted on 1/31/2020 5:29:26 PM (QP5 v5.185.11230.41888) */
INSERT /*+ APPEND */
      INTO  test_usage_facts_tt (characteristics,
                                 usage_bin,
                                 usage_bin_period,
                                 ui_from,
                                 ui_to,
                                 season_config_id,
                                 program_id,
                                 cust_class_ind,
                                 day_type,
                                 weather_zone,
                                 rate_group_id,
                                 channel_id,
                                 dummy_location_id,
                                 dummy_account_id,
                                 dummy_co_id,
                                 total_usage,
                                 avg_usage,
                                 total_days,
                                 read_date,
                                 next_read_date)
   SELECT characteristics,
          usage_bin,
          usage_bin_period,
          ui_from,
          ui_to,
          season_config_id,
          program_id,
          cust_class_ind,
          day_type,
          weather_zone,
          rate_group_id,
          channel_id,
          dummy_location_id,
          dummy_account_id,
          dummy_co_id,
          total_usage,
          avg_usage,
          total_days,
          read_date,
          next_read_date
     FROM (WITH dt
                AS (SELECT /*+ materialize */
                          billingcycle, read_date, next_read_date
                      FROM (SELECT billingcycle,
                                   read_date,
                                   LEAD (
                                      read_date)
                                   OVER (PARTITION BY billingcycle
                                         ORDER BY read_date)
                                      next_read_date
                              FROM test_dates)
                     WHERE next_read_date BETWEEN   TO_DATE (p_entry_date)
                                                  - parm_days_for_emr_recovery
                                              AND TO_DATE (p_entry_date) - 1),
                dummy_locations
                AS (SELECT /*+ materialize ordered full(cum) parallel(cum,8)  use_nl(md) use_nl(sm) */
                           cum.entry_date,
                           md.device_id,
                           sm.dummy_location_id,
                           sm.dummy_account_id,
                           md.readingcycle,
                           dt.billingcycle,
                           dt.read_date,
                           dt.next_read_date
                      FROM test_vee_override_gateways override,
                           test_cum_reads cum,
                           dummy_device md,
                           dt,
                           dummy_meter sm
                     WHERE     cum.device_id = md.device_id
                           AND p_entry_date <> dt.next_read_date
                           AND cum.entry_date = p_entry_date
                           AND md.readingcycle = dt.billingcycle
                           AND md.device_id = sm.device_id
                           AND TO_DATE (p_entry_date) - 1 / 86400 BETWEEN sm.effective_date
                                                                      AND   sm.end_effective_date
                                                                          -   1
                                                                            / 86400
                           AND cum.gateway_id = override.gateway_id)
           SELECT /*+ ordered  index(mra,dummy_usage_facts_daily_N1 )  use_nl(mra)  */
                  characteristics,
                  usage_bin,
                  usage_bin_period,
                  ui_from,
                  ui_to,
                  day_type,
                  weather_zone,
                  rate_group_id,
                  channel_id,
                  mra.dummy_location_id,
                  mra.dummy_account_id,
                  avg_usage AS total_usage,
                  avg_usage,
                  1 total_days,
                  dummy_locations.read_date,
                  dummy_locations.next_read_date,
                  season_config_id,
                  program_id,
                  cust_class_ind,
                  mra.dummy_co_id
             FROM dummy_locations, dummy_usage_facts_daily mra
            WHERE     1 = 1
                  AND dummy_locations.dummy_location_id =
                         mra.dummy_location_id
                  AND dummy_locations.dummy_account_id = mra.dummy_account_id
                  AND mra.ui_to >= dummy_locations.read_date + 1
                  AND mra.ui_to <= dummy_locations.next_read_date
                  AND mra.readcycle = dummy_locations.readingcycle)
/* Note : I have used dummy_names in above query */

Report message to a moderator

Previous Topic:	Calling webservice from plsql package (merged)
Next Topic:	Delete statement to retain last 90 days of data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 15:10:08 CDT 2025