Re: In-Memory Parallel Execution (11.2)

well, what you potentially observe could also be related to the execution plan shape used for PX. The "In Memory Parallel Execution" feature is about caching *Full Table Scans*, but you could also end up with a PX execution plan that drives a parallel Nested Loop join by a parallel full table scan. The inner row source then typically is some index driven access path, and this part will go via the buffer cache, no matter if you use "In Memory Parallel Execution" or not.

So in such cases this Nested Loop inner row source might potentially benefit a lot from the buffer cache at subsequent executions and could explain what you describe even without "In Memory Parallel Execution".

Another possibility is of course some caching outside Oracle, so some file system / SAN cache related caching that makes full table scans much faster while Oracle still records this as direct physical I/O from database perspective.

Randolf

Am 06.04.2015 um 11:28 schrieb Chitale, Hemant K:
>
> Thanks.
>
> I have the feeling that in an environment with (a) CACHE or KEEP *not* configured for the target table and (b) parallel_degree_policy still at MANUAL, re-executing a PQ is extremely fast. I suspect[ed] In-Memory Parallel Execution.
>
> Unfortunately, I don't have access to trace files.
>
> I'll have to test further and see if I can get session statistics isolated (yes, it's a shared environment). Unfortunately, v$service_stats isn't helpful enough.
>
>
> Hemant K Chitale
>
>
> -----Original Message-----
> From: Randolf Geist [mailto:randolf.geist_at_googlemail.com] On Behalf Of Randolf Geist
> Sent: Friday, April 03, 2015 9:03 PM
> To: Chitale, Hemant K
> Cc: oracle-l_at_freelists.org
> Subject: Re: In-Memory Parallel Execution (11.2)
>
> Hi Hemant,
>
> most of your questions were already addressed, just a few more comments.
>
> I can confirm that In-Memory Parallel Execution (not to be confused with the In-Memory Column Store) works in a non-RAC environment.
>
> And if objects are marked as CACHE then, as Tanel points out, the code path can also be used. However, there is a significant difference between MANUAL+CACHE and PARALEL_DEGREE_POLICY=AUTO when it comes to RAC: In the former case the intelligent distribution / placement of the segment across the n buffer caches doesn't happen and each instance buffer cache will potentially hold copies of the same blocks, whereas with AUTO the PX servers are aware of the segment distribution across the different buffer caches, so you effectively can cache larger segments than with MANUAL+CACHE.
>
> PARALLEL_FORCE_LOCAL shouldn't make a difference in principle, but see the previous paragraph - you won't be able to benefit from the segment distribution across several buffer caches then.
>
> A simple way to determine if In-Memory Parallel Execution got used or not is check the session statistics - if you make use of PX and you don't see "consistent gets direct" but only "consistent gets from cache" then it got used, assuming that all of the objects accessed are below the threshold and fit into the buffer cache.
>
> If you don't mark objects as CACHE and don't use AUTO, then you shouldn't see In-Memory Parallel Execution - the corresponding underscore parameter "_parallel_cluster_cache_policy" doesn't have a setting as far I know that completely disables the feature, although you could set it to "ADAPTIVE" when using AUTO to prevent the feature usage, but it doesn't prevent the feature usage when explicitly marking objects as CACHE.
>
> Randolf
>
>> In-Memory Parallel Execution in 11.2 Questions :
>> 1. Does In-Memory Parallel Execution work in a non-RAC environment ?
>> 2. Does it HAVE to have PARALLEL_DEGREE_POLICY='AUTO' or does it work with MANUAL ?
>> 3. In RAC does it still work if PARALLEL_FORCE_LOCAL is TRUE ?
>> 4. How do you determine if In-Memory Parallel Execution has been used ?
>> 5. Is there another switch (other than PARALLEL_DEGREE_POLICY) to disable it ?
>
> This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
>

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