I want to do performance tunning ORACLE query.

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
welcome 123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
welcome 123Author Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DcpKingCommented:
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Be careful PortletPaul, there was RIGHT JOIN also.
0
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.