Re: Odd restrictions on Temp table with indexes

Thank you so much Jonathan.

Actually I replied on the other thread which started originally with two of the queries(INSERT and SELECT) not using parallel processing. Also I am wondering if it's possible to have any alternate solution to make the read/write happen in parallel in presence of index in a global temporary table. As because it may not always be feasible and optimal to modify a normal query to a UNION query to make this processing happen in parallel for a GTT.

On Sat, Jul 1, 2023 at 2:42 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> The hint in the main query should use the query block name set$1, not
> select$1. (And the parallel(10) hints in the two subqueries are redundant).
> Here's an execution plan from an SQL monitor report that shows the plan
> (after a little hacking of your script to get some data into the table) and
> indications that parallelism has happened:
>
> Global Stats
> ===============================================================
> | Elapsed | Cpu | Concurrency | Other | Fetch | Buffer |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets |
> ===============================================================
> | 0.42 | 0.02 | 0.00 | 0.40 | 1 | 5 |
> ===============================================================
>
> Parallel Execution Details (DOP=10 , Servers Allocated=10)
>
> ========================================================================================================
> | Name | Type | Server# | Elapsed | Cpu | Concurrency |
> Other | Buffer | Wait Events |
> | | | | Time(s) | Time(s) | Waits(s) |
> Waits(s) | Gets | (sample #) |
>
> ========================================================================================================
> | PX Coordinator | QC | | 0.41 | 0.01 | |
> 0.40 | | |
> | p000 | Set 1 | 1 | 0.00 | 0.00 | |
> | | |
> | p001 | Set 1 | 2 | 0.00 | 0.00 | |
> | | |
> | p002 | Set 1 | 3 | 0.00 | 0.00 | |
> | | |
> | p003 | Set 1 | 4 | 0.00 | 0.00 | |
> | 3 | |
> | p004 | Set 1 | 5 | 0.00 | 0.00 | 0.00 |
> | 2 | |
> | p005 | Set 1 | 6 | 0.00 | 0.00 | |
> | | |
> | p006 | Set 1 | 7 | 0.00 | 0.00 | |
> | | |
> | p007 | Set 1 | 8 | 0.00 | 0.00 | |
> | | |
> | p008 | Set 1 | 9 | 0.00 | 0.00 | |
> | | |
> | p009 | Set 1 | 10 | 0.00 | 0.00 | |
> | | |
>
> ========================================================================================================
>
> SQL Plan Monitoring Details (Plan Hash Value=3611596291)
>
> ====================================================================================================================================================
> | Id | Operation | Name | Rows |
> Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
> | | | | (Estim) |
> | Active(s) | Active | | (Actual) | (%) | (# samples) |
>
> ====================================================================================================================================================
> | 0 | SELECT STATEMENT | | |
> | | | 11 | | | |
> | 1 | PX COORDINATOR | | |
> | | | 11 | | | |
> | 2 | PX SEND QC (RANDOM) | :TQ10000 | 2 |
> 4 | | | 10 | | | |
> | 3 | VIEW | | 2 |
> 4 | | | 10 | | | |
> | 4 | UNION-ALL | | |
> | | | 10 | | | |
> | 5 | PX SELECTOR | | |
> | | | 10 | | | |
> | 6 | TABLE ACCESS BY INDEX ROWID BATCHED | X | 1 |
> 2 | | | 1 | | | |
> | 7 | INDEX RANGE SCAN | INDX11 | 1 |
> 1 | | | 1 | | | |
> | 8 | PX SELECTOR | | |
> | | | 10 | | | |
> | 9 | TABLE ACCESS BY INDEX ROWID BATCHED | X | 1 |
> 2 | | | 1 | | | |
> | 10 | INDEX RANGE SCAN | INDX11 | 1 |
> 1 | | | 1 | | | |
>
> ====================================================================================================================================================
>
>
> You'll notice that the parallel execution details show that 2 out of the
> 10 PX processes have visited some buffers, and that the plan shows PX
> SELECTOR operations.
>
> Each index scan is a serial index scan, but I believe the optimizer has
> selected a separate PX process for each of the range scans, so that two
> range scans can run concurrently. I'd want to do a variety of experiments
> with larger sets of data and varying degrees of parallelism and branches of
> UNION ALL before making any confident claims for the exact mechanisms used,
> though.
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Sat, 1 Jul 2023 at 05:03, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Thank you so much Stefan.
>> I see the GTT index behaviour is expected and there the workaround
>> mentioned is converting it into a Union query. But it looks like it will
>> have additional index scans if it does that and that will have additional
>> overhead. However I did try to convert the sample select query into UNION
>> ALL but not seeing a parallel index scan happening in the plan. Not sure
>> what is missing. (
>> https://gist.github.com/oracle9999/5409f86ebd155fd103d90c30282309fe)
>>
>> However , I was thinking if there is any other way out, without adding
>> more overhead we can parallelize these queries. Making the table a normal
>> heap help table will help however, will not help in the current scenario
>> where it is populated/accessed simultaneously from multiple sessions for
>> multiple customers. Is there any other way possible here?
>>
>> Below are the two queries we were looking to parallelize one is INSERT
>> and the other one is SELECT.
>>
>> https://gist.github.com/oracle9999/ff2073c222398416c8095d109c233765
>> https://gist.github.com/oracle9999/618251c1e48b315dc70c73e157443773
>>
>> On Fri, Jun 30, 2023 at 6:21 PM Stefan Koehler <contact_at_soocs.de> wrote:
>>
>>> Hello Pap,
>>> I guess this blog post describes everything you need to know (and how to
>>> possibly work around).
>>>
>>>
>>> https://iusoltsev-wordpress-com.translate.goog/2019/07/10/index-on-temp-table-is-not-parallelized/?_x_tr_sl=auto&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=wapp
>>>
>>>
>>> Best Regards
>>> Stefan Koehler
>>>
>>> Independent Oracle performance consultant and researcher
>>> Website: http://www.soocs.de
>>> Twitter: _at_OracleSK
>>>
>>> > Pap <oracle.developer35_at_gmail.com> hat am 30.06.2023 13:14 CEST
>>> geschrieben:
>>> >
>>> >
>>> > Hello Listers,
>>> > There was another thread in which i found this restriction on global
>>> temporary tables. Wanted to have this put separately so as to make it bit
>>> clearer. And also to ensure, i am not assuming things wrong here. As this
>>> behaviour is little odd and i never heard of or read anywhere.So wanted to
>>> check with you all if this behaviour is correct that the Index access in
>>> Global temporary table can not be parallelized either for read or write
>>> operation? And if any workaround possible in such scenarios to make the
>>> operation run in parallel?
>>> > And also we have a customer application in which global temporary
>>> tables are used extensively and indexes are used to query those. I agree
>>> that in an ideal scenario we should not scan GTTs using indexes. However as
>>> i mentioned as part of the current design we have common procedures gets
>>> executed for different customers at same time from different sessions and
>>> populate same GTT for each of them. And then data is being fetched by each
>>> of the customer from the GTT then. And for some the index access is what is
>>> optimal for querying and grouping in certain way forrepresentationpurpose.
>>> > Below is the test case which i have created for both Normal heap table
>>> VS global temporary table. It shows the index access cant be parallelized
>>> while reading the GTT. And also you cant write on the GTT if it has the
>>> indexes present in it.
>>> > https://gist.github.com/oracle9999/909b1477a321cd2b4e49eb81327687eb
>>> > Regards
>>>
>>

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