Re: Is Parallelism happening at INSERT level?

Hi,

_at_Jonathan, I think each slave got 1MB(1131K) data not 10GB. Because "build table" size is 1131K (estimated) and 10GB is the result of the join.

_at_Ram, if most time of the SQL execution is consumed by "DIRECT READ/WRITE" from/to temp it means there could be wrong cardinality estimation(miss) for efgh table. Becuse hash table (based on efgh) could have been stored in PGA completely. Could you display(or gather stats) real number of blocks and rows of that table?.
Also, it looks the value of "optimizer_dynamic_sampling" params is 11 (auto) - from note section of the plan. In general, if parallel execution is used the database defines the level of sampling exactly (like 4,6), the auto sampling mechanism is different. What is the actual value of "optimizer_dynamic_sampling" in your system?

Best Regards

On Thu, May 7, 2020 at 2:03 AM Ram Raman <veeeraman_at_gmail.com> wrote:

>
> Thank you all.
>
> * Thanks Lothar for the explanation. Now i know what P->S signifies; next
> time I see a query with P->S I know there is no parallel operations
> happening
> 'above' it in the plan
>
> * Thanks a lot Jonathan. I saw only 4 slaves in operation in pxsession. I
> now have to read up on PQ_REPLICATE. And no, our system definitely cannot
> read
> 1.25Gb/sec. We are choking on IO. Many of the direct path reads are to
> temp also.
>
> * I got this query from dbms_xplan on a running query. Surprised it did
> not give the actual execution plan; had the statistics_level all set in the
> session.
> We are licensed for SQL_MONITOR. will try that in the next run.
>
> * Thanks for the tip Mark F: It is good to know that I have another option
> of splitting a table, efgh in this case, into four or more depending on the
> parallelism we
> want to use. Will keep that in mind as well for the next tuning
> opportunity, esp if we get a table with large number rows and is either not
> partitioned or not
> partitioned by the join column - is that reasoning correct? Sounds like a
> great tip.
>
> I should have added the information about the tables as well, but here
> they are:
>
> TABLE_NAME NUM_ROWS LAST_ANAL BLOCKS
> ------------------------ -------------------- --------- ----------
> efgh 193,031 30-APR-20 464
> abcd 0 06-MAY-20 0
> SCE 1,547,210,780 14-MAY-19 37444971
>
> All the 193,000 rows in efgh (has only 2 number columns) are unique with
> respect to esk, but does not have an index on it. Would an index help given
> that it has to operate on all the data in efgh? I doubt it since it has to
> grab all the rows.
>
> * Bloom filters: More reading I guess. Is it explained in the oracle
> manual?
>
> Ram.
>
> On Wed, May 6, 2020 at 12:30 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Until 19c a query that uses a Bloom filter will "lose" the Bloom filter
>> when it's changed to "insert as select".
>> However CTAS does work, and there is a patch, that allows insert /*+
>> append */ as select to use a Bloom filter.
>>
>> See blog and comments:
>> https://jonathanlewis.wordpress.com/2016/07/08/dml-and-bloom/
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>> On Wed, May 6, 2020 at 4:04 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>>
>>> Hi,
>>>
>>> I wonder if a Bloom Filter could be used on sce. Would that be faster
>>> than probing? I would think so ...
>>> There seems to be many rows not surviving the join.
>>>
>>> Regards
>>>
>>> Lothar
>>>
>>>
>
> --
>
>
>

-- 
*Chinar Aliyev*


Visit My         :Blog <http://chinaraliyev.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <https://www.linkedin.com/in/chinaraliyev/>*

My Twitter <https://twitter.com/MohamedHouri>      - ChinarAliyev
<https://twitter.com/ChinarAliyev>

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