Re: Is Parallelism happening at INSERT level?

From: Ram Raman <veeeraman_at_gmail.com>
Date: Wed, 6 May 2020 17:01:59 -0500
Message-ID: <CAHSa0M1jBU4S+zeTkWchBeMYshX6v=XUki4+E_54_qMnhNESUA_at_mail.gmail.com>

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
>>
>>

--

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

Received on Thu May 07 2020 - 00:01:59 CEST

This message: [ Message body ]
Next message: Chinar Aliyev: "Re: Is Parallelism happening at INSERT level?"
Previous message: Arjen Visser: "Re: DR Solution without Standby query"
In reply to: Jonathan Lewis: "Re: Is Parallelism happening at INSERT level?"
In reply to Jonathan Lewis: "Re: Is Parallelism happening at INSERT level?"
Next in thread: Chinar Aliyev: "Re: Is Parallelism happening at INSERT level?"
Reply: Chinar Aliyev: "Re: Is Parallelism happening at INSERT level?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message