Automatically running sql_advisor tasks from ADDM reports
Posted by John Hallas on November 12, 2009
STOP PRESS – 17 Nov 2009 – updated with latest code which works against both 10g and 11g databases
I am attaching scripts which I wrote a while ago to automatically pick any sql_ids reported in the latest ADDM and then run sql_advisor to report on any tuning advice. I am not suggesting that the information they provide is not available from EM or indeed every task reported needs resolving but it can be a good heads-up on a system you don’t know very well.
These are enabled every hour (can be less depending upon your snapshot interval) and they create a daily file which can be easily reviewed.
I find the real benefit is not on production databases but on dev and test databases that are being used for development prior to production implementation. This is for two reasons, firstly I hope that the team has a good handle on what is happening in production and are aware of issues and secondly we are most likely to be able to add most value and benefit in development environments before the code is made live.
A couple of ‘issuettes’. The output from the ADDM report is different between 10g and 11g so I have amended the awk file to cater for both versions. I have been having an ongoing problem with tghe sql_advisor tuning task timing out on some systems and consequently leaving the task created for the next run. I have therefore amended the loop to drop the task at several points which looks untidy in the output file bit does seem to resolve the problem.
I hoped to attach a zip file containing 4 scripts but cannot see how to do it without a plug-in which is a problem on my works PC. so in the meantime I have pasted the code of each of 4 files.
tuning_recommendations.ksh which is the controlling script
#! /bin/ksh # loop though the file produced from get_addm_report.sql and put the gathered sql-ids into a flat file # awk the file to get just the SQL_ID # for each sql_id create a task, execute that task, run the report and then delete the task # # The delete tuning task job is run an additional twice because if the tuning task times out then it does not clean up properly # Better to see a few failures in this job that not run the sql_tuning_advisor at all. #if [ -d /home/oracle/logs ] then rm /home/oracle/logs/tmp*.log else mkdir /home/oracle/logs exit fi if [ $# -ne 1 ] then echo "No ORACLE SID - exiting" exit fi # execute ORACLE's .profile # #. ~/.profile unset ORAENV_ASK # # set up environment variables. # ORACLE_SID=$1 . /usr/local/bin/oraenv ${ORACLE_SID} export ORACLE_HOME=`cat /etc/oratab | grep $ORACLE_SID | awk -F: '{print $2 }` export PATH=$ORACLE_HOME/bin:$PATH export ORAENV_ASK=NO today=`date +%d-%b-%Y`; export today LOGDIR=$HOME/logs LOGFILE=$LOGDIR/get_addm_${today}.log REPORTFILE=$LOGDIR/sql_advisor_report_${ORACLE_SID}_${today}.log # # sqlplus -s /nolog <<SQLEND connect / as sysdba spool $LOGDIR/tmp_${ORACLE_SID}_1.log @/shared/oracle/performance/get_addm_report.sql spool off exit SQLEND <p> </p> cat $LOGDIR/tmp_${ORACLE_SID}_1.log|awk -f /shared/oracle/performance/tuning_recommendations.awk |awk '!a[$0]++' > $LOGDIR/tmp_${ORACLE_SID}_2.log cat $LOGDIR/tmp_${ORACLE_SID}_2.log | awk '$0!~/^$/ {print $0}' > $LOGDIR/tmp_${ORACLE_SID}_3.log for PLAN in `cat $LOGDIR/tmp_${ORACLE_SID}_3.log` do sqlplus -s /nolog <<SQLEND >> $REPORTFILE connect / as sysdba begin DBMS_SQLTUNE.drop_tuning_task('test_task1'); end; / SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_task1'; @/shared/oracle/performance/sql_advisor.sql $PLAN exit SQLEND done # tidy up the report file # tidy up reports > 14 days old find $LOGDIR -name "sql_advisor_repor*.log" -mtime +14 -print -exec rm -f {} \;
get_addm_report.sql which gets each task from the last snapshot from dba_advisor_tasks
set long 10000000 set pagesize 50000 column get_clob format a80 select dbms_advisor.get_task_report (task_name) as ADDM_report from dba_advisor_tasks where task_id = ( select max(t. task_id) from dba_advisor_tasks t, dba_advisor_log l where t.task_id = l.task_id and t.advisor_name = 'ADDM' and l.status = 'COMPLETED');
tuning_recommendations.awk is a short awk script used to process the output from get_addm_report.sql
BEGIN{ #start at the first line #OUTFILE="$HOME/logs/outfile.log" } { { if (($1=="RATIONALE:") && ($2=="SQL")) #10G ADDM format { F1=$6 } if (($1=="Run") && ($2=="SQL") && ($3=="Tuning") && ($4=="Advisor") && ($7=="SQL") && ($10=="SQL_ID")) #11G ADDM format { F1=$11 } } VAR1=substr(F1,2,13) print VAR1 } END{ }
sql_advisor.sql runs the sql_advisor package against each task found.
DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; my_sqlid varchar2(30); BEGIN my_sqlid := '&1'; my_task_name := dbms_sqltune.create_tuning_task (sql_id=> my_sqlid, scope => 'COMPREHENSIVE', time_limit => 300, task_name => 'test_task1', description => 'test_task1' ); END; / BEGIN dbms_sqltune.execute_tuning_task (task_name => 'test_task1'); END; / SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_task1'; SET LONG 10000 SET LONGCHUNKSIZE 10000 SET LINESIZE 100 set pages 60 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'test_task1') FROM DUAL; begin DBMS_SQLTUNE.drop_tuning_task('test_task1'); end; /
We have a read only NFS mounted disk available on all database servers and the files are placed in there and initiated by a cron entry for each SID on an hourly basis
40 * * * * /shared/oracle/performance/tuning_recommendations.ksh SID >/dev/null 2>&1
Output is created in a folder $HOME/logs and 14 days worth of reports are kept.
A sample output report (only one task shown but certainly on this Peoplesoft database it would show many tasks)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK1') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : test_task1 Tuning Task Owner : SYS Scope : COMPREHENSIVE Time Limit(seconds) : 60 Completion Status : COMPLETED Started at : 11/12/2009 07:43:33 Completed at : 11/12/2009 07:44:16 Number of Statistic Findings : 1 Number of Index Findings : 1 ------------------------------------------------------------------------------- Schema Name: SYSADM SQL ID : cy3fmjha2sjnr SQL Text : SELECT M.EMPLID, M.EMPL_RCD, M.SCH_PRIM_ALT_IND, TO_CHAR(M.DUR,'YYYY-MM-DD'), M.SEQ_NO, M.CHNG_PRIMARY, M.SCHEDULE_GRP, M.SETID, M.WRKDAY_ID, M.SHIFT_ID, M.SCHED_HRS, M.SCH_CONFIG1, M.SCH_CONFIG2, M.SCH_CONFIG3, M.SCH_CONFIG4, TO_CHAR(M.START_DTTM,'YYYY-MM-DD-HH24.MI.SS.&quot;000000&quot;'), TO_CHAR(M.END_DTTM,'YYYY-MM-DD-HH24.MI.SS.&quot;000000&quot;'), M.SCHED_SOURCE, M.OFFDAY_IND, A.TIMEZONE, A.SCH_CATEGORY From PS_SCH_MNG_SCH_TBL M, PS_SCH_ADHOC_DTL A Where M.EMPLID = :1 and M.EMPL_RCD = :2 and M.SCH_PRIM_ALT_IND = :3 and M.DUR between TO_DATE(:4,'YYYY-MM-DD') and TO_DATE(:5,'YYYY-MM-DD') and A.EMPLID = M.EMPLID and A.EMPL_RCD = M.EMPL_RCD and A.SCH_PRIM_ALT_IND = M.SCH_PRIM_ALT_IND and A.DUR = M.DUR and A.SEQ_NO = M.SEQ_NO and A.SEQNUM = 1 Order By M.DUR Asc, M.SCHED_SOURCE Desc, M.SEQ_NO Desc ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- Optimizer statistics for index &quot;SYSADM&quot;.&quot;PS_SCH_MNG_SCH_TBL&quot; are stale. Recommendation -------------- - Consider collecting optimizer statistics for this index. execute dbms_stats.gather_index_stats(ownname =&gt; 'SYSADM', indname =&gt; 'PS_SCH_MNG_SCH_TBL', estimate_percent =&gt; DBMS_STATS.AUTO_SAMPLE_SIZE); Rationale --------- The optimizer requires up-to-date statistics for the index in order to select a good execution plan. 2- Index Finding (see explain plans section below) -------------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 94.67%) ------------------------------------------ DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK1') ---------------------------------------------------------------------------------------------------- - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index SYSADM.IDX$$_21C0F0001 on SYSADM.PS_SCH_ADHOC_DTL(&quot;EMPLID&quot;,&quot;SEQNUM&quot;,&quot;EMPL_RCD&quot;,&quot;SCH_PRIM_ALT_IND&quot;,&quot;DU R&quot;); - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index SYSADM.IDX$$_21C0F0002 on SYSADM.PS_SCH_MNG_SCH_TBL(&quot;EMPLID&quot;,&quot;DUR&quot;); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run &quot;Access Advisor&quot; using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 2070933151 ---------------------------------------------------------------------------------------------------- ------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me | Pstart| Pstop | ---------------------------------------------------------------------------------------------------- ------------------------ | 0 | SELECT STATEMENT | | 1 | 103 | 387 (1)| 00 :00:01 | | | |* 1 | FILTER | | | | | | | | | 2 | SORT ORDER BY | | 1 | 103 | 387 (1)| 00 :00:01 | | | | 3 | NESTED LOOPS | | 1 | 103 | 386 (1)| 00 :00:01 | | | | 4 | PARTITION RANGE ITERATOR | | 10 | 350 | 371 (1)| 00 :00:01 | KEY | KEY | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| PS_SCH_ADHOC_DTL | 10 | 350 | 371 (1)| 00 :00:01 | KEY | KEY | |* 6 | INDEX RANGE SCAN | PS_SCH_ADHOC_DTL | 10 | | 369 (1)| 00 :00:01 | KEY | KEY | | 7 | PARTITION RANGE ITERATOR | | 1 | 68 | 2 (0)| 00 :00:01 | KEY | KEY | | 8 | TABLE ACCESS BY LOCAL INDEX ROWID| PS_SCH_MNG_SCH_TBL | 1 | 68 | 2 (0)| 00 :00:01 | KEY | KEY | |* 9 | INDEX UNIQUE SCAN | PS_SCH_MNG_SCH_TBL | 1 | | 1 (0)| 00 :00:01 | KEY | KEY | ---------------------------------------------------------------------------------------------------- ------------------------ Predicate Information (identified by operation id): DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK1') ---------------------------------------------------------------------------------------------------- --------------------------------------------------- 1 - filter(TO_DATE(:4,'YYYY-MM-DD')&lt;=TO_DATE(:5,'YYYY-MM-DD')) 6 - access(&quot;A&quot;.&quot;EMPLID&quot;=:1 AND &quot;A&quot;.&quot;EMPL_RCD&quot;=TO_NUMBER(:2) AND &quot;A&quot;.&quot;SCH_PRIM_ALT_IND&quot;=:3 AND &quot;A&quot;.&quot;DUR&quot;&gt;=TO_DATE(:4,'YYYY-MM-DD') AND &quot;A&quot;.&quot;SEQNUM&quot;=1 AND &quot;A&quot;.&quot;DUR&quot;&lt;=TO_DATE(:5,'YYYY -MM-DD')) filter(&quot;A&quot;.&quot;SEQNUM&quot;=1) 9 - access(&quot;M&quot;.&quot;EMPLID&quot;=:1 AND &quot;M&quot;.&quot;EMPL_RCD&quot;=TO_NUMBER(:2) AND &quot;M&quot;.&quot;SCH_PRIM_ALT_IND&quot;=:3 AND &quot;A&quot;.&quot;DUR&quot;=&quot;M&quot;.&quot;DUR&quot; AND &quot;A&quot;.&quot;SEQ_NO&quot;=&quot;M&quot;.&quot;SEQ_NO&quot;) filter(&quot;M&quot;.&quot;DUR&quot;&gt;=TO_DATE(:4,'YYYY-MM-DD') AND &quot;M&quot;.&quot;DUR&quot;&lt;=TO_DATE(:5,'YYYY-MM-DD')) 2- Using New Indices -------------------- Plan hash value: 1209469329 ---------------------------------------------------------------------------------------------------- ------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me | Pstart| Pstop | ---------------------------------------------------------------------------------------------------- ------------------------ | 0 | SELECT STATEMENT | | 1 | 103 | 21 (10)| 00 :00:01 | | | |* 1 | FILTER | | | | | | | | | 2 | SORT ORDER BY | | 1 | 103 | 21 (10)| 00 :00:01 | | | |* 3 | HASH JOIN | | 1 | 103 | 20 (5)| 00 :00:01 | | | | 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| PS_SCH_ADHOC_DTL | 10 | 350 | 10 (0)| 00 :00:01 | ROWID | ROWID | |* 5 | INDEX RANGE SCAN | IDX$$_21C0F0001 | 10 | | 4 (0)| 00 :00:01 | | | |* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| PS_SCH_MNG_SCH_TBL | 13 | 884 | 9 (0)| 00 :00:01 | ROWID | ROWID | |* 7 | INDEX RANGE SCAN | IDX$$_21C0F0002 | 13 | | 3 (0)| 00 :00:01 | | | ---------------------------------------------------------------------------------------------------- ------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_DATE(:4,'YYYY-MM-DD')&lt;=TO_DATE(:5,'YYYY-MM-DD')) 3 - access(&quot;A&quot;.&quot;EMPLID&quot;=&quot;M&quot;.&quot;EMPLID&quot; AND &quot;A&quot;.&quot;EMPL_RCD&quot;=&quot;M&quot;.&quot;EMPL_RCD&quot; AND &quot;A&quot;.&quot;SCH_PRIM_ALT_IND&quot;=&quot;M&quot;.&quot;SCH_PRIM_ALT_IND&quot; AND &quot;A&quot;.&quot;DUR&quot;=&quot;M&quot;.&quot;DUR&quot; AND SYS_OP_DESCEND(&quot;A&quot;.&quot;DUR&quot;)=SYS_OP_DESCEND(&quot;M&quot;.&quot;DUR&quot;) AND &quot;A&quot;.&quot;SEQ_NO&quot;=&quot;M&quot;.&quot;SEQ_NO&quot;) 5 - access(&quot;A&quot;.&quot;EMPLID&quot;=:1 AND &quot;A&quot;.&quot;SEQNUM&quot;=1 AND &quot;A&quot;.&quot;EMPL_RCD&quot;=TO_NUMBER(:2) AND &quot;A&quot;.&quot;SCH_PRIM_ ALT_IND&quot;=:3 AND &quot;A&quot;.&quot;DUR&quot;&gt;=TO_DATE(:4,'YYYY-MM-DD') AND &quot;A&quot;.&quot;DUR&quot;&lt;=TO_DATE(:5,'YYYY-MM-DD')) 6 - filter(&quot;M&quot;.&quot;SCH_PRIM_ALT_IND&quot;=:3 AND &quot;M&quot;.&quot;EMPL_RCD&quot;=TO_NUMBER(:2)) 7 - access(&quot;M&quot;.&quot;EMPLID&quot;=:1 AND &quot;M&quot;.&quot;DUR&quot;&gt;=TO_DATE(:4,'YYYY-MM-DD') AND &quot;M&quot;.&quot;DUR&quot;&lt;=TO_DATE(:5,'YYY Y-MM-DD')) ------------------------------------------------------------------------------- PL/SQL procedure successfully completed.
The routine above works well but I am happy to consider any changes or improvements.
PS If anybody knows how to use a code tag and not have those horrible green wraparound marks please let me know
Raj jamadagni said
Nice idea, In 11G, you perhaps can use sql below to directly pick SQLIDs identified by ADDM for tuning, thus reducing your number of scripts. It may work in 10g too but I don’t have a 10g DB.
The commented columns get you number of executions and averaged elapsed time as reported by ADDM.
John Hallas said
Thanks Raj, I will look at that code but it is an old post now so is probably well out of date