SQL running slow

Hello Experts,

I have this SQL as below:

select 
qry1.visit_id,
qry1.protocol_name,
qry1.visit_status_name,
qry1.start_date,
qry1.end_date,
qry1.facility_id,
qry1.facility_name,
qry1.city,
qry1.country,
ms_apps_utilities.get_user_full_name(qry1.facility_contact) facility_contact_name,
ms_apps_utilities.get_user_full_name(qry1.apple_lead) apple_lead_name,
ms_apps_utilities.get_user_full_name(qry1.apple_verification_lead) apple_verification_lead_name,
(case when nvl(f.cap_approved_count,0)=0 then '0' else
ltrim(to_char(f.PER_OF_FINDINGS_APPROVED,'999') || chr(37))|| chr(47) || cast(f.cap_approved_count as varchar(20)) end) approved_findings,
(case when nvl(f.cap_closed_count,0)=0 then '0' else
ltrim(to_char(f.PER_OF_FINDINGS_CLOSED,'999') || chr(37)) || chr(47) || cast(f.cap_closed_count as varchar(20)) end) closed_findings,
(case when nvl(f.cap_verified_count,0)=0 then '0' else
ltrim(to_char(f.PER_OF_FINDINGS_verified,'999') || chr(37)) || chr(47) || cast(f.cap_verified_count as varchar(20)) end) verified_findings,
(case when nvl(qry3.total_cappa_count,0)=0 then '0' else
ltrim(to_char((f.CAP_TOTAL_COUNT/(qry3.total_cappa_count/100)),'999') || chr(37)) || chr(47) || cast(f.CAP_TOTAL_COUNT as varchar(20)) end) per_cappa_overdue,
qry1.cap_proposal_deadline,
f.cap_proposed_on,
ms_apps_utilities.get_user_full_name(ms_apps_utilities.get_user_name(f.cap_last_modified_by))
|| case when f.cap_last_modified_on is null then null else
' (' || cast(f.cap_last_modified_on as varchar(100)) || ')' end modified_by
from
(select
a.visit_id,
a.protocol_name,
a.visit_status,
ms_apps_utilities.get_display_value(100000,'MS SRA VISIT STATUS', a.visit_status) visit_status_name,
a.start_date,
a.end_date,
b.facility_id,
b.facility_name,
b.facility_address,
b.city,
b.state_province,
b.country,
b.facility_local_name,
b.facility_local_address,
b.latitude,
b.longitude,
b.facility_contact,
a.surprise,
a.visit_type,
a.include_cav,
a.facility_profile_date_sent,
a.facility_profile_date_submit,
a.sr_program_manager,
a.gsm,
a.onsite_lead,
a.apple_lead,
a.apple_verification_lead,
a.cap_proposal_deadline
from ms_sra_visit_setup a
inner join ms_sra_facility_info b
on a.visit_id = b.visit_id) qry1
left outer join
ms_sra_cap f
on qry1.visit_id = f.visit_id
left outer join
(select e.visit_id, count(e.action_id) total_cappa_count
from ms_sra_visit_actions e
group by e.visit_id) qry3
on qry1.visit_id = qry3.visit_id
where
((qry1.visit_id like :1)
or (:1 is null))
and
((qry1.protocol_name like :2)
or (:2 is null))
and
(qry1.Start_Date >= :3 OR :3 IS NULL)
AND 
(qry1.End_Date >= :3 OR :3 IS NULL)
AND
(qry1.Start_Date <= :4 OR :4 IS NULL)
AND 
(qry1.End_Date <= :4 OR :4 IS NULL)
and
((qry1.facility_id = :5)
or (:5 is null))
and
((qry1.facility_name = :6)
or (:6 is null))
and
((qry1.city = :7)
or (:7 is null))
and
((qry1.country = :8)
or (:8 is null))
and
((qry1.apple_verification_lead = :9)
or (:9 is null))
and
((qry1.apple_lead = :10)
or (:10 is null))
and
((qry1.cap_proposal_deadline = :11)
or (:11 is null))
and
((f.cap_proposed_on = :12)
or (:12 is null))
and
((f.cap_last_modified_on = :13)
or (:13 is null))
and
(qry1.visit_status = :14 OR :14 is null) 
;

Open in new window


And the plan is as below:

  
     Plan hash value: 764143824
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     1 |  3660 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                  |                       |     1 |  3660 |     8  (25)| 00:00:01 |
|   2 |   VIEW                            |                       |     1 |  3585 |     4   (0)| 00:00:01 |
|*  3 |    FILTER                         |                       |       |       |            |          |
|   4 |     NESTED LOOPS OUTER            |                       |     1 |  3514 |     4   (0)| 00:00:01 |
|   5 |      VIEW                         |                       |     1 |  3463 |     3   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                       |       |       |            |          |
|   7 |        NESTED LOOPS               |                       |     1 |   145 |     3   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL         | MS_SRA_FACILITY_INFO  |     1 |    65 |     2   (0)| 00:00:01 |
|*  9 |         INDEX UNIQUE SCAN         | MS_SRA_VISIT_SETUP_PK |     1 |       |     1   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_SETUP    |     1 |    80 |     1   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | MS_SRA_CAP            |     1 |    51 |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN           | MS_SRA_CAP_PK         |     1 |       |     1   (0)| 00:00:01 |
|  13 |   VIEW                            |                       |     4 |   300 |     3  (34)| 00:00:01 |
|  14 |    SORT GROUP BY                  |                       |     4 |    68 |     3  (34)| 00:00:01 |
|  15 |     TABLE ACCESS FULL             | MS_SRA_VISIT_ACTIONS  |    32 |   544 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$37633EB5" "A"@"SEL$3")
      USE_NL(@"SEL$37633EB5" "A"@"SEL$3")
      LEADING(@"SEL$37633EB5" "B"@"SEL$3" "A"@"SEL$3")
      INDEX(@"SEL$37633EB5" "A"@"SEL$3" ("MS_SRA_VISIT_SETUP"."VISIT_ID"))
      FULL(@"SEL$37633EB5" "B"@"SEL$3")
      FULL(@"SEL$6" "E"@"SEL$6")
      USE_NL(@"SEL$58A6D7F6" "F"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "QRY1"@"SEL$2" "F"@"SEL$1")
      INDEX_RS_ASC(@"SEL$58A6D7F6" "F"@"SEL$1" ("MS_SRA_CAP"."VISIT_ID"))
      NO_ACCESS(@"SEL$58A6D7F6" "QRY1"@"SEL$2")
      USE_HASH(@"SEL$2F70D407" "QRY3"@"SEL$5")
      LEADING(@"SEL$2F70D407" "from$_subquery$_006"@"SEL$7" "QRY3"@"SEL$5")
      NO_ACCESS(@"SEL$2F70D407" "QRY3"@"SEL$5")
      NO_ACCESS(@"SEL$2F70D407" "from$_subquery$_006"@"SEL$7")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$7")
      MERGE(@"SEL$5")
      OUTLINE(@"SEL$F23444D6")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      MERGE(@"SEL$F23444D6")
      OUTLINE_LEAF(@"SEL$2F70D407")
      OUTLINE_LEAF(@"SEL$6")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$37633EB5")
      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):
---------------------------------------------------
 
   1 - access("QRY1"."VISIT_ID"="QRY3"."VISIT_ID"(+))
   3 - filter(("F"."CAP_PROPOSED_ON"=:12 OR :12 IS NULL) AND ("F"."CAP_LAST_MODIFIED_ON"=:13 OR 
              :13 IS NULL))
   8 - filter((:6 IS NULL OR "B"."FACILITY_NAME"=:6) AND (:7 IS NULL OR "B"."CITY"=:7) AND (:8 IS 
              NULL OR "B"."COUNTRY"=:8) AND (:5 IS NULL OR "B"."FACILITY_ID"=TO_NUMBER(:5)))
   9 - access("A"."VISIT_ID"="B"."VISIT_ID")
  10 - filter((:14 IS NULL OR "A"."VISIT_STATUS"=:14) AND (:1 IS NULL OR "A"."VISIT_ID" LIKE :1) 
              AND (:2 IS NULL OR "A"."PROTOCOL_NAME" LIKE :2) AND (:9 IS NULL OR 
              "A"."APPLE_VERIFICATION_LEAD"=TO_NUMBER(:9)) AND (:10 IS NULL OR "A"."APPLE_LEAD"=TO_NUMBER(:10)) 
              AND (:11 IS NULL OR "A"."CAP_PROPOSAL_DEADLINE"=:11) AND (:3 IS NULL OR "A"."END_DATE">=:3) AND 
              (:4 IS NULL OR "A"."END_DATE"<=:4) AND (:3 IS NULL OR "A"."START_DATE">=:3) AND (:4 IS NULL OR 
              "A"."START_DATE"<=:4))
  12 - access("QRY1"."VISIT_ID"="F"."VISIT_ID"(+))
  

Open in new window


Can you suggest me how the performance of this SQL can be improved ?
LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>Yes it has on the below:
>MS_QS_LIST_OF_VALUES_N1 index on LOV_ID

please created 1 index with all 4 fields
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this part
JOIN (select e.visit_id, count(e.action_id) total_cappa_count
from ms_sra_visit_actions e
group by e.visit_id) qry3 on ... 

Open in new window

seems to imply in the explain plan that you don't have a index on visit_id for that table.
you may want to create one, but given the "number of rows" returned etc, it will eventually not really help.

now, when you ask about "improve":
* how many rows do the tables have
* how many rows are returned by your query
* how much time does it take for the query to complete?
* do you run the sql query tool on the server itself or on a dedicated client pc?
   => if the answer is "on the server itself" => please take the habit of NOT running sql server management studio on the sql box itself, but ALWAYS remotely.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
I am using Oracle database.

Please find details:

* how many rows do the tables have  

select count(*) from ms_sra_visit_setup;
-- no of records :85
select count(*) from ms_sra_facility_info;
-- no of records :85
select count(*) from ms_sra_cap;
-- no of records :21
select count(*) from ms_sra_visit_actions;
-- no of records :39

SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name ='MS SRA VISIT STATUS'
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            );
                            
-- no of records : 14                            

Open in new window


* how many rows are returned by your query

>>No of row returned are 85

* how much time does it take for the query to complete?
>> 1 min
* do you run the sql query tool on the server itself or on a dedicated client pc?

>>I am using oracle database and I am running it from SQL developer using a direct connection



After creating the index on the action table :

create index ms_sra_act_visit_idx on sgm.ms_sra_visit_actions(visit_id);


I get the below plan when I run the SLQ which I have posted in my actual question:

Plan hash value: 764143824
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     1 |  3660 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                  |                       |     1 |  3660 |     8  (25)| 00:00:01 |
|   2 |   VIEW                            |                       |     1 |  3585 |     4   (0)| 00:00:01 |
|*  3 |    FILTER                         |                       |       |       |            |          |
|   4 |     NESTED LOOPS OUTER            |                       |     1 |  3514 |     4   (0)| 00:00:01 |
|   5 |      VIEW                         |                       |     1 |  3463 |     3   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                       |       |       |            |          |
|   7 |        NESTED LOOPS               |                       |     1 |   145 |     3   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL         | MS_SRA_FACILITY_INFO  |     1 |    65 |     2   (0)| 00:00:01 |
|*  9 |         INDEX UNIQUE SCAN         | MS_SRA_VISIT_SETUP_PK |     1 |       |     1   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_SETUP    |     1 |    80 |     1   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | MS_SRA_CAP            |     1 |    51 |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN           | MS_SRA_CAP_PK         |     1 |       |     1   (0)| 00:00:01 |
|  13 |   VIEW                            |                       |     4 |   300 |     3  (34)| 00:00:01 |
|  14 |    SORT GROUP BY                  |                       |     4 |    68 |     3  (34)| 00:00:01 |
|  15 |     TABLE ACCESS FULL             | MS_SRA_VISIT_ACTIONS  |    32 |   544 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$37633EB5" "A"@"SEL$3")
      USE_NL(@"SEL$37633EB5" "A"@"SEL$3")
      LEADING(@"SEL$37633EB5" "B"@"SEL$3" "A"@"SEL$3")
      INDEX(@"SEL$37633EB5" "A"@"SEL$3" ("MS_SRA_VISIT_SETUP"."VISIT_ID"))
      FULL(@"SEL$37633EB5" "B"@"SEL$3")
      FULL(@"SEL$6" "E"@"SEL$6")
      USE_NL(@"SEL$58A6D7F6" "F"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "QRY1"@"SEL$2" "F"@"SEL$1")
      INDEX_RS_ASC(@"SEL$58A6D7F6" "F"@"SEL$1" ("MS_SRA_CAP"."VISIT_ID"))
      NO_ACCESS(@"SEL$58A6D7F6" "QRY1"@"SEL$2")
      USE_HASH(@"SEL$2F70D407" "QRY3"@"SEL$5")
      LEADING(@"SEL$2F70D407" "from$_subquery$_006"@"SEL$7" "QRY3"@"SEL$5")
      NO_ACCESS(@"SEL$2F70D407" "QRY3"@"SEL$5")
      NO_ACCESS(@"SEL$2F70D407" "from$_subquery$_006"@"SEL$7")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$7")
      MERGE(@"SEL$5")
      OUTLINE(@"SEL$F23444D6")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      MERGE(@"SEL$F23444D6")
      OUTLINE_LEAF(@"SEL$2F70D407")
      OUTLINE_LEAF(@"SEL$6")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$37633EB5")
      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):
---------------------------------------------------
 
   1 - access("QRY1"."VISIT_ID"="QRY3"."VISIT_ID"(+))
   3 - filter(("F"."CAP_PROPOSED_ON"=:12 OR :12 IS NULL) AND ("F"."CAP_LAST_MODIFIED_ON"=:13 OR 
              :13 IS NULL))
   8 - filter((:6 IS NULL OR "B"."FACILITY_NAME"=:6) AND (:7 IS NULL OR "B"."CITY"=:7) AND (:8 IS 
              NULL OR "B"."COUNTRY"=:8) AND (:5 IS NULL OR "B"."FACILITY_ID"=TO_NUMBER(:5)))
   9 - access("A"."VISIT_ID"="B"."VISIT_ID")
  10 - filter((:14 IS NULL OR "A"."VISIT_STATUS"=:14) AND (:1 IS NULL OR "A"."VISIT_ID" LIKE :1) 
              AND (:2 IS NULL OR "A"."PROTOCOL_NAME" LIKE :2) AND (:9 IS NULL OR 
              "A"."APPLE_VERIFICATION_LEAD"=TO_NUMBER(:9)) AND (:10 IS NULL OR "A"."APPLE_LEAD"=TO_NUMBER(:10)) 
              AND (:11 IS NULL OR "A"."CAP_PROPOSAL_DEADLINE"=:11) AND (:3 IS NULL OR "A"."END_DATE">=:3) AND 
              (:4 IS NULL OR "A"."END_DATE"<=:4) AND (:3 IS NULL OR "A"."START_DATE">=:3) AND (:4 IS NULL OR 
              "A"."START_DATE"<=:4))
  12 - access("QRY1"."VISIT_ID"="F"."VISIT_ID"(+))

Open in new window


If I try to use a with clause then I see the plan as below:

explain plan for 
WITH dis AS 
(SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name ='MS SRA VISIT STATUS'
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            )
  )
 select 
qry1.visit_id,
qry1.protocol_name,
qry1.visit_status_name,
qry1.start_date,
qry1.end_date,
qry1.facility_id,
qry1.facility_name,
qry1.city,
qry1.country,
ms_apps_utilities.get_user_full_name(qry1.facility_contact) facility_contact_name,
ms_apps_utilities.get_user_full_name(qry1.apple_lead) apple_lead_name,
ms_apps_utilities.get_user_full_name(qry1.apple_verification_lead) apple_verification_lead_name,
(case when nvl(cap_approved_count,0)=0 then '0' else
ltrim(
to_char(PER_OF_FINDINGS_APPROVED,'999') || chr(37))
|| chr(47) ||-- cast(
cap_approved_count
--as varchar(20))
end) approved_findings,
(case when nvl(f.cap_closed_count,0)=0 then '0' else
ltrim(to_char(f.PER_OF_FINDINGS_CLOSED,'999') || chr(37)) || chr(47) || cast(f.cap_closed_count as varchar(20)) end) closed_findings,
(case when nvl(f.cap_verified_count,0)=0 then '0' else
ltrim(to_char(f.PER_OF_FINDINGS_verified,'999') || chr(37)) || chr(47) || cast(f.cap_verified_count as varchar(20)) end) verified_findings,
(case when nvl(qry3.total_cappa_count,0)=0 then '0' else
ltrim(to_char((f.CAP_TOTAL_COUNT/(qry3.total_cappa_count/100)),'999') || chr(37)) || chr(47) || cast(f.CAP_TOTAL_COUNT as varchar(20)) end) per_cappa_overdue,
qry1.cap_proposal_deadline,
f.cap_proposed_on,
ms_apps_utilities.get_user_full_name(ms_apps_utilities.get_user_name(f.cap_last_modified_by))
|| case when f.cap_last_modified_on is null then null else
' (' || cast(f.cap_last_modified_on as varchar(100)) || ')' end modified_by
from
(select
a.visit_id,
a.protocol_name,
a.visit_status,
--ms_apps_utilities.get_display_value(100000,'MS SRA VISIT STATUS', a.visit_status) visit_status_name,
(select DISPLAYED_VALUE from dis where STORED_VALUE = a.visit_status and lov_name ='MS SRA VISIT STATUS')  visit_status_name,
a.start_date,
a.end_date,
b.facility_id,
b.facility_name,
b.facility_address,
b.city,
b.state_province,
b.country,
b.facility_local_name,
b.facility_local_address,
b.latitude,
b.longitude,
b.facility_contact,
a.surprise,
a.visit_type,
a.include_cav,
a.facility_profile_date_sent,
a.facility_profile_date_submit,
a.sr_program_manager,
a.gsm,
a.onsite_lead,
a.apple_lead,
a.apple_verification_lead,
a.cap_proposal_deadline
from ms_sra_visit_setup a
inner join ms_sra_facility_info b
on a.visit_id = b.visit_id) qry1
left outer join
ms_sra_cap f
on qry1.visit_id = f.visit_id
left outer join
(select e.visit_id, count(e.action_id) total_cappa_count
from ms_sra_visit_actions e
group by e.visit_id) qry3
on qry1.visit_id = qry3.visit_id
where
((qry1.visit_id like :1)
or (:1 is null))
and
((qry1.protocol_name like :2)
or (:2 is null))
and
(qry1.Start_Date >= :3 OR :3 IS NULL)
AND 
(qry1.End_Date >= :3 OR :3 IS NULL)
AND
(qry1.Start_Date <= :4 OR :4 IS NULL)
AND 
(qry1.End_Date <= :4 OR :4 IS NULL)
and
((qry1.facility_id = :5)
or (:5 is null))
and
((qry1.facility_name = :6)
or (:6 is null))
and
((qry1.city = :7)
or (:7 is null))
and
((qry1.country = :8)
or (:8 is null))
and
((qry1.apple_verification_lead = :9)
or (:9 is null))
and
((qry1.apple_lead = :10)
or (:10 is null))
and
((qry1.cap_proposal_deadline = :11)
or (:11 is null))
and
((f.cap_proposed_on = :12)
or (:12 is null))
and
((f.cap_last_modified_on = :13)
or (:13 is null))
and
(qry1.visit_status = :14 OR :14 is null) 
;

------Plan for the above SQL

Plan hash value: 600397091
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                         |     1 |  3615 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                  |                         |     1 |  4306 |    12   (9)| 00:00:01 |
|*  2 |   HASH JOIN                       |                         |     1 |  2278 |    10  (10)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL              | MS_QS_LOV_NAMES         |     1 |    34 |     2   (0)| 00:00:01 |
|   4 |    VIEW                           |                         |     5 | 11220 |     7   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                  |                         |     5 |   205 |     7   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID  | SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN           | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL            | MS_QS_LIST_OF_VALUES_T  |     5 |   175 |     6   (0)| 00:00:01 |
|   9 |   TABLE ACCESS FULL               | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|* 10 |  HASH JOIN OUTER                  |                         |     1 |  3615 |     8  (25)| 00:00:01 |
|  11 |   VIEW                            |                         |     1 |  3585 |     4   (0)| 00:00:01 |
|* 12 |    FILTER                         |                         |       |       |            |          |
|  13 |     NESTED LOOPS OUTER            |                         |     1 |  3469 |     4   (0)| 00:00:01 |
|  14 |      VIEW                         |                         |     1 |  3418 |     3   (0)| 00:00:01 |
|  15 |       NESTED LOOPS                |                         |       |       |            |          |
|  16 |        NESTED LOOPS               |                         |     1 |   145 |     3   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS FULL         | MS_SRA_FACILITY_INFO    |     1 |    65 |     2   (0)| 00:00:01 |
|* 18 |         INDEX UNIQUE SCAN         | MS_SRA_VISIT_SETUP_PK   |     1 |       |     1   (0)| 00:00:01 |
|* 19 |        TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_SETUP      |     1 |    80 |     1   (0)| 00:00:01 |
|  20 |      TABLE ACCESS BY INDEX ROWID  | MS_SRA_CAP              |     1 |    51 |     1   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN           | MS_SRA_CAP_PK           |     1 |       |     1   (0)| 00:00:01 |
|  22 |   VIEW                            |                         |     4 |   120 |     3  (34)| 00:00:01 |
|  23 |    SORT GROUP BY                  |                         |     4 |    68 |     3  (34)| 00:00:01 |
|  24 |     TABLE ACCESS FULL             | MS_SRA_VISIT_ACTIONS    |    32 |   544 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$42DFC41A" "T"@"SEL$11")
      LEADING(@"SEL$42DFC41A" "SI_LOCALES"@"SEL$12" "T"@"SEL$11")
      FULL(@"SEL$42DFC41A" "T"@"SEL$11")
      INDEX_RS_ASC(@"SEL$42DFC41A" "SI_LOCALES"@"SEL$12" ("SI_LOCALES"."LOCALE_ID"))
      USE_HASH(@"SEL$6A4BB856" "TL"@"SEL$10")
      USE_HASH(@"SEL$6A4BB856" "X"@"SEL$10")
      LEADING(@"SEL$6A4BB856" "LOVNAME"@"SEL$9" "X"@"SEL$10" "TL"@"SEL$10")
      FULL(@"SEL$6A4BB856" "TL"@"SEL$10")
      NO_ACCESS(@"SEL$6A4BB856" "X"@"SEL$10")
      FULL(@"SEL$6A4BB856" "LOVNAME"@"SEL$9")
      NLJ_BATCHING(@"SEL$37633EB5" "A"@"SEL$3")
      USE_NL(@"SEL$37633EB5" "A"@"SEL$3")
      LEADING(@"SEL$37633EB5" "B"@"SEL$3" "A"@"SEL$3")
      INDEX(@"SEL$37633EB5" "A"@"SEL$3" ("MS_SRA_VISIT_SETUP"."VISIT_ID"))
      FULL(@"SEL$37633EB5" "B"@"SEL$3")
      FULL(@"SEL$7" "E"@"SEL$7")
      USE_NL(@"SEL$58A6D7F6" "F"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "QRY1"@"SEL$2" "F"@"SEL$1")
      INDEX_RS_ASC(@"SEL$58A6D7F6" "F"@"SEL$1" ("MS_SRA_CAP"."VISIT_ID"))
      NO_ACCESS(@"SEL$58A6D7F6" "QRY1"@"SEL$2")
      USE_HASH(@"SEL$C540FB68" "QRY3"@"SEL$6")
      LEADING(@"SEL$C540FB68" "from$_subquery$_009"@"SEL$8" "QRY3"@"SEL$6")
      NO_ACCESS(@"SEL$C540FB68" "QRY3"@"SEL$6")
      NO_ACCESS(@"SEL$C540FB68" "from$_subquery$_009"@"SEL$8")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$9")
      MERGE(@"SEL$6")
      OUTLINE(@"SEL$057DF1AA")
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      MERGE(@"SEL$10")
      OUTLINE(@"SEL$0EE6DB63")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$11")
      MERGE(@"SEL$057DF1AA")
      OUTLINE_LEAF(@"SEL$C540FB68")
      OUTLINE_LEAF(@"SEL$7")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$37633EB5")
      MERGE(@"SEL$0EE6DB63")
      OUTLINE_LEAF(@"SEL$6A4BB856")
      MERGE(@"SEL$12")
      OUTLINE_LEAF(@"SEL$42DFC41A")
      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):
---------------------------------------------------
 
   1 - access("X"."LOV_VALUE_ID"="TL"."LOV_VALUE_ID"(+) AND "X"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   2 - access("X"."LOV_ID"="LOVNAME"."LOV_ID")
   3 - filter("LOVNAME"."LOV_NAME"='MS SRA VISIT STATUS' AND "LOVNAME"."ENTERPRISE_ID"=100000)
   6 - filter("ENABLED_FLAG"='Y')
   7 - access("LOCALE_ID"=TO_NUMBER(NVL("SI_DB_LOCALE_SV"."GETVALUE"(),'1009')))
   8 - filter("T"."STORED_VALUE"=:B1 AND "T"."ORG_ENTITY_ID"=1 AND "T"."ENTERPRISE_ID"=100000)
  10 - access("QRY1"."VISIT_ID"="QRY3"."VISIT_ID"(+))
  12 - filter(("F"."CAP_PROPOSED_ON"=:12 OR :12 IS NULL) AND ("F"."CAP_LAST_MODIFIED_ON"=:13 OR :13 
              IS NULL))
  17 - filter((:6 IS NULL OR "B"."FACILITY_NAME"=:6) AND (:7 IS NULL OR "B"."CITY"=:7) AND (:8 IS 
              NULL OR "B"."COUNTRY"=:8) AND (:5 IS NULL OR "B"."FACILITY_ID"=TO_NUMBER(:5)))
  18 - access("A"."VISIT_ID"="B"."VISIT_ID")
  19 - filter((:14 IS NULL OR "A"."VISIT_STATUS"=:14) AND (:1 IS NULL OR "A"."VISIT_ID" LIKE :1) AND 
              (:2 IS NULL OR "A"."PROTOCOL_NAME" LIKE :2) AND (:9 IS NULL OR 
              "A"."APPLE_VERIFICATION_LEAD"=TO_NUMBER(:9)) AND (:10 IS NULL OR "A"."APPLE_LEAD"=TO_NUMBER(:10)) 
              AND (:11 IS NULL OR "A"."CAP_PROPOSAL_DEADLINE"=:11) AND (:3 IS NULL OR "A"."END_DATE">=:3) AND (:4 
              IS NULL OR "A"."END_DATE"<=:4) AND (:3 IS NULL OR "A"."START_DATE">=:3) AND (:4 IS NULL OR 
              "A"."START_DATE"<=:4))
  21 - access("QRY1"."VISIT_ID"="F"."VISIT_ID"(+))
      
      

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
johnsoneSenior Oracle DBACommented:
I don't see how this query:

select e.visit_id, count(e.action_id) total_cappa_count
from ms_sra_visit_actions e
group by e.visit_id

Open in new window


could use an index.  2 columns are involved, the group by column and the column being counted (I assume this is so columns with NULL in ACTION_ID are not counted).  This would require an access to the base table anyway, so a full table scan would be the best method.

Looking at the row counts and bytes the plan is expecting to be returned, my guess is that your statistics are not up to date.  I would start there and then see where the query goes.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
I have already an index on action ID so I created an index on the visit_id for ms_sra_visit_actions table.


Plan by using the original SQL (what I have initially provided ):

Plan hash value: 764143824
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     1 |  3660 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                  |                       |     1 |  3660 |     8  (25)| 00:00:01 |
|   2 |   VIEW                            |                       |     1 |  3585 |     4   (0)| 00:00:01 |
|*  3 |    FILTER                         |                       |       |       |            |          |
|   4 |     NESTED LOOPS OUTER            |                       |     1 |  3514 |     4   (0)| 00:00:01 |
|   5 |      VIEW                         |                       |     1 |  3463 |     3   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                       |       |       |            |          |
|   7 |        NESTED LOOPS               |                       |     1 |   145 |     3   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL         | MS_SRA_FACILITY_INFO  |     1 |    65 |     2   (0)| 00:00:01 |
|*  9 |         INDEX UNIQUE SCAN         | MS_SRA_VISIT_SETUP_PK |     1 |       |     1   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_SETUP    |     1 |    80 |     1   (0)| 00:00:01 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | MS_SRA_CAP            |     1 |    51 |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN           | MS_SRA_CAP_PK         |     1 |       |     1   (0)| 00:00:01 |
|  13 |   VIEW                            |                       |     4 |   300 |     3  (34)| 00:00:01 |
|  14 |    SORT GROUP BY                  |                       |     4 |    68 |     3  (34)| 00:00:01 |
|  15 |     TABLE ACCESS FULL             | MS_SRA_VISIT_ACTIONS  |    32 |   544 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$37633EB5" "A"@"SEL$3")
      USE_NL(@"SEL$37633EB5" "A"@"SEL$3")
      LEADING(@"SEL$37633EB5" "B"@"SEL$3" "A"@"SEL$3")
      INDEX(@"SEL$37633EB5" "A"@"SEL$3" ("MS_SRA_VISIT_SETUP"."VISIT_ID"))
      FULL(@"SEL$37633EB5" "B"@"SEL$3")
      FULL(@"SEL$6" "E"@"SEL$6")
      USE_NL(@"SEL$58A6D7F6" "F"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "QRY1"@"SEL$2" "F"@"SEL$1")
      INDEX_RS_ASC(@"SEL$58A6D7F6" "F"@"SEL$1" ("MS_SRA_CAP"."VISIT_ID"))
      NO_ACCESS(@"SEL$58A6D7F6" "QRY1"@"SEL$2")
      USE_HASH(@"SEL$2F70D407" "QRY3"@"SEL$5")
      LEADING(@"SEL$2F70D407" "from$_subquery$_006"@"SEL$7" "QRY3"@"SEL$5")
      NO_ACCESS(@"SEL$2F70D407" "QRY3"@"SEL$5")
      NO_ACCESS(@"SEL$2F70D407" "from$_subquery$_006"@"SEL$7")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$7")
      MERGE(@"SEL$5")
      OUTLINE(@"SEL$F23444D6")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      MERGE(@"SEL$F23444D6")
      OUTLINE_LEAF(@"SEL$2F70D407")
      OUTLINE_LEAF(@"SEL$6")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$37633EB5")
      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):
---------------------------------------------------
 
   1 - access("QRY1"."VISIT_ID"="QRY3"."VISIT_ID"(+))
   3 - filter(("F"."CAP_PROPOSED_ON"=:12 OR :12 IS NULL) AND ("F"."CAP_LAST_MODIFIED_ON"=:13 OR 
              :13 IS NULL))
   8 - filter((:6 IS NULL OR "B"."FACILITY_NAME"=:6) AND (:7 IS NULL OR "B"."CITY"=:7) AND (:8 IS 
              NULL OR "B"."COUNTRY"=:8) AND (:5 IS NULL OR "B"."FACILITY_ID"=TO_NUMBER(:5)))
   9 - access("A"."VISIT_ID"="B"."VISIT_ID")
  10 - filter((:14 IS NULL OR "A"."VISIT_STATUS"=:14) AND (:1 IS NULL OR "A"."VISIT_ID" LIKE :1) 
              AND (:2 IS NULL OR "A"."PROTOCOL_NAME" LIKE :2) AND (:9 IS NULL OR 
              "A"."APPLE_VERIFICATION_LEAD"=TO_NUMBER(:9)) AND (:10 IS NULL OR "A"."APPLE_LEAD"=TO_NUMBER(:10)) 
              AND (:11 IS NULL OR "A"."CAP_PROPOSAL_DEADLINE"=:11) AND (:3 IS NULL OR "A"."END_DATE">=:3) AND 
              (:4 IS NULL OR "A"."END_DATE"<=:4) AND (:3 IS NULL OR "A"."START_DATE">=:3) AND (:4 IS NULL OR 
              "A"."START_DATE"<=:4))
  12 - access("QRY1"."VISIT_ID"="F"."VISIT_ID"(+))

Open in new window


Plan by using With Clause:

Plan hash value: 600397091
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                         |     1 |  3615 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                  |                         |     1 |  4306 |    12   (9)| 00:00:01 |
|*  2 |   HASH JOIN                       |                         |     1 |  2278 |    10  (10)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL              | MS_QS_LOV_NAMES         |     1 |    34 |     2   (0)| 00:00:01 |
|   4 |    VIEW                           |                         |     5 | 11220 |     7   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                  |                         |     5 |   205 |     7   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID  | SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN           | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL            | MS_QS_LIST_OF_VALUES_T  |     5 |   175 |     6   (0)| 00:00:01 |
|   9 |   TABLE ACCESS FULL               | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|* 10 |  HASH JOIN OUTER                  |                         |     1 |  3615 |     8  (25)| 00:00:01 |
|  11 |   VIEW                            |                         |     1 |  3585 |     4   (0)| 00:00:01 |
|* 12 |    FILTER                         |                         |       |       |            |          |
|  13 |     NESTED LOOPS OUTER            |                         |     1 |  3469 |     4   (0)| 00:00:01 |
|  14 |      VIEW                         |                         |     1 |  3418 |     3   (0)| 00:00:01 |
|  15 |       NESTED LOOPS                |                         |       |       |            |          |
|  16 |        NESTED LOOPS               |                         |     1 |   145 |     3   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS FULL         | MS_SRA_FACILITY_INFO    |     1 |    65 |     2   (0)| 00:00:01 |
|* 18 |         INDEX UNIQUE SCAN         | MS_SRA_VISIT_SETUP_PK   |     1 |       |     1   (0)| 00:00:01 |
|* 19 |        TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_SETUP      |     1 |    80 |     1   (0)| 00:00:01 |
|  20 |      TABLE ACCESS BY INDEX ROWID  | MS_SRA_CAP              |     1 |    51 |     1   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN           | MS_SRA_CAP_PK           |     1 |       |     1   (0)| 00:00:01 |
|  22 |   VIEW                            |                         |     4 |   120 |     3  (34)| 00:00:01 |
|  23 |    SORT GROUP BY                  |                         |     4 |    68 |     3  (34)| 00:00:01 |
|  24 |     TABLE ACCESS FULL             | MS_SRA_VISIT_ACTIONS    |    32 |   544 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$42DFC41A" "T"@"SEL$11")
      LEADING(@"SEL$42DFC41A" "SI_LOCALES"@"SEL$12" "T"@"SEL$11")
      FULL(@"SEL$42DFC41A" "T"@"SEL$11")
      INDEX_RS_ASC(@"SEL$42DFC41A" "SI_LOCALES"@"SEL$12" ("SI_LOCALES"."LOCALE_ID"))
      USE_HASH(@"SEL$6A4BB856" "TL"@"SEL$10")
      USE_HASH(@"SEL$6A4BB856" "X"@"SEL$10")
      LEADING(@"SEL$6A4BB856" "LOVNAME"@"SEL$9" "X"@"SEL$10" "TL"@"SEL$10")
      FULL(@"SEL$6A4BB856" "TL"@"SEL$10")
      NO_ACCESS(@"SEL$6A4BB856" "X"@"SEL$10")
      FULL(@"SEL$6A4BB856" "LOVNAME"@"SEL$9")
      NLJ_BATCHING(@"SEL$37633EB5" "A"@"SEL$3")
      USE_NL(@"SEL$37633EB5" "A"@"SEL$3")
      LEADING(@"SEL$37633EB5" "B"@"SEL$3" "A"@"SEL$3")
      INDEX(@"SEL$37633EB5" "A"@"SEL$3" ("MS_SRA_VISIT_SETUP"."VISIT_ID"))
      FULL(@"SEL$37633EB5" "B"@"SEL$3")
      FULL(@"SEL$7" "E"@"SEL$7")
      USE_NL(@"SEL$58A6D7F6" "F"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "QRY1"@"SEL$2" "F"@"SEL$1")
      INDEX_RS_ASC(@"SEL$58A6D7F6" "F"@"SEL$1" ("MS_SRA_CAP"."VISIT_ID"))
      NO_ACCESS(@"SEL$58A6D7F6" "QRY1"@"SEL$2")
      USE_HASH(@"SEL$C540FB68" "QRY3"@"SEL$6")
      LEADING(@"SEL$C540FB68" "from$_subquery$_009"@"SEL$8" "QRY3"@"SEL$6")
      NO_ACCESS(@"SEL$C540FB68" "QRY3"@"SEL$6")
      NO_ACCESS(@"SEL$C540FB68" "from$_subquery$_009"@"SEL$8")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$9")
      MERGE(@"SEL$6")
      OUTLINE(@"SEL$057DF1AA")
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      MERGE(@"SEL$10")
      OUTLINE(@"SEL$0EE6DB63")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$11")
      MERGE(@"SEL$057DF1AA")
      OUTLINE_LEAF(@"SEL$C540FB68")
      OUTLINE_LEAF(@"SEL$7")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$37633EB5")
      MERGE(@"SEL$0EE6DB63")
      OUTLINE_LEAF(@"SEL$6A4BB856")
      MERGE(@"SEL$12")
      OUTLINE_LEAF(@"SEL$42DFC41A")
      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):
---------------------------------------------------
 
   1 - access("X"."LOV_VALUE_ID"="TL"."LOV_VALUE_ID"(+) AND "X"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   2 - access("X"."LOV_ID"="LOVNAME"."LOV_ID")
   3 - filter("LOVNAME"."LOV_NAME"='MS SRA VISIT STATUS' AND "LOVNAME"."ENTERPRISE_ID"=100000)
   6 - filter("ENABLED_FLAG"='Y')
   7 - access("LOCALE_ID"=TO_NUMBER(NVL("SI_DB_LOCALE_SV"."GETVALUE"(),'1009')))
   8 - filter("T"."STORED_VALUE"=:B1 AND "T"."ORG_ENTITY_ID"=1 AND "T"."ENTERPRISE_ID"=100000)
  10 - access("QRY1"."VISIT_ID"="QRY3"."VISIT_ID"(+))
  12 - filter(("F"."CAP_PROPOSED_ON"=:12 OR :12 IS NULL) AND ("F"."CAP_LAST_MODIFIED_ON"=:13 OR :13 
              IS NULL))
  17 - filter((:6 IS NULL OR "B"."FACILITY_NAME"=:6) AND (:7 IS NULL OR "B"."CITY"=:7) AND (:8 IS 
              NULL OR "B"."COUNTRY"=:8) AND (:5 IS NULL OR "B"."FACILITY_ID"=TO_NUMBER(:5)))
  18 - access("A"."VISIT_ID"="B"."VISIT_ID")
  19 - filter((:14 IS NULL OR "A"."VISIT_STATUS"=:14) AND (:1 IS NULL OR "A"."VISIT_ID" LIKE :1) AND 
              (:2 IS NULL OR "A"."PROTOCOL_NAME" LIKE :2) AND (:9 IS NULL OR 
              "A"."APPLE_VERIFICATION_LEAD"=TO_NUMBER(:9)) AND (:10 IS NULL OR "A"."APPLE_LEAD"=TO_NUMBER(:10)) 
              AND (:11 IS NULL OR "A"."CAP_PROPOSAL_DEADLINE"=:11) AND (:3 IS NULL OR "A"."END_DATE">=:3) AND (:4 
              IS NULL OR "A"."END_DATE"<=:4) AND (:3 IS NULL OR "A"."START_DATE">=:3) AND (:4 IS NULL OR 
              "A"."START_DATE"<=:4))
  21 - access("QRY1"."VISIT_ID"="F"."VISIT_ID"(+))

Open in new window

0
 
sventhanCommented:
Is your Query involved a view (selecting from a view)?

What is the Oracle version?

I would see more Hash JOIN than nested loops.

if it is using a View then you might consider a hint to disable a view merging.
0
 
Swadhin RaySenior Technical Engineer Author Commented:
My oracle version is :

11.2.0.3.0
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
johnsone, to answer your question: as that subquery is JOINED on the grouped column, oracle could very well optimize this to do the group by only for the values of the joined column ...

anyhow: if your table columns are all < 100, your query shall not take 1 minute, unless you have a really serious issue on either the db server in general, the network, or the client pc.

however, if any of the "tables" is not really a table, but a view, a external table, an alias to a table on a remote db etc ... this could also be a part of the performance issue.

please clarify
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@Guy Hengel : Yes true this is because of one function i.e. MS_APPS_UTILITIES.GET_DISPLAY_VALUE

Which is internally calling :

SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
           AND lovvalue.locale_id = NVL (si_db_locale_sv.getvalue,1009 );


This is the only plance where this particular SQL is taling more CPU time to execute.


If I have 85 rows than it is hitting 85 times in the database.

Can we do something on this ?

>> ms_qs_list_of_values is a view  and  ms_qs_lov_names is ta table.

I tried to create the index on ms_qs_lov_names table but still no change in the execution or in plan.

If I tried to change the function like below:


create or replace
FUNCTION get_display_value (
                               p_lov_name IN ms_qs_lov_org_values_v.lov_name%TYPE       
                               ,p_stored_value IN ms_qs_lov_org_values_v.stored_value%TYPE
               ) RETURN VARCHAR2
                                RESULT_CACHE
                                 RELIES_ON (ms_qs_list_of_values) IS
       v_display_val VARCHAR2(4000);
    BEGIN
 
     SELECT DISPLAYED_VALUE into v_display_val
     
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name =p_lov_name
            and LOVVALUE.STORED_VALUE =  p_stored_value
            AND lovvalue.locale_id =
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            );
 
      RETURN v_display_val ;
      end;


But no change again.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, what is the explain plan of the function's query itself?
SELECT DISPLAYED_VALUE into v_display_val
     
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name =p_lov_name
            and LOVVALUE.STORED_VALUE =  p_stored_value
            AND lovvalue.locale_id =
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            ); 

Open in new window


if you remove the function  call from the original query, the performance problem goes away?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
yes .. if I remove this function then the plan changes drastically.

If you the plans what I have shared all of them have shown as below:

-- Identified from the plan 
   1 - access("X"."LOV_VALUE_ID"="TL"."LOV_VALUE_ID"(+) AND "X"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   2 - access("X"."LOV_ID"="LOVNAME"."LOV_ID")
   

Open in new window


Now this is getting pulled from the function which internally calls this below SQL :

SELECT DISPLAYED_VALUE into v_display_val
     
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name =p_lov_name
            and LOVVALUE.STORED_VALUE =  p_stored_value
            AND lovvalue.locale_id =
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            ); 

Open in new window


ms_qs_list_of_values  is a view.

Here is the metadata of the view:

CREATE OR REPLACE FORCE VIEW 
MS_QS_LIST_OF_VALUES
("LOV_VALUE_ID", "LOV_ID", "ORG_ENTITY_ID", "STORED_VALUE", "DISPLAYED_VALUE", "LOCALE_ID", "START_DATE", "END_DATE", "ENTERPRISE_ID", "LAST_UPDATED_BY", "LAST_UPDATE_DATE", "CREATED_BY", "CREATION_DATE", "XML", "COMMENTS", "DISPLAY_ORDER")
AS
  SELECT X.LOV_VALUE_ID,
    X.LOV_ID,
    X.ORG_ENTITY_ID,
    X.STORED_VALUE,
    NVL(TL.DISPLAYED_VALUE, X.DISPLAYED_VALUE) "DISPLAYED_VALUE",
    X.LOCALE_ID,
    X.START_DATE,
    X.END_DATE,
    X.ENTERPRISE_ID,
    X.LAST_UPDATED_BY,
    X.LAST_UPDATE_DATE,
    X.CREATED_BY,
    X.CREATION_DATE,
    X.XML,
    X.COMMENTS,
    X.DISPLAY_ORDER
  FROM
    (SELECT T.LOV_VALUE_ID,
      T.LOV_ID,
      T.ORG_ENTITY_ID,
      T.STORED_VALUE,
      T.DISPLAYED_VALUE,
      S.LOCALE_ID,
      T.START_DATE,
      T.END_DATE,
      T.ENTERPRISE_ID,
      T.LAST_UPDATED_BY,
      T.LAST_UPDATE_DATE,
      T.CREATED_BY,
      T.CREATION_DATE,
      T.XML,
      T.COMMENTS,
      T.DISPLAY_ORDER
    FROM MS_QS_LIST_OF_VALUES_T T,
      SI_ENABLED_LOCALES S
    ) X,
    MS_QS_LIST_OF_VALUES_TL TL
  WHERE X.LOV_VALUE_ID = TL.LOV_VALUE_ID (+)
  AND X.LOCALE_ID      = TL.LOCALE_ID (+);

Open in new window



And here is plan for the SQL which I am calling :

 
 explain plan for 
 SELECT  DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name ='MS SRA VISIT STATUS'
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            );
                            
Plan hash value: 4049286291
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |     4 | 17324 |    12   (9)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER          |                         |     4 | 17324 |    12   (9)| 00:00:01 |
|   2 |   TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|*  3 |   HASH JOIN                     |                         |     4 |  9212 |    10  (10)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL            | MS_QS_LOV_NAMES         |     1 |    34 |     2   (0)| 00:00:01 |
|   5 |    VIEW                         |                         |  1304 |  2889K|     7   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                |                         |  1304 | 53464 |     7   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T  |  1304 | 45640 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$07BDC5B4" "T"@"SEL$3")
      LEADING(@"SEL$07BDC5B4" "SI_LOCALES"@"SEL$4" "T"@"SEL$3")
      FULL(@"SEL$07BDC5B4" "T"@"SEL$3")
      INDEX_RS_ASC(@"SEL$07BDC5B4" "SI_LOCALES"@"SEL$4" ("SI_LOCALES"."LOCALE_ID"))
      SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "TL"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "TL"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "X"@"SEL$2")
      LEADING(@"SEL$F5BB74E1" "LOVNAME"@"SEL$1" "X"@"SEL$2" "TL"@"SEL$2")
      FULL(@"SEL$F5BB74E1" "TL"@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "X"@"SEL$2")
      FULL(@"SEL$F5BB74E1" "LOVNAME"@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$4")
      OUTLINE_LEAF(@"SEL$07BDC5B4")
      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):
---------------------------------------------------
 
   1 - access("X"."LOV_VALUE_ID"="TL"."LOV_VALUE_ID"(+) AND "X"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   3 - access("X"."LOV_ID"="LOVNAME"."LOV_ID")
   4 - filter("LOVNAME"."LOV_NAME"='MS SRA VISIT STATUS' AND "LOVNAME"."ENTERPRISE_ID"=100000)
   7 - filter("ENABLED_FLAG"='Y')
   8 - access("LOCALE_ID"=TO_NUMBER(NVL("SI_DB_LOCALE_SV"."GETVALUE"(),'1009')))
   9 - filter("T"."ORG_ENTITY_ID"=1 AND "T"."ENTERPRISE_ID"=100000)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think that view has an issue ....
  FROM MS_QS_LIST_OF_VALUES_T T,
      SI_ENABLED_LOCALES S

Open in new window


the subquery does basically a cross join of the 2 tables, is this really correct?

MS_QS_LIST_OF_VALUES_T does not have a locale_id field?

do you have a (one single) index on these fields?
lov_id  + org_entity_id  + ENTERPRISE_ID  + STORED_VALUE

otherwise I would presumably change this query/view technique
0
 
Swadhin RaySenior Technical Engineer Author Commented:
>> SI_ENABLED_LOCALES table is only having one records with the local id


>>MS_QS_LIST_OF_VALUES_T does not have a locale_id field?

do you have a (one single) index on these fields?
lov_id  + org_entity_id  + ENTERPRISE_ID  + STORED_VALUE

Yes it has on the below:

MS_QS_LIST_OF_VALUES_N1 index on LOV_ID

How we can change this SQL ?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
What I did was :

SQL> alter index MS_QS_LIST_OF_VALUES_N1 unusable;


SQL> create index MS_QS_LIST_OF_VALUES_IDX_1 on MS_QS_LIST_OF_VALUES_T(lov_id  , org_entity_id  , ENTERPRISE_ID  , STORED_VALUE);


Then I run :
 
 explain plan for 
 SELECT  DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name ='MS SRA VISIT STATUS'
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            );
 

Open in new window


Now see the explain plan (It's same as earlier):

    
      Plan hash value: 4049286291
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |     4 | 17324 |    12   (9)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER          |                         |     4 | 17324 |    12   (9)| 00:00:01 |
|   2 |   TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|*  3 |   HASH JOIN                     |                         |     4 |  9212 |    10  (10)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL            | MS_QS_LOV_NAMES         |     1 |    34 |     2   (0)| 00:00:01 |
|   5 |    VIEW                         |                         |  1304 |  2889K|     7   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                |                         |  1304 | 53464 |     7   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T  |  1304 | 45640 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$07BDC5B4" "T"@"SEL$3")
      LEADING(@"SEL$07BDC5B4" "SI_LOCALES"@"SEL$4" "T"@"SEL$3")
      FULL(@"SEL$07BDC5B4" "T"@"SEL$3")
      INDEX_RS_ASC(@"SEL$07BDC5B4" "SI_LOCALES"@"SEL$4" ("SI_LOCALES"."LOCALE_ID"))
      SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "TL"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "TL"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "X"@"SEL$2")
      LEADING(@"SEL$F5BB74E1" "LOVNAME"@"SEL$1" "X"@"SEL$2" "TL"@"SEL$2")
      FULL(@"SEL$F5BB74E1" "TL"@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "X"@"SEL$2")
      FULL(@"SEL$F5BB74E1" "LOVNAME"@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$4")
      OUTLINE_LEAF(@"SEL$07BDC5B4")
      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):
---------------------------------------------------
 
   1 - access("X"."LOV_VALUE_ID"="TL"."LOV_VALUE_ID"(+) AND "X"."LOCALE_ID"="TL"."LOCALE_ID"(+))
   3 - access("X"."LOV_ID"="LOVNAME"."LOV_ID")
   4 - filter("LOVNAME"."LOV_NAME"='MS SRA VISIT STATUS' AND "LOVNAME"."ENTERPRISE_ID"=100000)
   7 - filter("ENABLED_FLAG"='Y')
   8 - access("LOCALE_ID"=TO_NUMBER(NVL("SI_DB_LOCALE_SV"."GETVALUE"(),'1009')))
   9 - filter("T"."ORG_ENTITY_ID"=1 AND "T"."ENTERPRISE_ID"=100000)
   

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
thanks
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.