because I’ve worked on a lot of environments where I only have ssh access, i cannot use graphical tools like Toad or SQL Developer… so I have to find a suitable way to tune sql statements with sql*plus… I’m just describing how to use the sql tuning advisor, how to find the correct sql_id will be another artikel.<\/p>\n
TAKE CARE: you need the oracle diagnostic & tuning pack license!<\/p>\n
ok, not the technical part… there are 3 steps: create a tuning task, where you define what oracle should do, execute the tuning task and show the result of the tuning task. Lets start with #1<\/p>\n
\r\ndefine sql_id=abcdefghijkl\r\ndefine begin_snap=1234\r\ndefine end_snap=1235\r\n\r\nDECLARE\r\n l_sql_tune_task_id VARCHAR2(100);\r\nBEGIN\r\n l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( \r\n begin_snap => &begin_snap,\r\n end_snap => &end_snap,\r\n sql_id => '&sql_id',\r\n scope => DBMS_SQLTUNE.scope_comprehensive,\r\n time_limit => 7200, -- max runtime in seconds... so this means 2 hours\r\n task_name => '&sql_id._AWR_tuning_task');\r\n DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);\r\nEND;\r\n\/\r\n<\/pre>\nok, now you have to execute the tuning task<\/p>\n
\r\nEXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&sql_id._AWR_tuning_task');\r\n<\/pre>\nand when the task has finished, you can view the report with this sql statement:<\/p>\n
\r\nSET LONG 10000;\r\nSET PAGESIZE 1000\r\nSET LINESIZE 200\r\ncolumn RECOMMENDATIONS format a200\r\n-- optionally you can spool the output\r\n-- spool sql_advisor_&sql_id._AWR_tuning_task.txt\r\nSELECT DBMS_SQLTUNE.report_tuning_task('&sql_id._AWR_tuning_task') AS recommendations FROM dual;\r\n-- spool off\r\n<\/pre>\nif you want to accept a sql profile, the syntax to do so is withing the report, but are always the same…<\/p>\n
\r\nexec dbms_sqltune.accept_sql_profile(task_name => '&sql_id._AWR_tuning_task', replace => TRUE);\r\n<\/pre>\nif you want to undo (=remove) the sql profile, you just have to execute this:<\/p>\n
\r\nexec DBMS_SQLTUNE.drop_sql_profile (name => '&sql_id._AWR_tuning_task');\r\n<\/pre>\nyou can drop the tuning task including the report (but without the profile) with this:<\/p>\n
\r\nexec dbms_sqltune.drop_tuning_task(task_name => '&sql_id._AWR_tuning_task');\r\n<\/pre>\nif you want to see the which sql tuning tasks exist, you can query the related dba view…<\/p>\n
\r\nselect task_name from DBA_ADVISOR_TASKS where advisor_name='SQL Tuning Advisor';\r\n<\/pre>\nsmall hint: The automatic sql tuning advisor is running every night (if you haven’t disabled it). Internally it’s just a regular sql tuning task called ‘SYS_AUTO_SQL_TUNING_TASK’… you can get the report as described above (you just have to replace the task name) and see the recommendations of all automatically analysed sql statements within your AWR retention time… pretty cool to get an overview of slow sql. <\/p>\n","protected":false},"excerpt":{"rendered":"
because I’ve worked on a lot of environments where I only have ssh access, i cannot use graphical tools like Toad or SQL Developer… so I have to find a suitable way to tune sql statements with sql*plus… I’m just … Continue reading