ACE
  1. ACE
  2. ACE-618

updateOltPort is having Performance issue 1.5s versus 3s

        Details

        • Type: Bug Bug
        • Status: Ready for QA Ready for QA (View Workflow)
        • Priority: Urgent Urgent
        • Resolution: Unresolved
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: FN
        • Labels:
          None Labels
        • Customer:
          OMAN-Tel

          Description

          updateOltPort is having Performance issue 1.5s versus 3s

          <ser:updateOltPort>
          <!-Optional:->
          <msanName>IH95_01_MA5800_OLT</msanName>
          <newOltPort>1/0/3/0/15</newOltPort>
          <subscriberNo>24777728</subscriberNo>
          </ser:updateOltPort>

            Activity

            Ascending order - Click to sort in descending order
            Mbaye Lo made changes -
            Field Original Value New Value
            Gantt Options Milestone (set to milestone: having a due date but zero effort)
            Planned Start 2026-02-23 24:00 (milestone: set planned start date to due date)
            Planned End 2026-02-23 24:00 (milestone: set planned end date to due date)
            Zein Trabelsi made changes -
            Assignee Hassan Abdine [ habdine ] Zein Trabelsi [ ztrabelsi ]
            Zein Trabelsi made changes -
            Status Open Bug [ 10108 ] Dev Scheduled [ 10014 ]
            Zein Trabelsi made changes -
            Status Dev Scheduled [ 10014 ] Implementation in progress [ 10016 ]
            Hide
            Permalink
            Zein Trabelsi added a comment -

            optimize FN_AP_VIEW performance by rewriting PIVOT to CASE/WHEN aggregation

            The view was taking ~1 seconds due to the Oracle PIVOT operation on
            ELEMENTADDITIONALINFO scanning 83,520 rows (98.7% of total query cost)
            before joining to the target record.

            Replaced PIVOT with conditional MAX(CASE WHEN) aggregation and added
            an ATTRIBUTE IN filter to reduce scanned rows. Added MATERIALIZE hint
            on SELECTED_VERSION and merged redundant CTEs to minimize temp table
            reads. Converted implicit joins to explicit JOIN syntax for better
            optimizer predicate pushdown.

            Result: query time reduced from ~1s to 0.02s.

            Show
            Zein Trabelsi added a comment - optimize FN_AP_VIEW performance by rewriting PIVOT to CASE/WHEN aggregation The view was taking ~1 seconds due to the Oracle PIVOT operation on ELEMENTADDITIONALINFO scanning 83,520 rows (98.7% of total query cost) before joining to the target record. Replaced PIVOT with conditional MAX(CASE WHEN) aggregation and added an ATTRIBUTE IN filter to reduce scanned rows. Added MATERIALIZE hint on SELECTED_VERSION and merged redundant CTEs to minimize temp table reads. Converted implicit joins to explicit JOIN syntax for better optimizer predicate pushdown. Result: query time reduced from ~1s to 0.02s.
            Zein Trabelsi made changes -
            Status Implementation in progress [ 10016 ] To Be Released [ 10400 ]
            Khaled Khalil made changes -
            Status To Be Released [ 10400 ] Ready for QA [ 10023 ]
            Assignee Zein Trabelsi [ ztrabelsi ] Mbaye Lo [ mbayel ]
            Transition Time In Source Status Execution Times Last Executer Last Execution Date
            Open Bug Open Bug Dev Scheduled Dev Scheduled
            		21h 48m 1 Zein Trabelsi 27/Feb/26 12:42 PM
            Dev Scheduled Dev Scheduled Implementation in progress Implementation in progress
            		2d 18h 12m 1 Zein Trabelsi 02/Mar/26 6:55 AM
            Implementation in progress Implementation in progress To Be Released To Be Released
            		9s 1 Zein Trabelsi 02/Mar/26 6:55 AM
            To Be Released To Be Released Ready for QA Ready for QA
            		14m 37s 1 Khaled Khalil 02/Mar/26 7:09 AM
            Subversion JIRA

            Links Hierarchy

            		  Documentation

            Invalid license: EXPIRED

            Cancel

              People

              • Assignee:
                Mbaye Lo
                Reporter:
                Mbaye Lo
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Planned Start:
                  Planned End:

                  Drag and Drop