SQL tuning advisior using SQL id in oracle database 19c
step 1: create the tuning task for the sql id
SQL> declare
sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 41062,
end_snap => 44516,
sql_id => ‘2mjr0fw17sjuh’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => ‘2mjr0fw17sjuh_tuning_task’,
description => ‘tuning_in_OLBUI’);
dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
end;
DECLARE
sql_tune_task_id VARCHAR2(100);
BEGIN
sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => ‘2mjr0fw17sjuh’,
begin_snap => 41062,
end_snap => 44516,
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 10800,
task_name => ‘2mjr0fw17sjuh_tuning_task’,
description => ‘tuning_in_OLBUI’
);
DBMS_OUTPUT.PUT_LINE(‘sql_tune_task_id: ‘ || sql_tune_task_id);
END;
/
step 2: Execute the tuning task using the sql id
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘0p2pyz9cksyus_tuning_task’);
step 3: select the recommendation plan for the sql id
SELECT DBMS_SQLTUNE.report_tuning_task(‘2mjr0fw17sjuh_tuning_task’) AS recommendations FROM dual;
SQL> SELECT DBMS_SQLTUNE.report_tuning_task(‘0p2pyz9cksyus_tuning_task’) AS recommendations FROM dual;
RECOMMENDATIONS
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : 2mjr0fw17sjuh_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 10800
Completion Status : COMPLETED
Started at : 08/09/2024 11:47:20
Completed at : 08/09/2024 13:39:51
——————————————————————————-
Schema Name : PRXBI_DW
Container Name: PRXBPRD
SQL ID : 2mjr0fw17sjuh
SQL Text : select distinct T1600609.ROW_WID as c1
from
WC_CONTACT_D T1600609 /* Dim_WC_CONTACT_D_NON_COTS */ ,
WC_EMAIL_D T1733953 /* Dim_WC_EMAIL_D_Contact */ ,
WC_EVENT_D T1534004 /* Dim_WC_EVENT_D */ ,
WC_EVENT_D_TL T1603713 /* Dim_WC_EVENT_D_TL_Event_TL */
,
WC_OBU_D T1508940 /* Dim_WC_OBU_D */ ,
WC_COMM_CHN_D T1514563 /* Dim_WC_COMM_CHN_D */ ,
WC_PRIVACY_F T1734099 /* Fact_WC_PRIVACY_F_Privacy_Email
*/
where ( T1600609.EMAIL_WID = T1733953.ROW_WID and
T1508940.ROW_WID = T1734099.OBU_WID and T1514563.ROW_WID =
T1734099.COMM_WID and T1534004.ROW_WID = T1603713.EVENT_WID
and T1534004.ROW_WID = T1734099.EVENT_WID and T1514563.NAME =
‘Email’ and T1534004.DELETE_FLAG = ‘N’ and T1534004.ALPHA_CD
= ‘BCBDE’ and T1600609.DELETE_FLAG = ‘N’ and
T1603713.DELETE_FLAG = ‘N’ and T1603713.DEFAULT_LANG = ‘Y’
and T1733953.ROW_WID = T1734099.EMAIL_WID and
T1734099.DELETE_FLAG = ‘N’ and T1734099.VALUE = ‘OUT’ and
(T1508940.NAME in (‘Austria’, ‘Germany’, ‘Unspecified’)) )
——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-
1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 62.44%)
——————————————
– Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘2mjr0fw17sjuh_tuning_task’, task_owner => ‘SYS’, replace =>
TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 3 will improve its response time
62.44% over the original plan. However, this will increase the statement’s
resource consumption by an estimated 12.67% which may result in a reduction
of system throughput. Also, because these resources are consumed over a much
smaller duration, the response time of concurrent statements might be
negatively impacted.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
—————————————–
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
——————————————–
Weekly DB time (in sec) 0
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: 93.92%)
——————————————
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index “PRXBI_DW”.”WC_EVENT_D_M2″
because it is a prefix of the recommended index.
create index PRXBI_DW.IDX$$_B72C0001 on
PRXBI_DW.WC_EVENT_D(“DELETE_FLAG”,”ALPHA_CD”,”ROW_WID”);
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index PRXBI_DW.IDX$$_B72C0002 on
PRXBI_DW.WC_EVENT_D_TL(“DELETE_FLAG”,”DEFAULT_LANG”,”EVENT_WID”);
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index “PRXBI_DW”.”WC_OBU_D_M2″
because it is a prefix of the recommended index.
create index PRXBI_DW.IDX$$_B72C0003 on
PRXBI_DW.WC_OBU_D(“NAME”,”ROW_WID”);
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index
“PRXBI_DW”.”WC_CONTACT_D_M1″ because it is a prefix of the recommended
index.
create index PRXBI_DW.IDX$$_B72C0004 on
PRXBI_DW.WC_CONTACT_D(“DELETE_FLAG”,”EMAIL_WID”,”ROW_WID”);
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.
 | ReplyForwardAdd reaction |