Re: Parallel and QBname

I've just rediscovered a note I wrote a few months ago that shows that the parallel() hint being applied as a patch, so it's not necessary to switch to shared()/: https://jonathanlewis.wordpress.com/2019/08/21/sql_patch/

You'll notice in the sample code that one of the parallel hints is an object-level hint, the other is a statement-level hint. (Both are commented out).

The example

Regards
Jonathan Lewis

<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sun, 17 Jan 2021 at 17:45, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Given the content of my previous reply I would experiment with associating
> a shared() hint with an object in each of the query blocks that I wanted
> Oracle to generate a parallel sub-plan for. If necessary a shared() hint
> for every object in the query block. For example (assume t1 and t2 are both
> created as "select * from all_Objects")
>
> select
> v1.object_type,
> (select
> count(*)
> from t2
> where t2.object_type = v1.object_type
> ) ct
> from
> (select distinct object_type from t1) v1
> /
>
>
> I would work out the (default, or supplied) query block name for the
> count(*) query block and introduce through a SQL patch a hint aimed at t2
> in that query block. Since the query block would be called sel$2 the hint
> I'd try would be: shared (_at_sel$2 t2_at_sel$2 2)
>
> The problem with this approach is that in some cases Oracle could decide
> that if it was going to do a parallel full tablescan on t2 then it might
> also decide that unnesting the scalar subquery into an outer hash join (for
> example) would be a really good idea, and go parallel on the rest of your
> query; so you'd have to add extra hints to make sure that this didn't
> happen (maybe no_merge(_at_sel$2) or no_unnest(_at_sel$2) ... and I should have
> included a full(_at_sel$2 t2_at_sel$2) in my example anyway).
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Sun, 17 Jan 2021 at 17:13, Moustafa Ahmed <moustafa_dba_at_hotmail.com>
> wrote:
>
>> Jonathan
>>
>> Imagine a query which has many selects in the main select plus say
>> subqueries yielding several query blocks ,and we only need to run
>> parallelism for 1 or 2 query blocks not the whole main sql, yet we can not
>> edit the sql to add hints and of course can not alter session (which will
>> make the whole thing parallel of course).
>> how can we use a specific query block name via (sql profile or sql
>> patch) to make it parallel ?
>>
>>
>> On Jan 17, 2021, at 5:52 AM, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>
>> PARALLEL() is a statement level or object level hint, not a query block
>> hint.
>>
>> We can't see your query or the definitions of any tables of views used in
>> your query so we can't tell you why you're seeing so many Unused parallel
>> hints, or why 8 is the degree of parallelism reported. I haven't seen any
>> notes anywhere explaining why the parallel() hint never seems to get into
>> the Outline Information, but possibly it's a deliberate choice aimed at
>> encouraging people to enable automatic parallelism.
>>
>> If you want to create a baseline, patch, or profile with a "parallel"
>> hint in it you'll probably have to use SHARED(...) rather than PARALLEL(...)
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
> www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#m_-3439564933507495594_m_5121669228353908447_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

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