SQL taking long time

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

LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
slightwv (䄆 Netminder) Commented:
'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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Swadhin RaySenior Technical Engineer Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>Than any one can search like 'Adit'  or 'Aditya'  or 'Tech'

Still don't see the need for a regular expression.
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Swadhin RaySenior Technical Engineer Author Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Man, it's a typo! Change it to "STATE_PROVINCE" ;-)
0
 
Swadhin RaySenior Technical Engineer Author Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
sdstuberCommented:
what does your current code looks like and what error does it produce?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@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
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks a lot..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.