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 = 'aaa'; select /*+ FULL(e) */ * from aaa e … Continue reading Example of using sql profile to use switch to a different execution plan
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: SQL> @?/rdbms/admin/awrsqrpt.sql Continue reading How to generate execution plan created from AWR snapshot?
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 statistics collection for this instance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Note that this job … Continue reading How to schedule a snap for STATSPACK?
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 continuing. About to Truncate Statspack Tables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If would like … Continue reading How to delete or truncate Statspack data?
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; Continue reading How to gather stats without histograms?
— 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 set trimspool on DECLARE dbid v$database.dbid%TYPE; dbname v$database.name%TYPE; -- snap … Continue reading Script to automate generation AWR for a day
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, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%'; OCCUPANT_DESC SPACE_USAGE_KBYTES —————————————————————- … Continue reading How to view/change statistics retention and space used to store stats history?