CODEWITHSUNDEEP
sqloracle-databaseoracle11gindexingoracle-sqldeveloper
jimagic
jimagic

Reputation: 4065

Oracle SQL query taking too long like 60 minutes to execute

I have one query that taking too long, that is 60 minutes. I am programmer but i am not quite sure if all the table are indexes out.

here is the query, any table that ends with _V is view:

SELECT prod_eff.facility
     , prod_eff.product
     , (SELECT MIN (cbv1.bucket_header) 
          FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
         WHERE cbv1.version_id  = 1
           AND cbv1.min_datetime >= prod_eff.EFF_START_DATETIME
       ) min_eff
     , (SELECT MAX (cbv1.bucket_header)
          FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
         WHERE cbv1.version_id = 1
           AND cbv1.min_datetime < prod_eff.EFF_END_DATETIME
       ) max_eff
     , 1 valid
  FROM (
        SELECT pf.product
             , pf.facility
             , pf.eff_start_datetime
             , pf.eff_end_datetime
          FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
             , AMD_OARS.MASTER_FACILITY f
         WHERE pf.version_id = 114847
           AND pf.facility     = f.facility
           AND f.facility     != 'NONE'
           AND f.validated     = 1
       ) prod_eff
     , AMD_OARS.TEMP_SELECTED_PRODUCT tsp
 WHERE tsp.product = prod_eff.product
   AND (prod_eff.EFF_START_DATETIME BETWEEN 
            to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
            to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy  hh24:mi:ss.ff3') OR 
        prod_eff.EFF_END_DATETIME BETWEEN 
            to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
            to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') 
       )
 ORDER BY 1, 2, 3, 4 ;

This is the query I use to find out if some of the table are index or not. 

select table_name 
from dba_tables 
where (owner, table_name) not in (select table_owner, table_name from dba_indexes)
and table_name = 'TEMP_SELECTED_PRODUCT';

I am sorry I am newbie in database thing. I only know basic thing like sql join and writing basic queries. Any help would be appreciate. I am using Oracle SQL Developer.

can anybody please guide me if I am doing anything wrong? I would appreciate it.

UPDATE: I saw this video on how to execute 'explain plan for' and here is what i got result

Plan hash value: 1792060973

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |     1 |    78 |     4  (25)| 00:00:01 |
|   1 |  SORT AGGREGATE                |                      |     1 |    66 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | TEMP_CURRENT_BUCKETS |     1 |    66 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | TCB_PK               |     1 |       |     1   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE                |                      |     1 |    66 |            |          |
|*  5 |   TABLE ACCESS BY INDEX ROWID  | TEMP_CURRENT_BUCKETS |     1 |    66 |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | TCB_PK               |     1 |       |     1   (0)| 00:00:01 |
|   7 |  SORT ORDER BY                 |                      |     1 |    78 |     4  (25)| 00:00:01 |
|   8 |   NESTED LOOPS                 |                      |     1 |    78 |     3   (0)| 00:00:01 |
|   9 |    NESTED LOOPS                |                      |     1 |    69 |     2   (0)| 00:00:01 |
|  10 |     INDEX FULL SCAN            | TSP_PK               |     1 |    18 |     1   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID| BOM_PRODUCT_FACILITY |     1 |    51 |     1   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN          | BPF_PK               |     1 |       |     1   (0)| 00:00:01 |
|* 13 |    TABLE ACCESS BY INDEX ROWID | MASTER_FACILITY      |     1 |     9 |     1   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN          | MF_F_PK              |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CB"."MIN_DATETIME">=:B1)
   3 - access("CB"."VERSION_ID"=1)
   5 - filter("CB"."MIN_DATETIME"<:B1)
   6 - access("CB"."VERSION_ID"=1)
  11 - filter(INTERNAL_FUNCTION("PF"."EFF_START_DATETIME")>=TIMESTAMP' 2014-07-27 
              00:00:01.000000000' AND INTERNAL_FUNCTION("PF"."EFF_START_DATETIME")<=TIMESTAMP' 2015-12-20 
              00:00:00.000000000' OR INTERNAL_FUNCTION("PF"."EFF_END_DATETIME")>=TIMESTAMP' 2014-07-27 
              00:00:01.000000000' AND INTERNAL_FUNCTION("PF"."EFF_END_DATETIME")<=TIMESTAMP' 2015-12-20 
              00:00:00.000000000')
  12 - access("PF"."VERSION_ID"=114847 AND "TSP"."PRODUCT"="PF"."PRODUCT")
       filter("TSP"."PRODUCT"="PF"."PRODUCT" AND "PF"."FACILITY"<>'NONE')
  13 - filter("F"."VALIDATED"=1)
  14 - access("PF"."FACILITY"="F"."FACILITY")
       filter("F"."FACILITY"<>'NONE')

Upvotes: 0

Views: 22914

Answers (2)

Greg
Greg

Reputation: 233

SELECT prod_eff.facility
     , prod_eff.product
     , (SELECT MIN (cbv1.bucket_header) 
          FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
         WHERE cbv1.version_id  = 1
           AND cbv1.min_datetime >= prod_eff.EFF_START_DATETIME
       ) min_eff
     , (SELECT MAX (cbv1.bucket_header)
          FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
         WHERE cbv1.version_id = 1
           AND cbv1.min_datetime < prod_eff.EFF_END_DATETIME
       ) max_eff
     , 1 valid
  FROM (
        WITH prod_fac AS (
            SELECT pf.product
                 , pf.facility
                 , pf.eff_start_datetime
                 , pf.eff_end_datetime
              FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
             WHERE pf.version_id = 114847
               AND (pf.EFF_START_DATETIME BETWEEN 
                       to_timestamp('07/27/2014 00:00:01.000',
                                    'mm/dd/yyyy hh24:mi:ss.ff3') AND 
                       to_timestamp('12/20/2015 00:00:00.000',
                                    'mm/dd/yyyy  hh24:mi:ss.ff3') OR 
                    pf.EFF_END_DATETIME BETWEEN 
                       to_timestamp('07/27/2014 00:00:01.000',
                                    'mm/dd/yyyy hh24:mi:ss.ff3') AND 
                       to_timestamp('12/20/2015 00:00:00.000',
                                    'mm/dd/yyyy hh24:mi:ss.ff3') 
                   )
        )
        SELECT pf2.product
             , pf2.facility
             , pf2.eff_start_datetime
             , pf2.eff_end_datetime
          FROM prod_fac pf2, AMD_OARS.MASTER_FACILITY f
           AND pf2.facility = f.facility
           AND f.facility  != 'NONE'
           AND f.validated = 1
       ) prod_eff, AMD_OARS.TEMP_SELECTED_PRODUCT tsp
  WHERE prod_eff.product = tsp.product
 ORDER BY 1, 2, 3, 4 ;
  • I'd try and get rid of as much data as I could before the final join to your tsp table. The date range checks move to the inline view.
  • I don't like how you flipped the table order between the from class and the order in your where clause. tsp.product = prod_eff.product = > prod_eff.product = tsp.product
  • I like how you are using the between clause with the to_timestamp function. Many people try to trunc() the time and that blows the index use.
  • If you can somehow remove the >= and < in your two scalar queries that would be great. The optimizer makes horrible guesses for less than or greater than. Can you somehow work in a between clause with 4000 years before or 4000 years after?

Upvotes: 1

neshkeev
neshkeev

Reputation: 6476

During the checking your query I ran into this

AND (prod_eff.EFF_START_DATETIME BETWEEN 
        to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
        to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy  hh24:mi:ss.ff3') OR 
    prod_eff.EFF_END_DATETIME BETWEEN 
        to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
        to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') 
   )

As I see they are the same, i guess this part damages your query performance the most, get rid one of them, rewrite your query like this:

SELECT prod_eff.facility
     , prod_eff.product
     , (SELECT MIN (cbv1.bucket_header) 
          FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
         WHERE cbv1.version_id  = 1
           AND cbv1.min_datetime >= prod_eff.EFF_START_DATETIME
       ) min_eff
     , (SELECT MAX (cbv1.bucket_header)
          FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
         WHERE cbv1.version_id = 1
           AND cbv1.min_datetime < prod_eff.EFF_END_DATETIME
       ) max_eff
     , 1 valid
  FROM (
        SELECT pf.product
             , pf.facility
             , pf.eff_start_datetime
             , pf.eff_end_datetime
          FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
             , AMD_OARS.MASTER_FACILITY f
         WHERE pf.version_id = 114847
           AND pf.facility     = f.facility
           AND f.facility     != 'NONE'
           AND f.validated     = 1
       ) prod_eff
     , AMD_OARS.TEMP_SELECTED_PRODUCT tsp
 WHERE tsp.product = prod_eff.product
   AND prod_eff.EFF_START_DATETIME BETWEEN 
            to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
            to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy  hh24:mi:ss.ff3')
 ORDER BY 1, 2, 3, 4 ;

Upvotes: 0

Related Questions