Home » RDBMS Server » Performance Tuning » Query became very slow (Oracle 11.2.0.3)
Query became very slow [message #660100] Wed, 08 February 2017 23:04 Go to next message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
Hello, we are on Oracle 11.2.0.3 and since today one of our queries started running very slow (went from 71 ms to 3 minutes), which is causing a report to basically crash. Our client added more data to this table and I think the query (I am just pasting the part that is very slow here) would have to be changed.

SELECT distinct a.usr_key, a.actiondate
FROM aging_tracking a  
WHERE a.actiondate =
  (SELECT MAX(actiondate)
  FROM aging_tracking b
  WHERE b.usr_key = a.usr_key
  AND b.status    = 'P'
  )
AND a.actiondate BETWEEN sysdate AND sysdate +30

- Ran Explain Plan, which showed that actiondate has an index on it and it is being used. Without this last line of code (BETWEEN dates) the query runs super fast. Is there anything I can do to improve it?

thank you in advance for any input!
Re: Query became very slow [message #660103 is a reply to message #660100] Thu, 09 February 2017 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65199
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Re: Query became very slow [message #660105 is a reply to message #660100] Thu, 09 February 2017 02:08 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
Is it possible for any one USR_KEY to have multiple rows with the same ACTION_DATE, or should the the combination of the two be unique?
Re: Query became very slow [message #660137 is a reply to message #660105] Thu, 09 February 2017 10:56 Go to previous messageGo to next message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
Yes, it is possible for the USR_KEY to have multiple action dates. I re-wrote this part of query as follows:

SELECT usr_key, actiondate
FROM
(SELECT usr_key, actiondate, row_number() OVER(partition by usr_key ORDER BY usr_key DESC) a
FROM aging_tracking
WHERE actiondate BETWEEN sysdate AND (sysdate+30)
AND status    = 'P') b
WHERE  A=1


I am getting 154 records more though, so not sure at this point what is going on.

thank you!
Re: Query became very slow [message #660138 is a reply to message #660137] Thu, 09 February 2017 10:59 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
I didn't ask if a usr_key can have multiple action_dates, but if a usr_key can have the same action_date more than once. ie, is usr_key||action_date unique?
Re: Query became very slow [message #660139 is a reply to message #660138] Thu, 09 February 2017 11:14 Go to previous messageGo to next message
lotusdeva
Messages: 201
Registered: March 2005
Senior Member
yes, it can have the same actiondate more than once
Re: Query became very slow [message #660140 is a reply to message #660139] Thu, 09 February 2017 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65199
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Thu, 09 February 2017 07:18

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
This is to avoid all these roundtrips and waste of time and energy.

[Updated on: Fri, 24 March 2017 11:43]

Report message to a moderator

Re: Query became very slow [message #661560 is a reply to message #660100] Thu, 23 March 2017 14:12 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Reformatted the Query to assist with analysis.



SELECT DISTINCT
       a.usr_key'
      ,a.actiondate
FROM   aging_tracking a  
WHERE  a.actiondate = (SELECT MAX(actiondate)
                       FROM   aging_tracking b
                       WHERE  b.usr_key = a.usr_key
                       AND    b.status  = 'P'
                      )
AND    a.actiondate BETWEEN SYSDATE AND SYSDATE + 30



Suggested Approach:

1. Check and possibly refresh Database STATS on the "aging_tracking" Table.
Fresh Stats are what helps with determining how the Query will be processed
by the Oracle Cost Based Optimizer (CBO).

2. If you are still having problems after refreshing Stats,
then consider a different Composite Index including the "actiondate".
This new index may possibly replace an existing index with the column
"actiondate" in it. Try Testing the solution if possible. The following
Index will resolve the query with out the need to read the Table Data.
If an Index is created than it is a good idea to refresh the Stats
on the Table "aging_tracking".

If a Query can be resolved by an the data in a index then the table does not
have to be read into the Buffer Cache fro processing.

In the CREATE INDEX Script the Index Name and Tablespace will need modification.


CREATE INDEX aging_tracking_nu_i ON aging_tracking
  ( actiondate
   ,usr_key
   ,status)
TABLSPACE ???;


Also you may want to consider how you will want to handle the possibility
that the MAX(actiondate) returns NULL for some "usr_key"s?
Is "Null" an acceptable value? (It should be, but it is your decision.)
Re: Query became very slow [message #661580 is a reply to message #661560] Fri, 24 March 2017 02:54 Go to previous message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
Has the execution plan changed or is it static?
Previous Topic: How to identify index type for partitioned tables
Next Topic: Query is taking longer duration
Goto Forum:
  


Current Time: Mon Nov 20 15:09:04 CST 2017

Total time taken to generate the page: 0.01845 seconds