I am using oracle DB and facing problem while executing the queries. Query execution is very much slow, it takes nearly 6 mins to execute.
For solving this problem, I analyzed all the tables used in the query. But I noticed that just after analyzing when I am running the query it executes fast, but it takes time in subsequent runs.
Basically, query execution is only fast just after analyzing all the tables. I also checked the explain plan of query for both the cases and noticed that there is difference in the explain plan.
I am not understanding what is going wrong, can anyone please help me with this?
For analyzing tables I used query in below format:
BEGIN DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'USER2',
TabName => 'Name of specific tables used in this query',
Estimate_Percent => 0,
Method_Opt => 'FOR ALL COLUMNS SIZE 1',
Degree => 4,
Cascade => TRUE,
No_Invalidate => FALSE);
END;
Then for checking if tables are analyzed below query was used:
select table_name, last_analyzed from dba_tables where table_name in ('QM_TABLE_NAME1', 'ETC');
Below query was used for checking if high frequency gathering is enabled or not:
select dbms_stats.get_prefs('AUTO_TASK_STATUS') from dual;
Output of above query is:
dbms_stats.get_prefs('AUTO_TASK_STATUS')
----------------------------------------
OFF
For generating explain plan I used below queries:
select /*+ gather_plan_statistics */ ...
select * from table(dbms_xplan.display_cursor(9rc0qbr9a5h7a,0,format => 'adaptive allstats last'));
select * from table(dbms_xplan.display_cursor(9rc0qbr9a5h7a,1,format => 'adaptive allstats last'));
GATHER_PLAN_STATISTIC for query with slow execution has below note:
Note
-----
- statistics feedback used for this statement
The note statistics feedback used for this statement implies that statistics feedback was used for this query. With statistics feedback, Oracle can find statistics estimate mistakes on the first run and then improve the numbers on subsequent runs. Unfortunately, there are are rare times when improving statistics makes a query run worse. You may want to either disable this feature or try to significantly improve your initial statistics gathering to avoid bad statistics in the first place.
The least intrusive way to fix this problem is to add a hint that disables statistics feedback for your query. Without access to your precise configuration, I can only guess, but I think that one of these two hints should disable the statistics feedback:
/*+ opt_param('_optimizer_use_feedback' 'false') */
/*+ opt_param('optimizer_adaptive_statistics' 'false') */
While statistics feedback sounds like a good idea to me, apparently Oracle itself does not have much confidence in the feature, since the value still defaults to FALSE in 23c. You generally do NOT want to change a system parameter based on one bad query, but in this case you might want to consider changing OPTIMIZER_ADAPTIVE_STATISTICS back to FALSE for the entire database.
For another approach to solving this problem, you might want to consider removing the parameter Method_Opt => 'FOR ALL COLUMNS SIZE 1' from your statistics gathering call. That value effectively disables all histograms, which may be reason the statistics were bad in the first place. The default parameter value may gather useful histograms that avoid the problem entirely.
Since this problem is still happening intermittently, a different tuning technique may be called for. Ideally, we want to find the one bad feature that causes bad plans and fix it. But maybe you have multiple adaptive reoptimization features working against you. Instead of playing whack-a-mole with a few precise hints, this query might call for completely fixing the plan to a specific version.
There are multiple ways to fix an execution plan, like SQL plan baselines and SQL profiles. But since you have access to the query, one simple way is to find a complete list of SQL hints through dbms_xplan.display(format => 'outline'). See this answer for a full example.