January 20, 2010 at 1:03 am
Having trouble with a query timing out on an Oracle database (error ORA-01652), hope someone out here have some advice. Either on how to go about the ORA-01652 error or optimizing my query. For example, is there a better way of structuring the query without that many joins? Any help will be appreciated!
My query:
SELECT FIELD1, FIELD2, FIELD3…….
FROM J
LEFT OUTER JOIN S ON J.JOB = S.JOB AND
J.NAME = S.NAME
LEFT OUTER JOIN T ON SNUMERIC = T. NUMBER
LEFT OUTER JOIN R ON T.NUMBER = R. NUMBER
LEFT OUTER JOIN COMP C ON T.ID = C.ID AND T.VER = C. VER
LEFT OUTER JOIN PROD PC ON S.PRODUCT = PC. ID AND S. VER = PC. VER
LEFT OUTER JOIN VALUE V ON PC.CODE = V. CODE
WHERE S.NAME IS NOT NULL
AND (R.NUM1, UPPER(R.NAME), R.NUM2) IN
(SELECT RR.NUM1, UPPER(RR.NAME), RR. NUM2
FROM RES RR
LEFT OUTER JOIN TEST TT ON RR.NUM1 = TT.NUM2
GROUP BY RR.NUM1, UPPER(RR.NAME), RR.NUM2
HAVING COUNT(*) = 1)
January 20, 2010 at 6:02 am
ORA-01652 does not signals timeout but the inability to extend a temporary segment a.k.a. tablespace full 🙂
Oracle error stack is certainly pointing to affected tablespace, it could be either general temp tablespace or the temp tablespace set for that particular user.
How big are target tables? ... it looks like query requires a lot of temp space.
In general I would run, test and finetune query on Oracle side then execute it from SQL Server.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 6:27 am
🙂 But of course you are right, about ORA01652 being tablespace full problem. Would it be an option to expand the temp db?
Yes, the query is hefty and it should be like over a million of records, so no doubt that does not help on the query. I have no choise but to have it in one compact query like this and have not come up with at better way to optimize it.
About executing the the query on a SQL Server sounds very interesting (I'm a newbie on Oracle). Do you mean the import data task that can be launched within the SQL Mgm Studio? And what are the benefits of doing it with in the SQL Mgm Studio? Is it common for Oracle servers to have tablespace full issue? Never run into this on a SQL Server!
Thanks for the reply! 🙂
Mony
January 20, 2010 at 6:50 am
Welcome to the Oracle world... there is not such a thing as Temp DB in Oracle but we do have Temp tablespaces sitting on the same Oracle instance.
In regards to how common is this issue it mostly depends on how well designed was the database and how good - or bad - queries are.
When you have a well performing database and a particular query blows up temp tablespace you most certainly know something is wrong with the particular query.
Here is your troubleshooting to-do list...
1- Ask your Oracle DBA how big temp tablespace is - also ask him/her to confirm which tablespace got blow up - just look to alert.log for details.
2- Ask your Oracle DBA to run an explain plan on your query and give it back to you.
3- Test and fine tune your query by login into Oracle instance via SQLPLUS, then execute your query on Oracle side - when query runs as you like it then go back to SQL Server environment and run it from there.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 3, 2010 at 4:56 am
Afterlooking at the explain plan you may want to consider the floowing:
THE IS NOT NULL will result in a table scan.
You may want to use a Non-ANSI Standard JOIN on the WHERE Clause and filter records starting with the JOIN that eliminates the most records.
Functions on the Left side of the JOIN are very costly.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply