Home » RDBMS Server » Performance Tuning » sql need tuning - high cpu_time and buffer gets (oracle 12cR1 EE RAC, RHEL 6.5)
sql need tuning - high cpu_time and buffer gets [message #665162] Wed, 23 August 2017 13:01 Go to next message
gniewko
Messages: 1
Registered: August 2017
Junior Member
Hello,

I need help in tuning the query.
SELECT order_office_code,
                     priority,
                     MIN (application_date) application_date
                FROM xps.application a
                     LEFT JOIN
                     (SELECT o.unit_code
                        FROM xps.prior_sched s
                             JOIN xps.priority_schedule_unit o
                                ON (s.id = o.prior_schedule_id)
                       WHERE     SYSDATE BETWEEN s.SCHEDULE_FROM
                                             AND s.SCHEDULE_TO
                             AND priority_level = -1) v
                        ON a.order_office_code LIKE v.UNIT_CODE || '%'
               WHERE     status_id = 'b'
                     AND v.UNIT_CODE IS NULL
                     AND priority > 0
            GROUP BY order_office_code, priority
            ORDER BY 2 DESC, 3 ASC;

explain plan in pdf


Thanks for the tips. I no longer have an idea Sad

Best regards
  • Attachment: export.pdf
    (Size: 211.65KB, Downloaded 30 times)
Re: sql need tuning - high cpu_time and buffer gets [message #665165 is a reply to message #665162] Wed, 23 August 2017 17:56 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: sql need tuning - high cpu_time and buffer gets [message #665173 is a reply to message #665165] Thu, 24 August 2017 03:41 Go to previous messageGo to next message
gazzag
Messages: 905
Registered: November 2010
Location: Bristol, UK
Senior Member
As well as BlackSwan's points, please paste the contents of the PDF file enclosed in [CODE] tags to avoid us having to download anything.
Re: sql need tuning - high cpu_time and buffer gets [message #665178 is a reply to message #665162] Thu, 24 August 2017 08:11 Go to previous messageGo to next message
Alien
Messages: 275
Registered: June 1999
Senior Member
Hi,

are you sure this code does what you want it to do?

ON a.order_office_code LIKE v.UNIT_CODE || '%'
and
AND v.UNIT_CODE IS NULL

do not really make sense.

Regards,

Alien
Re: sql need tuning - high cpu_time and buffer gets [message #665182 is a reply to message #665178] Thu, 24 August 2017 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 12929
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's anti-join - equivalent to not exists:
AND NOT EXISTS (SELECT 1
                FROM xps.prior_sched s
                JOIN xps.priority_schedule_unit o
                ON (s.id = o.prior_schedule_id)
                WHERE SYSDATE BETWEEN s.SCHEDULE_FROM AND s.SCHEDULE_TO
                AND priority_level = -1
                AND a.order_office_code LIKE o.UNIT_CODE || '%'
               )
Re: sql need tuning - high cpu_time and buffer gets [message #665183 is a reply to message #665182] Thu, 24 August 2017 09:34 Go to previous message
Alien
Messages: 275
Registered: June 1999
Senior Member
My bad, I need more coffee.
Now I see it. I was looking at the ||'%' and missed the left join.

Regards,

Alien
Previous Topic: Can we delete specific execution plan in the db buffer for a statement
Next Topic: Performace issue
Goto Forum:
  


Current Time: Tue Nov 21 23:23:35 CST 2017

Total time taken to generate the page: 0.01733 seconds