peeking into global temporary table from other session and some tunning

From: GG <grzegorzof_at_interia.pl>
Date: Fri, 24 Nov 2017 18:04:31 +0100
Message-ID: <4221314a-3a60-a75c-ad8b-5d1eb21e854b_at_interia.pl>


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
Received on Fri Nov 24 2017 - 18:04:31 CET

Original text of this message