Solved

SQL running slow

Posted on 2014-01-23
15
301 Views
Last Modified: 2014-01-29
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 ?
0
Comment
Question by:Swadhin Ray
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39802695
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39802725
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
 
LVL 34

Expert Comment

by:johnsone
ID: 39802730
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39802764
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
 
LVL 18

Expert Comment

by:sventhan
ID: 39802790
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39802811
My oracle version is :

11.2.0.3.0
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39802820
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
PRTG Network Monitor: Intuitive Network Monitoring

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

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39802844
@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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39802871
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39802897
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39802950
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39802990
>> 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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39803095
>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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39803255
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39819915
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

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

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now