Below are the 2 SQL statements, the first one uses the index AAA_IDX and then second one does a full table scan and in this case we want to make the first one that uses the index use the same execution plan as the 2nd one. SQL statement: select * from aaa e where ename…
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:…
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…
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…
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;
— 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…
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,…
