Solved

Tuning SQL query

Posted on 2013-11-13
13
450 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
ID: 39644035
Do you have an explain plan? (in text not image)
0
 
LVL 48

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
 
LVL 48

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
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: 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 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 500 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 37

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 37

Accepted Solution

by:
Geert Gruwez earned 500 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 37

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

930 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

14 Experts available now in Live!

Get 1:1 Help Now