How to generate execution plan created from AWR snapshot?

Using $ORACLE_HOME/rdbms/admin/awrsqrpt.sql one can generate execution plan from AWR by passing start snap id, end snap id and SQL ID. It will ask for most of the same inputs like awrrpt.sql, type of report (html/text), start snap id, end snap id, SQL id and report name and create a output in the file specified. Example:…

How to schedule a snap for STATSPACK?

One can automate taking of snapshots of STATSPACK by executing DBMS_JOB or by scheduling it through crontab. Schedule automatic STATSPACK through DBMS_JOB – By executing @?/rdbms/admin/spauto.sql it will schedule to run statspack once every hour which is the default. Output: SQL> connect perstat@TESTDB Password: ********** SQL> @?/rdbms/admin/spauto.sql PL/SQL procedure successfully completed. Job number for automated…

How to delete or truncate Statspack data?

One can truncate data in STATSPACK or delete range of snaps stored. To truncate STATSPACK data Using $ORACLE_HOME/rdbms/admin/sptrunc.sql, one can truncate STATSPACK data. To run the script you will need to connect as PERFSTAT user. perfstat@TESTDB> @?/rdbms/admin/sptrunc.sql Warning ~~~~~~~ Running sptrunc.sql removes ALL data from Statspack tables. You may wish to export the data before…

How to gather stats without histograms?

When passing method_opt as 'FOR ALL COLUMNS SIZE 1', it means no histogram is collected for columns on the table(s). exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'FOR ALL COLUMNS SIZE 1'); One can find the default value by executing as follows: select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;

Script to automate generation AWR for a day

— The following anonymous PL/SQL code can be used to generate a script which can be run to generate AWR report for one day. Currently the script runs to create report for yesterday’s report. It creates a report between each snap. set serveroutput on set feedback off spool rpt.sql set termout off set linesize 200…

How to view/change statistics retention and space used to store stats history?

See the oldest statistics history available SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY ————————————————————————— 25-OCT-11 10.00.55.093000000 PM -07:00 See the stats retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— 31 Modify retention SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it’s size SQL> select occupant_desc,…