Home » SQL & PL/SQL » Client Tools » Problems in queries with CLOB fields (SqlDeveloper or TOAD)
Problems in queries with CLOB fields [message #480582] Mon, 25 October 2010 18:20 Go to next message
ranietog
Messages: 1
Registered: March 2009
Junior Member
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

Re: Problems in queries with CLOB fields [message #480583 is a reply to message #480582] Mon, 25 October 2010 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Problems in queries with CLOB fields [message #480787 is a reply to message #480583] Wed, 27 October 2010 03:30 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You are trying to displaying the actual data in the CLOB columns in TOAD ? If the CLOB's contains alot of data, and you try display the actual CLOB data in TOAD or any other GUI tool on your client,It might hang depending on your client resources.
Previous Topic: how to pass ambersand ( & ) as input parameter to a stored procedure ?
Next Topic: Sql developer hanging while quering table with clob
Goto Forum:
  


Current Time: Wed Sep 23 12:22:33 CDT 2020