Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL taking long time

Posted on 2014-01-08
15
Medium Priority
?
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
15 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39764994
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39765028
'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
ID: 39765044
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39765056
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 77

Expert Comment

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

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

Expert Comment

by:sdstuber
ID: 39765063
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 74

Expert Comment

by:sdstuber
ID: 39765065
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39765075
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
ID: 39767551
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 14

Expert Comment

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

Author Comment

by:Swadhin Ray
ID: 39770388
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 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39770390
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 74

Expert Comment

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

Author Comment

by:Swadhin Ray
ID: 39776189
@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
ID: 39776237
Thanks a lot..
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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