Re: Parallel and QBname

From: Moustafa Ahmed <moustafa_dba_at_hotmail.com>
Date: Sun, 17 Jan 2021 17:13:08 +0000
Message-ID: <BN6PR06MB27852EBA896835B7AC7DE4C0E1A50_at_BN6PR06MB2785.namprd06.prod.outlook.com>

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

[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png]<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>

On Sat, 16 Jan 2021 at 18:09, Moustafa Ahmed <moustafa_dba_at_hotmail.com<mailto:moustafa_dba_at_hotmail.com>> wrote: Hello folks

Running dbms_xplan.display_cursor with advanced options on a sql running with parallel slaves I can see as you know “Outline Data” and QBnames..

1-I know the sql is running in parallel.
2-I see “PQ_DISTRIBUTE_WINDOW”, “PQ_DISTRIBUTE” in outline data.
3-no related parallel variables in “OPT_PARAM”.
4-hint report part shows

Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 6 (U - Unused (5))

---

   0 - STATEMENT

         U -  PARALLEL(8) / duplicate hint
         U -  PARALLEL(8) / duplicate hint
         U -  PARALLEL(8) / duplicate hint
         U -  PARALLEL(8) / hint overridden by another in parent query block
         U -  PARALLEL(8) / hint overridden by another in parent query block
           -  PARALLEL(8)



1-Why I can not see a “Query Block” with the parralele hint and its degree?

2-what should I do If want to inject the same parralel hint with same degree on same qb?

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

Received on Sun Jan 17 2021 - 18:13:08 CET