Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Core dump : launcher.sh: line 1606: 4872 Aborted

_AZ_Sep 27 2017

Hello,

since recently i started getting core dumps while using SQldeveloper ( 17.2 on Fedora 24) with TimesTen.  I'm not doing anything exotic - connect, select blah from table where simple condition. Today that seemed to be too much for SQD and it crashes right away.

[az@hp Test1]$ sqldeveloper 
 Oracle SQL Developer
 Copyright (c) 1997, 2017, Oracle and/or its affiliates. All rights reserved.
#
# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00007fea62d3d43d, pid=4872, tid=0x00007fea181d0700
#
# JRE version: Java(TM) SE Runtime Environment (8.0_144-b01) (build 1.8.0_144-b01)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.144-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# V  [libjvm.so+0x7d243d]  klassItable::initialize_itable_for_interface(int, KlassHandle, bool, Thread*)+0xdd
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# An error report file with more information is saved as:
# /tmp/hs_err_pid4872.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.java.com/bugreport/crash.jsp
#
/opt/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 1606:  4872 Aborted                 (core dumped) ${JAVA} "${APP_VM_OPTS[@]}" ${APP_ENV_VARS} -classpath ${APP_CLASSPATH} ${APP_MAIN_CLASS} "${APP_APP_OPTS[@]}"

Can't attach log file - getting  wrong content. But here are a few lines. Maybe it will be helpful:

[az@hp Test1]$ more /tmp/hs_err_pid4872.log

#
# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00007fea62d3d43d, pid=4872, tid=0x00007fea181d0700
#
# JRE version: Java(TM) SE Runtime Environment (8.0_144-b01) (build 1.8.0_144-b01)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.144-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# V  [libjvm.so+0x7d243d]  klassItable::initialize_itable_for_interface(int, KlassHandle, bool, Thread*)+0xdd
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java aga
in
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.java.com/bugreport/crash.jsp
#
---------------  T H R E A D  ---------------
Current thread (0x0000000000a41000):  JavaThread "RaptorTaskThread01-Open Connection" [_thread_in_vm, id=4911, stack(0x00007fea180d000
0,0x00007fea181d1000)]
siginfo: si_signo: 11 (SIGSEGV), si_code: 1 (SEGV_MAPERR), si_addr: 0x00007fecc9746810
Registers:
RAX=0x00000000634c8a4f, RBX=0x0000000000a408e0, RCX=0x00007fe9af101590, RDX=0x00000000634c8a50
RSP=0x00007fea181ce6c0, RBP=0x00007fea181ce810, RSI=0x0000000000a41000, RDI=0x00007fe9af0ff798
R8 =0x0000000000a41000, R9 =0x0000000000000001, R10=0x0000000000000001, R11=0x00000000000000bd
R12=0x00007fea628623a0, R13=0x0000000000000948, R14=0x0000000000000040, R15=0x0000000000a41000
RIP=0x00007fea62d3d43d, EFLAGS=0x0000000000010206, CSGSFS=0x002b000000000033, ERR=0x0000000000000004
  TRAPNO=0x000000000000000e
Top of Stack: (sp=0x00007fea181ce6c0)
0x00007fea181ce6c0:   00007fea1092a8b0 00007fea181ce700
0x00007fea181ce6d0:   000000e9181ce720 00007fe9af136cc8
0x00007fea181ce6e0:   00007fe9af146588 00007fe9af145e58
0x00007fea181ce6f0:   0000000100ba3428 00007fea181ce790
0x00007fea181ce700:   0000001400000001 0000000000000000
0x00007fea181ce710:   00007fea00000007 0000000000a41000
0x00007fea181ce720:   01007fea181ce750 00007fe9af0ff798
0x00007fea181ce730:   00007fea1092a980 00007fe9af101590
0x00007fea181ce740:   634c8a501092a8c0 0000000000a41000
0x00007fea181ce750:   00007fea181ce8b0 00007fea62d3f1a3
0x00007fea181ce760:   0000000000a41000 00007fea62e21c37
0x00007fea181ce770:   00007fea181ce7e0 00007fea181ce870

and tail:

Signal Handlers:
SIGSEGV: [libjvm.so+0xacba10], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGBUS: [libjvm.so+0xacba10], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGFPE: [libjvm.so+0x924ba0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGPIPE: [libjvm.so+0x924ba0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGXFSZ: [libjvm.so+0x924ba0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGILL: [libjvm.so+0x924ba0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGUSR1: SIG_DFL, sa_mask[0]=00000000000000000000000000000000, sa_flags=none
SIGUSR2: [libjvm.so+0x9263e0], sa_mask[0]=00000000000000000000000000000000, sa_flags=SA_RESTART|SA_SIGINFO
SIGHUP: [libjvm.so+0x9277e0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGINT: [libjvm.so+0x9277e0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGTERM: [libjvm.so+0x9277e0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
SIGQUIT: [libjvm.so+0x9277e0], sa_mask[0]=11111111011111111101111111111110, sa_flags=SA_RESTART|SA_SIGINFO
---------------  S Y S T E M  ---------------
OS:Fedora release 24 (Twenty Four)
uname:Linux 4.11.12-100.fc24.x86_64 #1 SMP Fri Jul 21 17:35:20 UTC 2017 x86_64
libc:glibc 2.23 NPTL 2.23 
rlimit: STACK 8192k, CORE 0k, NPROC 29234, NOFILE 4096, AS infinity
load average:1.86 1.22 1.19
/proc/meminfo:
MemTotal:        7671700 kB
MemFree:         3372960 kB
MemAvailable:    4465072 kB
Buffers:           75648 kB
Cached:          1299336 kB
SwapCached:            0 kB
Active:          3059816 kB
Inactive:         796688 kB
Active(anon):    2482688 kB
Inactive(anon):    98604 kB
Active(file):     577128 kB
Inactive(file):   698084 kB
Unevictable:          32 kB
Mlocked:              32 kB
SwapTotal:       8388604 kB
SwapFree:        8388604 kB
Dirty:               220 kB
Writeback:             0 kB
AnonPages:       2481504 kB
Mapped:           566076 kB
Shmem:             99928 kB
Slab:             117704 kB
SReclaimable:      68520 kB
SUnreclaim:        49184 kB
KernelStack:       10688 kB
PageTables:        29860 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    12224452 kB
Committed_AS:    5540884 kB
VmallocTotal:   34359738367 kB
VmallocUsed:           0 kB
VmallocChunk:          0 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
ShmemPmdMapped:        0 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:      187252 kB
DirectMap2M:     3430400 kB
DirectMap1G:     4194304 kB
CPU:total 4 (initial active 4) (4 cores per cpu, 1 threads per core) family 21 model 96 stepping 1, cmov, cx8, fxsr, mmx, sse, sse2, sse3, ssse3, sse4.1, sse4.2, popcnt, avx, avx2, aes, clmul, mmxext, 3dnowpref, lzcnt, sse4a, tsc, tscinvbit, tscinv, bmi1
/proc/cpuinfo:
processor    : 0
vendor_id    : AuthenticAMD
cpu family    : 21
model        : 96
model name    : AMD A10-8700P Radeon R6, 10 Compute Cores 4C+6G
stepping    : 1
microcode    : 0x6006110
cpu MHz        : 1800.000
cache size    : 1024 KB
physical id    : 0
siblings    : 4
core id        : 0
cpu cores    : 2
apicid        : 16
initial apicid    : 0
fpu        : yes
fpu_exception    : yes
cpuid level    : 13
wp        : yes
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good acc_power nopl nonstop_tsc cpuid extd_apicid aperfmperf pni pclmulqdq monitor ssse3 fma cx16 sse4_1 sse4_2 movbe popcnt aes xsave avx f16c lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs xop skinit wdt lwp fma4 tce nodeid_msr tbm topoext perfctr_core perfctr_nb bpext ptsc mwaitx cpb hw_pstate vmmcall fsgsbase bmi1 avx2 smep bmi2 xsaveopt arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean flushbyasid decodeassists pausefilter pfthreshold avic overflow_recov
bugs        : fxsave_leak sysret_ss_attrs null_seg
bogomips    : 3593.28
TLB size    : 1536 4K pages
clflush size    : 64
cache_alignment    : 64
address sizes    : 48 bits physical, 48 bits virtual
power management: ts ttp tm 100mhzsteps hwpstate cpb eff_freq_ro acc_power [13]
processor    : 1
vendor_id    : AuthenticAMD
cpu family    : 21
model        : 96
model name    : AMD A10-8700P Radeon R6, 10 Compute Cores 4C+6G
stepping    : 1
microcode    : 0x6006110
cpu MHz        : 1300.000
cache size    : 1024 KB
physical id    : 0
siblings    : 4
core id        : 1
cpu cores    : 2
apicid        : 17
initial apicid    : 1
fpu        : yes
fpu_exception    : yes
cpuid level    : 13
wp        : yes
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good acc_power nopl nonstop_tsc cpuid extd_apicid aperfmperf pni pclmulqdq monitor ssse3 fma cx16 sse4_1 sse4_2 movbe popcnt aes xsave avx f16c lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs xop skinit wdt lwp fma4 tce nodeid_msr tbm topoext perfctr_core perfctr_nb bpext ptsc mwaitx cpb hw_pstate vmmcall fsgsbase bmi1 avx2 smep bmi2 xsaveopt arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean flushbyasid decodeassists pausefilter pfthreshold avic overflow_recov
bugs        : fxsave_leak sysret_ss_attrs null_seg
bogomips    : 3588.09
TLB size    : 1536 4K pages
clflush size    : 64
cache_alignment    : 64
address sizes    : 48 bits physical, 48 bits virtual
power management: ts ttp tm 100mhzsteps hwpstate cpb eff_freq_ro acc_power [13]
processor    : 2
vendor_id    : AuthenticAMD
cpu family    : 21
model        : 96
model name    : AMD A10-8700P Radeon R6, 10 Compute Cores 4C+6G
stepping    : 1
microcode    : 0x6006110
cpu MHz        : 1300.000
cache size    : 1024 KB
physical id    : 0
siblings    : 4
core id        : 2
cpu cores    : 2
apicid        : 18
initial apicid    : 2
fpu        : yes
fpu_exception    : yes
cpuid level    : 13
wp        : yes
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good acc_power nopl nonstop_tsc cpuid extd_apicid aperfmperf pni pclmulqdq monitor ssse3 fma cx16 sse4_1 sse4_2 movbe popcnt aes xsave avx f16c lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs xop skinit wdt lwp fma4 tce nodeid_msr tbm topoext perfctr_core perfctr_nb bpext ptsc mwaitx cpb hw_pstate vmmcall fsgsbase bmi1 avx2 smep bmi2 xsaveopt arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean flushbyasid decodeassists pausefilter pfthreshold avic overflow_recov
bugs        : fxsave_leak sysret_ss_attrs null_seg
bogomips    : 3588.09
TLB size    : 1536 4K pages
clflush size    : 64
cache_alignment    : 64
address sizes    : 48 bits physical, 48 bits virtual
power management: ts ttp tm 100mhzsteps hwpstate cpb eff_freq_ro acc_power [13]
processor    : 3
vendor_id    : AuthenticAMD
cpu family    : 21
model        : 96
model name    : AMD A10-8700P Radeon R6, 10 Compute Cores 4C+6G
stepping    : 1
microcode    : 0x6006110
cpu MHz        : 1300.000
cache size    : 1024 KB
physical id    : 0
siblings    : 4
core id        : 3
cpu cores    : 2
apicid        : 19
initial apicid    : 3
fpu        : yes
fpu_exception    : yes
cpuid level    : 13
wp        : yes
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good acc_power nopl nonstop_tsc cpuid extd_apicid aperfmperf pni pclmulqdq monitor ssse3 fma cx16 sse4_1 sse4_2 movbe popcnt aes xsave avx f16c lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs xop skinit wdt lwp fma4 tce nodeid_msr tbm topoext perfctr_core perfctr_nb bpext ptsc mwaitx cpb hw_pstate vmmcall fsgsbase bmi1 avx2 smep bmi2 xsaveopt arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean flushbyasid decodeassists pausefilter pfthreshold avic overflow_recov
bugs        : fxsave_leak sysret_ss_attrs null_seg
bogomips    : 3588.09
TLB size    : 1536 4K pages
clflush size    : 64
cache_alignment    : 64
address sizes    : 48 bits physical, 48 bits virtual
power management: ts ttp tm 100mhzsteps hwpstate cpb eff_freq_ro acc_power [13]
Memory: 4k page, physical 7671700k(3372960k free), swap 8388604k(8388604k free)
vm_info: Java HotSpot(TM) 64-Bit Server VM (25.144-b01) for linux-amd64 JRE (1.8.0_144-b01), built on Jul 21 2017 21:57:33 by "java_re" with gcc 4.3.0 20080428 (Red Hat 4.3.0-8)
time: Wed Sep 27 18:54:03 2017
elapsed time: 24 seconds (0d 0h 0m 24s)

Thank you

Comments

SeánMacGC 
Sekar_BLUE4EVER wrote:
Hi I am trying to implement partitioning in a complex environment where there are two many join operations in most of the SQL's. I was reading about the partition wise join operation where join on tables will be paralleled if the keys are the same and the boundaries are same as well. 
I am trying to do a basic test case on a simple table if partition wise join works , while it works when there is no parallel operation the partition wise joins don't seem to work with parallel hint.

On what are you basing this observation, ie, why do you believe that partition wise joins are not in operation when parallelized?

Sekar_BLUE4EVER

This links shows a sample plan in case parallel partition wise joins are used . https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm And its completely of different format to what I get

SeánMacGC 
Sekar_BLUE4EVER wrote:
This links shows a sample plan in case parallel partition wise joins are used . https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm And its completely of different format to what I get

That's not reliable -- you're comparing two different versions of the optimizer to start with, and you're not comparing like with like (you don't have as much info in your explain plans), and there's nothing I can see in your parallel plan to demonstrate that partition-wise joins are not in play.

JohnWatson2

You can try hacking your statistics. For example, this gives me a parallel partition wise join:

orclx> exec dbms_stats.delete_table_stats(user,'t1h')
PL/SQL procedure successfully completed.
orclx> exec dbms_stats.delete_table_stats(user,'t2h')
PL/SQL procedure successfully completed.
orclx> alter session set optimizer_dynamic_sampling=0;
Session altered.
orclx> set autot trace exp
orclx> select /*+ parallel(2) */ count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x = t2.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 1341648614
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |          |    1 |    52 |  811  (1)| 00:00:01 |      |      |        |      |            |
|  1 |  SORT AGGREGATE          |          |    1 |    52 |            |          |      |      |        |      |            |
|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |
|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    52 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |
|  4 |    SORT AGGREGATE        |          |    1 |    52 |            |          |      |      |  Q1,00 | PCWP |            |
|  5 |      PX PARTITION HASH ALL|          |  328K|    16M|  811  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |
|*  6 |      HASH JOIN          |          |  328K|    16M|  811  (1)| 00:00:01 |      |      |  Q1,00 | PCWP |            |
|  7 |        TABLE ACCESS FULL  | T1H      |  328K|  8345K|  404  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |
|  8 |        TABLE ACCESS FULL  | T2H      |  328K|  8345K|  404  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  6 - access("T1"."X"="T2"."X")
Note
-----
  - Degree of Parallelism is 2 because of hint
orclx>
Sekar_BLUE4EVER

The plan which i get doesnt have PX PARTITION HASH ALL which means that the partition wise join is not used . Its explained in the oracle blog here https://blogs.oracle.com/datawarehousing/partition-wise-joins-ii

1 person found this helpful
SeánMacGC 
Sekar_BLUE4EVER wrote:
The plan which i get doesnt have PX PARTITION HASH ALL which means that the partition wise join is not used . Its explained in the oracle blog here https://blogs.oracle.com/datawarehousing/partition-wise-joins-ii

OK, have you now, and some more about it here: https://blogs.oracle.com/datawarehousing/partition-wise-joins

Sekar_BLUE4EVER 
SM Join
  SM cost: 116.27     (partition-wise join)

Thanks. I tried enabling 10053 trace and the optimizer doesnt even consider partition-wise-join in the trace where as without parallel hint I can clearly see that the optimizer is considering the PWJ and using it at the end. Could there be some optimizer parameters which are affecting this?

AndrewSayer

It seems to do the expected PWJ when you hint for a DOP equal to the number of partitions:

sql>explain plan for

  2  select /*+parallel(4)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x= t2.x;

Explained.

PLAN_TABLE_OUTPUT

________________________________________________________________________________________________________________________________________

Plan hash value: 1341648614

-----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT          |          |    1 |    10 |    76  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE          |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    10 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE        |          |    1 |    10 |            |          |      |      |  Q1,00 | PCWP |            |

|  5 |      PX PARTITION HASH ALL|          | 59998 |  585K|    76  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|*  6 |      HASH JOIN          |          | 59998 |  585K|    76  (0)| 00:00:01 |      |      |  Q1,00 | PCWP |            |

|  7 |        TABLE ACCESS FULL  | T1H      | 29999 |  292K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  8 |        TABLE ACCESS FULL  | T2H      | 59998 |  585K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

-----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

  1 - SEL$1

  7 - SEL$1 / T1@SEL$1

  8 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE NONE)

      USE_HASH(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

  6 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 4 because of hint

If I double that to 8, I get:

sql>explain plan for

  2  select /*+parallel(8)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x= t2.x;

Explained.

PLAN_TABLE_OUTPUT

_____________________________________________________________________________________________________________________________________________

Plan hash value: 3645041885

----------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT              |          |    1 |    10 |    38  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE                |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR              |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)        | :TQ10001 |    1 |    10 |            |          |      |      |  Q1,01 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE            |          |    1 |    10 |            |          |      |      |  Q1,01 | PCWP |            |

|*  5 |      HASH JOIN                |          | 59998 |  585K|    38  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  6 |      PART JOIN FILTER CREATE  | :BF0000  | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  7 |        PX RECEIVE              |          | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  8 |        PX SEND BROADCAST LOCAL| :TQ10000 | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,00 | P->P | BCST LOCAL |

|  9 |          PX BLOCK ITERATOR    |          | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|  10 |          TABLE ACCESS FULL    | T1H      | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  11 |      PX BLOCK ITERATOR        |          | 59998 |  585K|    19  (0)| 00:00:01 |:BF0000|:BF0000|  Q1,01 | PCWC |            |

|  12 |        TABLE ACCESS FULL      | T2H      | 59998 |  585K|    19  (0)| 00:00:01 |:BF0000|:BF0000|  Q1,01 | PCWP |            |

----------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

  1 - SEL$1

  10 - SEL$1 / T1@SEL$1

  12 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PQ_MAP(@"SEL$1" "T2"@"SEL$1")

      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" BROADCAST NONE)

      USE_HASH(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

  5 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 8 because of hint

58 rows selected.

Note the different distribution method chosen. You can hint for the initial one though:

sql>explain plan for

  2  select /*+parallel(8) PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE NONE)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x

Explained.

PLAN_TABLE_OUTPUT

________________________________________________________________________________________________________________________________________

Plan hash value: 1341648614

-----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT          |          |    1 |    10 |    38  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE          |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    10 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE        |          |    1 |    10 |            |          |      |      |  Q1,00 | PCWP |            |

|  5 |      PX PARTITION HASH ALL|          | 59998 |  585K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|*  6 |      HASH JOIN          |          | 59998 |  585K|    38  (0)| 00:00:01 |      |      |  Q1,00 | PCWP |            |

|  7 |        TABLE ACCESS FULL  | T1H      | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  8 |        TABLE ACCESS FULL  | T2H      | 59998 |  585K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

-----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

  1 - SEL$1

  7 - SEL$1 / T1@SEL$1

  8 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE NONE)

      USE_HASH(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

  6 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 8 because of hint

53 rows selected.

Sekar_BLUE4EVER

Thanks Andrew, i tried it and yes I get the same behaviour as well. But it doesnt make much sense if PWJ is only going to be used when the DOP is equal to the number of partitions. Could it be a bug ? What would be the problem in using PWJ when using DOP as 2 for tables with 4 partitions ? Couldn't it do PWJ 2 time once for P1 and P2 and the second time around for P3 and P4?

SUPRIYO DEY

without using hint even you can achieve the same. But you need to enable parallel in table level. Which i don't find in your case.

please look at the demo:-

SQL> alter table t1h parallel;

Table altered.

SQL>  alter table t2h parallel;

Table altered.

SQL> select count(t1.y), count(t2.y)    from t1h t1,t2h t2     where t1.x= t2.x;

Execution Plan

----------------------------------------------------------

Plan hash value: 593443938

--------------------------------------------------------------------------------

---------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time

     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

---------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    52 |    29   (0)| 00:0

0:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    52 |            |

     |       |       |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |

     |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |    52 |            |

     |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    52 |            |

     |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|          | 26732 |  1357K|    29   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |          | 26732 |  1357K|    29   (0)| 00:0

0:01 |       |       |  Q1,00 | PCWP |            |

|   7 |        TABLE ACCESS FULL  | T2H      | 26731 |   678K|    14   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | T1H      | 26833 |   681K|    14   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

---------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("T1"."X"="T2"."X")

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         57  recursive calls

          0  db block gets

        514  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         11  sorts (memory)

          0  sorts (disk)

          1  rows processed

in this case automatic degree of parallelism is used by Oracle.

even if you go for parallel hint of DOP 2 still it happen

SQL> select /*+ parallel(2) */ count(t1.y), count(t2.y)    from t1h t1,t2h t2     where t1.x = t2.x;

Execution Plan

----------------------------------------------------------

Plan hash value: 593443938

--------------------------------------------------------------------------------

---------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time

     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

---------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    52 |    57   (0)| 00:0

0:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    52 |            |

     |       |       |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |

     |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |    52 |            |

     |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    52 |            |

     |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|          | 26732 |  1357K|    57   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |          | 26732 |  1357K|    57   (0)| 00:0

0:01 |       |       |  Q1,00 | PCWP |            |

|   7 |        TABLE ACCESS FULL  | T2H      | 26731 |   678K|    29   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | T1H      | 26833 |   681K|    28   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

---------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("T1"."X"="T2"."X")

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Degree of Parallelism is 2 because of hint

Statistics

----------------------------------------------------------

         47  recursive calls

          0  db block gets

        514  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         11  sorts (memory)

          0  sorts (disk)

          1  rows processed

From the DOC its mentioned that

https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#CIHCDBIF

Considerations for full partition-wise joins also apply to partial partition-wise joins:

  • The degree of parallelism does not need to equal the number of partitions. In Figure 4-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.
Sekar_BLUE4EVER

Strange enough I replicated the exact same steps as you have given but it doesnt work for me . I tried enabling parallel 2 for the tables and the result is the same (PWJ not used). The only case when PWJ was used was when I set parallel for the tables to same as number of partitions. I am  testing it in version 11.2.0.3 and 11.2.0.1  and the result is the same. Practically it wouldnt be possible to have the same number of parallel processes as number of partitions. Can you tell me the version you tested on?

SUPRIYO DEY

11.2.0.4

as per doc

Considerations for full partition-wise joins also apply to partial partition-wise joins:

  • The degree of parallelism does not need to equal the number of partitions. In Figure 4-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.
Mohamed Houri 

Sekar_BLUE4EVER wrote:
Thanks Andrew, i tried it and yes I get the same behaviour as well. But it doesnt make much sense if PWJ is only going to be used when the DOP is equal to the number of partitions. Could it be a bug ? What would be the problem in using PWJ when using DOP as 2 for tables with 4 partitions ? Couldn't it do PWJ 2 time once for P1 and P2 and the second time around for P3 and P4?

You know that "partition-wise" join has been used when you see that the partition operation is above the join operation.

But one important requirement for the partition-wise join to occur is that it requires from Oracle to read the partitioned object via Partition granule i.e. you see PX PARTITION in the execution plan. If a Block granule is used (PX BLOCK ITERATOR) instead of Partition granule (PX PARTITION) then partition wise join will not kick in.

Best regards

Mohamed Houri

1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2017
Added on Sep 27 2017
#sql-developer
0 comments
411 views