<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=utf-8">
<META content="MSHTML 6.00.6001.18203" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff background="">
<DIV><FONT face=Arial size=2>Hi all,<BR><BR>Reviewing the Oracle version of
OpenCMS (7.0.5) script for creating tables (create_tables.sql) I've noticed than
some column type is varchar (not varchar2) such
as:<BR>CMS_STATICEXPORT_LINKS.LINK_ID<BR>CMS_ONLINE_STRUCTURE.RESOURCE_ID<BR>CMS_HISTORY_STRUCTURE.PARENT_ID<BR><BR>Is
there any reason why this is so? Why these columns aren't varchar2? :-S ... is
this a bug?<BR><BR>We've run some large test: xmlcontent publish+static
export (>10.000 contents) and we've monitored Oracle identifying two slow
queries:<BR>SELECT
OCMS.CMS_OFFLINE_STRUCTURE.STRUCTURE_ID,OCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID<BR>...<BR>FROM
OCMS.CMS_OFFLINE_STRUCTURE,OCMS.CMS_OFFLINE_RESOURCES <BR>WHERE
OCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_PATH=:1 AND
OCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID=OCMS.CMS_OFFLINE_RESOURCES.RESOURCE_ID
<BR>ORDER BY OCMS.CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE ASC<BR><BR>and
<BR><BR>SELECT
OCMS.CMS_ONLINE_STRUCTURE.STRUCTURE_ID,OCMS.CMS_ONLINE_STRUCTURE.RESOURCE_ID,<BR>...<BR>FROM
OCMS.CMS_ONLINE_STRUCTURE,OCMS.CMS_ONLINE_RESOURCES <BR>WHERE
OCMS.CMS_ONLINE_STRUCTURE.RESOURCE_PATH=:1 AND
<BR>
OCMS.CMS_ONLINE_STRUCTURE.RESOURCE_ID=OCMS.CMS_ONLINE_RESOURCES.RESOURCE_ID
<BR>ORDER BY OCMS.CMS_ONLINE_STRUCTURE.STRUCTURE_STATE ASC<BR><BR>Query1
Statistics:<BR>% Total DB Time: 52% <BR>590000 executions (0.018 secs per exec)
<BR>Buffer gets: 60% of total <BR>Disk reads: 5% of total <BR><BR>Query2
Statistics:<BR>% Total DB Time: 26% <BR>320000 executions (0.016 secs per
exec)<BR>Buffer gets: 33% of total <BR>Disk reads: 83% del
total</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Query1 Explain Plan</FONT></DIV>
<DIV><FONT face=Arial size=2>SELECT STATEMENT ALL ROWS</FONT></DIV>
<DIV><FONT face=Arial size=2> SORT ORDER BY</FONT></DIV>
<DIV><FONT face=Arial size=2> HASH JOIN</FONT></DIV>
<DIV><FONT face=Arial size=2> TABLE ACCESS BY INDEX
ROWID TABLE OCMS.CMS_OFFLINE_STRUCTURE</FONT></DIV>
<DIV><FONT face=Arial size=2> INDEX RANGE
SCAN INDEX OCMS.CMS_OFFLINE_STRUCTURE_07_IDX</FONT></DIV>
<DIV><FONT face=Arial size=2> TABLE ACCESS
FULL TABLE OCMS.CMS_OFFLINE_RESOURCES (Cardinality 32900, Cost
290!!!)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Query2 Explain Plan</FONT></DIV>
<DIV><FONT face=Arial size=2>SELECT STATEMENT ALL ROWS</FONT></DIV>
<DIV><FONT face=Arial size=2> SORT ORDER BY</FONT></DIV>
<DIV><FONT face=Arial size=2> HASH JOIN </FONT></DIV>
<DIV><FONT face=Arial size=2> TABLE ACCESS BY
INDEX ROWID TABLE OCMS.CMS_ONLINE_STRUCTURE</FONT></DIV>
<DIV><FONT face=Arial size=2> INDEX
RANGE SCAN INDEX OCMS.CMS_ONLINE_STRUCTURE_07_IDX</FONT></DIV>
<DIV><FONT face=Arial
size=2> TABLE ACCESS FULL
TABLE OCMS.CMS_ONLINE_RESOURCES (Cardinality 34200, cost 290!!)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>30000 rows table full acces for 900000
executions :-((((</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I don't understand these FULL TABLE ACCESS for
CMS_ONLINE_RESOURCES and CMS_OFFLINE_RESOURCES? These tables are indexed by
RESOURCE_ID... but:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>CMS_ONLINE_STRUCTURE.RESOURCE_ID type VARCHAR
(36)</FONT></DIV>
<DIV><FONT face=Arial size=2>CMS_OFFLINE_STRUCTURE.RESOURCE_ID type VARCHAR
(36)</FONT></DIV>
<DIV><FONT face=Arial size=2>CMS_OFFLINE_RESOURCES.RESOURCE_ID type
VARCHAR2(36)</FONT></DIV>
<DIV><FONT face=Arial size=2>and CMS_ONLINE_RESOURCE.RESOURCE_ID type
VARCHAR2(36)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV>Could be these difference of data types (structure.resource_id varchar
versus resource.resouce_id varchar2) the reason for not using the
index in the join (structure.resource_id = resource.resource_id)? <FONT
face=Arial size=2> Any tuning ideas?</DIV>
<DIV><BR>Kind regards!</DIV>
<DIV> </DIV>
<DIV>b</FONT></DIV></BODY></HTML>