Re: Parallel and QBname
Date: Sun, 17 Jan 2021 17:45:40 +0000
Message-ID: <CAGtsp8nz=mBqULjdZG39YRBxfAT3vk=Tbr+98qK8n8VODGyG=g_at_mail.gmail.com>
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
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
On Sun, 17 Jan 2021 at 17:13, Moustafa Ahmed <moustafa_dba_at_hotmail.com>
wrote:
> Jonathan
v1.object_type,
(select
count(*)
from t2
where t2.object_type = v1.object_type
) ct
from
(select distinct object_type from t1) v1
/
Jonathan Lewis
>
> 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_5121669228353908447_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 17 2021 - 18:45:40 CET