STEP 1: Create tuning task for the specific SQL_ID:
SQL> 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;
/
Enter value for sql_id: axsbgh684sf7q
old 4: task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
new 4: task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> 'axsbgh684sf7q',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
PL/SQL procedure successfully completed.
STEP 2: Check the status of newly created task:
SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';
Enter value for task_name: SQL_TUNNING_TASK_SQL_ID
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 = 'SQL_TUNNING_TASK_SQL_ID'
TASK_NAME STATUS
------------------------------ -----------
SQL_TUNNING_TASK_SQL_ID INITIALSTEP 3: Execute the newly created task:
SQL> exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
Enter value for task_name: SQL_TUNNING_TASK_SQL_ID
PL/SQL procedure successfully completed.STEP 4: Check the status after executing the task:
SQL> SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';
Enter value for task_name: SQL_TUNNING_TASK_SQL_ID
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 = 'SQL_TUNNING_TASK_SQL_ID'
TASK_NAME STATUS
------------------------------ -----------
SQL_TUNNING_TASK_SQL_ID COMPLETEDSTEP 5: Execute the Below Query to get the Advisory Report:
SQL> SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200SQL> SQL>
SET PAGESIZE 24
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;
SQL> SQL> SP2-0268: linesize option not a valid number
SQL> SQL> Enter value for task_name: SQL_TUNNING_TASK_SQL_ID
old 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 dual
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 : 02/04/2021 05:42:52
Completed at : 02/04/2021 05:46:01
-------------------------------------------------------------------------------
Schema Name: GGADMIN
SQL ID : axsbgh684sf7q
SQL Text : UPDATE "WW_TEMP"."WW_CONTACTS_V1" x SET x."ROW_ID" =
:a17,x."X_OCN" = :a18,x."W_SPECIAL_AGREEMENT" =
:a19,x."CONTACT_ROW_ID" = :a20,x."ACCNT_ROW_ID" =
:a21,x."ACCNT_STATUS" = :a22,x."ACCNT_TYPE" = :a23,x."PARTY_UID"
= :a24,x."PERSON_UID" = :a25,x."ACCNT_CON_ASSOC" =
:a26,x."DOMAIN_NAME" = :a27,x."FST_NAME" = :a28,x."LAST_NAME" =
:a29,x."ID" = :a30,x."OCH_NUMBER" = :a31,x."EMAIL_POS_NO" =
RECOMMENDATIONS
--------------------------------------------------------------------------------
:a32,x."EMAIL_IDS" = :a33 WHERE x."ROW_ID" = :b0 AND x."X_OCN" =
:b1 AND x."W_SPECIAL_AGREEMENT" = :b2 AND x."CONTACT_ROW_ID" is
NULL AND x."ACCNT_ROW_ID" is NULL AND x."ACCNT_STATUS" is NULL
AND x."ACCNT_TYPE" is NULL AND x."PARTY_UID" is NULL AND
x."PERSON_UID" is NULL AND x."ACCNT_CON_ASSOC" is NULL AND
x."DOMAIN_NAME" = :b10 AND x."FST_NAME" is NULL AND
x."LAST_NAME" is NULL AND x."ID" is NULL AND x."OCH_NUMBER" is
NULL AND x."EMAIL_POS_NO" = :b15 AND x."EMAIL_IDS" = :b16 AND
ROWNUM = 1
Bind Variables :
18 - (VARCHAR2(128)):1-X67P9M
19 - (VARCHAR2(128)):4146348
20 - (VARCHAR2(2000)):72213
21 - (VARCHAR2(2000)):mic.sch
22 - (VARCHAR2(32)):2
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
RECOMMENDATIONS
--------------------------------------------------------------------------------
1- 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: 99.28%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index WW_TEMP.IDX$$_ADF80001 on
WW_TEMP.WW_CONTACTS_V1("ROW_ID","W_SPECIAL_AGREEMENT");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
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.
2- Alternative Plan Finding
RECOMMENDATIONS
--------------------------------------------------------------------------------
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- --------------
--
1 320515616 2021-02-03/20:00:55 0.002 AWR not reproducib
le
2 348407149 2021-02-04/05:43:48 0.003 Cursor Cache not reproducib
le
Information
-----------
- All alternative plans other than the Original Plan could not be
reproduced in the current environment.
RECOMMENDATIONS
--------------------------------------------------------------------------------
- The plan with id 1 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
- The plan with id 2 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 348407149
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
RECOMMENDATIONS
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 56 | 281 (1)| 00:0
0:04 |
| 1 | UPDATE | WW_CONTACTS_V1 | | | |
|
|* 2 | COUNT STOPKEY | | | | |
|
|* 3 | TABLE ACCESS FULL| WW_CONTACTS_V1 | 1 | 56 | 281 (1)| 00:0
0:04 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
3 - filter("X"."ROW_ID"=:B0 AND "X"."W_SPECIAL_AGREEMENT"=:B2 AND
"X"."EMAIL_IDS"=:B16 AND "X"."DOMAIN_NAME"=:B10 AND "X"."X_OCN"=:B
1 AND
"X"."EMAIL_POS_NO"=:B15 AND "X"."CONTACT_ROW_ID" IS NULL AND
"X"."ACCNT_ROW_ID" IS NULL AND "X"."ACCNT_STATUS" IS NULL AND
"X"."ACCNT_TYPE" IS NULL AND "X"."PARTY_UID" IS NULL AND "X"."PERS
RECOMMENDATIONS
--------------------------------------------------------------------------------
ON_UID" IS
NULL AND "X"."ACCNT_CON_ASSOC" IS NULL AND "X"."FST_NAME" IS NULL
AND
"X"."LAST_NAME" IS NULL AND "X"."ID" IS NULL AND "X"."OCH_NUMBER"
IS NULL)
2- Using New Indices
--------------------
Plan hash value: 3472112789
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | UPDATE STATEMENT | | 1 | 56 | 2
(0)| 00:00:01 |
| 1 | UPDATE | WW_CONTACTS_V1 | | |
| |
|* 2 | COUNT STOPKEY | | | |
RECOMMENDATIONS
--------------------------------------------------------------------------------
| |
|* 3 | TABLE ACCESS BY INDEX ROWID| WW_CONTACTS_V1 | 1 | 56 | 2
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_ADF80001 | 1 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
3 - filter("X"."EMAIL_IDS"=:B16 AND "X"."DOMAIN_NAME"=:B10 AND "X"."X_OCN"=:B
1 AND
"X"."EMAIL_POS_NO"=:B15 AND "X"."CONTACT_ROW_ID" IS NULL AND "X"."
ACCNT_ROW_ID" IS NULL
AND "X"."ACCNT_STATUS" IS NULL AND "X"."ACCNT_TYPE" IS NULL AND "X
"."PARTY_UID" IS NULL
AND "X"."PERSON_UID" IS NULL AND "X"."ACCNT_CON_ASSOC" IS NULL AND
"X"."FST_NAME" IS
NULL AND "X"."LAST_NAME" IS NULL AND "X"."ID" IS NULL AND "X"."OCH
RECOMMENDATIONS
--------------------------------------------------------------------------------
_NUMBER" IS NULL)
4 - access("X"."ROW_ID"=:B0 AND "X"."W_SPECIAL_AGREEMENT"=:B2)
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 2
------
Plan Origin :Cursor Cache
Plan Hash Value :348407149
Executions :203847
Elapsed Time :0.003 sec
CPU Time :0.003 sec
Buffer Gets :492
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
RECOMMENDATIONS
--------------------------------------------------------------------------------
2. The plan matches the original plan.
3. The plan with id 2 could not be reproduced in the current environment. For
this reason, a SQL plan baseline cannot be created to instruct the Oracle optimi
zer to pick this plan in the future.
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | UPDATE STATEMENT | | | | 281 (100)|
|
| 1 | UPDATE | WW_CONTACTS_V1 | | | |
|
| 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| WW_CONTACTS_V1 | 1 | 56 | 281 (1)| 00:0
0:04 |
--------------------------------------------------------------------------------
------
RECOMMENDATIONS
--------------------------------------------------------------------------------
Plan 1
------
Plan Origin :AWR
Plan Hash Value :320515616
Executions :203576
Elapsed Time :0.002 sec
CPU Time :0.002 sec
Buffer Gets :434
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan with id 1 could not be reproduced in the current environment. For
this reason, a SQL plan baseline cannot be created to instruct the Oracle optimi
zer to pick this plan in the future.
------------------------------------------------------
SQL>
report says that by creating index we will get 99% benifit
create index WW_TEMP.IDX$$_ADF80001 on
WW_TEMP.WW_CONTACTS_V1("ROW_ID","W_SPECIAL_AGREEMENT");
To accept sql profile follow below link
https://dbatracker.com/2021/02/05/accepting-sql-profiles-from-sql-tunning-advsior/
STEP 6: To Drop the Tuning Task:
SQL> execute dbms_sqltune.drop_tuning_task('&TASK_NAME');
Enter value for task_name: SQL_TUNNING_TASK_SQL_ID
PL/SQL procedure successfully completed.
DBATracker 