?
Solved

I want to do performance tunning ORACLE query.

Posted on 2014-09-19
12
Medium Priority
?
168 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
[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
12 Comments
 
LVL 51

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 51

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 49

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

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 51

Expert Comment

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

Accepted Solution

by:
PortletPaul earned 1000 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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 35

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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

741 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