CODEWITHSUNDEEP
sqloracle-database
Elvis Lou
Elvis Lou

Reputation: 172

How can I optimize the SELECT statement running on an Oracle database?

I have a SELECT statement in ORACLE:

SELECT COUNT(DISTINCT ds1.endpoint_msisdn)                  multiple30,
       dss1.service,
       dss1.endpoint_provisioning_id,
       dss1.company_scope,
       Nvl(x.subscription_status, dss1.subscription_status) subscription_status
FROM   daily_summary ds1
       join daily_summary ds2
         ON ds1.endpoint_msisdn = ds2.endpoint_msisdn,
       daily_summary_static dss1,
       daily_summary_static dss2,
       (SELECT NULL subscription_status
        FROM   dual
        UNION ALL
        SELECT -2 subscription_status
        FROM   dual) x
WHERE  ds1.summary_ts >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
       AND ds1.summary_ts <= To_date('10-04-2012', 'dd-mm-yyyy')
       AND dss1.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
       AND dss1.last_active <= To_date('10-04-2012', 'dd-mm-yyyy')
       AND dss2.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
       AND dss2.last_active <= To_date('10-04-2012', 'dd-mm-yyyy')
       AND dss1.service <> dss2.service
       AND ( dss1.company_scope = 2
              OR dss1.company_scope = 5 )
       AND ( dss2.company_scope = 2
              OR dss2.company_scope = 5 )
       AND dss1.company_scope = dss2.company_scope
       AND ds1.endpoint_noc_id = dss1.endpoint_noc_id
       AND ds1.endpoint_host_id = dss1.endpoint_host_id
       AND ds1.endpoint_instance_id = dss1.endpoint_instance_id
       AND ds2.endpoint_noc_id = dss2.endpoint_noc_id
       AND ds2.endpoint_host_id = dss2.endpoint_host_id
       AND ds2.endpoint_instance_id = dss2.endpoint_instance_id
       AND dss1.endpoint_provisioning_id = dss2.endpoint_provisioning_id
       AND Least(1, ds1.total_actions) = 1
       AND Least(1, ds2.total_actions) = 1
GROUP  BY dss1.service,
          dss1.endpoint_provisioning_id,
          dss1.company_scope,
          Nvl(x.subscription_status, dss1.subscription_status);

This query took about 26 minutes to return in my environment, but if I remove the section:

dss1.last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND
                   dss1.last_active <= to_date('10-04-2012','dd-mm-yyyy') AND
                    dss2.last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND
                   dss2.last_active <= to_date('10-04-2012','dd-mm-yyyy') AND

it only took 20 seconds to run.

We have index on the column last_active, I don't know why the section slow down the performance so much? any ideas?

if i run this , it was very fast.

select * from daily_summary_static where last_active >= to_date('10-04-2012','dd-mm-yyyy') - 30 AND last_active <= to_date('10-04-2012','dd-mm-yyyy');

Explain plan for the statement, i don't see any full table scan here, don't know why it run so slowly:

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                      |     1 |   119 |   204   (3)| 00:00:03 |
|   1 |  SORT GROUP BY                          |                      |     1 |   119 |   204   (3)| 00:00:03 |
|   2 |   MERGE JOIN CARTESIAN                  |                      |     1 |   119 |   203   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS BY INDEX ROWID          | DAILY_SUMMARY        |     1 |    27 |    11   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                      |     1 |   116 |   199   (3)| 00:00:03 |
|*  5 |      HASH JOIN                          |                      |     3 |   267 |   178   (3)| 00:00:03 |
|*  6 |       HASH JOIN                         |                      |     1 |    65 |   140   (2)| 00:00:02 |
|   7 |        TABLE ACCESS BY INDEX ROWID      | DAILY_SUMMARY_STATIC |    61 |  1647 |    37   (3)| 00:00:01 |
|   8 |         BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |          |
|   9 |          BITMAP AND                     |                      |       |       |            |          |
|  10 |           BITMAP CONVERSION FROM ROWIDS |                      |       |       |            |          |
|  11 |            SORT ORDER BY                |                      |       |       |            |          |
|* 12 |             INDEX RANGE SCAN            | DSS_LAST_ACTIVE      |   560 |       |     3   (0)| 00:00:01 |
|  13 |           BITMAP OR                     |                      |       |       |            |          |
|  14 |            BITMAP CONVERSION FROM ROWIDS|                      |       |       |            |          |
|* 15 |             INDEX RANGE SCAN            | DSS_C_SCOPE_IDX      |   560 |       |    18   (0)| 00:00:01 |
|  16 |            BITMAP CONVERSION FROM ROWIDS|                      |       |       |            |          |
|* 17 |             INDEX RANGE SCAN            | DSS_C_SCOPE_IDX      |   560 |       |     1   (0)| 00:00:01 |
|  18 |        TABLE ACCESS BY INDEX ROWID      | DAILY_SUMMARY        |  1773 | 67374 |   102   (0)| 00:00:02 |
|* 19 |         INDEX RANGE SCAN                | DS_DAILY_ACTIVE_IDX  |  1767 |       |    14   (0)| 00:00:01 |
|  20 |       TABLE ACCESS BY INDEX ROWID       | DAILY_SUMMARY_STATIC |    61 |  1464 |    37   (3)| 00:00:01 |
|  21 |        BITMAP CONVERSION TO ROWIDS      |                      |       |       |            |          |
|  22 |         BITMAP AND                      |                      |       |       |            |          |
|  23 |          BITMAP CONVERSION FROM ROWIDS  |                      |       |       |            |          |
|  24 |           SORT ORDER BY                 |                      |       |       |            |          |
|* 25 |            INDEX RANGE SCAN             | DSS_LAST_ACTIVE      |   560 |       |     3   (0)| 00:00:01 |
|  26 |          BITMAP OR                      |                      |       |       |            |          |
|  27 |           BITMAP CONVERSION FROM ROWIDS |                      |       |       |            |          |
|* 28 |            INDEX RANGE SCAN             | DSS_C_SCOPE_IDX      |   560 |       |    18   (0)| 00:00:01 |
|  29 |           BITMAP CONVERSION FROM ROWIDS |                      |       |       |            |          |
|* 30 |            INDEX RANGE SCAN             | DSS_C_SCOPE_IDX      |   560 |       |     1   (0)| 00:00:01 |
|* 31 |      INDEX RANGE SCAN                   | DS_PKEY              |     8 |       |     2   (0)| 00:00:01 |
|  32 |    BUFFER SORT                          |                      |     2 |     6 |   193   (3)| 00:00:03 |
|  33 |     VIEW                                |                      |     2 |     6 |     4   (0)| 00:00:01 |
|  34 |      UNION-ALL                          |                      |       |       |            |          |
|  35 |       FAST DUAL                         |                      |     1 |       |     2   (0)| 00:00:01 |
|  36 |       FAST DUAL                         |                      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Upvotes: 1

Views: 1456

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Since this section actually makes it slower:

AND dss1.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND dss1.last_active <= To_date('10-04-2012', 'dd-mm-yyyy')
AND dss2.last_active >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND dss2.last_active <= To_date('10-04-2012', 'dd-mm-yyyy')

it seems likely to me that the index on last_active is used when it shouldn't, i.e. it prevents using another, more useful index. My bet is on another index that would be used for joining dss1 and dss2, but in fact it doesn't really matter.

Therefore, I propose to disable index usage by deliberately changing the block like this so the query optimizer can no longer use the index:

AND (dss1.last_active+0) >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND (dss1.last_active+0) <= To_date('10-04-2012', 'dd-mm-yyyy')
AND (dss2.last_active+0) >= To_date('10-04-2012', 'dd-mm-yyyy') - 30
AND (dss2.last_active+0) <= To_date('10-04-2012', 'dd-mm-yyyy')

Upvotes: 2

Related Questions