Solved

Tuning SQL query

Posted on 2013-11-13
13
448 Views
Last Modified: 2013-11-15
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
Comment
Question by:Swadhin Ray
  • 6
  • 4
  • 3
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Do you have an explain plan? (in text not image)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
            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
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
you need to provide the explain plan
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
When I am trying to get the explain plan for this query I am getting invalid SQL
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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
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
Comment Utility
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
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 500 total points
Comment Utility
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
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
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
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
Comment Utility
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
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
Comment Utility
Using with and putting the functions outside works for me... 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 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

18 Experts available now in Live!

Get 1:1 Help Now