Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, stephane.

Asked: June 08, 2009 - 12:41 pm UTC

Last updated: July 07, 2009 - 6:17 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hello

I am working on Oracle V8.1.7.4.0 and I have one performance issue with this table:

CREATE TABLE C2W_MESSAGE_SDW
(
CMD VARCHAR2(12),
OLD_ID NUMBER,
OLD_Y_MESSAGE_TYPE VARCHAR2(50),
OLD_Y_MESSAGE_ID VARCHAR2(100),
OLD_Y_MESSAGE CLOB,
OLD_Y_MESSAGE_STATUS NUMBER,
OLD_Y_MESSAGE_TIMESTAMP DATE,
NEW_ID NUMBER,
NEW_Y_MESSAGE_TYPE VARCHAR2(50),
NEW_Y_MESSAGE_ID VARCHAR2(100),
NEW_Y_MESSAGE CLOB,
NEW_Y_MESSAGE_STATUS NUMBER,
NEW_Y_MESSAGE_TIMESTAMP DATE,
ORDERID INTEGER
)
TABLESPACE WEBCHANNEL_DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

I have very bad performance when I delete one line or select all the fields of this table even if clob lenght is less than 1000. I though first it is due to CLOB type but performance are ok when I insert a new line (insertion is done through a trigger). So perhaps there is another explanation.

Remark: This table has always less than 100 lines/there is no index

and Tom said...

tables have rows, not lines :)


Here is my guess

a) table was once much larger than it is now
b) therefore table has allocated a lot of space (query dba_segments, see how big it is)
c) the delete must full scan (no index)
d) the delete must full scan all of the blocks - there are a lot of them (see (a))


solution:

1) index table if possible, so
2) reorganize table if (1) is not possible (alter table t move)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Selecting CLOB column makes fetching one by one

Joaquin Gonzalez, July 07, 2009 - 3:49 am UTC

Hi Tom,

I paste my test case, I hope you can help.

(10.2.0.3.0 - 64bi Solaris):

drop table test purge;
CREATE TABLE test
( x number,
val CLOB
) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "GESAFIN"
LOB ("VAL") STORE AS (
TABLESPACE "GESAFIN" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE ) ;

insert into test select rownum, rpad('X',1000,'X') from dual connect by level<=100;
commit;

set arraysize 13
select x from test;
select val from test;



Checking how many fetches did the querys

43.DESA.sys> select EXECUTIONS, FETCHES from v$sql where sql_text='select x from test';
EXECUTIONS FETCHES
---------- ----------
1 9

1 fila seleccionada.

43.DESA.sys> select EXECUTIONS, FETCHES from v$sql where sql_text='select val from test';
EXECUTIONS FETCHES
---------- ----------
1 101



Checking the number of fetches with event 10046:

PARSING IN CURSOR #31 len=18 dep=0 uid=71 oct=3 lid=71 tim=9369917735690 hv=498814776 ad='4e4fa338'
select x from test
END OF STMT
PARSE #31:c=10000,e=38959,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=9369917735679
EXEC #31:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9369917735955
FETCH #31:c=0,e=135,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=9369917736270
FETCH #31:c=0,e=101,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917737395
FETCH #31:c=0,e=129,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917778455
FETCH #31:c=0,e=118,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917814198
FETCH #31:c=0,e=128,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917844345
FETCH #31:c=0,e=115,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917866263
FETCH #31:c=0,e=103,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917887138
FETCH #31:c=0,e=118,p=0,cr=6,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917903654
FETCH #31:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=8,dep=0,og=1,tim=9369917909345
STAT #31 id=1 cnt=100 pid=0 pos=1 obj=123063 op='TABLE ACCESS FULL TEST (cr=30 pr=0 pw=0 time=620 us)'

PARSING IN CURSOR #32 len=20 dep=0 uid=71 oct=3 lid=71 tim=9369948129295 hv=3426193149 ad='c045b1d8'
select val from test
END OF STMT
PARSE #32:c=20000,e=5290,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=9369948129284
EXEC #32:c=0,e=153,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9369948129616
FETCH #32:c=0,e=184,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948130970
FETCH #32:c=0,e=72,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948140029
FETCH #32:c=0,e=198,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948145704
FETCH #32:c=0,e=59,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948151327
FETCH #32:c=0,e=79,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948156580
FETCH #32:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948161792
FETCH #32:c=0,e=50,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948167029
FETCH #32:c=0,e=60,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948172606
FETCH #32:c=0,e=196,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948178297
FETCH #32:c=0,e=77,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948183919
FETCH #32:c=0,e=57,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948189275
FETCH #32:c=0,e=209,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948194867
FETCH #32:c=0,e=61,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948200375
FETCH #32:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948205640
FETCH #32:c=0,e=44,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948211135
FETCH #32:c=0,e=44,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948216247
FETCH #32:c=0,e=56,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948221506
FETCH #32:c=0,e=106,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948227277
FETCH #32:c=0,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948232450
FETCH #32:c=0,e=73,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948237632
FETCH #32:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948242821
FETCH #32:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948247990
FETCH #32:c=0,e=116,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948253276
FETCH #32:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948258580
FETCH #32:c=0,e=48,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948264021
FETCH #32:c=0,e=54,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948269173
FETCH #32:c=0,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948274401
... continues...

Why when selecting the clob column it fetches rows one by one?

Thanks,

Joaquin Gonzalez
Tom Kyte
July 07, 2009 - 6:17 pm UTC

because you are looking at sqlplus - sqlplus (a very simple command line program designed to print out ad-hoc query results) parsed your query and discovered "hey, there is a REALLY big field here we need to print out, lets go slow by slow so we can do that easily"

In short, it was a design decision made by the sqlplus developer, they don't array fetch longs/lobs - by their decision.

that doesn't mean you cannot array fetch them - just that they decided "no, we will not, don't want to"

ops$tkyte%ORA10GR2> CREATE TABLE test
  2  (  x number,
  3      val CLOB
  4  ) ;

Table created.

ops$tkyte%ORA10GR2> insert into test select rownum, rpad('X',1000,'X') from dual connect by level<=100;

100 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> begin
  2          for x in ( select * from test )
  3          loop
  4                  null;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.


SELECT * FROM TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         23          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0         24          0         100



plsql (as of 10g and above) implicitly array fetches 100 rows at a time with implicit cursors. It only called fetch twice (once to get rows 1-100 and once more to discover "no more data")


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here