Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

Tuning SQL query

Hello Experts,

Need help in tuning this SQL statements:

SELECT
       DECODE(vsetup.visit_status, '4', 'TODAY', '1','UPCOMING', '2', 'UPCOMING','3' ,'UPCOMING', '5','FINAL', '6','FINAL') report_type
      ,(ms_apps_utilities.get_display_value(100000  ,'MS SRA VISIT STATUS',vsetup.visit_status)) AS VISIT_STATUS
      ,vsetup.visit_status as visit_status_id
      ,lp.visit_id
      ,LP.USER_ID
      ,lp.APP_ASSIGNMENT_ID  
      ,lp.metric_id
      ,lp.pid
      ,lp.instance_id
      ,LP.IS_OFFLINE
      ,fac.facility_id
      ,fac.facility_name
      ,fac.city
      ,fac.state_province
      ,fac.country
      ,fac.facility_address
      ,FAC.FACILITY_LOCAL_NAME
      ,fac.facility_local_address
      ,FAC.LONGITUDE
      ,FAC.LATITUDE
      ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760852'
                                        ,'MS_SRA_VISIT' ,lp.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                             as VISIT_EDIT_FLAG
    ,DECODE (MS_SRA_SECURITY.CHECK_USER_FORM_ACCESS('2109760852'
                                        ,'MS_SRA_VISIT_SETUP' ,vsetup.visit_id),0,'D',1,'N', 2,'N',3,'Y',4,'Y' )
                             as VS_EDIT_FLAG
        ,vsetup.DD_PROCESS_INSTANCE_ID as vs_pid
        , MS_APPS_UTILITIES.GET_INFOLET_ID('MS_SRA_VISIT_SETUP')  as VS_METRIC_ID
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.ONSITE_LEAD) as ONSITE_LEAD
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_LEAD) as APPLE_LEAD
        ,MS_APPS_UTILITIES.GET_USER_FULL_NAME(VSETUP.APPLE_VERIFICATION_LEAD) as APPLE_VERIFICATION_LEAD
        ,ms_apps_utilities.get_user_full_name(VSETUP.SR_PROGRAM_MANAGER) as SR_PROGRAM_MANAGER
 ,vsetup.start_date
      ,vsetup.protocol_name
      ,to_char(to_date(vsetup.tpa_submission_deadline
                      ,'dd-mon-yy')
              ,'MON dd,yyyy') as TPA_DATE
      ,to_char(to_date(vsetup.FINAL_REPORT_DEADLINE
                      ,'dd-mon-yy')
              ,'MON dd,yyyy') as FINAL_REPORT_DEADLINE        
      ,(to_char(to_date(vsetup.start_date
                       ,'dd-mon-yy')
               ,'MON dd') || to_char(to_date(vsetup.end_date
                                             ,'dd-mon-yy')
                                     ,' - MON dd,yyyy')) as SHOW_DATE
                                     
,AUD.AUDIT_COMPANY
,AUD.TPA_AUDITOR
,aud.IS_LEAD 
,ASN.ASSIGNMENTS
 from MS_SRA_VISIT_LP_RPT LP,
  MS_SRA_VISIT_SETUP VSETUP,
( SELECT *
from   (select
      VISIT_ID
      ,FACILITY_ID
      ,DD_OBJECT_TYPE 
      ,facility_name
      ,city
      ,state_province
      ,country
      ,facility_address
      ,FACILITY_LOCAL_NAME
      ,facility_local_address
      ,LONGITUDE
        ,LATITUDE
               ,Row_number() 
                 over ( 
                   PARTITION BY visit_id 
                   ORDER BY CASE WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1 WHEN 
                 DD_OBJECT_TYPE = 
                 'MS_SRA_VISIT_SETUP' then 2 end) RN 
        FROM   ms_sra_facility_info
        where  DD_OBJECT_TYPE in ( 'MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP' )) 
where  RN = 1) fac,
(select  
a.AUDITOR_ASSIGNMENTS,a.VISIT_ID ,
nvl(coalesce(C.CATEGORY_NAME , S.SECTION_NAME, 'Facility'),'No Assignment to this User') as ASSIGNMENTS
from ms_sra_visit_setup_asn a 
left join MS_SRA_PROTOCOL_CATEGORY C
on SUBSTR(a.AUDITOR_ASSIGNMENTS,3,225) = C.CATEGORY_ID 
left join MS_SRA_PROTOCOL_SECTION S
on SUBSTR(a.AUDITOR_ASSIGNMENTS,3,255) =S.SECTION_ID) ASN,
(select 
 ms_apps_utilities.get_user_full_name(auditor) AS tpa_auditor
      ,(SELECT org_entity_name
          FROM si_org_entities_t
         where ORG_ENTITY_ID = AUD_TPA_ID) as AUDIT_COMPANY
      ,IS_LEAD,visit_id
from ms_sra_visit_setup_aud) aud
where VSETUP.VISIT_ID =LP.VISIT_ID
  and FAC.VISIT_ID =VSETUP.VISIT_ID 
  and ASN.VISIT_ID = VSETUP.VISIT_ID 
  and aud.visit_id =VSETUP.VISIT_ID 
   and VSETUP.VISIT_STATUS in ('1','2','3','4','5','6')
   and LP.USER_ID = MS_APPS_UTILITIES.GET_USER_ID('2109760852')
   order by DECODE(VSETUP.VISIT_STATUS, '4', 1, '5', 2, '6', 3, '1',4,'2',5,'3',6) ;

Open in new window

0
Swadhin Ray
Asked:
Swadhin Ray
  • 6
  • 4
  • 3
2 Solutions
 
PortletPaulCommented:
Do you have an explain plan? (in text not image)
0
 
PortletPaulCommented:
            SELECT *
            FROM (
                SELECT
                      VISIT_ID
                    , FACILITY_ID
                    , DD_OBJECT_TYPE
                    , facility_name
                    , city
                    , state_province
                    , country
                    , facility_address
                    , FACILITY_LOCAL_NAME
                    , facility_local_address
                    , LONGITUDE
                    , LATITUDE
                    , Row_number() OVER (
                                        PARTITION BY visit_id ORDER BY CASE 
                                                WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1
                                                WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT_SETUP' THEN 2
                                                END
                                        ) RN
                FROM ms_sra_facility_info
                WHERE DD_OBJECT_TYPE IN ('MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP')
                )
            WHERE RN = 1

Open in new window

why not just limit this to WHERE DD_OBJECT_TYPE = 'MS_SRA_VISIT' ?
and why do you need row_number() ?
0
 
Swadhin RaySenior Technical Engineer Author Commented:
because I can have same facility with multiple visit_id .

If there is a visit and I have two records in facility info table then I need to pick the one based on the DD_OBJECT_TYPE .

So I should pick it from MS_SRA_VISIT and if I do not have 'MS_SRA_VISIT' record the I should take it from MS_SRA_VISIT_SETUP.


This is where it is taking long time ...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PortletPaulCommented:
you need to provide the explain plan
0
 
Swadhin RaySenior Technical Engineer Author Commented:
When I am trying to get the explain plan for this query I am getting invalid SQL
0
 
Swadhin RaySenior Technical Engineer Author Commented:
When I run a explain plan in PLSQL Developer then i am getting the plan as below:

SELECT STATEMENT, GOAL = ALL_ROWS			21	2	16818
 TABLE ACCESS BY INDEX ROWID	SGM	SI_ORG_ENTITIES_T	1	1	14
  INDEX SKIP SCAN	SGM	SI_ORG_ENTITIES_N1	1	1	
 SORT ORDER BY			21	2	16818
  HASH JOIN			20	2	16818
   HASH JOIN OUTER			13	2	5412
    HASH JOIN			11	2	5368
     HASH JOIN			6	1	153
      NESTED LOOPS					
       NESTED LOOPS			3	2	246
        TABLE ACCESS FULL	SGM	MS_SRA_VISIT_LP_RPT	2	2	68
        INDEX UNIQUE SCAN	SGM	MS_SRA_VISIT_SETUP_PK	1	1	
       TABLE ACCESS BY INDEX ROWID	SGM	MS_SRA_VISIT_SETUP	1	1	89
      TABLE ACCESS FULL	SGM	MS_SRA_VISIT_SETUP_AUD	2	726	21780
     VIEW	SYS		5	840	2126040
      HASH JOIN RIGHT OUTER			5	840	43680
       TABLE ACCESS FULL	SGM	MS_SRA_PROTOCOL_CATEGORY	2	25	750
       TABLE ACCESS FULL	SGM	MS_SRA_VISIT_SETUP_ASN	2	840	18480
    TABLE ACCESS FULL	SGM	MS_SRA_PROTOCOL_SECTION	2	12	264
   VIEW	SGM		6	599	3416097
    WINDOW SORT PUSHED RANK			6	599	94043
     TABLE ACCESS FULL	SGM	MS_SRA_FACILITY_INFO	5	599	94043

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
I got the plan  as below :

PLAN_TABLE_OUTPUT
Plan hash value: 2822095280
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     2 | 16818 |    21  (24)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID      | SI_ORG_ENTITIES_T        |     1 |    14 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                 | SI_ORG_ENTITIES_N1       |     1 |       |     1   (0)| 00:00:01 |
|   3 |  SORT ORDER BY                    |                          |     2 | 16818 |    21  (24)| 00:00:01 |
|*  4 |   HASH JOIN                       |                          |     2 | 16818 |    20  (20)| 00:00:01 |
|*  5 |    HASH JOIN OUTER                |                          |     2 |  5412 |    13  (16)| 00:00:01 |
|*  6 |     HASH JOIN                     |                          |     2 |  5368 |    11  (19)| 00:00:01 |
|*  7 |      HASH JOIN                    |                          |     1 |   153 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS                |                          |       |       |            |          |
|   9 |        NESTED LOOPS               |                          |     2 |   246 |     3   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS FULL         | MS_SRA_VISIT_LP_RPT      |     2 |    68 |     2   (0)| 00:00:01 |
|* 11 |         INDEX UNIQUE SCAN         | MS_SRA_VISIT_SETUP_PK    |     1 |       |     1   (0)| 00:00:01 |
|* 12 |        TABLE ACCESS BY INDEX ROWID| MS_SRA_VISIT_SETUP       |     1 |    89 |     1   (0)| 00:00:01 |
|  13 |       TABLE ACCESS FULL           | MS_SRA_VISIT_SETUP_AUD   |   726 | 21780 |     2   (0)| 00:00:01 |
|  14 |      VIEW                         |                          |   840 |  2076K|     5  (20)| 00:00:01 |
|* 15 |       HASH JOIN RIGHT OUTER       |                          |   840 | 43680 |     5  (20)| 00:00:01 |
|  16 |        TABLE ACCESS FULL          | MS_SRA_PROTOCOL_CATEGORY |    25 |   750 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL          | MS_SRA_VISIT_SETUP_ASN   |   840 | 18480 |     2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS FULL             | MS_SRA_PROTOCOL_SECTION  |    12 |   264 |     2   (0)| 00:00:01 |
|* 19 |    VIEW                           |                          |   599 |  3336K|     6  (17)| 00:00:01 |
|* 20 |     WINDOW SORT PUSHED RANK       |                          |   599 | 94043 |     6  (17)| 00:00:01 |
|* 21 |      TABLE ACCESS FULL            | MS_SRA_FACILITY_INFO     |   599 | 94043 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORG_ENTITY_ID"=:B1)
       filter("ORG_ENTITY_ID"=:B1)
   4 - access("from$_subquery$_004"."VISIT_ID"="VSETUP"."VISIT_ID")
   5 - access("S"."SECTION_ID"(+)=TO_NUMBER(SUBSTR("A"."AUDITOR_ASSIGNMENTS",3,255)))
   6 - access("from$_subquery$_009"."VISIT_ID"="VSETUP"."VISIT_ID")
   7 - access("VISIT_ID"="VSETUP"."VISIT_ID")
  10 - filter("LP"."USER_ID"="MS_APPS_UTILITIES"."GET_USER_ID"('2109760852'))
  11 - access("VSETUP"."VISIT_ID"="LP"."VISIT_ID")
  12 - filter("VSETUP"."VISIT_STATUS"='1' OR "VSETUP"."VISIT_STATUS"='2' OR 
              "VSETUP"."VISIT_STATUS"='3' OR "VSETUP"."VISIT_STATUS"='4' OR "VSETUP"."VISIT_STATUS"='5' OR 
              "VSETUP"."VISIT_STATUS"='6')
  15 - access("C"."CATEGORY_ID"(+)=TO_NUMBER(SUBSTR("A"."AUDITOR_ASSIGNMENTS",3,225)))
  19 - filter("RN"=1)
  20 - filter(ROW_NUMBER() OVER ( PARTITION BY "VISIT_ID" ORDER BY CASE "DD_OBJECT_TYPE" WHEN 
              'MS_SRA_VISIT' THEN 1 WHEN 'MS_SRA_VISIT_SETUP' THEN 2 END )<=1)
  21 - filter("DD_OBJECT_TYPE"='MS_SRA_VISIT' OR "DD_OBJECT_TYPE"='MS_SRA_VISIT_SETUP')

Open in new window

0
 
Geert GruwezOracle dbaCommented:
you've got a load of function calls within the body of the query
sometimes it helps to load constants (or unchanging values) first:

it's rather difficult to see which are constants and which aren't from this side.

then use the precalculated in memory tables to drawn upon in the body

with 
  x1 as (
  select MS_APPS_UTILITIES.GET_USER_ID('2109760852') LPUSER_ID from dual),
  x2 as (
  select /*+ MATERIALIZE */ 
   ms_apps_utilities.get_user_full_name(auditor) AS tpa_auditor
      ,(SELECT org_entity_name
          FROM si_org_entities_t
         where ORG_ENTITY_ID = AUD_TPA_ID) as AUDIT_COMPANY
      ,IS_LEAD,visit_id
from ms_sra_visit_setup_aud) 
select ... 

Open in new window


also the index skip scan is usually a bad performer
0
 
Geert GruwezOracle dbaCommented:
what helps me most in cost-based environments with bigger queries is running the query with a unique literal, running it and afterwards checking where it spent most time

alter session set statistics_level = all;
SELECT -- UNIQUE_ID_001
       DECODE(vsetup.visit_status, '4', 'TODAY', '1','UPCOMING', '2', 'UPCOMING','3'
...;

select sql_text, hash_value, sql_id, executions, parse_calls, disk_reads, buffer_gets, sql_fulltext 
from v$sql
where sql_text like '%--UNIQUE_ID_001%'
  and users_executing = 0; 

Open in new window


find the hash_value and pass it on to below script
this will provide detailed info of every line in the query execution plan and how long it took to process
save the script first as for example: pe.sql
and then execute in sqlplus as (123456 is the hash_value from earlier query)
@pe 123456

define hv=&1
-- set line 2000
-- set pagesize 0
select child_number from gv$sql where hash_value = &hv
/
set verify off
column QueryPlan format a200
--
WITH preqry as 
(
  select /*+ MATERIALIZE */
         LAST_OUTPUT_ROWS, 
         XP.id          as xpid, 
         XP.parent_id, 
         XP.child_number, 
         XP.operation, 
         XP.options, 
         XP.object_owner,
         XP.object_name,  
         XP.position, 
         XP.cardinality, 
         ELAPSED_TIME, 
         XP.partition_start, 
         XP.partition_stop
    from sys.gv_$sql_plan            XP,
         sys.gv_$sql_plan_statistics PS
   where XP.hash_value   = PS.hash_value(+)
     and XP.child_number = PS.child_number(+)
     and XP.id           = PS.operation_id(+)
     and XP.hash_value   = &hv
     and XP.child_number = 0
)
select -- '. ' || to_char(nvl(LAST_OUTPUT_ROWS, 0), 'fm000,000,009') ||
       to_char(XPID, '999') || ' ' || to_char(PARENT_ID, '999') ||
       LPAD(' ',2*(LEVEL-1))
       || ' ' || OPERATION
       || ' ' || OPTIONS 
       || ' ' || OBJECT_OWNER || '.' || OBJECT_NAME
       || ' ' || DECODE(XPID, 0, 'Cost = '|| POSITION)
       || DECODE(CARDINALITY, NULL, NULL, ' (card=' || CARDINALITY || ', tim=' || ELAPSED_TIME || ', rows=' || LAST_OUTPUT_ROWS || ')')
       || DECODE(PARTITION_START, NULL, NULL, ' PART (' || PARTITION_START || ',' || PARTITION_STOP || ')')
        as  QueryPlan
  from preqry
 start with XPID = 0 
connect by prior XPID         = PARENT_ID
       and prior CHILD_NUMBER = CHILD_NUMBER
order by XPID
/

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
After checking I got the below result from the hash value :

   0  SELECT STATEMENT  . Cost = 1
   1    0   FIXED TABLE FULL SYS.X$KGLCURSOR_CHILD  (card=1, tim=912842, rows=0)


Hash Value: 932939196
0
 
Geert GruwezOracle dbaCommented:
lol... that's the execution plan for my last query

if you run your original query with the first line modified like this
SELECT -- UNIQUE_ID_001
 rest of your query here

and then run this query
(ah i see i forgot a space)
select sql_text, hash_value, sql_id, executions, parse_calls, disk_reads, buffer_gets, sql_fulltext
from v$sql
where sql_text like '%-- UNIQUE_ID_001%'
  and users_executing = 0;

then it should show you the original query in sql_text
and give the hash_value for it
it should also have executions 1

that hash_value should be used in my last query for getting the time spent
0
 
Geert GruwezOracle dbaCommented:
if you execute the query for finding the hash from a different session while it's executing use this query instead

select sql_text, hash_value, sql_id, executions, parse_calls, disk_reads, buffer_gets, sql_fulltext
from v$sql
where sql_text like '%-- UNIQUE_ID_001%';

you'll find more than 1 ... :)
0
 
Swadhin RaySenior Technical Engineer Author Commented:
Using with and putting the functions outside works for me... thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

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

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now