ORA-00600:[15570]内部错误一例

一套Linux上的10.2.0.1系统出现ORA-00600:[15570]内部错误,日志如下:

Sat Jun 5 11:33:17 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 2190K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==
Sat Jun 5 14:57:25 2010
Thread 1 advanced to log sequence 16540
Current log# 3 seq# 16540 mem# 0: /ora_data/mantas/redo03.log
Sat Jun 5 14:58:37 2010
Errors in file /opt/oracle/admin/mantas/udump/mantas_ora_10803.trc:
ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], []
Sat Jun 5 14:58:37 2010
Errors in file /opt/oracle/admin/mantas/udump/mantas_ora_10903.trc:
ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], []
Sat Jun 5 14:58:39 2010
Errors in file /opt/oracle/admin/mantas/udump/mantas_ora_10801.trc:
ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], []

##23393_trc.rtf##

/opt/oracle/admin/mantas/udump/mantas_ora_23393.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle
System name: Linux
Node name: mandbdev.ssfcu.inet
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine: i686
Instance name: mantas
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 23393, image: [email protected]

*** SERVICE NAME:(mantas.ssfcu.inet) 2010-06-05 09:17:10.438
*** SESSION ID:(540.115) 2010-06-05 09:17:10.438
*** 2010-06-05 09:17:10.438
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT t94.ACCT_INTRL_ID, t94.FO_TRXN_SEQ_ID, t94.TRXN_EXCTN_DT, t94.CASH_TRXN_ACTVY_RISK_NB, t94.CUST_INTRL_ID, t94.CUST_EFCTV_RISK_NB, t94.TRXN_BASE_AM, t94.D_HR_TRXN_AM, t94.D_LRF_TRXN_AM FROM (-- Only one part of UNION ALL will be executed
-- threshold 'N' define which of them
-- 'N' is parameter that indicates what account are included for customer focus to monitor :
-- Y - cover only accounts for which a customer plays a primary role
-- N - cover account over which a customer has a discretion
-- PR-39791 singhan dated 03/23/09
-- Replaced (BUS_DAY_AGE = 0 and bus_day_fl='Y' ) with Clndr_Day_Age = 0
-- Included "Exclude Cash Transaction Report Exemption Accounts" filter using coalesce (a.CASH_RPT_EXMPT_FL, ' ')<>'Y'
-- Date: 1st Feb 2010 : PR 42362 : Exclude cancelled and canceling transactions
-- tr.CXL_PAIR_TRXN_INTRL_ID is null
SELECT
t.TRXN_BASE_AM
, c.CUST_INTRL_ID
, t.CASH_TRXN_ACTVY_RISK_NB
, t.FO_TRXN_SEQ_ID
, t.TRXN_EXCTN_DT
, a.ACCT_INTRL_ID
, CASE WHEN t.TRXN_BASE_AM-TRUNC(t.TRXN_BASE_AM,-4)=0 THEN t.TRXN_BASE_AM
WHEN t.TRXN_ACTVY_AM-TRUNC(t.TRXN_ACTVY_AM,-4)=0 THEN t.TRXN_BASE_AM
ELSE 0
END as d_LRF_TRXN_AM
, c.CUST_EFCTV_RISK_NB
, CASE WHEN t.CASH_TRXN_NTITY_RISK_NB>0 or t.CASH_TRXN_ACTVY_RISK_NB>0 THEN t.TRXN_BASE_AM ELSE 0 END D_HR_TRXN_AM
FROM
BUSINESS.CASH_TRXN t
inner join BUSINESS.ACCT a on a.ACCT_INTRL_ID = t.ACCT_INTRL_ID
inner join BUSINESS.CUST c on c.CUST_INTRL_ID=a.PRMRY_CUST_INTRL_ID
WHERE
-- Exclude cancelled and canceling transactions
-- PR 42362
t.CXL_PAIR_TRXN_INTRL_ID is null
--Exclude Exempted Customers
and c.CUST_EFCTV_RISK_NB <> -2
-- Cover customers either from all jurisdiction or from Incl_Jurisdictions_Lst only
and ('Y'='Y' or c.JRSDCN_CD in ('BUS'))
-- Include Retail Customer Accounts Only
and a.MANTAS_ACCT_HOLDR_TYPE_CD='CR'
-- Cover only specific accounts
and a.MANTAS_ACCT_BUS_TYPE_CD in ('RBK', 'RBR')
--
and t.MANTAS_TRXN_PRDCT_CD in ('CURRENCY')
and t.MANTAS_TRXN_PURP_CD = 'GENERAL'
-- Cover either all transaction or only form the Incl_Trans_Src_Lst
and ('Y'='Y' or t.SRC_SYS_CD in ('Inactive'))
-- PR-39791 start
-- Exclude Cash Transaction Report Exemption Accounts
and coalesce (a.CASH_RPT_EXMPT_FL, ' ')<>'Y'
and t.TRXN_EXCTN_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.TRXN_EXCTN_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL')
and t.DATA_DUMP_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.DATA_DUMP_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL')
-- PR-39791 start
-- Parameter that indicates what account are included for customer focus to monitor :
-- Y - cover only accounts for which a customer plays a primary role
-- N - cover account over which a customer has a discretion
and 'N' = 'Y'
-----------------------------------------
UNION ALL
------------------------------------------
SELECT
t.TRXN_BASE_AM
, c.CUST_INTRL_ID
, t.CASH_TRXN_ACTVY_RISK_NB
, t.FO_TRXN_SEQ_ID
, t.TRXN_EXCTN_DT
, a.ACCT_INTRL_ID
, CASE WHEN t.TRXN_BASE_AM-TRUNC(t.TRXN_BASE_AM,-4)=0 THEN t.TRXN_BASE_AM
WHEN t.TRXN_ACTVY_AM-TRUNC(t.TRXN_ACTVY_AM,-4)=0 THEN t.TRXN_BASE_AM
ELSE 0
END as d_LRF_TRXN_AM
, c.CUST_EFCTV_RISK_NB
, CASE WHEN t.CASH_TRXN_NTITY_RISK_NB>0 or t.CASH_TRXN_ACTVY_RISK_NB>0 THEN t.TRXN_BASE_AM ELSE 0 END d_HR_TRXN_AM
FROM
BUSINESS.CASH_TRXN t
inner join BUSINESS.ACCT a on t.acct_intrl_id = a.acct_intrl_id
inner join BUSINESS.CUST_ACCT ca on a.acct_intrl_id = ca.acct_intrl_id
inner join BUSINESS.CUST c on ca.CUST_INTRL_ID = c.CUST_INTRL_ID
inner join BUSINESS.CUST_ACCT_ROLE car on ca.cust_acct_role_cd = car.cust_acct_role_cd
WHERE
-- Exclude cancelled and canceling transactions
-- PR 42362
t.CXL_PAIR_TRXN_INTRL_ID is null
--Exclude Exempted Customers
and c.CUST_EFCTV_RISK_NB <> -2
-- Cover customers either from all jurisdiction or from Incl_Jurisdictions_Lst only
and ('Y'='Y' or c.JRSDCN_CD in ('BUS'))
-- Include Retail Customer Accounts Only
and a.MANTAS_ACCT_HOLDR_TYPE_CD='CR'
-- Cover only specific accounts
and a.MANTAS_ACCT_BUS_TYPE_CD in ('RBK', 'RBR')
--
and t.MANTAS_TRXN_PRDCT_CD in ('CURRENCY')
and t.MANTAS_TRXN_PURP_CD = 'GENERAL'
-- Cover either all transaction or only form the Incl_Trans_Src_Lst
and ('Y'='Y' or t.SRC_SYS_CD in ('Inactive'))
-- PR-39791 start
-- Exclude Cash Transaction Report Exemption Accounts
and coalesce (a.CASH_RPT_EXMPT_FL, ' ')<>'Y'
and t.TRXN_EXCTN_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.TRXN_EXCTN_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL')
and t.DATA_DUMP_DT <= (select k0.clndr_dt from MANTAS.KDD_CAL k0 where k0.Clndr_Day_Age = 0 and k0.clndr_nm = 'SYSCAL') and t.DATA_DUMP_DT > (select k1.clndr_dt from MANTAS.KDD_CAL k1 where k1.Clndr_Day_Age = 30 and k1.clndr_nm = 'SYSCAL')
-- PR-39791 end
-- Parameter that indicates what account are included for customer focus to monitor :
-- Y - cover only accounts for which a customer plays a primary role
-- N - cover account over which a customer has a discretion
and 'N' = 'N'
-- Account Customer Role will be viewed as disretionary
and (car.trdng_auth_fl = 'Y' or car.wdrwl_auth_fl = 'Y' or car.poa_fl = 'Y')
) t94, RULE_MATCHER_TEMP910701_0 t106 WHERE t94.CUST_INTRL_ID = t106.FOCAL_ENTITY_ID ORDER BY t94.FO_TRXN_SEQ_ID ASC

CALL STACK
--------------

ksedst <- ksedmp <- ksfdmp <- kgeriv <- kgeasi
<- qerpxFetch <- opifch2 <- kpoal8 <- opiodr <- ttcpip
<- opitsk <- opiino <- opiodr <- opidrv <- sou2o
<- opimai_real <- main <- libc_start_main PROCESS STATE ------------------- SO: 0x3ed2a160, type: 4, owner: 0x3f7e28f4, flag: INIT/-/-/0x00 (session) sid: 540 trans: (nil), creator: 0x3f7e28f4, flag: (8000041) USR/- BSY/-/-/-/-/- DID: 0001-0020-00000265, short-term DID: 0000-0000-00000000 txn branch: (nil) oct: 3, prv: 0, sql: 0x36e5e194, psql: 0x36c493d8, user: 65/KDD_ALG O/S info: user: mantas, term: , ospid: 26740, machine: manappdev program: mantas@manappdev (TNS V1-V3) application name: mantas@manappdev (TNS V1-V3), hash value=0 last wait for 'PX Deq: Execute Reply' blocking sess=0x(nil) seq=1052 wait_time=150 seconds since wait started=0 sleeptime/senderid=c8, passes=1, =0 Dumping Session Wait History for 'PX Deq: Execute Reply' count=1 wait_time=150 sleeptime/senderid=c8, passes=1, =0 for 'PX Deq: Execute Reply' count=1 wait_time=21 sleeptime/senderid=c8, passes=2, =0 for 'PX Deq: Execute Reply' count=1 wait_time=2 sleeptime/senderid=c8, passes=1, =0 for 'PX Deq: Execute Reply' count=1 wait_time=3 sleeptime/senderid=a, passes=1, =0 for 'PX Deq: Execute Reply' count=1 wait_time=165 sleeptime/senderid=c8, passes=1, =0 for 'PX Deq Credit: send blkd' count=1 wait_time=1918048 sleeptime/senderid=10010002, passes=1, qref=0 for 'db file sequential read' count=1 wait_time=4045 file#=1e, block#=5681, blocks=1 for 'db file sequential read' count=1 wait_time=13290 file#=1e, block#=7a99, blocks=1 for 'db file sequential read' count=1 wait_time=5331 file#=1e, block#=c4, blocks=1 for 'PX Deq: Execute Reply' count=1 wait_time=21 sleeptime/senderid=c8, passes=1, =0 temporary object counter: 0 ============ Plan Table ============ ---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop | ---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+ | 0 | SELECT STATEMENT | | | | 67 | | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10004 | 12 | 1884 | 67 | 00:00:01 |:Q1004| P->S |QC (ORDER) | | |
| 3 | SORT ORDER BY | | 12 | 1884 | 67 | 00:00:01 |:Q1004| PCWP | | | |
| 4 | PX RECEIVE | | 12 | 1884 | 66 | 00:00:01 |:Q1004| PCWP | | | |
| 5 | PX SEND RANGE | :TQ10003 | 12 | 1884 | 66 | 00:00:01 |:Q1003| P->P |RANGE | | |
| 6 | BUFFER SORT | | 12 | 1884 | | |:Q1003| PCWP | | | |
| 7 | NESTED LOOPS | | 12 | 1884 | 66 | 00:00:01 |:Q1003| PCWP | | | |
| 8 | BUFFER SORT | | | | | |:Q1003| PCWC | | | |
| 9 | PX RECEIVE | | | | | |:Q1003| PCWP | | | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P |BROADCAST | | |
| 11 | TABLE ACCESS FULL | RULE_MATCHER_TEMP910701_0| 82 | 820 | 3 | 00:00:01 | | | | | |
| 12 | VIEW | | 1 | 147 | | |:Q1003| PCWP | | | |
| 13 | UNION ALL PUSHED PREDICATE | | | | | |:Q1003| PCWP | | | |
| 14 | FILTER | | | | | |:Q1003| PCWC | | | |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID | CASH_TRXN | 1 | 79 | 3 | 00:00:01 |:Q1003| PCWC | | | |
| 16 | NESTED LOOPS | | 1 | 140 | 7 | 00:00:01 |:Q1003| PCWP | | | |
| 17 | BUFFER SORT | | | | | |:Q1003| PCWC | | | |
| 18 | PX RECEIVE | | | | | |:Q1003| PCWP | | | |
| 19 | PX SEND BROADCAST | :TQ10001 | | | | | | S->P |BROADCAST | | |
| 20 | NESTED LOOPS | | 1 | 61 | 3 | 00:00:01 | | | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | CUST | 1 | 27 | 1 | 00:00:01 | | | | | |
| 22 | INDEX UNIQUE SCAN | PK_CUST | 1 | | 1 | 00:00:01 | | | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | ACCT | 1 | 34 | 2 | 00:00:01 | | | | | |
| 24 | INDEX RANGE SCAN | ACCT_CUST_IDX | 4 | | 1 | 00:00:01 | | | | | |
| 25 | PX PARTITION RANGE ITERATOR | | 1 | | 2 | 00:00:01 |:Q1003| PCWC | | KEY | KEY |
| 26 | INDEX RANGE SCAN | CASH_ACCTID_IDX | 1 | | 2 | 00:00:01 |:Q1003| PCWP | | KEY | KEY |
| 27 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 |:Q1003| PCWP | | | |
| 28 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 |:Q1003| PCWP | | | |
| 29 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | |
| 30 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | |
| 32 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | |
| 34 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | |
| 35 | TABLE ACCESS BY LOCAL INDEX ROWID | CASH_TRXN | 1 | 79 | 3 | 00:00:01 |:Q1003| PCWC | | 1 | 1 |
| 36 | NESTED LOOPS | | 1 | 173 | 18 | 00:00:01 |:Q1003| PCWP | | | |
| 37 | BUFFER SORT | | | | | |:Q1003| PCWC | | | |
| 38 | PX RECEIVE | | | | | |:Q1003| PCWP | | | |
| 39 | PX SEND BROADCAST | :TQ10002 | | | | | | S->P |BROADCAST | | |
| 40 | NESTED LOOPS | | 5 | 470 | 5 | 00:00:01 | | | | | |
| 41 | NESTED LOOPS | | 5 | 350 | 4 | 00:00:01 | | | | | |
| 42 | NESTED LOOPS | | 5 | 290 | 2 | 00:00:01 | | | | | |
| 43 | TABLE ACCESS BY INDEX ROWID | CUST | 1 | 27 | 1 | 00:00:01 | | | | | |
| 44 | INDEX UNIQUE SCAN | PK_CUST | 1 | | 1 | 00:00:01 | | | | | |
| 45 | INDEX RANGE SCAN | PK_CUST_ACCT | 5 | 155 | 1 | 00:00:01 | | | | | |
| 46 | TABLE ACCESS BY INDEX ROWID | CUST_ACCT_ROLE | 1 | 12 | 1 | 00:00:01 | | | | | |
| 47 | INDEX UNIQUE SCAN | PK_CUST_ACCT_ROLE | 1 | | 1 | 00:00:01 | | | | | |
| 48 | TABLE ACCESS BY INDEX ROWID | ACCT | 1 | 24 | 1 | 00:00:01 | | | | | |
| 49 | INDEX UNIQUE SCAN | PK_ACCT | 1 | | 1 | 00:00:01 | | | | | |
| 50 | PX PARTITION RANGE ITERATOR | | 1 | | 2 | 00:00:01 |:Q1003| PCWC | | KEY | KEY |
| 51 | INDEX RANGE SCAN | CASH_ACCTID_IDX | 1 | | 2 | 00:00:01 |:Q1003| PCWP | | KEY | KEY |
| 52 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 |:Q1003| PCWP | | | |
| 53 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 |:Q1003| PCWP | | | |
| 54 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | |
| 55 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | |
| 57 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | |
| 58 | TABLE ACCESS BY INDEX ROWID | KDD_CAL | 1 | 19 | 1 | 00:00:01 | | | | | |
| 59 | INDEX UNIQUE SCAN | AK1_KDD_CAL | 1 | | 1 | 00:00:01 | | | | | |
---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+

该问题提交了SR,MOS给出的回复:”This is high likely caused by bug 4611578,bug 4611578 is complete fixed in 8397251.Parallel Execution plan shows a UNION ALL under a nested loop row source that is the bug pattern”。在使用并行的情况下存在UNION ALL PUSHED PREDICATE以合并某些由NESTED LOOP获得的数据源是引发该4611578 bug的典型情况。 Oracle GCS建议通过修改PUSHED PREDICATE的相关隐式参数来避免谓词前推的发生:

Please try if one of the following workaround help:

- alter session set "_optimizer_push_pred_cost_based" = false; or
- alter session set "_push_join_union_view" = false; or
- use hint NO_PUSH_PRED)

I can get backport for bug 8397251 on top of 10.2.0.4 10.2.0.5
See Doc 209768.1 and there section 5.2.2

The parameter _push_join_union_view chan switch off a optimization
for queries with UNION ALL and joins, so all queries with this pattern
can be affected, the better workaound is here to switch off parallelism.

设置隐式参数_push_join_union_view为false将禁用union all和join操作的pushed predicate特性,可能会影响到系统中其他SQL的性能;MOS认为最好的workaround方式是取消查询的并行度。这个case最后通过加入NO_PARALLEL提示解决了。


Posted

in

by

Tags:

Comments

2 responses to “ORA-00600:[15570]内部错误一例”

  1. admin Avatar
    admin

    SQL statements that run in parallel with NO_PARALLEL hints
    PURPOSE
    ——-

    This document explians the common mistakes when you use
    a NO_PARALLEL/NO_PARALLEL_INDEX hints. It explains
    why a query runs in parallel even though you use a NO_PARALLEL
    hinty.

    FAQ’s about Parallel/Noparallel Hints.
    —————————————
    The existing (NO)PARALLEL hint are specified as table/index
    attributes instead of a statement attribute.
    This means we try to parallel this part of the SQL where
    we access this object.

    The examples are developed on a Oracle10g database

    WE have two tables with paralle degree 4.

    SQL> select table_name, degree
    from all_tables
    where table_name in (‘EMPLOYEES’,’DEPARTMENTS’);

    TABLE_NAME DEGREE
    —————————— ———-
    EMPLOYEES 4
    DEPARTMENTS 4

    We try to force that the query runs serial with one NO_PARALLEL hint.

    select /*+ no_parallel(e) */ e.LAST_NAME , d.DEPARTMENT_NAME
    from EMPLOYEES e , DEPARTMENTS d
    where e.DEPARTMENT_ID = d.DEPARTMENT_ID;

    Execution plan ( summary )
    ————————————————————————————-
    |Id| Operation | Name |Rows|Bytes|Cost| TQ |IN-OUT|PQ Distrib|
    ————————————————————————————-
    | 0| SELECT STATEMENT | |106 |2862 | 4| | | |
    | 1| PX COORDINATOR | | | | | | | |
    | 2| PX SEND QC (RANDOM) | :TQ10002 |106 |2862 | 4|Q1,02 | P->S |QC (RAND) |
    | 3| HASH JOIN | |106 |2862 | 4|Q1,02 | PCWP | |
    | 4| PX RECEIVE | | 27 | 432 | 1|Q1,02 | PCWP | |
    | 5| PX SEND HASH | :TQ10001 | 27 | 432 | 1|Q1,01 | P->P | HASH |
    | 6| PX BLOCK ITERATOR | | 27 | 432 | 1|Q1,01 | PCWC | |
    | 7| TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 1|Q1,01 | PCWP | |
    | 8| BUFFER SORT | | | | |Q1,02 | PCWC | |
    | 9| PX RECEIVE | |107 |1177 | 3|Q1,02 | PCWP | |
    |10| PX SEND HASH | :TQ10000 |107 |1177 | 3| | S->P | HASH |
    |11| TABLE ACCESS FULL| EMPLOYEES |107 |1177 | 3| | | |
    ————————————————————————————-

    You see that we access the table EMPLOYEES in serial.
    You have to look into the IN-out column the value s->P says
    that the table is accessed in serial.
    With one NO_PARALLEL hint we execute some parts in parallel
    and not in serial.
    That is the expected behaviour and not a bug.
    With the NO_PARALLEL hint on EMPLOYEES we only prohibit the query
    to access the table EMPLOYEES in parallel. We have not changed
    something on the table DEPARTMENTS. So this table can access
    in parallel.

    The same query with a NO_PARALLEL hint on both tables.

    select /*+ no_parallel(e) no_parallel(d) */
    e.LAST_NAME , d.DEPARTMENT_NAME
    from EMPLOYEES e , DEPARTMENTS d
    where e.DEPARTMENT_ID = d.DEPARTMENT_ID;

    Execution plan (summary )

    ——————————————————————————
    |Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
    ——————————————————————————
    | 0|SELECT STATEMENT | | 106| 2862| 6 (17)|00:00:01|
    | 1| MERGE JOIN | | 106| 2862| 6 (17)|00:00:01|
    | 2| TABLE ACCESS BY INDEX ROWID|DEPARTMENTS| 27| 432| 2 (0)|00:00:01|
    | 3| INDEX FULL SCAN |DEPT_ID_PK | 27| | 1 (0)|00:00:01|
    | 4| SORT JOIN | | 107| 1177| 4 (25)|00:00:01|
    | 5| TABLE ACCESS FULL |EMPLOYEES | 107| 1177| 3 (0)|00:00:01|
    ——————————————————————————

    We see that both tables are scaned in serial.

    The NO_PARALLEL/NO_PARALLEL_INDEX only prohitit that SQL statement
    access this object in parallel. It does not prohibit that the complete
    SQL statement runs in parallel. There is at the moment no single hint to
    switch off parallelism on statment level.
    Youi have to use NO_PARALLEL/NO_PARALLEL_INDEX for every objects which
    is involved in the query.

    You can use the commands
    1.) ALTER SESSION DISABLE PARALLEL DML;
    2.) ALTER SESSION DISABLE PARALLEL DDL;
    3.) ALTER SESSION DISABLE PARALLEL QUERY;
    to prohibit parallelism.

  2. admin Avatar
    admin

    Disable Parallel Execution on Session/System Level
    PURPOSE
    ——-

    To explain how to disable Parallel Execution on Session/System level

    SCOPE & APPLICATION
    ——————-

    DBA’s and Support Analysts

    Disable Parallel Exceution on Session Level
    ——————————————–

    To disable PX on session level you can use the
    following three commands together.

    1.) ALTER SESSION DISABLE PARALLEL DML;
    2.) ALTER SESSION DISABLE PARALLEL DDL;
    3.) ALTER SESSION DISABLE PARALLEL QUERY;

    Disable Parallel Exceution on System Level
    ——————————————

    To disable PX on datbase level you have two possiblities.

    1.) Set PARALLEL_MIN_SERVERS = 0
    PARALLEL_MAX_SERVERS = 0
    PARALLEL_AUTOMATIC_TUNING = FALSE;
    and restart the database.
    In 10g and higher versions you can change this parameter with a ALTER SYSTEM command and
    you do not have to restart the database.
    PARALLEL_AUTOMATIC_TUNING is deprecated in 10.1 and the default is FALSE.

    2.) Without restarting the database.
    Set the degree of all tables and indices to 1.
    You can use the command
    ALTER TABLE PARALLEL 1;
    ALTER INDEX PARALLEL 1,

    In the second case you can not be 100% sure that a statement runs in parallel.
    All statement which have parallel hints are still run in parallel, because
    the PARALLEL hint overrides the degree of the objects.

Leave a Reply

Your email address will not be published. Required fields are marked *