Hello, we have some tables with CLOB fields. Since two weeks ago, some queries that worked on 20 seconds or less didn't work from the application. The user has a query that he said worked on 20 seconds two weeks ago on TOAD but now the same query doesn't work. The query use some CLOB fields from two tables, if I remove the fields CLOB, the sentence work well. I read on some documents that those fields have problems for performance. Do you know about problems with those fields? Why the query worked fine until two weeks ago, and now it doesn't work ? I reccreated the database on weekend with export and import but the situation is the same. I changed the query and I used the dbms_lob.subst function and the performance of the query is better, It work well sometimes but in another times hang (from Sqldeveloper). I show both queries below, with first sentence the PC hang, with the second (with dbms_lob.substr) work better but sometimes hang.
We have Oracle 10.2.0.4.0, A server with 4 processors Intel XEON 3 Ghz with Red Hat Enterprise Linux Server release 5.4 16 bits, RAM 16 Gigas
The fields in comments are CLOBS
1.
select
a.TICKET_OWNER,
a.COPIANUMBER,
a.INCIDENT_ID ,
a.AFFECTED_ITEM,
a.CATEGORY,
a.SUBCATEGORY,
a.PRODUCT_TYPE,
a.CONTACT_NAME,
a.FIRST_NAME,
a.LOCATION,
b.ALTERNATE_CONTACT,
b.CONTACT_FIRST,
b.CALLBACK_CONTACT,
b.CONTACT_LAST,
a.COMPANY,
a.OPEN_TIME,
a.OPENED_BY,
a.OPEN_GROUP,
a.PRIORITY_CODE,
a.SEVERITY,
b.TITLE,
a.BRIEF_DESCRIPTION ,
---b.DESCRIPTION,
a.ASSIGNMENT,
a.ASSIGNEE_NAME,
---a.UPDATE_ACTION,
a.UPDATED_BY,
a.ACTOR,
a.PROBLEM_STATUS,
b.JUSTIFICACION_ESC,
a.RESOLVED_BY,
a.RESOLVED_GROUP,
a.RESOLVED_TIME,
a.CLOSED_GROUP,
---a.ACTION,
---a.RESOLUTION ,
---a.CLOSING_COMMENTS,
a.CLOSE_TIME,
a.CLOSED_BY ,
a.CAUSE_CODE,
a.RESOLUTION_CODE,
b.OWNER_NAME,
b.AFFECTED_ITEM,
---b.RESOLUTION,
b.CLOSE_TIME,
b.CLOSED_BY,
b.RESOLUTION_CODE,
b.CALLBACK_TYPE
from USR_SMDB.PROBSUMMARYM1 a, USR_SMDB.INCIDENTSM1 b
WHERE a.INCIDENT_ID = b.INCIDENT_ID AND to_char(a.OPEN_TIME,'YYYYMMDD') between '20100901' and '20101010'
ORDER BY a.INCIDENT_ID ASC
--------------------------------------------------------------------------------
2.
select
a.TICKET_OWNER,
a.COPIANUMBER,
a.INCIDENT_ID ,
a.AFFECTED_ITEM,
a.CATEGORY,
a.SUBCATEGORY,
a.PRODUCT_TYPE,
a.CONTACT_NAME,
a.FIRST_NAME,
a.LOCATION,
b.ALTERNATE_CONTACT,
b.CONTACT_FIRST,
b.CALLBACK_CONTACT,
b.CONTACT_LAST,
a.COMPANY,
a.OPEN_TIME,
a.OPENED_BY,
a.OPEN_GROUP,
a.PRIORITY_CODE,
a.SEVERITY,
b.TITLE,
a.BRIEF_DESCRIPTION ,
dbms_lob.substr(b.DESCRIPTION,500,1) DESCRIPTION,
a.ASSIGNMENT,
a.ASSIGNEE_NAME,
dbms_lob.substr(a.UPDATE_ACTION,500,1) UPDATE_ACTION,
a.UPDATED_BY,
a.ACTOR,
a.PROBLEM_STATUS,
b.JUSTIFICACION_ESC,
a.RESOLVED_BY,
a.RESOLVED_GROUP,
a.RESOLVED_TIME,
a.CLOSED_GROUP,
dbms_lob.substr(a.ACTION,500,1) ACTION,
dbms_lob.substr(a.RESOLUTION,1000,1) RESOLUTION,
dbms_lob.substr(a.CLOSING_COMMENTS,500,1) CLOSING_COMMENTS,
a.CLOSE_TIME,
a.CLOSED_BY ,
a.CAUSE_CODE,
a.RESOLUTION_CODE,
b.OWNER_NAME,
b.AFFECTED_ITEM,
dbms_lob.substr(b.RESOLUTION,1000,1) RESOLUTION,
b.CLOSE_TIME,
b.CLOSED_BY,
b.RESOLUTION_CODE,
b.CALLBACK_TYPE
from USR_SMDB.PROBSUMMARYM1 a, USR_SMDB.INCIDENTSM1 b
WHERE a.INCIDENT_ID = b.INCIDENT_ID AND
to_char(a.OPEN_TIME,'YYYYMMDD') between '20100901' and
'20101015'
ORDER BY a.INCIDENT_ID ASC
Thanks for your help.
Ruben
DBA
|
] 
Blog
Search
Help
Members
Register
Login
Home
