IPM Query Causing Excessive Use of Tempdb / Temporary Tablespace (Doc ID 1309080.1)

Last updated on FEBRUARY 26, 2016

Applies to:

Oracle Imaging and Process Management - Version 7.6.2.0.0 to 10.1.3.6 [Release Stellent to 10gR3]
Information in this document applies to any platform.

Symptoms

A query appears to be causing excessive use of the temporary tablespace on the database.  In other words, a query is accessing the imaging database, and retrieving too much data.

The DBA is indicating a query is causing the tempdb space to send alerts indicating the space is almost exhausted. How do we restrict or control the amount of temporary space this query uses?

Below is an example query, which can cause problems:

SELECT Col1064,Col1065,Col1066,Col1058,Col1060,Col1059,Col1061, Col1062,Col1057,Col1069,Col1063,Col1070,Col1071,Col1067,Col1068, Col1078,Col1079,Col1072,Col1073,Col1074,Col1075,Col1076,Col1077, Col1082,Col1080,DATABASEMP_1.MIMETYPE Col1048,DATABASEMP_1.PROVIDERID Col1049,Col1054
FROM ACORDE.dbo.DATABASEMP DATABASEMP_1
RIGHT OUTER JOIN (
SELECT IDXAllData_0.DTYPE Col1081,
IDXAllData_0.TONmbr Col1064,IDXAllData_0.CNum Col1065,
IDXAllData_0.LnNo Col1066,IDXAllData_0.DocType Col1058,
IDXAllData_0.SomeClmnNam1 Col1060,IDXAllData_0.SomeClmnNam1 Col1059,
IDXAllData_0.SomeClmnNam2 Col1061,IDXAllData_0.SomeClmnNam2 Col1062,
IDXAllData_0.SomeClmnNam3 Col1057,IDXAllData_0.SomeClmnNam3 Col1069,
IDXAllData_0.SomeClmnNam4 Col1063,IDXAllData_0.SomeClmnNam4 Col1070,
IDXAllData_0.SomeClmnNam5 Col1071,IDXAllData_0.SomeClmnNam5 Col1067,
IDXAllData_0.SClmnDt Col1068,IDXAllData_0.SomeClmnNam Col1078,
IDXAllData_0.SomeClmnNam6 Col1079,IIDXAllData_0.SomeClmnNam6 Col1072,
IDXAllData_0.SomeClmnNam7 Col1073,IDXAllData_0.SomeClmnNam7 Col1074,
IDXAllData_0.SomeClmnNam8 Col1075,IDXAllData_0.SomeClmnNam8 Col1076,
IDXAllData_0.SomeClmnNam9 Col1077,IDXAllData_0.SomeClmnNam9 Col1082,
IDXAllData_0.RECID Col1080,CX_SomeClmnNam_2.RECID Col1051, CX_SomeNam_2.DOCCREATEDATE Col1054,CX_SomeNam_2.SomeDocRecID Col1055,
CX_SomeNam_2.DOCSTGID Col1052,CX_DOCUMENTS_2.RMID Col1053
FROM ACORDE.dbo.CX_SomeNam CX_SomeNam_2,
ACORDE.dbo.IDXAllData IDXAllData_0
WHERE IDXAllData_0.RECID=CX_DOCUMENTS_2.RECID) Qry1083
ON Col1081=DATABASEMP_1.PMID

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms