Re: Parallelism

Are you expecting parallel DML too? A quick explain plan will tell you most of the information you need here. My guess is that you’re only using parallel query and that’s being serialised inserting into the table. Of course, your append hint is requiring an exclusive lock on your table so there’s always a chance you’re just waiting forever for something else.

V$px_session and the usual v$ views are what you should be looking at when asking what the parallel slaves are doing (if there are any).

Hope that helps,
Andy

On Mon, 26 Aug 2019 at 19:27, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> I have an old Oracle 11.2.0.4 instance into which I have to load some data:
>
> SQL> insert /*+ APPEND */ into LOG select /*+ PARALLEL(8) */ * from LOG6;
>
>
> What I don't understand is that parallelism is practically non-existing:
>
> op - 14:19:11 up 20 days, 23:54, 5 users, load average: 1.14, 0.92, 0.77
> Tasks: 680 total, 3 running, 474 sleeping, 0 stopped, 0 zombie
> %Cpu(s): 2.7 us, 0.5 sy, 0.0 ni, 96.3 id, 0.4 wa, 0.0 hi, 0.2 si,
> 0.0 st
> KiB Mem : 65613180 total, 6174376 free, 39190992 used, 20247812 buff/cache
> KiB Swap: 29241340 total, 29241340 free, 0 used. 23073784 avail Mem
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
> COMMAND
> 7786 oracle 20 0 1408616 405472 61564 R 84.8 0.6 3:08.25 oracle
> 6500 oracle 20 0 1086016 61332 54420 S 1.7 0.1 1:11.46 oracle
> 6510 oracle 20 0 1086016 65652 58632 S 1.7 0.1 1:10.09 oracle
> 6512 oracle 20 0 1070028 53456 47772 R 1.7 0.1 0:12.96 oracle
> 6514 oracle 20 0 1070028 53928 48040 S 1.7 0.1 0:12.73 oracle
> 6502 oracle 20 0 1086076 61124 54168 S 1.3 0.1 1:12.68 oracle
>
> I thought that there will be 8 processes spending significant amount of
> CPU resources. The process burning CPU is doing no IO:
>
> [root_at_carpo ~]# strace -e trace=file -p 7786
> strace: Process 7786 attached
> strace: [ Process PID=7786 runs in x32 mode. ]
> strace: [ Process PID=7786 runs in 64 bit mode. ]
>
> That is probably to be expected because processes do not burn CPU while
> doing IO. What exactly is that process doing?
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l