Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ExaData vs RAC : Execution Plan huge difference.

WeGoOrclJun 14 2017 — edited Oct 13 2017

Hello Everyone,

I am not sure if this would be the right forum to as this question, but please help me in-case if you have some thought.

We are using Exadata X6 Half Rack box and when we are running SQLs on the database, we are seeing execution plan not correct as they should be. I have created a test case to replicate this on Exadata Machine (11g) and Non-Exadada Machine (RAC 12g local). The RAC plan is much more optimized than Exadata. This kind of behavior is happening most of the SQLs that we are running and it they are not performing well. I have an example to explain this situation.

Exadata -

SQL> CLEAR SCREEN

SQL> SET TIMING ON

SQL> SET LINE 100

SQL> SET PAGESIZE 1000

SQL> SET ECHO ON

SQL> SHOW SGA

Total System Global Area 42757922816 bytes

Fixed Size 2262656 bytes

Variable Size 29393684864 bytes

Database Buffers 13287555072 bytes

Redo Buffers 74420224 bytes

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

Elapsed: 007

Elapsed: 00:00:00.009

SQL> CREATE TABLE X1 (ID NUMBER, SLS_AMT NUMBER(10,2));

Table X1 created.

Elapsed: 269

Elapsed: 00:00:00.270

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

1,000,000 rows inserted.

Elapsed: 10.342

Elapsed: 00:00:10.342

SQL> CREATE TABLE X2 (ID NUMBER, SLS_DT DATE);

Table X2 created.

Elapsed: 184

Elapsed: 00:00:00.187

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

1,000,000 rows inserted.

Elapsed: 02.114

Elapsed: 00:00:02.116

SQL> CREATE TABLE X3 (ID NUMBER, SLS_DT DATE, TXN_TYP NUMBER);

Table X3 created.

Elapsed: 157

Elapsed: 00:00:00.158

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

1,000,000 rows inserted.

Elapsed: 02.162

Elapsed: 00:00:02.163

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

1,000,000 rows inserted.

Elapsed: 02.132

Elapsed: 00:00:02.133

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

1,000,000 rows inserted.

Elapsed: 02.106

Elapsed: 00:00:02.107

SQL> COMMIT;

Commit complete.

Elapsed: 439

Elapsed: 00:00:00.440

SQL> SELECT COUNT(1) FROM X1;

COUNT(1)

----------

1000000

Elapsed: 030

Elapsed: 00:00:00.031

SQL> SELECT COUNT(1) FROM X2;

COUNT(1)

----------

1000000

Elapsed: 030

Elapsed: 00:00:00.030

SQL> SELECT COUNT(1) FROM X3;

COUNT(1)

----------

3000000

Elapsed: 046

Elapsed: 00:00:00.047

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

PL/SQL procedure successfully completed.

Elapsed: 319

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

PL/SQL procedure successfully completed.

Elapsed: 236

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

PL/SQL procedure successfully completed.

Elapsed: 782

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: 023

Elapsed: 00:00:00.025

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 280163641

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 3241M (4)|999:59:59 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | NESTED LOOPS OUTER | | 1000K| 4882K| 3241M (4)|999:59:59 |

| 3 | TABLE ACCESS STORAGE FULL | X1 | 1000K| 4882K| 595 (3)| 00:00:08 |

| 4 | VIEW | | 1 | | 3241 (4)| 00:00:39 |

|* 5 | HASH JOIN SEMI | | 1 | 13 | 3241 (4)| 00:00:39 |

|* 6 | TABLE ACCESS STORAGE FULL| X2 | 1 | 5 | 702 (3)| 00:00:09 |

|* 7 | TABLE ACCESS STORAGE FULL| X3 | 1000K| 7812K| 2531 (3)| 00:00:31 |

--------------------------------------------------------------------------------------

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: 162

Elapsed: 00:00:00.164

It is trying to a nested loop, assuming there is only 1 rows in the X2 table. I run this query but I cancelled after 5 min.

I ran the same SQL on the Non-Exadata (local) 12c box, and here is what I got.

Non-Exadata

SQL> SHOW SGA

Total System Global Area 5033164800 bytes

Fixed Size 3842424 bytes

Variable Size 973082248 bytes

Database Buffers 4043309056 bytes

Redo Buffers 12931072 bytes

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

Elapsed: 015

Elapsed: 00:00:00.015

SQL> CREATE TABLE X1 (ID NUMBER, SLS_AMT NUMBER(10,2));

Table X1 created.

Elapsed: 281

Elapsed: 00:00:00.281

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

1,000,000 rows inserted.

Elapsed: 10.156

Elapsed: 00:00:10.156

SQL> CREATE TABLE X2 (ID NUMBER, SLS_DT DATE);

Table X2 created.

Elapsed:

Elapsed: 00:00:00.000

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

1,000,000 rows inserted.

Elapsed: 04.071

Elapsed: 00:00:04.071

SQL> CREATE TABLE X3 (ID NUMBER, SLS_DT DATE, TXN_TYP NUMBER);

Table X3 created.

Elapsed:

Elapsed: 00:00:00.016

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

1,000,000 rows inserted.

Elapsed: 04.741

Elapsed: 00:00:04.742

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

1,000,000 rows inserted.

Elapsed: 02.304

Elapsed: 00:00:02.305

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

1,000,000 rows inserted.

Elapsed: 04.813

Elapsed: 00:00:04.813

SQL> COMMIT;

Commit complete.

Elapsed: 009

Elapsed: 00:00:00.009

SQL> SELECT COUNT(1) FROM X1;

COUNT(1)

----------

1000000

Elapsed: 012

Elapsed: 00:00:00.013

SQL> SELECT COUNT(1) FROM X2;

COUNT(1)

----------

1000000

Elapsed: 012

Elapsed: 00:00:00.014

SQL> SELECT COUNT(1) FROM X3;

COUNT(1)

----------

3000000

Elapsed: 025

Elapsed: 00:00:00.025

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

PL/SQL procedure successfully completed.

Elapsed: 02.232

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

PL/SQL procedure successfully completed.

Elapsed: 221

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

PL/SQL procedure successfully completed.

Elapsed: 643

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: 035

Elapsed: 00:00:00.035

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 412553624

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 18 | | 7600 (1)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 18 | | | |

|* 2 | HASH JOIN OUTER | | 1000K| 17M| 16M| 7600 (1)| 00:00:01 |

| 3 | TABLE ACCESS FULL | X1 | 1000K| 4882K| | 562 (1)| 00:00:01 |

| 4 | VIEW | VW_DCL_6A88A1F5 | 1000K| 12M| | 5042 (1)| 00:00:01 |

|* 5 | HASH JOIN SEMI | | 1000K| 17M| 16M| 5042 (1)| 00:00:01 |

| 6 | TABLE ACCESS FULL | X2 | 1000K| 4882K| | 676 (1)| 00:00:01 |

| 7 | VIEW | VW_SQ_1 | 1000K| 12M| | 2370 (1)| 00:00:01 |

|* 8 | TABLE ACCESS FULL| X3 | 1000K| 7812K| | 2370 (1)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("X1"."ID"="ID"(+))

5 - access("X2"."ID"="ITEM_1")

8 - filter("TXN_TYP"=1)

22 rows selected

Elapsed: 146

Elapsed: 00:00:00.146

-- Execute SQL

SQL> 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);

COUNT(1)

----------

1000000

Elapsed: 915

Elapsed: 00:00:00.916

In both examples, we have same SQL but different plans. Could you please suggest some thing need to be tuned.

Regards

Trivendra

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 10 2017

Added on Jun 14 2017

#exadata, #performance-availability

7 comments

2,802 views