Sometime due to change in data volume or High volatility of data in table sub-optimal plan may be picked and to correct that OLD plan may be used for that query,
1=> Restoring Optimizer Statistics You can use DBMS_STATS to restore old versions of statistics that are stored in the data dictionary. Sometime we can restore OLD stats jus to compare and check the execution differences. Restrictions for Restoring Optimizer Statistics When restoring previous versions of statistics, various limitations apply. Restrictions include the following: DBMS_STATS.RESTORE_*_STATS procedures cannot restore user-defined statistics. Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics. Dropping a table removes the workload data used by the automatic histogram feature and the statistics history used by DBMS_STATS.RESTORE_*_STATS. Without this data, these features do not work properly. Therefore, to remove all rows from a table and repopulate it, Oracle recommends using TRUNCATE instead of dropping and re-creating the table. If a table resides in the recycle bin, then flashing back the table also retrieves the statistics. Procedures to restore Statistics RESTORE_DICTIONARY_STATS RESTORE_FIXED_OBJECTS_STATS RESTORE_SCHEMA_STATS RESTORE_SYSTEM_STATS RESTORE_TABLE_STATS Views Helpful in Restoring Statistics Dictionary views display the time of statistics modifications. You can use the following views to determine the time stamp to be use for the restore operation: The DBA_OPTSTAT_OPERATIONS view contains history of statistics operations performed at schema and database level using DBMS_STATS The DBA_TAB_STATS_HISTORY views contain a history of table statistics modifications. Demo Let we restore OLD statistics for a table which is not performing up to the mark. Current status of table statistics as below. SQL> SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='EMP_DATA'; TABLE_NAME LAST_ANALYZED NUM_ROWS ---------- -------------------- ---------- EMP_DATA 28-MAY-2022 23:31:07 282 SQL> SELECT COUNT(*) FROM EMP_DATA; COUNT(*) ---------- 282 SQL> SELECT INDEX_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='IDX_EMPD'; INDEX_NAME LAST_ANALYZED NUM_ROWS ------------------------------------------------------------------------------------------------------- IDX_EMPD 28-MAY-2022 23:31:07 282 Now let we check available stats for that table as below. COL TABLE_NAME FORMAT a10 SELECT TABLE_NAME, TO_CHAR(STATS_UPDATE_TIME,'YYYY-MM-DD:HH24:MI:SS') AS STATS_MOD_TIME FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='EMP_DATA' AND OWNER='ABHI_TEST' ORDER BY STATS_UPDATE_TIME DESC;SQL> SQL> 2 3 4 5 6 TABLE_NAME STATS_MOD_TIME ---------- ------------------- EMP_DATA 2022-05-28:23:31:07 EMP_DATA 2022-05-28:23:27:20 EMP_DATA 2022-05-28:23:26:59 EMP_DATA 2022-05-28:23:25:44 EMP_DATA 2022-05-28:23:24:08 EMP_DATA 2022-05-28:23:23:07 EMP_DATA 2022-05-28:17:03:04 EMP_DATA 2022-05-28:17:01:43 EMP_DATA 2022-05-28:16:59:42 EMP_DATA 2022-05-28:16:58:44 EMP_DATA 2022-05-28:16:57:02 EMP_DATA 2022-05-28:16:22:49 EMP_DATA 2022-05-28:16:21:57 EMP_DATA 2022-05-28:16:09:23 EMP_DATA 2022-05-28:16:08:37 15 rows selected. As we can see we are also having stats on 2022-05-28:16:08:37.Let we change current stats to OLD one and check count and Histogram details as below. SQL> BEGIN DBMS_STATS.RESTORE_TABLE_STATS( 'ABHI_TEST','EMP_DATA', TO_TIMESTAMP('2022-05-28:16:08:37','YYYY-MM-DD:HH24:MI:SS') ); END; / PL/SQL procedure successfully completed. Now let we check NUM_ROWS and HISTOGRAM status for that table. SQL> SELECT TABLE_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='EMP_DATA'; TABLE_NAME LAST_ANALYZED NUM_ROWS ------------------------------ -------------------- ---------- EMP_DATA 11-SEP-2021 01:41:51 1300 SQL> SQL> SQL> SELECT INDEX_NAME,LAST_ANALYZED,NUM_ROWS FROM DBA_IND_STATISTICS WHERE INDEX_NAME='IDX_EMPD'; INDEX_NAME LAST_ANALYZED NUM_ROWS ------------------------------------------------- IDX_EMPD 04-JAN-2022 12:13:09 1300 SQL> SELECT TABLE_NAME,COLUMN_NAME,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='EMP_DATA'; TABLE_NAME COLUMN_NAME HISTOGRAM ------------------------------ ----------------------------- EMP_DATA ID NONE EMP_DATA GENDER FREQUENCY SQL> SELECT COUNT(*) FROM EMP_DATA; COUNT(*) ---------- 282 SQL> SO as we can see Table statistics got changed. 2=> Optimizer Statistics Retention Period You can configure the retention period using the DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. The default is 31 days. After which time the statistics are scheduled for purging. Obtaining Optimizer Statistics History You can use the following procedure to obtain information about the optimizer statistics history: GET_STATS_HISTORY_RETENTION -This function can retrieve the current statistics history retention value. GET_STATS_HISTORY_AVAILABILITY - This function retrieves the oldest time stamp when statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp. Changing the Optimizer Statistics Retention Period SQL> SET SERVEROUTPUT ON SQL> DECLARE v_stats_retn NUMBER; v_stats_date DATE; BEGIN v_stats_retn := DBMS_STATS.GET_STATS_HISTORY_RETENTION; DBMS_OUTPUT.PUT_LINE('The retention setting is ' || v_stats_retn || '.'); v_stats_date := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY; DBMS_OUTPUT.PUT_LINE('Earliest restore date is ' || v_stats_date || '.'); END; /SQL> 2 3 4 5 6 7 8 9 10 11 12 The retention setting is 31. Earliest restore date is 27-APRIL-2022 23:25:52. PL/SQL procedure successfully completed. SQL> You can configure the retention period using theDBMS_STATS.ALTER_STATS_HISTORY_RETENTION
procedure. The default is 31 days. Here we are changing Retentions from 31 days to 80 days .We can do as below. SQL> EXECUTE set_opt_stats_retention(80); PL/SQL procedure successfully completed. SQL> 3=> Reporting on Past Statistics Gathering Operations We can use below Functions to report OLD stats details. REPORT_STATS_OPERATIONS REPORT_SINGLE_STATS_OPERATION Using REPORT_STATS_OPERATIONS to view all operations done for statistics related, ====================================================================== col myRptFile new_value myRptFile noprint select 'dbms_stats_rpt_' || to_char(sysdate,'yyyymmdd_hh24miss') || '.html' myRptFile from dual; var myRpt clob begin :myRpt := dbms_stats.report_stats_operations ( since => systimestamp -1, until => systimestamp, detail_level => 'TYPICAL', format => 'HTML' ); end; / set long 2000000 spool &myRptFile print myRpt spool offGenerates a report of the specified operation. REPORT_SINGLE_STATS_OPERATION col myRptFile new_value myRptFile noprint select 'dbms_stats_rpt_' || to_char(sysdate,'yyyymmdd_hh24miss') || '.html' myRptFile from dual; var myRpt clob begin :myRpt:=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION ( OPID => 889 , FORMAT => 'HTML' ); end; / set long 2000000 spool &myRptFile print myRpt spool off ======
![]()
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444