Solved

I want to do performance tunning ORACLE query.

Posted on 2014-09-19
12
159 Views
Last Modified: 2014-10-14
This query is having many unions using the same table1, which are rules for the sum counts columns in the main query, is this query can be performance tunned in the better way by optimizing the query.

select e.compid as compid, 
'Level1_Manager' as Level1_Manager
,ap.Level2_Manager_id as Level2_Manager
,ac.contact_id as Level3_Manager
,count(*) as totalReq 
,SUM(case when IO_COMMENTS_DT is not null and TRUNC(to_date(IO_COMMENTS_DT,'mm/dd/yy hh24:mi') - to_date(REQUESTED_DT,'mm/dd/yy hh24:mi')) <= 30 then 1 else 0 end) as permissionedrequest
,SUM(case when IO_COMMENTS_DT is null  then 1 else 0 end) as pendingaction
,SUM(case when IO_COMMENTS_DT is not null and TRUNC(to_date(IO_COMMENTS_DT,'mm/dd/yy hh24:mi') - to_date(REQUESTED_DT,'mm/dd/yy hh24:mi')) > 30  then 1 else 0 end) as nonpermissionedactioned
,SUM(case when IO_COMMENTS_DT is null and TRUNC(sysdate - REQUESTED_DT) > 30  then 1 else 0 end) as nonpermissionedunactioned
,SUM(case when IO_COMMENTS_DT is not null then 1 else 0 end) as actioned
, SUM(case when IO_COMMENTS_DT is not null and IS_APPROVED_REJECTED = 'Approved' then 1 else 0 end) as approved
,SUM(case when IO_COMMENTS_DT is not null and IS_APPROVED_REJECTED = 'Rejected' then 1 else 0 end) as rejected
,count(ft.ticket_id) flagged
from table1 e, table2 ac, table3 ap,
(
    select ticket_id
    from
    (
        --Ticket reused 3 or more times
        select ticket_id, count(1)
        from table1
        group by ticket_id
        having ticket_id is not null
        and count(1) >= 3
    )
    union
    select ticket_id
    from
    (
        --Ticket reused beyond 3 days
        select ticket_id, max(requested_dt) - min(requested_dt)
        from table1
        group by ticket_id
        having ticket_id is not null
        and ( max(requested_dt) - min(requested_dt)) >= 3
    )
    union
    select ticket_id
    from
    (
        -- Ticket reused by 3 or more users
        select ticket_id, count(1) from (
            select ticket_id, requested_by
            from table1
            group by ticket_id, requested_by
            having ticket_id is not null
        )
        group by ticket_id
        having count(1) >= 3
    )
    union
    select ticket_id
    from
    (
        --Ticket reused against 2 or more distinct object instances
        select ticket_id, count(1) from (
            select ticket_id, object_nm
            from table1
            group by ticket_id, object_nm
            having ticket_id is not null
        )
        group by ticket_id
        having count(1) >= 2
    )
) ft
where e.table3_id = ac.seal_id(+) --and compid='AUS'
and ac.contact_type(+) = 'Level3 Manager Tech (Level3_Manager)'
and e.table3_id = ap.secid(+)  
and ft.ticket_id (+) = e.ticket_id
group by ap.Level2_Manager_id,compid,ac.contact_id  ;

Open in new window

This is a example with dummy values Output looks like this:
compid,Level1_Manager,Level2_Manager,Level3_Manager,TOTALREQ,PERMISSIONEDREQUEST,PENDINGACTION,NONPERMISSIONEDACTIONED,NONPERMISSIONEDUNACTIONED,ACTIONED,APPROVED,REJECTED,FLAGGED
ABC,LEV1_000,LEV2_650,LEV3_111,100,0,100,0,80,0,0,0,60
DEF,LEV1_000,LEV2_555,LEV3_999,200,0,200,0,170,0,0,0,150
0
Comment
Question by:welcome 123
12 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40332410
What's the problem with the query?
It's running slow or you think is too complex?

Only thing that I can see that you can change for have some (little) improvement in the performance is substituting the "count(*) as totalReq " to "count(1) as totalReq".
The rest we'll need to know the execution plan to check if there's missing indexes or not.
0
 

Author Comment

by:welcome 123
ID: 40332421
Actually I am looking into the union part, where I am using same table1 values for different cases, and calling it in 4 times, instead if I call one time and have all the ticket id of the 4 rules in ft alias table.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40332428
Being called 4 times doesn't means that it's wrong. If the indexes are all created for a correct use, there's no problem with your query.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40332456
Q1. How big is the dataset being queried?
Q2. How many processors can you parallelise this over?

If you're working with a lot of data in table1 then taking the set of unions out and making them run in parallel to a temporary table and joining on that may improve matters. This is because Oracle normally (I believe) does the union stuff sequentially.

If this query gets run a lot you might also consider storing the result of the unions (that you call "ft") into its own table periodically (daily?) and pulling just the preprocessed results from there - i.e. don't do the work if you don't have to!

hth

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40332582
the "count(*) as totalReq "
to "count(1) as totalReq"

will make no difference I'm afraid, the optimizer ignores the * (i.e. it does not pull all fields)
but there is no harm in making the change.

-------------------------

The UNIONS probably are hurting that query, try this, I think it is the equivalent but you should compare it to the current of course.
   SELECT ticket_id
   FROM table1
   WHERE  ticket_id IS NOT NULL
   GROUP BY ticket_id
   HAVING count(1) >= 3 --Ticket reused 3 or more times
       OR (max(requested_dt) - min(requested_dt)) >= 3 -- Ticket reused by 3 or more users
       OR count(distinct object_nm) >= 2 --Ticket reused against 2 or more distinct object instances

Open in new window


I have a question before going further, are all those outer joins actually needed?
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 48

Expert Comment

by:PortletPaul
ID: 40332615
well I don't see much else, I would of course encourage you to stop using (+) and use ANSI standard join syntax instead.

If this is still slower than it should be I suggest you provide the explain plan (as text not an image)
SELECT
      e.compid             AS compid
    , 'Level1_Manager'     AS Level1_Manager
    , ap.Level2_Manager_id AS Level2_Manager
    , ac.contact_id        AS Level3_Manager
    , COUNT(*)             AS totalReq
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NOT NULL AND
            TRUNC(to_date(IO_COMMENTS_DT, 'mm/dd/yy hh24:mi') - to_date(REQUESTED_DT, 'mm/dd/yy hh24:mi')) <= 30 THEN 1
            ELSE 0 END)    AS permissionedrequest
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NULL THEN 1
            ELSE 0 END)    AS pendingaction
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NOT NULL AND
            TRUNC(to_date(IO_COMMENTS_DT, 'mm/dd/yy hh24:mi') - to_date(REQUESTED_DT, 'mm/dd/yy hh24:mi')) > 30 THEN 1
            ELSE 0 END)    AS nonpermissionedactioned
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NULL AND
            TRUNC(sysdate - REQUESTED_DT) > 30 THEN 1
            ELSE 0 END)    AS nonpermissionedunactioned
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NOT NULL THEN 1
            ELSE 0 END)    AS actioned
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NOT NULL AND
            IS_APPROVED_REJECTED = 'Approved' THEN 1
            ELSE 0 END)    AS approved
    , SUM(CASE
            WHEN IO_COMMENTS_DT IS NOT NULL AND
            IS_APPROVED_REJECTED = 'Rejected' THEN 1
            ELSE 0 END)    AS rejected
    , COUNT(ft.ticket_id)  flagged
FROM table1 e
      LEFT JOIN table2 ac
                  ON e.table3_id = ac.seal_id
                        AND ac.contact_type = 'Level3 Manager Tech (Level3_Manager)'
      LEFT JOIN table3 ap
                  ON e.table3_id = ap.secid
      LEFT JOIN (
                  SELECT
                        ticket_id
                  FROM table1
                  WHERE ticket_id IS NOT NULL
                  GROUP BY
                        ticket_id
                  HAVING COUNT(1) >= 3 --Ticket reused 3 or more times
                  OR (MAX(requested_dt) - MIN(requested_dt)) >= 3 -- Ticket reused by 3 or more users
                  OR COUNT(DISTINCT object_nm) >= 2 --Ticket reused against 2 or more distinct object instances
            ) ft
                  ON e.ticket_id = ft.ticket_id
-- WHERE compid='AUS'
GROUP BY
      ap.Level2_Manager_id
    , compid
    , ac.contact_id;

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40332627
Be careful PortletPaul, there was RIGHT JOIN also.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40332662
I have to admit I am so used to ANSI these days I struggle at times with (+)

and ft.ticket_id (+) = e.ticket_id

It is in reverse order but e remains the 'from table' does it really form a right outer join?

I can't test anything and I'm off to bed immediately (way to late for coping with "ye olde joins")
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40332672
where e.table3_id = ac.seal_id(+) --> RIGHT JOIN
and ac.contact_type(+) = 'Level3 Manager Tech (Level3_Manager)' --> LEFT JOIN
and e.table3_id = ap.secid(+)  --> RIGHT JOIN
and ft.ticket_id (+) = e.ticket_id --> LEFT JOIN
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40332806
Apart from the unions, I see two other possible performance problems in this query:
1. The "trunc" and "to_date" conversions on the date columns.  
2. The outer joins.

Applying any operators (like: trunc, to_date, to_char, upper, etc.) to database column values means that Oracle cannot use indexes (if any) on those columns unless you have function-based indexes that exactly match this query syntax.

Outer joins can add a significant performance penalty compared to standard (or inner joins), but this varies based on: the number of records in the table; the size of your Oracle SGA; and other factors.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40333781
I think removing the unions and reducing that part of the from clause should make a difference.
Then:
I think we need an explain plan to go much further

when you do this please also provide the query you used
(that may seem silly but we can't be sure what changes you have made until we see the query being used)
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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

21 Experts available now in Live!

Get 1:1 Help Now