?
Solved

SQL taking long time

Posted on 2014-01-08
15
Medium Priority
?
446 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 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 78

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 78

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 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