Create an Oracle SQL TUNING TASK manually with the sql_id ..
Create Oracle Tasksave this on task.sqlset long 100000000 pagesize 1000 linesize 300 verify off col recommendations for a200 accept task_name - prompt 'Task_Name: ' declare ret_val varchar2(32700); begin ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit); dbms_sqltune.execute_tuning_task('&&Task_name'); end; / ============== @task.sql Task_Name: SQLT Enter value for sql_id: 40ww88ggjtx4y Enter value for time_limit: 3200 SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 select dbms_sqltune.report_tuning_task('&&task_name') as recommendations from dual ;==========================================================STEP 1: Create tuning task for the specific SQL_ID: declare task_nm varchar2(100); begin task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID'); end; / STEP 2: Check the status of newly created task: SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME'; STEP 3: Execute the newly created task: exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME'); STEP 4: Check the status after executing the task: SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME'; STEP 5: Execute the Below Query to get the Advisory Report: SET LONG 10000 PAGESIZE 1000 LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual; SET PAGESIZE 24old 1: SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual new 1: SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNNING_TASK_SQL_ID') AS recommendations FROM dualNOTE: Replace task name in above query RECOMMENDATIONS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : SQL_TUNNING_TASK_SQL_ID Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 05/06/2021 09:27:26 Completed at : 05/06/2021 09:27:27 ------------------------------------------------------------------------------- Schema Name: SYS SQL ID : 3kywng531fcxu SQL Text : delete from tab_stats$ where obj#=:1 Bind Variables : 1 - (NUMBER):7231 ------------------------------------------------------------------------------- There are no recommendations to improve the statement. ------------------------------------------------------------------------------- STEP 6: To Drop the Tuning Task: execute dbms_sqltune.drop_tuning_task('&TASK_NAME');==================================================== define p_inst=1 column dt heading 'Date/Hour' format a11 set linesize 500 pages 100 select * from ( select min(snap_id) as snap_id, to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr from ( select snap_id, s.instance_number, begin_interval_time start_time, end_interval_time end_time, snap_level, flush_elapsed, lag(s.startup_time) over (partition by s.dbid, s.instance_number order by s.snap_id) prev_startup_time, s.startup_time from dba_hist_snapshot s, gv$instance i where 1=1 -- and begin_interval_time between trunc(sysdate)- p_days and sysdate and begin_interval_time > sysdate -4 and s.instance_number = i.instance_number and s.instance_number = &p_inst order by snap_id ) group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') order by snap_id, start_time ) pivot (sum(snap_id) for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23') ) order by dt; Date/Hour '00' '01' '02' '03' '04' '05' '06' '07' '08' '09' '10' '11' '12' '13' '14' '15' '16' '17' '18' '19' '20' '21' '22' '23' ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 05/01/21 32911 32912 32913 32914 32915 32916 32917 32918 32919 32920 32921 32922 32923 32924 32925 32926 32927 32928 32929 32930 05/02/21 32931 32932 32933 32934 32935 32936 32937 32938 32939 32940 32941 32942 32943 32944 32945 32946 32947 32948 32949 32950 32951 32952 32953 32954 05/03/21 32955 32956 32957 32958 32959 32960 32961 32962 32963 32964 32965 32966 32967 32968 32969 32970 32971 32972 32973 32974 32975 32976 32977 32978 05/04/21 32979 32980 32981 32982 32983 32984 32985 32986 32987 32988 32989 32990 32991 32992 32993 32994 32995 32996 32997 32998 32999 33000 33001 33002 05/05/21 33003 33004 33005 DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 32911 , end_snap => 32926, sql_id => '1j6tnz8fcm4c3', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 560, task_name => '1j6tnz8fcm4c3_AWR_tuning_task', description => 'Tuning task for statement 1j6tnz8fcm4c3 in AWR.'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '1j6tnz8fcm4c3', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 560, task_name => '1j6tnz8fcm4c3_AWR_tuning_task', description => 'Tuning task for statement 1j6tnz8fcm4c3'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1j6tnz8fcm4c3_AWR_tuning_task'); PL/SQL procedure successfully completed. SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '1j6tnz8fcm4c3_AWR_tuning_task' ) FROM DUAL;DBMS_SQLTUNE.REPORT_TUNING_TASK('1J6TNZ8FCM4C3_AWR_TUNING_TASK') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : 1j6tnz8fcm4c3_AWR_tuning_task Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 560 Completion Status : COMPLETED Started at : 05/05/2021 03:16:45 Completed at : 05/05/2021 03:16:46 ------------------------------------------------------------------------------- Schema Name: SYS SQL ID : 1j6tnz8fcm4c3 SQL Text : delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history where savtime < :1 and savtime > timestamp '1900-01-01 00:00:00 -0:0' and rownum <= :2 Bind Variables : 2 - (NUMBER):10000 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) -------------------------------------------------------------------------------EXEC DBMS_SQLTUNE.drop_tuning_task (task_name => '1j6tnz8fcm4c3_AWR_tuning_task');
==============
Test case !! create table t (n number ); SQL> create table t (n number ); declare begin for i in 1 .. 100000 loop insert into t values(i+1); commit; end loop; end; / QL> create index t_idx on t(n); Index created. SQL> exec dbms_stats.gather_table_stats('SCOTT','T'); PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. select /*+ no_index(t t_idx) */ * from t where n=987 ; set linesize 300 col sql_text for a50 wrap col parsing_schema_name for a20 select sql_id,parsing_schema_name,sql_text from gv$sql where sql_text like '%from t where n=987%' SQL_ID PARSING_SCHEMA_NAME SQL_TEXT ------------- -------------------- -------------------------------------------------- 3w7p0ga8tv0fb SCOTT select /*+ no_index(t t_idx) */ * from t where n=9 87 3w7p0ga8tv0fb select /*+ no_index(t t_idx) */ * from t where n=9 87 DECLARE v_sql_tune_task_id VARCHAR2(100); BEGIN v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '3w7p0ga8tv0fb', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 1000, task_name => '3w7p0ga8tv0fb_ms_tuning_task', description => 'Tuning task for the SQL statement with the ID3w7p0ga8tv0fb from the cursor cache'); DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id); END; / PL/SQL procedure successfully completed. SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME'; Enter value for task_name: 3w7p0ga8tv0fb_ms_tuning_task old 1: SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME' new 1: SELECT task_name, status FROM dba_advisor_log WHERE task_name = '3w7p0ga8tv0fb_ms_tuning_task' TASK_NAME STATUS -------------------------------------------------------------------------------------------------------------------------------- ----------- 3w7p0ga8tv0fb_ms_tuning_task INITIAL set long 65536 set longchunksize 65536 set linesize 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('3w7p0ga8tv0fb_ms_tuning_task', 'TEXT', 'TYPICAL', 'FINDINGS') FROM DUAL; SET LONG 1000000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '3w7p0ga8tv0fb_ms_tuning_task' ) FROM DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : 3w7p0ga8tv0fb_ms_tuning_task Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1000 Completion Status : COMPLETED Started at : 12/02/2023 12:58:02 Completed at : 12/02/2023 12:58:06 DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 3w7p0ga8tv0fb SQL Text : select /*+ no_index(t t_idx) */ * from t where n=987 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 98.94%) ------------------------------------------ - Consider accepting the recommended SQL profile. A SQL plan baseline corresponding to the plan with the SQL profile will also be created. execute dbms_sqltune.accept_sql_profile(task_name => '3w7p0ga8tv0fb_ms_tuning_task', task_owner => 'SYS', replace => TRUE); Validation results DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .001512 .000026 98.28 % CPU Time (s): .001541 .000026 98.31 % User I/O Time (s): 0 0 DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- Buffer Gets: 190 2 98.94 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1601196873 DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 69 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 1 | 5 | 69 (2)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=987) DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- 2- Using SQL Profile -------------------- Plan hash value: 2946670127 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('3W7P0GA8TV0FB_MS_TUNING_TASK') ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("N"=987) ------------------------------------------------------------------------------- DECLARE sqlprofile_name VARCHAR2(30); BEGIN sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name => '3w7p0ga8tv0fb_ms_tuning_task', name => 'sql_profile_1', force_match => true); END; PL/SQL procedure successfully completed. alter session set nls_date_format='dd-mm-YYYY hh24:mi'; set linesize 400 pagesize 400 column name format a25 column category format a15 column status format a15 col created for a28 col sql_text for a100 wrap select name, category, created,status ,sql_text from dba_sql_profiles; NAME CATEGORY CREATED STATUS SQL_TEXT ------------------------- --------------- ---------------------------- --------------- ---------------------------------------------------------------------------------------------------- sql_profile_1 DEFAULT 02-DEC-23 01.07.41.000000 PM ENABLED select /*+ no_index(t t_idx) */ * from t where n=987 sqlplus /nolog SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 2 13:17:39 2023 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> connect scott/vihaan123 Connected. SQL> select /*+ no_index(t t_idx) */ * from t where n=987 ; N ---------- 987 col PLAN_TABLE_OUTPUT for a150 select * from table(dbms_xplan.display_cursor('8k564npmnpwdv', null, 'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 8k564npmnpwdv, child number 1 ------------------------------------- select /*+ no_index(t t_idx) */ * from t where n=987 Plan hash value: 1601196873 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| T | 1 | ------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=987) Note ----- - SQL profile sql_profile_1 used for this statement - SQL plan baseline SQL_PLAN_4dmarvn36j4u294ecae5c used for this statement - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 26 rows selected. select sql_id , child_number , is_bind_aware , is_bind_sensitive , is_shareable , to_char(exact_matching_signature) sig , executions , plan_hash_value, sql_plan_baseline from gv$sql where 1=1 and sql_id in ('8k564npmnpwdv','3w7p0ga8tv0fb' ) ; alter session set optimizer_capture_sql_plan_baselines = true; set linesize 500 col SQL_ID for a18 col PLAN_NAME for a35 col CREATOR for a18 col CREATED for a30 col HOURS for a12 select dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id, ( select to_number(regexp_replace(plan_table_output,'^[^0-9]*')) from table(dbms_xplan.display_sql_plan_baseline(sql_handle,plan_name)) where plan_table_output like 'Plan hash value: %') plan_hash_value ,plan_name,SQL_HANDLE ,enabled,accepted,fixed,reproduced ,dbms_xplan.format_time_s(elapsed_time/1e6) hours,creator,origin,created,last_modified,last_executed ,sql_text from dba_sql_plan_baselines b where sql_text like '%select /*+ no_index(t t_idx) */ * from t where n=987%' order by sql_id,hours desc ; SQL_ID PLAN_HASH_VALUE PLAN_NAME SQL_HANDLE ENA ACC FIX REP HOURS CREATOR ORIGIN CREATED LAST_MODIFIED LAST_EXECUTED SQL_TEXT ------------------ --------------- ----------------------------------- ------------------------------ --- --- --- --- ------------ ------------------ ----------------------------- ------------------------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 3w7p0ga8tv0fb 1601196873 SQL_PLAN_4dmarvn36j4u294ecae5c SQL_46cd57dd06689342 YES YES NO YES 00:00:00 SCOTT AUTO-CAPTURE 02-DEC-23 12.54.08.000000 PM 02-DEC-23 12.54.08.000000 PM 02-DEC-23 12.54.08.000000 PM select /*+ no_index(t t_idx) */ * from t where n=987 3w7p0ga8tv0fb 2946670127 SQL_PLAN_4dmarvn36j4u20c80c615 SQL_46cd57dd06689342 YES NO NO YES 00:00:00 SCOTT AUTO-CAPTURE 02-DEC-23 01.18.14.000000 PM 02-DEC-23 01.18.14.000000 PM select /*+ no_index(t t_idx) */ * from t where n=987 6t4pnkz6d9w11 1427923351 SQL_PLAN_5tss04rw268vqfea79147 SQL_5ce30025f8232376 YES YES NO YES 00:00:00 SYS AUTO-CAPTURE 02-DEC-23 02.05.43.000000 PM 02-DEC-23 02.05.43.000000 PM 02-DEC-23 02.05.43.000000 PM select dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id, define sql_handle='SQL_46cd57dd06689342' define sql_plan='SQL_PLAN_4dmarvn36j4u294ecae5c' select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&sql_plan', format=>'BASIC')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- SQL handle: SQL_46cd57dd06689342 SQL text: select /*+ no_index(t t_idx) */ * from t where n=987 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_4dmarvn36j4u294ecae5c Plan id: 2498539100 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 1601196873 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T | ---------------------------------- 20 rows selected.from SNAP !!! 1 DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 159954 , end_snap => 159955, sql_id => 'xa5m3ggy97233', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => 'ga5m3ggy97233_AWR_tuning_task1', description => 'Tuning task for statement ga5m3ggy97233 in AWR'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / 2 EXECUTE DBMS_SQLTUNE.execute_tuning_task(task_name => 'xa5m3ggy97233_AWR_tuning_task1') 3 set long 65536 set longchunksize 65536 set linesize 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('xa5m3ggy97233_AWR_tuning_task1', 'TEXT', 'TYPICAL', 'FINDINGS') FROM DUAL;===from AWR --- select min(snap_id), max(snap_id) from dba_hist_sqlstat where sql_id='&sql_id' ; select min(snap_id), max(snap_id) from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='&sql_id' and SAMPLE_TIME > sysdate -1; MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------ 164804 164840 define snap1=164839 define snap2=164840 define sql_id='49xvssdzd07su' DECLARE a VARCHAR2(100); BEGIN a:= DBMS_SQLTUNE.create_tuning_task ( begin_snap=> &snap1, end_snap=>&snap2, sql_id=> '&sql_id', scope => 'comprehensive', task_name => 'T_&sql_id', time_limit=>7200); END; / checkset linesize 300 pagesize 400 col TASK_NAME for a20 SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME'; -- execute exec dbms_sqltune.execute_tuning_task ('T_49xvssdzd07su'); -- report set long 65536 set longchunksize 65536 set linesize 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('T_49xvssdzd07su', 'TEXT', 'TYPICAL', 'FINDINGS') FROM DUAL;
2 comments:
Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor (Doc ID 262687.1)
Automatic SQL Tuning Advisor Task Does Not Run At PDB Level (Doc ID 2538576.1)
https://anuj-singh.blogspot.com/2011/02/dbmssqltune-with-example.html
Post a Comment