Hi I am using version 11.2.0.4 of oracle.
We have one query which is using global temporary table and its a reporting query which can run simultaneously from multiple session. Now what is happening is this query was giving performance issue due to wrong cardinality estimation for the global temporary table and we tried putting higher degree of dynamic sampling as hint to the query, but it didn't help, so we tried gathering stats on the global temporary table just before the SELECT query run and it has fixed the issue and everything was running OK. And we were assuming there is very less possibility that any other parallel session will override the stats during(between stats gathering and preparing the explain plan for the query) that small time.
But suddenly we see , sometimes this query running for ever due to a bad plan, and its happening due to the reason, in between the STATS GATHERING and forming the explain plan for the SELECT query, some parallel session overrides the stats. So the issue comes when extreme cases occur, like some parallel session executing the report for smaller client actually holding ~100 rows in the temp table, but the current session is running for a big client, which would ideally hold ~1million records in the temp table, so ideal plan would be to full scan the transaction table( which is joined to the global temporary table), but due to stats get overridden by the smaller client, its going for indexed path and running forever. and we need to kill the query to run that again and to have the correct stats, and then it runs fine then.
So my question is as session level stats for GTT is only available on 12C and we have time to move into 12C, so how can i fix this issue? We cant really serialize the report execution and also we don't want to use baseline to force the specific execution path for the query execution as that will result into sub-optimal plan for the query. Is there any other option to handle this scenario?