Oracle 9i (9.2.0.7) -- Performance issue with LOB data
The LOB column's storage parameters in table definition is as follows:
LOB (CONTENT) STORE AS
( TABLESPACE XX_L_DATA
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 2M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
One of the report is taking too long to finish. The procedure code for running the report is doing a frequent SELECT..FOR UPDATE. It is going to generate report in excel sheet eventually. The report runs fine with less data,but it hung up while dealing large amount of data. I observed wait events during the execution. I observed a big number for "direct path read (lob)" as well as "direct path write (lob)". Will it help if i increase PCTVERSION value? Please add your valuable suggestions or any useful references.
( TABLESPACE XX_L_DATA
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 2M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
One of the report is taking too long to finish. The procedure code for running the report is doing a frequent SELECT..FOR UPDATE. It is going to generate report in excel sheet eventually. The report runs fine with less data,but it hung up while dealing large amount of data. I observed wait events during the execution. I observed a big number for "direct path read (lob)" as well as "direct path write (lob)". Will it help if i increase PCTVERSION value? Please add your valuable suggestions or any useful references.
0