Home » RDBMS Server » Performance Tuning » Change in he execution plan (12c)
Change in he execution plan [message #639664] Mon, 13 July 2015 20:49 Go to next message
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 #639665 is a reply to message #639664] Mon, 13 July 2015 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
jinga wrote on Mon, 13 July 2015 18:49
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.


The most likely reason you are seeing something different is because something is different.

How can we reproduce what you report?

Might this be OS dependent or Oracle version dependent?
Re: Change in he execution plan [message #639667 is a reply to message #639664] Tue, 14 July 2015 01:06 Go to previous messageGo to next message
John Watson
Messages: 7608
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 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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 Go to previous messageGo to next message
John Watson
Messages: 7608
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>
and I can understand why (though it took me a while).
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 Go to previous messageGo to next message
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?
Re: Change in he execution plan [message #639735 is a reply to message #639734] Tue, 14 July 2015 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
>Any one point out why varied response times?
SQL running against multi-user database can be impacted by other SQL running concurrently.
At one point in time the RAM may be bottleneck, at another time CPU could be the bottleneck or at a different time the disk subsystem could be the bottleneck.
Re: Change in he execution plan [message #639740 is a reply to message #639734] Wed, 15 July 2015 01:30 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
You keep talking about execution plans, but you do not show any.

What happened when you tried either of the re-writes that I suggested?
Re: Change in he execution plan [message #639741 is a reply to message #639740] Wed, 15 July 2015 02:48 Go to previous message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is the join to table2 actually necessary?
Can there be rows in table1 without matching rows in table2 and do you care for the purposes of this query?
Previous Topic: Query not have much cost on Explain Plan but very very slow
Next Topic: Help for tuning the sql Query
Goto Forum:
  


Current Time: Mon Oct 15 17:15:02 CDT 2018