Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-13689

Prevent Oracle full table scan on nx_update_read_acls

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.9.1
    • Fix Version/s: 5.8.0-HF07, 5.9.2
    • Component/s: Core VCS

      Description

      A part of the nx_update_read_acls is doing TABLE ACCESS FULL on hierarchy and hierarchy_read_acls.

      SELECT	/*+ gather_plan_statistics */ r.id	   FROM nuxeo.hierarchy_read_aclr
      	   JOIN nuxeo.hierarchy h ON h.id = r.id	 
                 JOIN nuxeo.hierarchy_read_acl rr ON rr.id = h.parentid
      WHERE r.acl_id <> '-' AND rr.acl_id = '-'
      
      Plan hash value: 4002556013
      
      | Id  | Operation		      | Name			      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
      |   0 | SELECT STATEMENT	      | 			      |      1 |	|      0 |00:00:12.55 |   77143 |  70036 |	 |	 |	    |
      |*  1 |  HASH JOIN		      | 			      |      1 |  18549 |      0 |00:00:12.55 |   77143 |  70036 |   894K|   894K|  352K (0)|
      |*  2 |   HASH JOIN		      | 			      |      1 |  19538 |      1 |00:00:07.74 |   48782 |  41703 |   894K|   894K|  355K (0)|
      |   3 |    TABLE ACCESS BY INDEX ROWID| HIERARCHY_READ_ACL	      |      1 |   9014 |      1 |00:00:00.01 |       5 |      0 |	 |	 |	    |
      |*  4 |     INDEX RANGE SCAN	      | HIERARCHY_READ_ACL_ACL_ID_IDX |      1 |   9014 |      1 |00:00:00.01 |       4 |      0 |	 |	 |	    |
      |   5 |    TABLE ACCESS FULL	      | HIERARCHY		      |      1 |   2746K|   2873K|00:00:14.37 |   48777 |  41703 |	 |	 |	    |
      |*  6 |   TABLE ACCESS FULL	      | HIERARCHY_READ_ACL	      |      1 |   2607K|   2715K|00:00:21.74 |   28361 |  28333 |	 |	 |	    |
      

      We can try to use CONNECT BY to prevent this.

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: