| Change in he execution plan [message #639664] |
Mon, 13 July 2015 20:49  |
jinga
Messages: 116
Registered: January 2003
|
Senior Member |
|
|
I am getting two different explain plans from two different environment. query is similar to this.
select e.* from table1 e, table 2 d
where e.col = d.col
and d.col = 20;
In this case lets assume that my table 1 table has 30000 records and table 2 has 2 million records. In one environment I am seeing range scan and hash join and another environment I am seeing unique scan, nested loops and hash join. I wonder why I am seeing this difference.
|
|
|
|
|
|
| Re: Change in he execution plan [message #639667 is a reply to message #639664] |
Tue, 14 July 2015 01:06  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
THere is no purprse in the join, you hould remove it. I am surprised that Oracle does not ignore it, like this:
orclz>
orclz> set autot on exp
orclz>
orclz> select e.* from emp e,dept d where e.deptno=d.deptno and d.deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17:00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23:00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPTNO"=10)
orclz> |
|
|
|
| Re: Change in he execution plan [message #639677 is a reply to message #639667] |
Tue, 14 July 2015 03:03 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Oracle can only edit out the join if there's a foreign key on that column from e to d.
Otherwise you could have rows in e where col = 20 which don't have corresponding rows in d.
|
|
|
|
| Re: Change in he execution plan [message #639679 is a reply to message #639677] |
Tue, 14 July 2015 03:19 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You are correct:orclz> alter table emp drop constraint fk_deptno;
Table altered.
orclz> set autot on exp
orclz> select e.* from emp e,dept d where e.deptno=d.deptno and d.deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17:00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23:00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 130586116
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 123 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPTNO"=10)
3 - filter("E"."DEPTNO"=10)
orclz>
How about trying an equivalent (I think) SQL that might push te CBO to a different plan:
orclz> select * from emp where deptno=10 and exists(select 1 from dept where dept.deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17:00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23:00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1783302997
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "DEPT" "DEPT" WHERE
"DEPT"."DEPTNO"=10))
2 - filter("DEPTNO"=10)
3 - access("DEPT"."DEPTNO"=10)
orclz>
|
|
|
|
| Re: Change in he execution plan [message #639734 is a reply to message #639664] |
Tue, 14 July 2015 22:08 |
jinga
Messages: 116
Registered: January 2003
|
Senior Member |
|
|
|
While it generated different explain plans in each of the two oracle instances, it was puzzling to see different response times ( drastic difference) for three of us. One got 11 seconds( to bring 300000+ rows to dbartisan), another dba got 170 seconds and another dba got 55 seconds. three of us tried it different times of the day and not at the same time. Any one point out why varied response times?
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
