Details
-
Improvement
-
Resolution: Obsolete
-
Medium
-
None
-
None
Description
From external source:
Our DBA has analyzed SQL statements sent by eZ Publish to our Oracle server and came up with few suggestions that could improve things:
"EZPublish is sending a lot of queries to the database that are exactly the same except for the literals in the search expression. This is causing Oracle to treat them as separate statements which causes a lot of overhead as each statement has to be parsed separately. If the literals are replaced by bind variables, the statements can use the same execution plan, which will make the queries execute faster and put less load on the database.
Can you raise a request with eZ to change the code so that it uses bind variables instead of literals. Below are two examples of queries that are executed frequently; the values that should be replaced by bind variables are in red.
SELECT COUNT(DISTINCT ezcontentobject_tree.node_id) AS COUNT
FROM ezcontentobject_tree
,ezcontentobject
,ezcontentclass
,ezcontentobject_name
WHERE ezcontentobject_tree.path_string LIKE '/1/2/121/194/95667/100693/%'
AND ezcontentobject_tree.depth <= 6
AND ezcontentclass.version = 0
AND ezcontentobject_tree.node_id != 100693
AND ezcontentobject_tree.contentobject_id = ezcontentobject.id
AND ezcontentclass.id = ezcontentobject.contentclass_id
AND ezcontentobject_tree.contentobject_id =
ezcontentobject_name.contentobject_id
AND ezcontentobject_tree.contentobject_version =
ezcontentobject_name.content_version
AND (bitand(ezcontentobject_name.language_id
,ezcontentobject.language_mask) > 0 AND
bitand(ezcontentobject.language_mask -
bitand(ezcontentobject.language_mask
,ezcontentobject_name.language_id)
,1) + bitand(ezcontentobject.language_mask -
bitand(ezcontentobject.language_mask
,ezcontentobject_name.language_id)
,8) / 4 +
bitand(ezcontentobject.language_mask -
bitand(ezcontentobject.language_mask
,ezcontentobject_name.language_id)
,2) * 2 <
bitand(ezcontentobject_name.language_id
,1) + bitand(ezcontentobject_name.language_id
,8) / 4 +
bitand(ezcontentobject_name.language_id
,2) * 2)
AND bitand(ezcontentobject.language_mask
,11) > 0;
SELECT ezcontentobject.*
,ezcontentobject_tree.*
,ezcontentclass.serialized_name_list AS class_serialized_name_list
,ezcontentclass.identifier AS class_identifier
,ezcontentclass.is_container AS is_container
,ezcontentobject_name.NAME AS NAME
,ezcontentobject_name.real_translation
FROM ezcontentobject_tree
,ezcontentobject
,ezcontentclass
,ezcontentobject_name
WHERE node_id = 50555
AND ezcontentobject_tree.contentobject_id = ezcontentobject.id
AND ezcontentclass.version = 0
AND ezcontentclass.id = ezcontentobject.contentclass_id
AND bitand(ezcontentobject.language_mask
,11) > 0
AND ezcontentobject_tree.contentobject_id =
ezcontentobject_name.contentobject_id
AND ezcontentobject_tree.contentobject_version =
ezcontentobject_name.content_version
AND (bitand(ezcontentobject_name.language_id
,ezcontentobject.language_mask) > 0 AND
bitand(ezcontentobject.language_mask -
bitand(ezcontentobject.language_mask
,ezcontentobject_name.language_id)
,1) + bitand(ezcontentobject.language_mask -
bitand(ezcontentobject.language_mask
,ezcontentobject_name.language_id)
,8) / 4 +
bitand(ezcontentobject.language_mask -
bitand(ezcontentobject.language_mask
,ezcontentobject_name.language_id)
,2) * 2 <
bitand(ezcontentobject_name.language_id
,1) + bitand(ezcontentobject_name.language_id
,8) / 4 +
bitand(ezcontentobject_name.language_id
,2) * 2);
"