We have a database who has recently moved to the Cloud, and started having performance issues with some queries. I've been trying to detect possible bottlenecks at database level and troubleshoot performance, by actively monitoring those queries executions, generating and analyzing AWR reports, testing alternative plans for the Optimizer to choose, but even after running Toad SQL Optimizer, no better execution plan was found.
We still have the old database up and running for testing purposes, and the same queries, with the same exactly execution plan, takes less than a minute to run, while production environment in the cloud takes 42 minutes to finish. Session aren't hanging, as it is possible to check through V$session, v$sql and other sources.
Here's the big deal:
After a scrutiny comparison between the two databases, apart from the platform each one is running, the only difference I could find was, these three parameters exist in old production server, and are not supported in RDS:
_complex_view_merging
_gby_hash_aggregation_enabled
_optimizer_push_pred_cost_based
RDS Support informed that the informed parameters are not supported for Oracle running on cloud.
My main questions are, assuming this differences can be the root cause:
How can I test the query against those parameters, to check whether the query is using or not on those parameters. Just like we can "alter index index_name monitoring usage"... to check if an index is being used by a specific query.
Note: The suggestion of: try to disable the parameters in the test environment and run the query can't be done at this time, since the instance serves a lot of schemas that Dev team uses. So any other recommendations will be appreciated.
Thanks in advance.