Re: peeking into global temporary table from other session and some tunning

If the query is the same one being run each time then it’s only going to do a hard parse (and dynamically sample) once rather than once per execution (well it will reparse when the cursor cache is flushed or if the sql is invalidated somehow). I find it much more likely that the cardinalities you are seeing in the execution plan were very correct for the first execution, but further executions of the process were just not the same.

Are you able to know in advance what the size of the GTT will be before you call this process? It may be worth having two separate SQLs, one for the nested loop, the other for the hash join. The SQLs only need to be different enough to not share a sql_id, dynamic sampling will then kick in for the first parse of either query and you should get an accurate picture of the size of these tables and how the join is expected to behave, and therefore a decent plan for each version.

If you upgrade to 12c you could take advantage of private GTT statistics, meaning your sessions that are using the GTT can gather stats without effecting any other session. This will cause your SQL to hard parse (it must in order to take advantage of the new statistics).

How is the GTT being populated? You might actually find that using that query instead as a subquery is a better idea as the CBO may be able to apply the predicates you are using to influence it’s decision (should the predicates be trigger ACS). Additionally, better plans might be available.

Hope that helps,
Andrew

On Fri, 24 Nov 2017 at 18:41, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Could you create and share sqld360 reportfor a couple of such queries? and
> if possible trace 10053 for the execution with the data in gtt
>
> 24 нояб. 2017 г. 21:35 пользователь "GG" <grzegorzof_at_interia.pl> написал:
>
> W dniu 2017-11-24 o 18:29, Sayan Malakshinov pisze:
>>
>> Hi,
>>
>> > especially the result of dynamic sampling level 2 (default) on gtt
>> involved in joins
>>
>> Have you deleted table statistics of this gtt?
>>
>> There is no statistics there , only dynamic_sampling 2 takes place .
>>
>> G
>>
>>
>>

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