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 |
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
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 #659601 is a reply to message #659600] |
Mon, 23 January 2017 12:58 |
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 |
|
Michel Cadot
Messages: 68694 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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:36:46 CDT 2024
|