Hi all,
The execution plan of complex view was updated due to the TABLE (which used in this view) was updated the statistic by checking the LAST_ANALYZED from DBA_TAB_STATISTICS. And the performance of query this view was degraded after the table statistics updated automatically.
In addition, there haven't any scheduled job to gather the table statistics on this table. May I know how to trigger to update the value of "LAST_ANALYZED" of this table, if there haven't batch insert/delete the records in this table? Will it be auto gather table statistics once reached on the threshold during in database maintenance window?
Also, I found that if only to gather the table statistic it will not affected the execution plan of this view, if performed to gather table statistics by schema, it will updated the execution plan of complex view. And the query performance was improved after gather schema table statisticts.
And how to change the execution plan to fixed plan?
Thanks in advance.
Regards,
Jennifer