0

Hi i am trying to insert a clob using the jdbc, through a maven plugin. But it is taking about 10 minutes to insert. This is exceptionally slow, and i was wondering if there was another way to do it. The clob needs to have line breaks. My insert is being called from a sql file and it looks like this:

INSERT INTO SCHEMANAME.ATABLENAME VALUES (1,1,'ASTRING','ANOTHERSTRING','STRING WITH LINEBREAKS

BLAH BLAH

BLAH BLAH
BLAH BLAH
BLAH','FINALSTRING',sysdate);
Matt
  • 2,803
  • 9
  • 33
  • 57

3 Answers3

0

If you are wondering if there was another way to do it, could you try to insert a null value to the CLOB column at first, and do a update with the CLOB column after the first insert?

user1094698
  • 145
  • 6
  • Follow is the link for question "Insert CLOB into Oracle database", may give you some hints. http://stackoverflow.com/questions/10727521/insert-clob-into-oracle-database?rq=1 – user1094698 Aug 15 '13 at 17:31
0

Pull AWR and ADDM reports and see what the insert is waiting on. Take manual snapshots if needed to get several data points to check against.

10 minutes to insert a single row indicates that there is locking/blocking/waiting going on inside your DB.

Bart K
  • 684
  • 5
  • 10
  • Is there a guide you can point me too where i can learn how to pull those reports? – Matt Sep 27 '11 at 13:07
  • You can do it through OEM if you have the set up, otherwise there are SQL scripts in $ORACLE_HOME/rdbms/admin. You'll probably need expanded privs to run them if you're not SYS or SYSTEM. Check these links: https://forums.oracle.com/forums/thread.jspa?threadID=1075428 http://www.oracle-base.com/articles/10g/AutomaticWorkloadRepository10g.php – Bart K Sep 27 '11 at 13:48
-1

The CLOB is certainly not the problem here. The enormously long time of 10 minutes indicates that some sort of time-out is involved. Two of them come to mind:

  • The network connection is unreliable and it takes a long time to transmit a few bytes.
  • Two database sessions are trying to insert or update the same row in Oracle. If the first one is able to do the insert or update but doesn't commit the connection, the second one will be blocked until the first on commits, rolls back or ends the session.

The second one is more likely.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • I have narrowed it down to the specific clob inserts...everything else runs quick and smoothly table creations/alters/updates..etc everything before the inserts is speedy fast. – Matt Sep 23 '11 at 20:42
  • The two inserts would only block each other if they were inserting the same value for a unique index (i.e. PK). But if that was the case, one of the two processes would throw an error. –  Sep 23 '11 at 22:01