[opencms-dev] Oracle performance && varchar columns

[Bernat López]

Hi all,

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:
CMS_STATICEXPORT_LINKS.LINK_ID
CMS_ONLINE_STRUCTURE.RESOURCE_ID
CMS_HISTORY_STRUCTURE.PARENT_ID

Is there any reason why this is so? Why these columns aren't varchar2? :-S ... is this a bug?

We've run some large test: xmlcontent publish+static export (>10.000 contents) and we've monitored Oracle identifying two slow queries:
SELECT OCMS.CMS_OFFLINE_STRUCTURE.STRUCTURE_ID,OCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID
...
FROM OCMS.CMS_OFFLINE_STRUCTURE,OCMS.CMS_OFFLINE_RESOURCES 
WHERE OCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_PATH=:1 AND OCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID=OCMS.CMS_OFFLINE_RESOURCES.RESOURCE_ID 
ORDER BY OCMS.CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE ASC

and 

SELECT OCMS.CMS_ONLINE_STRUCTURE.STRUCTURE_ID,OCMS.CMS_ONLINE_STRUCTURE.RESOURCE_ID,
...
FROM OCMS.CMS_ONLINE_STRUCTURE,OCMS.CMS_ONLINE_RESOURCES 
WHERE OCMS.CMS_ONLINE_STRUCTURE.RESOURCE_PATH=:1 AND 
      OCMS.CMS_ONLINE_STRUCTURE.RESOURCE_ID=OCMS.CMS_ONLINE_RESOURCES.RESOURCE_ID 
ORDER BY OCMS.CMS_ONLINE_STRUCTURE.STRUCTURE_STATE ASC

Query1 Statistics:
% Total DB Time: 52% 
590000 executions (0.018 secs per exec) 
Buffer gets: 60% of total 
Disk reads: 5% of total 

Query2 Statistics:
% Total DB Time: 26% 
320000 executions (0.016 secs per exec)
Buffer gets: 33% of total 
Disk reads: 83% del total

Query1 Explain Plan
SELECT STATEMENT ALL ROWS
  SORT ORDER BY
    HASH JOIN
     TABLE ACCESS BY INDEX ROWID TABLE OCMS.CMS_OFFLINE_STRUCTURE
       INDEX RANGE SCAN INDEX OCMS.CMS_OFFLINE_STRUCTURE_07_IDX
       TABLE ACCESS FULL TABLE OCMS.CMS_OFFLINE_RESOURCES (Cardinality 32900, Cost 290!!!)

Query2 Explain Plan
SELECT STATEMENT ALL ROWS
  SORT ORDER BY
    HASH JOIN 
      TABLE ACCESS BY INDEX ROWID TABLE OCMS.CMS_ONLINE_STRUCTURE
        INDEX RANGE SCAN INDEX OCMS.CMS_ONLINE_STRUCTURE_07_IDX
          TABLE ACCESS FULL TABLE OCMS.CMS_ONLINE_RESOURCES (Cardinality 34200, cost 290!!)

30000 rows table full acces for 900000 executions :-((((

I don't understand these FULL TABLE ACCESS for CMS_ONLINE_RESOURCES and CMS_OFFLINE_RESOURCES? These tables are indexed by RESOURCE_ID... but:

CMS_ONLINE_STRUCTURE.RESOURCE_ID type VARCHAR (36)
CMS_OFFLINE_STRUCTURE.RESOURCE_ID type VARCHAR (36)
CMS_OFFLINE_RESOURCES.RESOURCE_ID type VARCHAR2(36)
and CMS_ONLINE_RESOURCE.RESOURCE_ID type VARCHAR2(36)

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)?  Any tuning ideas?

Kind regards!

b
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20090328/52da2f3e/attachment.htm>