{"id":956,"date":"2024-12-27T20:20:09","date_gmt":"2024-12-27T20:20:09","guid":{"rendered":"https:\/\/w3buddy.com\/?p=956"},"modified":"2026-01-15T13:15:48","modified_gmt":"2026-01-15T07:45:48","slug":"step-by-step-guide-to-creating-and-executing-sql-tuning-task","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/step-by-step-guide-to-creating-and-executing-sql-tuning-task\/","title":{"rendered":"Step-by-Step Guide to Creating and Executing a SQL Tuning Task"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL tuning is crucial for optimizing query performance in Oracle databases. This step-by-step guide explains how to create and execute a SQL tuning task, along with a detailed overview of task parameters for various scenarios.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. <strong>Find the SQL_ID:<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re not using a SQL Tuning Set (which is a set of pre-captured SQL IDs) or manually supplying the query, you will need the SQL ID of the query you want to analyze. Here are some ways to find the SQL_ID of a query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>From the V$SQL view<\/strong> (if the query is still in the cache):<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> SELECT * FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM %';<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>From a particular session<\/strong> (for example, a user named &#8216;TEST&#8217;):<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT SQL_ID FROM V$SESSION WHERE USERNAME='TEST';<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>From AWR Reports<\/strong>: The SQL_ID information for top resource\/time-consuming queries is also available in AWR reports.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">2. <strong>Create the SQL Tuning Task:<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can create a tuning task from different sources such as the cursor cache, AWR, tuning set, or by manually supplying the SQL query. Below are the examples with explanations for the task parameters:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>From the Cursor Cache<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  v_sql_tune_task_id VARCHAR2(100);\nBEGIN\n  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (\n                          sql_id      => '7d95f5850jkjr',     -- SQL_ID of the query to be tuned\n                          scope       => DBMS_SQLTUNE.scope_comprehensive, -- Scope of the tuning (comprehensive tuning)\n                          time_limit  => 1000,                 -- Time limit in seconds for the task\n                          task_name   => 'test_tuning_task',   -- Name of the tuning task\n                          description => 'Tuning task for the SQL statement with the ID:7d95f5850jkjr from the cursor cache' -- Task description\n  );\n  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);\nEND;\n\/<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Explanation of Parameters<\/strong>:\n<ul class=\"wp-block-list\">\n<li><strong>sql_id:<\/strong> The unique identifier of the SQL query to be tuned.<\/li>\n\n\n\n<li><strong>scope:<\/strong> Defines the level of tuning (comprehensive or basic). The scope_comprehensive option performs a full analysis, including various aspects like physical and logical I\/O.<\/li>\n\n\n\n<li><strong>time_limit:<\/strong> Specifies how long the tuning task should run (in seconds).<\/li>\n\n\n\n<li><strong>task_name:<\/strong> The name for the SQL tuning task.<\/li>\n\n\n\n<li><strong>description:<\/strong> A description of the task.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>From the AWR<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  v_sql_tune_task_id VARCHAR2(100);\nBEGIN\n  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (\n                          begin_snap  => 617,                -- AWR snapshot ID for the start of the period\n                          end_snap    => 620,                -- AWR snapshot ID for the end of the period\n                          sql_id      => '7d95f5850jkjr',    -- SQL_ID of the query to be tuned\n                          scope       => DBMS_SQLTUNE.scope_comprehensive,\n                          time_limit  => 1000,\n                          task_name   => 'test_tuning_task',\n                          description => 'Tuning task for the SQL statement with the ID:7d95f5850jkjr from the AWR');\n  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);\nEND;\n\/<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Explanation of Parameters<\/strong>:\n<ul class=\"wp-block-list\">\n<li><strong>begin_snap and end_snap:<\/strong> Snapshot IDs from AWR that define the period during which the SQL performance is analyzed. These snapshots provide historical data for the tuning task.<\/li>\n\n\n\n<li><strong>sql_id:<\/strong> The SQL identifier for the query.<\/li>\n\n\n\n<li><strong>scope, time_limit, task_name, description:<\/strong> Same as above.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>From the Tuning Set<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  v_sql_tune_task_id VARCHAR2(100);\nBEGIN\n  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (\n                          sqlset_name => 'sql_tuning_set_0800_1200',  -- Name of the SQL Tuning Set to be used\n                          scope       => DBMS_SQLTUNE.scope_comprehensive,\n                          time_limit  => 1000,\n                          task_name   => 'test_tuning_task',\n                          description => 'Tuning task for a particular SQL tuning set.');\n  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);\nEND;\n\/<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Explanation of Parameters<\/strong>:\n<ul class=\"wp-block-list\">\n<li><strong>sqlset_name:<\/strong> The name of the pre-defined SQL Tuning Set that contains the SQL queries to be tuned.<\/li>\n\n\n\n<li><strong>scope, time_limit, task_name, description:<\/strong> Same as above.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>By Manually Supplying the Query<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  v_sql_text VARCHAR2(1000);\n  v_sql_tune_task_id VARCHAR2(100);\nBEGIN\n  v_sql_text := 'SELECT U.USERID, U.USERNAME ' ||\n                'FROM USERS U, REGION R ' ||\n                'WHERE U.REGID=R.REGID AND U.USERID=:uid AND R.REGCLASS=:cid';\n  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (\n                          sql_text    => v_sql_text,          -- SQL query text for tuning\n                          bind_list   => sql_binds(anydata.ConvertNumber(100)),  -- Bind variables for the query\n                          user_name   => 'HR',                -- The user under which the query is executed\n                          scope       => DBMS_SQLTUNE.scope_comprehensive,\n                          time_limit  => 1000,\n                          task_name   => 'test_tuning_task',\n                          description => 'Tuning task for a problematic query...');\n  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);\nEND;\n\/<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Explanation of Parameters<\/strong>:\n<ul class=\"wp-block-list\">\n<li><strong>sql_text:<\/strong> The SQL query you want to tune (instead of using sql_id).<\/li>\n\n\n\n<li><strong>bind_list:<\/strong> A list of bind variables for the query (if applicable).<\/li>\n\n\n\n<li><strong>user_name: <\/strong>The user who runs the query.<\/li>\n\n\n\n<li><strong>scope, time_limit, task_name, description:<\/strong> Same as above.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">3. <strong>Execute the Tuning Task:<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Once the tuning task has been created, you can execute it as follows:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> EXECUTE DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task');<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">4. <strong>Cancel the Tuning Task (if needed):<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If the execution is taking too long or causing performance issues, or if you need to cancel the task for any other reason, use:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> EXECUTE DBMS_SQLTUNE.cancel_tuning_task(task_name => 'test_tuning_task');<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">5. <strong>Pause\/Resume the Tuning Task:<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can also pause or resume the tuning task during execution:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Pause the task<\/strong>:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> EXECUTE DBMS_SQLTUNE.interrupt_tuning_task(task_name => 'test_tuning_task');<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Resume the task<\/strong>:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> EXECUTE DBMS_SQLTUNE.resume_tuning_task(task_name => 'test_tuning_task');<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">6. <strong>Monitor the Status of the Tuning Task:<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To check the status of the tuning task, use the following query:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE UPPER(TASK_NAME)='TEST_TUNING_TASK';\nTASK_NAME                      STATUS\n------------------------------ -----------\ntest_tuning_task               COMPLETED<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">7. <strong>Get the Resulting Report:<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Once the task is completed, you can get the tuning report using:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-\">SET LONG 1000\nSET LONGCHUNKSIZE 1000\nSET LINESIZE 100\nSELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_tuning_task') FROM DUAL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This will generate a comprehensive tuning report that helps you analyze and improve the performance of the SQL query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By following these steps, you can efficiently create, execute, and manage SQL tuning tasks in Oracle, ensuring that your queries are optimized for the best performance.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL tuning is crucial for optimizing query performance in Oracle databases. This step-by-step guide explains how to create and execute a SQL tuning task, along with a detailed overview of task parameters for various scenarios. 1. Find the SQL_ID: If you&#8217;re not using a SQL Tuning Set (which is a set of pre-captured SQL IDs) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-956","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/956","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=956"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/956\/revisions"}],"predecessor-version":[{"id":1558,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/956\/revisions\/1558"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=956"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=956"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=956"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}