Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

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
0
welcome 123
Asked:
welcome 123
2 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now