column table_owner format a15
column table_name format a20
column index_name format a20
column column_name format a20
Select owner,table_name,index_name,column_name FROM dba_ind_columns Order by owner,table_name,column_name Where owner=?INFODBA? AND table_name=?PFND0WORKFLOWAUDIT?;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '3hh7grtrjjffc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '1_tuning_task11',
description => 'Tuning task1 for statement 3hh7grtrjjffc');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1_tuning_task11');
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('1_tuning_task11') from dual;
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME;
execute dbms_sqltune.drop_tuning_task('3hh7grtrjjffc_tuning_task11');
########################################################################################################################
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '3hh7grtrjjffc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '3hh7grtrjjffc_tuning_task11',
description => 'Tuning task1 for statement 3hh7grtrjjffc');
2 3 4 5 6 7 8 9 10 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
11 END;
/ 12
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3hh7grtrjjffc_tuning_task11');
PL/SQL procedure successfully completed.
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('3hh7grtrjjffc_tuning_task11') from dual;SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('3HH7GRTRJJFFC_TUNING_TASK11')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 3hh7grtrjjffc_tuning_task11
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 500
Completion Status : COMPLETED
Started at : 04/24/2019 11:48:02
Completed at : 04/24/2019 11:48:03
-------------------------------------------------------------------------------
Schema Name: INFODBA
SQL ID : 3hh7grtrjjffc
SQL Text : SELECT DISTINCT t_01.puid, t_01.pfnd0LoggedDate,
t_01.pfnd0SequenceNumber FROM PFND0WORKFLOWAUDIT t_01 WHERE
SUBSTR ( t_01.pfnd0Job , 1 , 14 ) IN ( SELECT DISTINCT SUBSTR
( t_02.pfnd0Job , 1 , 14 ) AS fnd0JobExpr_Attachment FROM
PFND0WORKFLOWAUDIT t_02 WHERE ( ( t_02.pfnd0EventTypeName =
'__Add_Attachment' ) AND ( t_02.pfnd0SecondaryObject = :1 ) ) )
ORDER BY t_01.pfnd0LoggedDate ASC , t_01.pfnd0SequenceNumber
ASC
Bind Variables :
1 - (VARCHAR2(32)):xeUxkwNJaDPwJDAAAAAAAAAAAAA
-------------------------------------------------------------------------------
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: 40.43%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'3hh7grtrjjffc_tuning_task11', task_owner => 'SYS', replace =>
TRUE);
Validation results
------------------
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): .000258 .000141 45.34 %
CPU Time (s): .000369 0 100 %
User I/O Time (s): 0 0
Buffer Gets: 5 3 40 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 11468 0 100 %
Physical Write Bytes: 0 0
Rows Processed: 0 0
Fetches: 0 0
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
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: 99.72%)
------------------------------------------
- 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
"INFODBA"."PIPFND0WORKFLOW_7" because it is a prefix of the recommended
index.
create index INFODBA.IDX$$_5ED20001 on
INFODBA.PFND0WORKFLOWAUDIT("PFND0SECONDARYOBJECT","PFND0EVENTTYPENAME");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index INFODBA.IDX$$_5ED20002 on
INFODBA.PFND0WORKFLOWAUDIT(SUBSTR("PFND0JOB",1,14));
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 249828257
----------------------------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
----------------------------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 105 | 8857 (1)| 00
:00:01 |
| 1 | SORT ORDER BY | | 1 | 105 | 8857 (1)| 00
:00:01 |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 105 | 8856 (1)| 00
:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| PFND0WORKFLOWAUDIT | 1 | 51 | 1 (0)| 00
:00:01 |
|* 4 | INDEX RANGE SCAN | PIPFND0WORKFLOW_7 | 1 | | 1 (0)| 00
:00:01 |
| 5 | TABLE ACCESS FULL | PFND0WORKFLOWAUDIT | 635K| 32M| 8853 (1)| 00
:00:01 |
----------------------------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SUBSTR("T_01"."PFND0JOB",1,14)=SUBSTR("T_02"."PFND0JOB",1,14))
3 - filter("T_02"."PFND0EVENTTYPENAME"='__Add_Attachment')
4 - access("T_02"."PFND0SECONDARYOBJECT"=:1)
2- Using SQL Profile
--------------------
Plan hash value: 980714332
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 8859 (1)| 00:01:47 |
| 1 | SORT UNIQUE | | 1 | 105 | 8858 (1)| 00:01:47 |
|* 2 | HASH JOIN | | 1 | 105 | 8857 (1)| 00:01:47 |
|* 3 | TABLE ACCESS BY INDEX ROWID| PFND0WORKFLOWAUDIT | 1 | 51 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PIPFND0WORKFLOW_7 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | PFND0WORKFLOWAUDIT | 635K| 32M| 8853 (1)| 00:01:47 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SUBSTR("T_01"."PFND0JOB",1,14)=SUBSTR("T_02"."PFND0JOB",1,14))
3 - filter("T_02"."PFND0EVENTTYPENAME"='__Add_Attachment')
4 - access("T_02"."PFND0SECONDARYOBJECT"=:1)
3- Using New Indices
--------------------
Plan hash value: 1370449358
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 25 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 105 | 25 (4)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 105 | 24 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PFND0WORKFLOWAUDIT | 1 | 51 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_5ED20001 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PFND0WORKFLOWAUDIT | 2 | 108 | 23 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX$$_5ED20002 | 496 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T_02"."PFND0SECONDARYOBJECT"=:1 AND
"T_02"."PFND0EVENTTYPENAME"='__Add_Attachment')
6 - access("PFND0WORKFLOWAUDIT"."SYS_QSMMIX_VCOL_5001"=SUBSTR("T_02"."PFND0JOB",1,14))
-------------------------------------------------------------------------------
2804328466
No comments:
Post a Comment