Sandeep, September 08, 2015 - 3:50 pm UTC
Hi Tom,
The only difference is from the hardware side, rest is same because the same schema got imported (data pump) from server-1 to server-2. As I have said it is just a default database installation. so only a hardware change is capable enough to change execution plan ?
More differences
Hemant K Chitale, September 10, 2015 - 8:27 am UTC
Besides the system statistics and the clustering factor, the hight water mark for tables (BLOCKS in USER_TABLES) and the number of leaf blocks and height (LEAF_BLOCKS and BLEVEL in USER_INDEXES) can also mean that costs for potential execution plans can be different.
Differences in patch level and instance parameters can also cause differences in execution plans.
Differences in hardware profile, clustering, high water mark etc can cause differences in execution runtime even if the execution plan is exactly the same.
note : btw : "SQL Tuning Advisor" is a distinct feature and anyone reading the title would think your question relates to that feature, when it really doesn't.
A reader, September 10, 2015 - 1:45 pm UTC
Hi Hemant,
Thank You.
Would like to know, if I can set the title "SQL Tuning adviser suggesting plan in one database while not in another for the same query". Would like to know, how can I change. If you know please let me know.
And if you also could elaborate, a little bit more with a suitable example it will help me understanding better. If I am not using a tool like toad, or SQL Developer, what should be the script, which will be pulling out the hardware profile and optimizer statistics, from both the databases, putting them in one place I can easily compare.
And where you actually find out this part of information in a definite way (may be mathematically) since this is the hardware profile, so the optimizer behaving this way?
Regards,
Sandeep
Hardware profile ?
Hemant K Chitale, September 15, 2015 - 9:01 am UTC
You can get CPU_COUNT from the database (SHOW PARAMETER CPU_COUNT which shows how many Cores or Threads Oracle has identified).
You can query V$OSSTAT for the number of CPUs, cumulative CPU time and memory size.
But it would be better to get the Hardware Profile (Clock Speed, Memory Speed, Disk i/o transfer time) from the System Administrator.
Optimizer statistics on tables and indexes are in USER_TAB_STATISTICS, USER_TAB_COL_STATISTICS and USER_IND_STATISTICS.
September 16, 2015 - 3:59 am UTC
Agreed. The database can only really get whatever information the OS/hardware is prepared to "share" with it. In these days of virtualisation, you can sometimes see some anomalous data.
for MERGE Statements
Rajeshwaran, Jeyabal, November 30, 2021 - 7:01 am UTC
Is that "MERGE" command is not possible for sql tuning advisor ?
c##rajesh@DEHDL> variable n varchar2(80)
c##rajesh@DEHDL> declare
2 l_sql long;
3 begin
4 l_sql := ' merge into emp e1 using emp e2
5 on (e1.empno = e2.empno)
6 when matched then update set e1.ename = e2.ename ' ;
7
8 :n := dbms_sqltune.create_tuning_task( sql_text => l_sql,
9 scope =>'COMPREHENSIVE' ,
10 task_name =>'MY_DEMO_TASK' );
11 end;
12 /
PL/SQL procedure successfully completed.
c##rajesh@DEHDL> exec dbms_sqltune.execute_tuning_task(:n);
PL/SQL procedure successfully completed.
c##rajesh@DEHDL> select dbms_sqltune.report_tuning_task(:n) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:N)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : MY_DEMO_TASK
Tuning Task Owner : C##RAJESH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/30/2021 01:36:50
Completed at : 11/30/2021 01:37:41
-------------------------------------------------------------------------------
Schema Name : C##RAJESH
Container Name: DEHDL
SQL ID : g8crsb3tw9dv2
SQL Text : merge into emp e1 using emp e2
on (e1.empno = e2.empno)
when matched then update set e1.ename = e2.ename
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Type of SQL statement not supported.
-------------------------------------------------------------------------------
looked into the docs, but still don't see any restriction about it. kindly help us to understand.
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_SQLTUNE.html#GUID-02C23DDE-6304-49E4-9DFC-EA39D44F943C https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-advisor.html#GUID-36E17A9F-7912-483D-A10F-7748BBB924E0
December 01, 2021 - 8:09 am UTC
What version? I don't get that on 19c
SQL> variable n varchar2(80)
SQL> declare
2 l_sql long;
3 begin
4 l_sql := ' merge into emp e1 using emp e2
5 on (e1.empno = e2.empno)
6 when matched then update set e1.ename = e2.ename ' ;
7
8 :n := dbms_sqltune.create_tuning_task( sql_text => l_sql,
9 scope =>'COMPREHENSIVE' ,
10 task_name =>'MY_DEMO_TASK' );
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_sqltune.execute_tuning_task(:n);
PL/SQL procedure successfully completed.
SQL> select dbms_sqltune.report_tuning_task(:n) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:N)
-----------------------------------------------------------------------------------------------------------
---------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : MY_DEMO_TASK
Tuning Task Owner : MCDONAC
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 12/01/2021 16:08:37
Completed at : 12/01/2021 16:08:37
-------------------------------------------------------------------------------
Schema Name : MCDONAC
Container Name: PDB1
SQL ID : 2cmrchhh7x4sz
SQL Text : merge into emp e1 using emp e2
on (e1.empno = e2.empno)
when matched then update set e1.ename
= e2.ename
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a NO_MERGE hint.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 322671678
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 14 | 182 | 6 (17)| 00:00:01 |
| 1 | MERGE | EMP | | | | |
| 2 | VIEW | | | | | |
| 3 | MERGE JOIN | | 14 | 1064 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | EMP_PK | 14 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E1"."EMPNO"="E2"."EMPNO")
filter("E1"."EMPNO"="E2"."EMPNO")
-------------------------------------------------------------------------------
version
Rajeshwaran, Jeyabal, December 01, 2021 - 8:12 am UTC
That was on 18c (18.10) running on Exacc. Hence provided the doc links pointing to 18c version.
from 19c
Rajeshwaran, jeyabal, December 01, 2021 - 8:23 am UTC
Even on 19c (19.12) still got the same error, can you please confirm what version of 19c was used in your above demo?
demo@PDB19> variable n varchar2(80)
demo@PDB19> declare
2 l_sql long;
3 begin
4 l_sql := ' merge into emp e1 using emp e2
5 on (e1.empno = e2.empno)
6 when matched then update set e1.ename = e2.ename ' ;
7
8 :n := dbms_sqltune.create_tuning_task( sql_text => l_sql,
9 scope =>'COMPREHENSIVE' ,
10 task_name =>'MY_DEMO_TASK' );
11 end;
12 /
PL/SQL procedure successfully completed.
demo@PDB19> exec dbms_sqltune.execute_tuning_task(:n);
PL/SQL procedure successfully completed.
demo@PDB19> select dbms_sqltune.report_tuning_task(:n) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:N)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : MY_DEMO_TASK
Tuning Task Owner : DEMO
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 12/01/2021 03:18:51
Completed at : 12/01/2021 03:18:55
-------------------------------------------------------------------------------
Schema Name : DEMO
Container Name: PDB19
SQL ID : d0x7n6h8qbs17
SQL Text : merge into emp e1 using emp e2
on (e1.empno = e2.empno)
when matched then update set e1.ename = e2.ename
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- Type of SQL statement not supported.
-------------------------------------------------------------------------------
demo@PDB19> select banner_full from v$version;
BANNER_FULL
------------------------------------------------------------------------------------------------------
---
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
demo@PDB19>
December 02, 2021 - 4:35 am UTC
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 2 12:34:38 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Thu Dec 02 2021 10:35:04 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0