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