SYS@SAM AS SYSDBA> create table test(n number,w varchar2(3));
Table created.
SYS@SAM AS SYSDBA> insert into test values(1,'sam');
1 row created.
SYS@SAM AS SYSDBA> commit;
Commit complete.
SYS@SAM AS SYSDBA> create index test1 on test(w);
Index created.
SYS@SAM AS SYSDBA> select * from test where w='sam';
N W
---------- ---
1 sam
SYS@SAM AS SYSDBA> select * from test where lower(w)='sam';
N W
---------- ---
1 sam
SYS@SAM AS SYSDBA> declare
tempstring varchar2(300);
task_id varchar2(200);
begin
tempstring := ' select * from test where w=''sam''';
task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE2');
2 3 4 5 6 7 dbms_sqltune.execute_tuning_task('SQLTUNE2');
end;
/ 8 9
PL/SQL procedure successfully completed.
Now let's check the recommendations from Tuning advisor(Note:We should have valid license from Oracle)
SYS@SAM AS SYSDBA> set long 5000
SYS@SAM AS SYSDBA> select dbms_sqltune.report_tuning_task('SQLTUNE2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : SQLTUNE2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/22/2017 01:47:35
Completed at : 05/22/2017 01:47:38
--------------------------------------------------------------
-----------------
Schema Name: SYS
SQL ID : 14vwm534mggft
SQL Text : select * from test where w='sam'
------------------------------------------------------------------------
-------
FINDINGS SECTION (2 findings)
------------------------------------------------------------------
-------------
1- Statistics Finding
---------------------
Table "SYS"."TEST" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabnam
e =>
'TEST', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for
the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statem
ent.
Recommendation (estimated benefit: 33.55%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQ
LTUNE2',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its pl
an 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 P
rofile % Improved
------------- ----------------
----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000054 .000048
11.11 %
CPU Time (s): .0001 0 1
00 %
User I/O Time (s): 0 0
Buffer Gets: 3 2 33.
33 %
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
-----
1. Statistics for the original plan were averaged over 10
executions.
2. Statistics for the SQL profile plan were averaged over 10 ex
ecutions.
-------------------------------------------------------------------
------------
EXPLAIN PLANS SECTION
---------------------------------------------------------------------
----------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020
-----------------------------------------------------
---------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)
| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 5 | 2 (0)| 0
0:00:01 |
--------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
2- Using SQL Profile
--------------------
Plan hash value: 3981825257
--------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
----------------------------------------------------------
---------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (34)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 5 | 3
(34)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 |
| 2 (50)| 00:00:01 |
----------------------------------------------------
---------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
------------------------------------------------------------------
------------------------------------------------------------------
**************************************************************************
Another small test case
manually executing sql tuning advisor for better execution plan
SYS@TEST AS SYSDBA> grant select on apps.fnd_concurrent_requests to scott;
Grant succeeded.
SYS@TEST AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@fdlxebsdvdb1 ~> sqlplus scott/scott
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 22 03:51:04 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@TEST > create table test1 as select * from apps.fnd_concurrent_requests;
Table created.
SCOTT@TEST > create table test2 as select * from apps.fnd_concurrent_requests where rownum < 1000;
Table created.
SCOTT@TEST >
select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from test1 a,test2 b where a.request_id=b.request_id;
gather the stats
exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
SCOTT@TEST >
login as sysdba
select /*+ gather_plan_statistics */ a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from scott.test1 a,scott.test2 b where a.request_id=b.request_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1004 | 24096 | 1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 1004 | 24096 | 1903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 1888 (3)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18407 consistent gets
96 physical reads
0 redo size
25574 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
full table scan is happening.
lets create a index
create index test1_i on test1(request_id);
SYS@TEST AS SYSDBA> create index test1_i on scott.test1(request_id);
Index created.
SYS@TEST AS SYSDBA>
run gather stats again
SYS@TEST AS SYSDBA> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
set autotrace on;
SYS@TEST AS SYSDBA> select /*+ gather_plan_statistics */ a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from scott.test1 a,scott.test2 b where a.request_id=b.request_id;
----------------------------------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1004 | 24096 | 1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 1004 | 24096 | 1903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 1888 (3)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18407 consistent gets
0 physical reads
0 redo size
25574 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
lets create the index on test 2 also
create index test2_i on scott.test2(request_id);
SYS@TEST AS SYSDBA> create index test2_i on scott.test2(request_id);
Index created.
SYS@TEST AS SYSDBA> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
no change happened
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1004 | 24096 | 1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 1004 | 24096 | 1903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 1888 (3)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18407 consistent gets
0 physical reads
0 redo size
25574 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
SYS@TEST AS SYSDBA>
lets create a tuning task
SYS@TEST AS SYSDBA> declare
tempstring varchar2(32767);
task_id varchar2(32767);
begin
tempstring := 'select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from scott.test1 a,scott.test2 b where a.request_id=b.request_id';
task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE2');
dbms_sqltune.execute_tuning_task('SQLTUNE2');
end; 2 3 4 5 6 7 8
9 /
PL/SQL procedure successfully completed.
SYS@TEST AS SYSDBA> set long 5000
SYS@TEST AS SYSDBA> select dbms_sqltune.report_tuning_task('SQLTUNE2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : SQLTUNE2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 02/22/2018 05:37:38
Completed at : 02/22/2018 05:37:41
--------------------------------------------------------------
-----------------
Schema Name: SYS
SQL ID : 40gbu4v53uscg
SQL Text : select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE fr
om
scott.test1 a,scott.test2 b where a.request_id=b.request_id
-----------------------------------------------------
--------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------------
-----
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this stat
ement.
Recommendation (estimated benefit: 95.13%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => '
SQLTUNE2',
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 t
o completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------
- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .072423 .003432
95.26 %
CPU Time (s): .072589 .003499 9
5.17 %
User I/O Time (s): 0 0
Buffer Gets: 18345 893 9
5.13 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 999 999
Fetches: 999 999
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.
-----------------------------------------------------------------
--------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------
------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
-----------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 2077 | 49848 |
1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 2077 | 49848 | 1
903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 |
9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 188
8 (3)| 00:00:04 |
----------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
2- Using SQL Profile
--------------------
Plan hash value: 2712629061
----------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 2077 | 4984
8 | 2019 (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 28 | 3 (34)|
00:00:01 |
| 2 | NESTED LOOPS | | 2077 | 49848 | 2
019 (1)| 00:00:05 |
| 3 | TABLE ACCESS FULL | TEST2 | 999 |
9990 | 16 (7)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST1_I | 2 | | 2 (
50)| 00:00:01 |
--------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
----------------------------------------------------------------------------
---
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
2547 recursive calls
985 db block gets
3793 consistent gets
40 physical reads
780 redo size
45402 bytes sent via SQL*Net to client
39769 bytes received via SQL*Net from client
189 SQL*Net roundtrips to/from client
129 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@TEST AS SYSDBA>
SYS@TEST AS SYSDBA> set long 5000
SYS@TEST AS SYSDBA> select dbms_sqltune.report_tuning_task('SQLTUNE2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : SQLTUNE2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 02/22/2018 05:37:38
Completed at : 02/22/2018 05:37:41
--------------------------------------------------------------
-----------------
Schema Name: SYS
SQL ID : 40gbu4v53uscg
SQL Text : select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE fr
om
scott.test1 a,scott.test2 b where a.request_id=b.request_id
-----------------------------------------------------
--------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------------
-----
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this stat
ement.
Recommendation (estimated benefit: 95.13%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => '
SQLTUNE2',
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 t
o completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------
- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .072423 .003432
95.26 %
CPU Time (s): .072589 .003499 9
5.17 %
User I/O Time (s): 0 0
Buffer Gets: 18345 893 9
5.13 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 999 999
Fetches: 999 999
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.
-----------------------------------------------------------------
--------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------
------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
-----------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 2077 | 49848 |
1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 2077 | 49848 | 1
903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 |
9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 188
8 (3)| 00:00:04 |
----------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
2- Using SQL Profile
--------------------
Plan hash value: 2712629061
----------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 2077 | 4984
8 | 2019 (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 28 | 3 (34)|
00:00:01 |
| 2 | NESTED LOOPS | | 2077 | 49848 | 2
019 (1)| 00:00:05 |
| 3 | TABLE ACCESS FULL | TEST2 | 999 |
9990 | 16 (7)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST1_I | 2 | | 2 (
50)| 00:00:01 |
--------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
----------------------------------------------------------------------------
---
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
2547 recursive calls
985 db block gets
3793 consistent gets
40 physical reads
780 redo size
45402 bytes sent via SQL*Net to client
39769 bytes received via SQL*Net from client
189 SQL*Net roundtrips to/from client
129 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@TEST AS SYSDBA>
SYS@TEST AS SYSDBA> execute dbms_sqltune.accept_sql_profile(task_name =>'SQLTUNE2',task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
SYS@TEST AS SYSDBA> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
Table created.
SYS@SAM AS SYSDBA> insert into test values(1,'sam');
1 row created.
SYS@SAM AS SYSDBA> commit;
Commit complete.
SYS@SAM AS SYSDBA> create index test1 on test(w);
Index created.
SYS@SAM AS SYSDBA> select * from test where w='sam';
N W
---------- ---
1 sam
SYS@SAM AS SYSDBA> select * from test where lower(w)='sam';
N W
---------- ---
1 sam
SYS@SAM AS SYSDBA> declare
tempstring varchar2(300);
task_id varchar2(200);
begin
tempstring := ' select * from test where w=''sam''';
task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE2');
2 3 4 5 6 7 dbms_sqltune.execute_tuning_task('SQLTUNE2');
end;
/ 8 9
PL/SQL procedure successfully completed.
Now let's check the recommendations from Tuning advisor(Note:We should have valid license from Oracle)
SYS@SAM AS SYSDBA> set long 5000
SYS@SAM AS SYSDBA> select dbms_sqltune.report_tuning_task('SQLTUNE2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : SQLTUNE2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/22/2017 01:47:35
Completed at : 05/22/2017 01:47:38
--------------------------------------------------------------
-----------------
Schema Name: SYS
SQL ID : 14vwm534mggft
SQL Text : select * from test where w='sam'
------------------------------------------------------------------------
-------
FINDINGS SECTION (2 findings)
------------------------------------------------------------------
-------------
1- Statistics Finding
---------------------
Table "SYS"."TEST" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabnam
e =>
'TEST', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for
the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statem
ent.
Recommendation (estimated benefit: 33.55%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQ
LTUNE2',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its pl
an 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 P
rofile % Improved
------------- ----------------
----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000054 .000048
11.11 %
CPU Time (s): .0001 0 1
00 %
User I/O Time (s): 0 0
Buffer Gets: 3 2 33.
33 %
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
-----
1. Statistics for the original plan were averaged over 10
executions.
2. Statistics for the SQL profile plan were averaged over 10 ex
ecutions.
-------------------------------------------------------------------
------------
EXPLAIN PLANS SECTION
---------------------------------------------------------------------
----------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020
-----------------------------------------------------
---------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)
| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 5 | 2 (0)| 0
0:00:01 |
--------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
2- Using SQL Profile
--------------------
Plan hash value: 3981825257
--------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
----------------------------------------------------------
---------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (34)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 5 | 3
(34)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 |
| 2 (50)| 00:00:01 |
----------------------------------------------------
---------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
------------------------------------------------------------------
------------------------------------------------------------------
**************************************************************************
Another small test case
manually executing sql tuning advisor for better execution plan
SYS@TEST AS SYSDBA> grant select on apps.fnd_concurrent_requests to scott;
Grant succeeded.
SYS@TEST AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@fdlxebsdvdb1 ~> sqlplus scott/scott
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 22 03:51:04 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@TEST > create table test1 as select * from apps.fnd_concurrent_requests;
Table created.
SCOTT@TEST > create table test2 as select * from apps.fnd_concurrent_requests where rownum < 1000;
Table created.
SCOTT@TEST >
select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from test1 a,test2 b where a.request_id=b.request_id;
gather the stats
exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
SCOTT@TEST >
login as sysdba
select /*+ gather_plan_statistics */ a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from scott.test1 a,scott.test2 b where a.request_id=b.request_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1004 | 24096 | 1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 1004 | 24096 | 1903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 1888 (3)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18407 consistent gets
96 physical reads
0 redo size
25574 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
full table scan is happening.
lets create a index
create index test1_i on test1(request_id);
SYS@TEST AS SYSDBA> create index test1_i on scott.test1(request_id);
Index created.
SYS@TEST AS SYSDBA>
run gather stats again
SYS@TEST AS SYSDBA> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
set autotrace on;
SYS@TEST AS SYSDBA> select /*+ gather_plan_statistics */ a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from scott.test1 a,scott.test2 b where a.request_id=b.request_id;
----------------------------------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1004 | 24096 | 1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 1004 | 24096 | 1903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 1888 (3)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18407 consistent gets
0 physical reads
0 redo size
25574 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
lets create the index on test 2 also
create index test2_i on scott.test2(request_id);
SYS@TEST AS SYSDBA> create index test2_i on scott.test2(request_id);
Index created.
SYS@TEST AS SYSDBA> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
no change happened
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1004 | 24096 | 1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 1004 | 24096 | 1903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 1888 (3)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18407 consistent gets
0 physical reads
0 redo size
25574 bytes sent via SQL*Net to client
1246 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
SYS@TEST AS SYSDBA>
lets create a tuning task
SYS@TEST AS SYSDBA> declare
tempstring varchar2(32767);
task_id varchar2(32767);
begin
tempstring := 'select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE from scott.test1 a,scott.test2 b where a.request_id=b.request_id';
task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>'SQLTUNE2');
dbms_sqltune.execute_tuning_task('SQLTUNE2');
end; 2 3 4 5 6 7 8
9 /
PL/SQL procedure successfully completed.
SYS@TEST AS SYSDBA> set long 5000
SYS@TEST AS SYSDBA> select dbms_sqltune.report_tuning_task('SQLTUNE2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : SQLTUNE2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 02/22/2018 05:37:38
Completed at : 02/22/2018 05:37:41
--------------------------------------------------------------
-----------------
Schema Name: SYS
SQL ID : 40gbu4v53uscg
SQL Text : select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE fr
om
scott.test1 a,scott.test2 b where a.request_id=b.request_id
-----------------------------------------------------
--------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------------
-----
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this stat
ement.
Recommendation (estimated benefit: 95.13%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => '
SQLTUNE2',
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 t
o completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------
- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .072423 .003432
95.26 %
CPU Time (s): .072589 .003499 9
5.17 %
User I/O Time (s): 0 0
Buffer Gets: 18345 893 9
5.13 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 999 999
Fetches: 999 999
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.
-----------------------------------------------------------------
--------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------
------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
-----------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 2077 | 49848 |
1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 2077 | 49848 | 1
903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 |
9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 188
8 (3)| 00:00:04 |
----------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
2- Using SQL Profile
--------------------
Plan hash value: 2712629061
----------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 2077 | 4984
8 | 2019 (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 28 | 3 (34)|
00:00:01 |
| 2 | NESTED LOOPS | | 2077 | 49848 | 2
019 (1)| 00:00:05 |
| 3 | TABLE ACCESS FULL | TEST2 | 999 |
9990 | 16 (7)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST1_I | 2 | | 2 (
50)| 00:00:01 |
--------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
----------------------------------------------------------------------------
---
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
2547 recursive calls
985 db block gets
3793 consistent gets
40 physical reads
780 redo size
45402 bytes sent via SQL*Net to client
39769 bytes received via SQL*Net from client
189 SQL*Net roundtrips to/from client
129 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@TEST AS SYSDBA>
SYS@TEST AS SYSDBA> set long 5000
SYS@TEST AS SYSDBA> select dbms_sqltune.report_tuning_task('SQLTUNE2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLTUNE2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : SQLTUNE2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 02/22/2018 05:37:38
Completed at : 02/22/2018 05:37:41
--------------------------------------------------------------
-----------------
Schema Name: SYS
SQL ID : 40gbu4v53uscg
SQL Text : select a.LAST_UPDATE_DATE,b.PHASE_CODE,b.STATUS_CODE fr
om
scott.test1 a,scott.test2 b where a.request_id=b.request_id
-----------------------------------------------------
--------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------------
-----
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this stat
ement.
Recommendation (estimated benefit: 95.13%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => '
SQLTUNE2',
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 t
o completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------
- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .072423 .003432
95.26 %
CPU Time (s): .072589 .003499 9
5.17 %
User I/O Time (s): 0 0
Buffer Gets: 18345 893 9
5.13 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 999 999
Fetches: 999 999
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.
-----------------------------------------------------------------
--------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------
------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3916441650
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
-----------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 2077 | 49848 |
1903 (3)| 00:00:04 |
|* 1 | HASH JOIN | | 2077 | 49848 | 1
903 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST2 | 999 | 9990 |
9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 236K| 3238K| 188
8 (3)| 00:00:04 |
----------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
2- Using SQL Profile
--------------------
Plan hash value: 2712629061
----------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 2077 | 4984
8 | 2019 (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 28 | 3 (34)|
00:00:01 |
| 2 | NESTED LOOPS | | 2077 | 49848 | 2
019 (1)| 00:00:05 |
| 3 | TABLE ACCESS FULL | TEST2 | 999 |
9990 | 16 (7)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST1_I | 2 | | 2 (
50)| 00:00:01 |
--------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."REQUEST_ID"="B"."REQUEST_ID")
----------------------------------------------------------------------------
---
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
2547 recursive calls
985 db block gets
3793 consistent gets
40 physical reads
780 redo size
45402 bytes sent via SQL*Net to client
39769 bytes received via SQL*Net from client
189 SQL*Net roundtrips to/from client
129 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@TEST AS SYSDBA>
SYS@TEST AS SYSDBA> execute dbms_sqltune.accept_sql_profile(task_name =>'SQLTUNE2',task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
SYS@TEST AS SYSDBA> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
No comments:
Post a Comment