Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Tuning SQL query

Posted on 2013-11-13
13
Medium Priority
?
462 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39644035
Do you have an explain plan? (in text not image)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39644045
            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
ID: 39644113
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39644119
you need to provide the explain plan
0
 
LVL 16

Author Comment

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

Author Comment

by:Swadhin Ray
ID: 39644158
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 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 2000 total points
ID: 39644283
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 38

Expert Comment

by:Geert Gruwez
ID: 39644305
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
ID: 39647142
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 38

Accepted Solution

by:
Geert Gruwez earned 2000 total points
ID: 39647157
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 38

Expert Comment

by:Geert Gruwez
ID: 39647199
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
ID: 39650920
Using with and putting the functions outside works for me... thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

688 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