Home » SQL & PL/SQL » SQL & PL/SQL » SQL Execution Plan (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
SQL Execution Plan [message #659599] Mon, 23 January 2017 12:20 Go to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
Hello Everyone,

Could you please help with one of the problem I am facing in Exadata Environment. I was able to create a test case to replicate my issue. The problem is with Plan which is generated below. I am not sure why generated plan show only 1 row for X2 table, where as it has 1,000,000 rows.


SQL> SELECT * FROM v$version;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production    
PL/SQL Release 11.2.0.4.0 - Production                                          
CORE	11.2.0.4.0	Production                                                        
TNS for Linux: Version 11.2.0.4.0 - Production                                  
NLSRTL Version 11.2.0.4.0 - Production                                          


SQL> SET PAGESIZE 1000
SQL> CLEAR SCREEN
SQL> SET ECHO ON
SQL> DROP TABLE X1 PURGE;

Table X1 dropped.

Elapsed: 00:00:00.224
SQL> DROP TABLE X2 PURGE;

Table X2 dropped.

Elapsed: 00:00:00.051
SQL> DROP TABLE X3 PURGE;

Table X3 dropped.

Elapsed: 00:00:00.237
SQL> CREATE TABLE X1 (ID NUMBER, SLS_AMT NUMBER(10,2));

Table X1 created.

Elapsed: 00:00:00.015
SQL> INSERT INTO X1 SELECT LEVEL, DBMS_RANDOM.VALUE(1,1000)  FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:09.520
SQL> CREATE TABLE X2 (ID NUMBER, SLS_DT DATE);

Table X2 created.

Elapsed: 00:00:00.022
SQL> INSERT INTO X2 SELECT LEVEL, SYSDATE + (LEVEL*.0025)    FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:01.749
SQL> CREATE TABLE X3 (ID NUMBER, SLS_DT DATE, TXN_TYP NUMBER);

Table X3 created.

Elapsed: 00:00:00.023
SQL> INSERT INTO X3 SELECT LEVEL, SYSDATE + (LEVEL*.0025), 1 FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:01.873
SQL> INSERT INTO X3 SELECT LEVEL, SYSDATE + (LEVEL*.0025), 2 FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:01.822
SQL> INSERT INTO X3 SELECT LEVEL, SYSDATE + (LEVEL*.0025), 3 FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:01.785
SQL> SELECT COUNT(1) FROM X1;

  COUNT(1)
----------
   1000000

Elapsed: 00:00:00.034
SQL> SELECT COUNT(1) FROM X2;

  COUNT(1)
----------
   1000000

Elapsed: 00:00:00.023
SQL> SELECT COUNT(1) FROM X3;

  COUNT(1)
----------
   3000000

Elapsed: 00:00:00.055
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'X1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'X2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'X3');

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
SELECT COUNT(1)
FROM   X1
       LEFT JOIN X2
              ON X1.ID = X2.ID
                 AND EXISTS (SELECT 1
                             FROM   X3
                             WHERE  X2.ID = X3.ID
                                    AND TXN_TYP = 1);

Plan FOR succeeded.

Elapsed: 00:00:00.009
SQL> SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 280163641                                                                          
                                                                                                    
--------------------------------------------------------------------------------------              
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |              
--------------------------------------------------------------------------------------              
|   0 | SELECT STATEMENT              |      |     1 |     5 |  3183M  (2)|999:59:59 |              
|   1 |  SORT AGGREGATE               |      |     1 |     5 |            |          |              
|   2 |   NESTED LOOPS OUTER          |      |  1000K|  4882K|  3183M  (2)|999:59:59 |              
|   3 |    TABLE ACCESS STORAGE FULL  | X1   |  1000K|  4882K|   586   (2)| 00:00:08 |              
|   4 |    VIEW                       |      |     1 |       |  3184   (2)| 00:00:39 |              
|*  5 |     HASH JOIN SEMI            |      |     1 |    13 |  3184   (2)| 00:00:39 |  -- Show 1 Row, but it has  1,000,000 Rows
|*  6 |      TABLE ACCESS STORAGE FULL| X2   |     1 |     5 |   690   (2)| 00:00:09 |              
|*  7 |      TABLE ACCESS STORAGE FULL| X3   |  1000K|  7812K|  2490   (2)| 00:00:30 |              
--------------------------------------------------------------------------------------              
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   5 - access("X2"."ID"="X3"."ID")                                                                  
   6 - filter("X1"."ID"="X2"."ID")                                                                  
   7 - storage("TXN_TYP"=1)                                                                         
       filter("TXN_TYP"=1)                                                                          

 22 rows selected 

Elapsed: 00:00:00.027


Execution plan on OEM

/forum/fa/13422/0/

I can change this SQL to alter the SQL Plan, but please help me to understand if I am see any thing unusual.

Regards
Trivendra

Re: SQL Execution Plan [message #659600 is a reply to message #659599] Mon, 23 January 2017 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because it knows there can be only 1 X2 with same ID than X1 one, see the filter at line 6.

Re: SQL Execution Plan [message #659601 is a reply to message #659600] Mon, 23 January 2017 12:58 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
Thanks Michel, Thanks for your input.

I ran same test case in non-exadata box(local - Oracle 12c) and it show a different plan and using the HASH joins instead of NESTED LOOP. I know my local (non-exadata box) and Production Exadata box runs on different hardware and Oracle Software, but can they so different in generating the execution plan.

SQL> SELECT BANNER FROM V$VERSION;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    
PL/SQL Release 12.1.0.2.0 - Production                                          
CORE	12.1.0.2.0	Production                                                        
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                         
NLSRTL Version 12.1.0.2.0 - Production                      

SQL> SET PAGESIZE 1000
SQL> CLEAR SCREEN
SQL> SET ECHO ON
SQL> DROP TABLE X1 PURGE;

Table X1 dropped.

Elapsed: 00:00:00.309
SQL> DROP TABLE X2 PURGE;

Table X2 dropped.

Elapsed: 00:00:00.018
SQL> DROP TABLE X3 PURGE;

Table X3 dropped.

Elapsed: 00:00:08.139
SQL> CREATE TABLE X1 (ID NUMBER, SLS_AMT NUMBER(10,2));

Table X1 created.

Elapsed: 00:00:00.016
SQL> INSERT INTO X1 SELECT LEVEL, DBMS_RANDOM.VALUE(1,1000)  FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:10.230
SQL> CREATE TABLE X2 (ID NUMBER, SLS_DT DATE);

Table X2 created.

Elapsed: 00:00:00.014
SQL> INSERT INTO X2 SELECT LEVEL, SYSDATE + (LEVEL*.0025)    FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:03.662
SQL> CREATE TABLE X3 (ID NUMBER, SLS_DT DATE, TXN_TYP NUMBER);

Table X3 created.

Elapsed: 00:00:00.010
SQL> INSERT INTO X3 SELECT LEVEL, SYSDATE + (LEVEL*.0025), 1 FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:01.220
SQL> INSERT INTO X3 SELECT LEVEL, SYSDATE + (LEVEL*.0025), 2 FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:02.447
SQL> INSERT INTO X3 SELECT LEVEL, SYSDATE + (LEVEL*.0025), 3 FROM DUAL CONNECT BY LEVEL<=1000000;

1,000,000 rows inserted.

Elapsed: 00:00:01.248
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.014
SQL> SELECT COUNT(1) FROM X1;

  COUNT(1)
----------
   1000000

Elapsed: 00:00:00.015
SQL> SELECT COUNT(1) FROM X2;

  COUNT(1)
----------
   1000000

Elapsed: 00:00:00.016
SQL> SELECT COUNT(1) FROM X3;

  COUNT(1)
----------
   3000000

Elapsed: 00:00:00.029
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'X1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'X2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'X3');

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
SELECT COUNT(1)
FROM   X1
       LEFT JOIN X2
              ON X1.ID = X2.ID
                 AND EXISTS (SELECT 1
                             FROM   X3
                             WHERE  X2.ID = X3.ID
                                    AND TXN_TYP = 1);

Plan FOR succeeded.

Elapsed: 00:00:00.010
SQL> SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 1213360556                                                                         
                                                                                                    
-------------------------------------------------------------------------------------------------   
| Id  | Operation             | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |   
-------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT      |                 |     1 |    18 |       |  7157   (1)| 00:00:01 |   
|   1 |  SORT AGGREGATE       |                 |     1 |    18 |       |            |          |   
|*  2 |   HASH JOIN OUTER     |                 |  1000K|    17M|    16M|  7157   (1)| 00:00:01 |   
|   3 |    TABLE ACCESS FULL  | X1              |  1000K|  4882K|       |   584   (1)| 00:00:01 |   
|   4 |    VIEW               | VW_DCL_6A88A1F5 |   704K|  8942K|       |  4926   (1)| 00:00:01 |   
|*  5 |     HASH JOIN SEMI    |                 |   704K|  8942K|    16M|  4926   (1)| 00:00:01 |   
|   6 |      TABLE ACCESS FULL| X2              |  1000K|  4882K|       |   686   (1)| 00:00:01 |   
|*  7 |      TABLE ACCESS FULL| X3              |  1000K|  7812K|       |  2480   (1)| 00:00:01 |   
-------------------------------------------------------------------------------------------------   
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - access("X1"."ID"="ID"(+))                                                                    
   5 - access("X2"."ID"="X3"."ID")                                                                  
   7 - filter("TXN_TYP"=1)                                                                          

 21 rows selected 

Elapsed: 00:00:00.038

Re: SQL Execution Plan [message #659602 is a reply to message #659601] Mon, 23 January 2017 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, they can and they will.

When I said "it knows", I was talking about the storage system, something that can't know a non Exadata system (unless you compute histogram columns statistics).

[Updated on: Mon, 23 January 2017 13:53]

Report message to a moderator

Re: SQL Execution Plan [message #659603 is a reply to message #659602] Mon, 23 January 2017 13:18 Go to previous message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
Thanks Michel for pointer. Let me dig deep. Smile
Previous Topic: Edition Based Redefinition - Triggers on Editioning Views
Next Topic: scrambling the data for a column in a tables
Goto Forum:
  


Current Time: Thu Mar 28 04:59:38 CDT 2024