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 we 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 to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here