Sql query takes more than 2 minutes to execute, whereas it takes less than 30 seconds to execute for
The Sql query takes 2 minutes to execute for the first time, whereas at the second time it takes only 30 seconds to execute and the third time it takes only 7 seconds to execute in production environment.
What is the solution to execute the query in less than 30 seconds for the first time.
Find the execution plan for this query:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 797 | 1032 (1)| 00:00:13 |
| 1 | SORT ORDER BY | | 1 | 797 | 1032 (1)| 00:00:13 |
| 2 | HASH GROUP BY | | 1 | 797 | 1032 (1)| 00:00:13 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1 | 797 | 1030 (1)| 00:00:13 |
| 5 | NESTED LOOPS | | 1 | 762 | 1029 (1)| 00:00:13 |
| 6 | NESTED LOOPS | | 1 | 740 | 1028 (1)| 00:00:13 |
| 7 | NESTED LOOPS | | 1 | 663 | 1027 (1)| 00:00:13 |
| 8 | NESTED LOOPS | | 1 | 561 | 1026 (1)| 00:00:13 |
| 9 | NESTED LOOPS | | 1 | 488 | 1025 (1)| 00:00:13 |
| 10 | NESTED LOOPS | | 1 | 465 | 1024 (1)| 00:00:13 |
| 11 | NESTED LOOPS | | 4 | 1568 | 1023 (1)| 00:00:13 |
| 12 | TABLE ACCESS BY INDEX ROWID| S_LST_OF_VAL