SQL & PL/SQL

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select query running too long/ really slow ..

Kenny - SnL!-OracleApr 25 2017 — edited Jun 13 2017

Hi Techies,

The below 2 queries are running too slow, 1st one takes ~10 hours (47000 seconds), the second one takes ~1 hr (4300 seconds).

I tried using Parallel hints within the queries, though the execution plan looks good query still takes hours to execute.

DB VERSION: 11.2.0.4,

OS - Linux: Red Hat Enterprise Linux Server release 6.7 (Santiago)

Table Stats are latest

No Delete/Leaf nodes

Info about TABLES involved ---

1) PAYMENTS - 420 GB in size

2) payments2 has around 500 partitions

QUERY1 :

=-=-=-=-=

Here is the explain Plan [without Parallel Hint ]:

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1485035457

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

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

| 0 | SELECT STATEMENT | | 1 | 14 | 14M (1)| 48:46:57 | | |

| 1 | SORT AGGREGATE | | 1 | 14 | | | | |

|* 2 | FILTER | | | | | | | |

| 3 | TABLE ACCESS FULL | PAYMENTS_OLD_APR20_2017 | 1048M| 13G| 14M (1)| 48:43:47 | | |

| 4 | MERGE JOIN | | 10 | 90 | 7 (0)| 00:00:01 | | |

| 5 | PARTITION HASH SINGLE | | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY |

PLAN_TABLE_OUTPUT

|* 6 | INDEX RANGE SCAN | PAYMENTS2_FID | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY |

|* 7 | SORT JOIN | | 14616 | 73080 | 3 (0)| 00:00:01 | | |

| 8 | BITMAP CONVERSION TO ROWIDS| | 14616 | 73080 | 3 (0)| 00:00:01 | | |

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

And QUERY 1 with PARALLEL HINT:

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

PLAN_TABLE_OUTPUT

Plan hash value: 3420827645

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

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

| 0 | SELECT STATEMENT | | 1 | 14 | 523K (1)| 01:44:38 | | | | | |

| 1 | SORT AGGREGATE | | 1 | 14 | | | | | | | |

|* 2 | FILTER | | | | | | | | | | |

| 3 | PX COORDINATOR | | | | | | | | | | |

| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1048M| 13G| 507K (1)| 01:41:27 | | | Q1,00 | P->S | QC (RAND) |

| 5 | PX BLOCK ITERATOR | | 1048M| 13G| 507K (1)| 01:41:27 | | | Q1,00 | PCWC | |

PLAN_TABLE_OUTPUT

| 6 | TABLE ACCESS FULL | PAYMENTS_OLD_APR20_2017 | 1048M| 13G| 507K (1)| 01:41:27 | | | Q1,00 | PCWP | |

| 7 | MERGE JOIN | | 10 | 90 | 7 (0)| 00:00:01 | | | | | |

| 8 | PARTITION HASH SINGLE | | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY | | | |

|* 9 | INDEX RANGE SCAN | PAYMENTS2_FID | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY | | | |

|* 10 | SORT JOIN | | 14616 | 73080 | 3 (0)| 00:00:01 | | | | | |

| 11 | BITMAP CONVERSION TO ROWIDS| | 14616 | 73080 | 3 (0)| 00:00:01 | | | | | |

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

30 rows selected.

QUERY 2 EXPLAIN PLAN :

=-=-=-=-=-=-=-=-=-=-=-=-=

PLAN_TABLE_OUTPUT

Plan hash value: 2847235613

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 8183K (1)| 27:16:45 | | |

| 1 | SORT AGGREGATE | | 1 | 10 | | | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION HASH ALL | | 3943M| 36G| 8132K (1)| 27:06:31 | 1 | 512 |

| 4 | TABLE ACCESS FULL | PAYMENTS2 | 3943M| 36G| 8132K (1)| 27:06:31 | 1 | 512 |

| 5 | MERGE JOIN | | 10 | 90 | 7 (0)| 00:00:01 | | |

| 6 | PARTITION HASH SINGLE | | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY |

|* 7 | INDEX RANGE SCAN | PAYMENTS2_FID | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY |

|* 8 | SORT JOIN | | 14616 | 73080 | 3 (0)| 00:00:01 | | |

| 9 | BITMAP CONVERSION TO ROWIDS| | 14616 | 73080 | 3 (0)| 00:00:01 | | |

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

QUERY2 WITH PARALLEL HINT:

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

PLAN_TABLE_OUTPUT

Plan hash value: 3329289893

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 1556K (1)| 05:11:14 | | | | | |

| 1 | SORT AGGREGATE | | 1 | 10 | | | | | | | |

|* 2 | FILTER | | | | | | | | | | |

| 3 | PX COORDINATOR | | | | | | | | | | |

| 4 | PX SEND QC (RANDOM) | :TQ10000 | 3943M| 36G| 1504K (1)| 05:01:00 | | | Q1,00 | P->S | QC (RAND) |

| 5 | PX PARTITION HASH ALL | | 3943M| 36G| 1504K (1)| 05:01:00 | 1 | 512 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS FULL | PAYMENTS2 | 3943M| 36G| 1504K (1)| 05:01:00 | 1 | 512 | Q1,00 | PCWP | |

| 7 | MERGE JOIN | | 10 | 90 | 7 (0)| 00:00:01 | | | | | |

| 8 | PARTITION HASH SINGLE | | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY | | | |

|* 9 | INDEX RANGE SCAN | PAYMENTS2_FID | 2 | 8 | 4 (0)| 00:00:01 | KEY | KEY | | | |

|* 10 | SORT JOIN | | 14616 | 73080 | 3 (0)| 00:00:01 | | | | | |

| 11 | BITMAP CONVERSION TO ROWIDS| | 14616 | 73080 | 3 (0)| 00:00:01 | | | | | |

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

29 rows selected.

This post has been answered by John Spencer on Apr 25 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on May 29 2017

Added on Apr 25 2017

#pl-sql, #sql-pl-sql

16 comments

15,243 views