Reports are timing out with ORA-01652 Error

[Lokesh Reddy]

Hi Cognos Community,

We have 3 reports in our Cognos production which has Data level security applied for particular group of users in Framework Manager.

So, when these users run the reports, the reports are getting timed out with the below error 

"XQE-DAT-0001 Data source adapter error: java.sql.SQLException: ORA-01652: unable to extend temp segment by 64 in tablespace TEMP"

But when we admins and developers run these reports, they are running fine.

We grab the SQL that is getting generated on Database Engine while user is running report. And this Query is very big.


Can anyone please suggest what might be the problem here and how can we resolve this problem.


When i research for the error "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP", People are saying that it is Oracle Database Error and by increasing the TEMP Space it may get resolved.



Thanks,

Lokeswara

[MFGF]

Hi Cognos Community,

We have 3 reports in our Cognos production which has Data level security applied for particular group of users in Framework Manager.

So, when these users run the reports, the reports are getting timed out with the below error

"XQE-DAT-0001 Data source adapter error: java.sql.SQLException: ORA-01652: unable to extend temp segment by 64 in tablespace TEMP"

But when we admins and developers run these reports, they are running fine.

We grab the SQL that is getting generated on Database Engine while user is running report. And this Query is very big.


Can anyone please suggest what might be the problem here and how can we resolve this problem.


When i research for the error "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP", People are saying that it is Oracle Database Error and by increasing the TEMP Space it may get resolved.



Thanks,

Lokeswara

The error is being generated by your Oracle database, as your research suggests. You'll need to get your DBAs to increase the TEMP space available to the database. I'd expect the reason why some users get the error and some don't is down to the amount of data the query is attempting to retrieve for each user. You indicated you have data level security, which would potentially generate different filters for each user, so there could be different volumes of data being requested for each, dependent on the specific filter values.
Bottom line here, though, is that it's an Oracle issue not a Cognos issue.

Cheers!

MF.

[Lokesh Reddy]

Thank you for your valuable suggestion, Mark !

[cognostechie]

Hi Cognos Community,

We have 3 reports in our Cognos production which has Data level security applied for particular group of users in Framework Manager.

So, when these users run the reports, the reports are getting timed out with the below error

"XQE-DAT-0001 Data source adapter error: java.sql.SQLException: ORA-01652: unable to extend temp segment by 64 in tablespace TEMP"

But when we admins and developers run these reports, they are running fine.

We grab the SQL that is getting generated on Database Engine while user is running report. And this Query is very big.


Can anyone please suggest what might be the problem here and how can we resolve this problem.


When i research for the error "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP", People are saying that it is Oracle Database Error and by increasing the TEMP Space it may get resolved.



Thanks,

Lokeswara

Hi

The reason why the reports are running fine for admin users is because they probably have access to all of the data in which case the SQL will be a straight pull without applying any filters and that won't require much temp space but in case of users with limited access, the SQL will need to store data in temp spaces and then stitch the queries together.

I have successfully tuned reports to run in less than a minute which were earlier taking days to run and most of the times, timing out. Not sure if this applies in your case but if the FM model has joins and the report queries also have joins then that is definitely one reason for the query to be big and the reports to be slow. Most people do not analyze the SQL but in this case, Cognos creates the same query multiple times because of the joins in both the report and the package and the queries are stitched in a way that it makes the report very slow, not for pulling the data out of the DB but because of what it does on the server for organizing the data after it is pulled. This is also the reason why any output in Excel format is slower than HTML.

If what I said is the case then you might want to hand write the SQL (if possible for you) for the entire report (not a good practice) and use that in one query in the report. The best practice is to have star schema design DW and a FM model on top of that but if that is not possible then you might want to do what I suggested. I have done this for many reports for a company which has a FM model reading straight from an ERP (Deltek Costpoint) DB which is in a snowflake structure. All those reports had a significant performance improvement bring down the time taken from hours, in some cases days, to less than a minute.