You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the overhead in statistics collection. Identify a solution to this problem.
You observe that suboptimal execution plans for the queries are being generated on a table that previously used less resources. You have collected statistics on these tables two days ago. The optimizer statistics retention period is set to 31 days. You are able to find the timestamp information about statistics update from the DBA_TAB_STATS_HISTORY view. Because it is a frequently queried table, you would like the optimizer to generate better plans.Which action would enable you to use the previous set of statistics on the objects that may lead to better execution plans?
The SALES table in your database undergoes frequent changes. While observing the statistics for the table in the middle of the day, you find a change since the last observation. But the information about the statistics collection is not recorded in the DBA_TAB_STATS_HISTORY. Identify the reason for this.
You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command at regular intervals:SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'AUTO');You need statistics to be collected more quickly. How do you achieve this?
Which is the correct description of SQL profiling while using SQL Tuning Advisor?
After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)
