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

Hi!

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.

Regards

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 11.2.0.3 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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

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