Solved

SQL taking long time

Posted on 2014-01-08
15
432 Views
Last Modified: 2014-01-13
Hello Experts,

I have the below SQL which is taking long time to run.

select /*+ index_desc(F VISIT_ID) */ * from (
  select 
    f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
  v.visit_id,
  TRUNC(v.start_date) start_date,
  
  DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active',
  'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed'  )
  
  /*
  CASE
      WHEN v.visit_status IN ('1','2','3')
      THEN 'Pending'
      WHEN v.visit_status IN ('4','5','6')
      THEN 'Active'
      WHEN v.visit_status = 'D'
      THEN 'Cancelled'
      WHEN v.visit_status IN ('8','9','10','11','12','13','14')
      THEN 'Completed'
    END       
    */
    AS filter_status,
    
    V.VISIT_STATUS as VISIT_STATUS
    ,MS_SRA_VISIT_SETUP_HOOK_PKG.ms_sra_get_link(v.dd_process_instance_id,'2109760788',v.visit_status,v.visit_id) LINK
   from 
  
  SR_MS_SRA_VISIT_SETUP V, 
(select 
    VISIT_ID
    ,FACILITY_ID,
    facility_name,
    city,
    STATE_PROVINCE,
    COUNTRY
 from SR_MS_SRA_FACILITY_INFO_PER p
 where DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
 ) F
 
 where F.VISIT_ID = V.VISIT_ID
 order by F.VISIT_ID desc
 
 ) 
 where FILTER_STATUS     = NVL(:STATUS,FILTER_STATUS)
 AND ((Regexp_Instr(NVL(facility_name,'NULL'),NVL(trim(:global_status), NVL(facility_name,'NULL')), 1,1,0,'i') > 0)
OR (Regexp_Instr(NVL(visit_id,'NULL'),NVL(trim(:global_status), NVL(visit_id,'NULL')),1,1,0,'i')              > 0)
OR (Regexp_Instr(NVL(visit_status,'NULL'),NVL(trim(:global_status), NVL(visit_status,'NULL')),1,1,0,'i')      > 0)
OR (Regexp_Instr(NVL(city,'NULL'),NVL(trim(:global_status), NVL(city,'NULL')),1,1,0,'i')                      > 0)
OR (regexp_instr(NVL(state_province,'NULL'),NVL(trim(:global_status), NVL(state_province,'NULL')),1,1,0,'i')  > 0)
or (REGEXP_INSTR(NVL(TO_CHAR(START_DATE),'null'),NVL(TRIM(:global_status), NVL(TO_CHAR(START_DATE),'null')),1,1,0,'i') > 0)
OR (REGEXP_INSTR(NVL(COUNTRY,'null'),NVL(TRIM(:global_status), NVL(COUNTRY,'null')),1,1,0,'i')                > 0))
 ;

Open in new window


I have the  below index created on this table:

create index fac_idx_1 on SR_MS_SRA_FACILITY_INFO_PER (visit_id) ; 

CREATE INDEX FAC_IDX_2 ON SR_MS_SRA_FACILITY_INFO_PER (DD_OBJECT_TYPE) ; 

create index vst_idx_1 on SR_MS_SRA_VISIT_SETUP (VISIT_ID) ;

Open in new window


Here is the explain plan for the SQL:

Plan hash value: 2402614722
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     1 |    97 |     8  (13)| 00:00:01 |
|   1 |  SORT ORDER BY                |                             |     1 |    97 |     8  (13)| 00:00:01 |
|   2 |   NESTED LOOPS                |                             |       |       |            |          |
|   3 |    NESTED LOOPS               |                             |     1 |    97 |     7   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | SR_MS_SRA_VISIT_SETUP       |     4 |   128 |     4   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | FAC_IDX_1                   |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| SR_MS_SRA_FACILITY_INFO_PER |     1 |    65 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$5C160134" "P"@"SEL$3")
      USE_NL(@"SEL$5C160134" "P"@"SEL$3")
      LEADING(@"SEL$5C160134" "V"@"SEL$2" "P"@"SEL$3")
      INDEX(@"SEL$5C160134" "P"@"SEL$3" ("SR_MS_SRA_FACILITY_INFO_PER"."VISIT_ID"))
      FULL(@"SEL$5C160134" "V"@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A")
      OUTLINE_LEAF(@"SEL$5C160134")
      ALL_ROWS
      OPT_PARAM('optimizer_index_cost_adj' 40)
      OPT_PARAM('star_transformation_enabled' 'true')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_index_join_enabled' 'false')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(DECODE("V"."VISIT_STATUS",'1','Pending','2','Pending','3','Pending','4','Active','5','A
              ctive','6','Active','D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed'
              ,'12','Completed','13','Completed','14','Completed')=NVL(:STATUS,DECODE("V"."VISIT_STATUS",'1','Pendi
              ng','2','Pending','3','Pending','4','Active','5','Active','6','Active','D','Cancelled','8','Completed
              ','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed
              ')))
   5 - access("VISIT_ID"="V"."VISIT_ID")
   6 - filter("DD_OBJECT_TYPE"='MS_SRA_VISIT_SETUP' AND ( REGEXP_INSTR 
              (NVL("FACILITY_NAME",'NULL'),NVL(TRIM(:GLOBAL_STATUS),NVL("FACILITY_NAME",'NULL')),1,1,0,'i')>0 OR  
              REGEXP_INSTR ("V"."VISIT_ID",NVL(TRIM(:GLOBAL_STATUS),NVL("V"."VISIT_ID",'NULL')),1,1,0,'i')>0 OR  
              REGEXP_INSTR (NVL("V"."VISIT_STATUS",'NULL'),NVL(TRIM(:GLOBAL_STATUS),NVL("V"."VISIT_STATUS",'NULL'))
              ,1,1,0,'i')>0 OR  REGEXP_INSTR (NVL("CITY",'NULL'),NVL(TRIM(:GLOBAL_STATUS),NVL("CITY",'NULL')),1,1,0
              ,'i')>0 OR  REGEXP_INSTR (NVL("STATE_PROVINCE",'NULL'),NVL(TRIM(:GLOBAL_STATUS),NVL("STATE_PROVINCE",
              'NULL')),1,1,0,'i')>0 OR  REGEXP_INSTR (NVL(TO_CHAR(TRUNC(INTERNAL_FUNCTION("V"."START_DATE"))),'null
              '),NVL(TRIM(:GLOBAL_STATUS),NVL(TO_CHAR(TRUNC(INTERNAL_FUNCTION("V"."START_DATE"))),'null')),1,1,0,'i
              ')>0 OR  REGEXP_INSTR (NVL("COUNTRY",'null'),NVL(TRIM(:GLOBAL_STATUS),NVL("COUNTRY",'null')),1,1,0,'i
              ')>0))

Open in new window

0
Comment
Question by:Swadhin Ray
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
I suppose your "base table" is SR_MS_SRA_VISIT_SETUP?! You should create an appropriate index on that table, so you get rid of the FTS...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
'faster' really isn't a performance goal.
How long is it currently taking?
What is your expectation for the final time?


regexp in Oracle tends to be a pretty expensive operation.  I would try replacing all the regexp_instr calls with regular instr calls since it doesn't look like you are actually doing any regular expressions.
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
The expected rum time of the SQL is 2 sec which is currently taking 15 secs.

Now why I am using "Regexp_Instr" because to handel search functionality.  

For example :

If the Facility name is : 'Aditya info Tech'
Than any one can search like 'Adit'  or 'Aditya'  or 'Tech'
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
are the Rows estimates in your plan approximately correct?  

if not, which are wrong and by how much?


I agree about the regexp's  try something like this instead...


 WHERE filter_status = NVL( :status, filter_status)
   AND ((TRIM( :global_status) IS NULL
     AND (facility_name IS NULL
       OR visit_id IS NULL
       OR city IS NULL
       OR state_provice IS NULL
       OR start_date IS NULL
       OR country IS NULL))
     OR INSTR(LOWER(facility_name), LOWER( :global_status)) > 0
     OR INSTR(LOWER(visit_id), LOWER( :global_status)) > 0
     OR INSTR(LOWER(city), LOWER( :global_status)) > 0
     OR INSTR(LOWER(state_provice), LOWER( :global_status)) > 0
     OR INSTR(LOWER(TO_CHAR(start_date)), LOWER( :global_status)) > 0
     OR INSTR(LOWER(country), LOWER( :global_status)) > 0)



note however, an "instr" type search is not indexable - that will only be usable as a filter after all rows are pulled by other conditions
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Than any one can search like 'Adit'  or 'Aditya'  or 'Tech'

Still don't see the need for a regular expression.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
also,  is the query the problem or is it the function?

how much time does it take to execute this function?

 ms_sra_visit_setup_hook_pkg.ms_sra_get_link


how much time does it take to execute the query without that function?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If the Facility name is : 'Aditya info Tech'
Than any one can search like 'Adit'  or 'Aditya'  or 'Tech'


no need for regexps for something simple like that.  Just use regular instr.

The only thing "special" you were doing was case insensitive searching which you can do with lower() or upper()


another thing  the TO_CHAR on start_date should have a format mask on it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Also,  if filter_status is not null then I would also change the first condition  from

FILTER_STATUS     = NVL(:STATUS,FILTER_STATUS)    -- this isn't indexable

to

( :status IS NULL OR filter_status = :status)   -- this is
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
I changed the below SQL as said :

 select  * from (
 select 
    f.Facility_id,
    f.facility_name,
    f.city,
    F.STATE_PROVINCE,
    F.COUNTRY,
  v.visit_id,
  TRUNC(v.start_date) start_date,
  
  DECODE(V.VISIT_STATUS, '1', 'Pending', '2','Pending', '3', 'Pending','4' ,'Active', '5','Active', '6','Active',
  'D','Cancelled','8','Completed','9','Completed','10','Completed','11','Completed','12','Completed','13','Completed','14','Completed'  )
    AS filter_status,
    
    V.VISIT_STATUS as VISIT_STATUS
    ,MS_SRA_VISIT_SETUP_HOOK_PKG.ms_sra_get_link(v.dd_process_instance_id,'2109760788',v.visit_status,v.visit_id) LINK
   from 
  
  SR_MS_SRA_VISIT_SETUP V, 
(select 
    VISIT_ID
    ,FACILITY_ID,
    facility_name,
    city,
    STATE_PROVINCE,
    COUNTRY
 from SR_MS_SRA_FACILITY_INFO_PER p
 where DD_OBJECT_TYPE='MS_SRA_VISIT_SETUP'
 ) F
 
 where F.VISIT_ID = V.VISIT_ID
 order by F.VISIT_ID desc
 
 ) 
 WHERE (:status IS NULL OR filter_status = :status)
 

 /*
  AND (
 (Regexp_Instr(NVL(facility_name,'NULL'),NVL(trim(:global_status), NVL(facility_name,'NULL')), 1,1,0,'i') > 0)
OR (Regexp_Instr(NVL(visit_id,'NULL'),NVL(trim(:global_status), NVL(visit_id,'NULL')),1,1,0,'i')              > 0)
OR (Regexp_Instr(NVL(visit_status,'NULL'),NVL(trim(:global_status), NVL(visit_status,'NULL')),1,1,0,'i')      > 0)
OR (Regexp_Instr(NVL(city,'NULL'),NVL(trim(:global_status), NVL(city,'NULL')),1,1,0,'i')                      > 0)
OR (regexp_instr(NVL(state_province,'NULL'),NVL(trim(:global_status), NVL(state_province,'NULL')),1,1,0,'i')  > 0)
or (REGEXP_INSTR(NVL(TO_CHAR(START_DATE),'null'),NVL(TRIM(:global_status), NVL(TO_CHAR(START_DATE),'null')),1,1,0,'i') > 0)
OR (REGEXP_INSTR(NVL(COUNTRY,'null'),NVL(TRIM(:GLOBAL_STATUS), NVL(COUNTRY,'null')),1,1,0,'i')                > 0)
)
*/
AND ((TRIM( :global_status) IS NULL
     AND (facility_name IS NULL
       OR visit_id IS NULL
       OR CITY IS NULL
      -- OR state_provice IS NULL
       OR start_date IS NULL
       OR country IS NULL))
     OR INSTR(LOWER(facility_name), LOWER( :global_status)) > 0
     OR INSTR(LOWER(visit_id), LOWER( :global_status)) > 0
     OR INSTR(LOWER(CITY), LOWER( :global_status)) > 0
     --OR INSTR(LOWER(state_provice), LOWER( :global_status)) > 0
     OR INSTR(LOWER(TO_CHAR(START_DATE)), LOWER( :global_status)) > 0
     OR INSTR(LOWER(country), LOWER( :global_status)) > 0)

 ;

Open in new window


If I uncomment state_provice column then I am getting the below error :

ORA-00904: "STATE_PROVICE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 59 Column: 21

Open in new window


If I try to run this SQL after commenting then I get the no records.

Where as if I use the regexp then I was getting the records.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
Man, it's a typo! Change it to "STATE_PROVINCE" ;-)
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
The issue was not with the name as even I commented it but in place of or we used and ..

(TRIM( :global_status) IS NULL
     AND (facility_name IS NULL
       OR visit_id IS NULL


Changed to

(TRIM( :global_status) IS NULL
     OR (facility_name IS NULL
       OR visit_id IS NULL
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
The issue was not with the name
ORA-00904: "STATE_PROVICE": invalid identifier

Open in new window

so this was not related to the name??!!
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what does your current code looks like and what error does it produce?
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
@Alexander Eßer : If you see I have commented that line and was giving null records when I was executing the SQL because in

(TRIM( :global_status) IS NULL
     AND (facility_name IS NULL
       OR visit_id IS NULL

and I changed it to ;

(TRIM( :global_status) IS NULL
     OR (facility_name IS NULL
       OR visit_id IS NULL

And SQL is running fast and taking less time...
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
Comment Utility
Thanks a lot..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now