Details

      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);
      "
      

        Issue Links

          Activity

          Hide
          ezrobot added a comment -

          This issue has been automatically closed due to the lack of activity over a long period of time. It is very likely that it is obsolete, but if you think it is still valid, do not hesitate to reopen it and mention why.

          Show
          ezrobot added a comment - This issue has been automatically closed due to the lack of activity over a long period of time. It is very likely that it is obsolete, but if you think it is still valid, do not hesitate to reopen it and mention why.

            People

            • Assignee:
              unknown
              Reporter:
              Jostein- Knudsen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: