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

From: Mladen Gogala <>
Date: Fri, 24 Nov 2017 12:21:30 -0500
Message-ID: <>


It is not possible to peek into other session's data. I am afraid that even 12c statistics is not of much help because global temporary tables are often fluctuating wildly and any statistics can change on commit. Inserting OPTIMIZER_DYNAMIC_SAMPLING=4 is actually not that hard, you can set it on the system level.


On 11/24/2017 12:04 PM, GG wrote:
> Hello,
>  there is an app for which 90% of reporting queries suffers from
> performance degradation since couple of days .
> We've regathered stats, forced hard parse and still plans are bad,
> especially the result of dynamic sampling level 2 (default)
> on gtt involved in joins (estimated 160M vs 74k in reality (actual
> rows) ) and plan is switching from NL having this table as driving to
> HJ or even not considering it as a first step  in plans (other tables
> are 200M rows and above) .
> So is there any way we can see what data comes to GTT (defined as on
> commit preserve rows) table from other sql session ?
> The tricky part is with explain plan from other session the
> cardinality is 1 as the gtt has data only for session which populated
> it .
> My current ideas:
> - inject hint dynamic_sampling(4) into all queries involving this gtt
> (rather hard)
> - heavy hint queries , this can be risky as the data distribution is
> not always same and the query is dynamic
> Any other ideas ?
> This is EE
> Unfortunately I don't have any good plans in AWR .
> I can provide some details in terms of query if that helps .
> Regards .
> GG
> --

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Received on Fri Nov 24 2017 - 18:21:30 CET

Original text of this message