Forum Stats

  • 3,732,979 Users
  • 2,246,664 Discussions
  • 7,856,452 Comments

Discussions

Performance issue when debug mode enabled, page with multiple LOVs

Jeffrey Kemp
Jeffrey Kemp Member Posts: 193 Red Ribbon

When I enable debug mode on my APEX application, the load time for some pages jumps to 20+ seconds (in worst cases, up to 100 seconds). With debug mode off, performance returns to expected (<4 seconds to load).

The debug log points to all the SQL queries needed to load the values for the select lists. Each SQL takes approximately 1 second +/- 0.1s. The performance of the page changes depending on how many select lists there are to load.

The page has multiple select list items (some in a form, some in an interactive grid) based on a variety of non-trivial SQL queries. I've tuned the SQL queries as much as possible, to the point where they run in <0.01 second each when run from SQL Developer. The fact that they are very fast in SQL Developer, as well as when they are run by APEX when it is not in debug mode, shows that the problem doesn't lie with the SQL queries themselves.

OEM indicates that the majority of the time for APEX_PUBLIC_USER is waiting on CPU, the majority for the following query:

SELECT

        /*+ opt_param('parallel_execution_enabled', 'false') */

        /* EXEC_FROM_DBMS_XPLAN */

        id                     ,

        parent_id              ,

        partition_id           ,

        TIMESTAMP              ,

        optimizer              ,

        position               ,

        search_columns         ,

        depth                  ,

        operation              ,

        OPTIONS                ,

        object_name            ,

        object_owner           ,

        object_type            ,

        NULL AS object_instance,

        cardinality            ,

        bytes                  ,

        temp_space             ,

        cost                   ,

        io_cost                ,

        cpu_cost               ,

        TIME                   ,

        partition_start        ,

        partition_stop         ,

        object_node            ,

        other_tag              ,

        distribution           ,

        NULL                   ,

        access_predicates      ,

        filter_predicates      ,

        other                  ,

        NULL                   ,

        NULL                   ,

        other_xml              ,

        sql_profile            ,

        sql_plan_baseline      ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL                   ,

        NULL

FROM

        (

                SELECT

                        /*+ no_merge */

                        vp.id id                                 ,

                        vp.parent_id parent_id                   ,

                        vp.partition_id                          ,

                        vp.timestamp                             ,

                        vp.optimizer                             ,

                        vp.search_columns                        ,

                        vp.depth depth                           ,

                        vp.position position                     ,

                        vp.operation operation                   ,

                        vp.options OPTIONS                       ,

                        vp.cost cost                             ,

                        vp.time TIME                             ,

                        vp.cardinality cardinality               ,

                        vp.bytes bytes                           ,

                        vp.object_node object_node               ,

                        vp.object_name object_name               ,

                        vp.object_owner object_owner             ,

                        vp.object_type object_type               ,

                        NULL AS object_instance                  ,

                        vp.other_tag other_tag                   ,

                        vp.partition_start partition_start       ,

                        vp.partition_stop partition_stop         ,

                        vp.distribution distribution             ,

                        vp.temp_space temp_space                 ,

                        vp.io_cost io_cost                       ,

                        vp.cpu_cost cpu_cost                     ,

                        vp.filter_predicates filter_predicates   ,

                        vp.access_predicates access_predicates   ,

                        vp.other other                           ,

                        vp.projection projection                 ,

                        vp.qblock_name qblock_name               ,

                        vp.object_alias object_alias             ,

                        vp.other_xml other_xml                   ,

                        v$sql.sql_profile sql_profile            ,

                        v$sql.sql_plan_baseline sql_plan_baseline,

                        0 starts                                 ,

                        0 outrows                                ,

                        0 crgets                                 ,

                        0 cugets                                 ,

                        0 reads                                  ,

                        0 writes                                 ,

                        0 etime                                  ,

                        0 mem_opt                                ,

                        0 mem_one                                ,

                        NULL last_mem_used                       ,

                        NULL last_mem_usage                      ,

                        0 opt_cnt                                ,

                        0 one_cnt                                ,

                        0 multi_cnt                              ,

                        0 max_tmp                                ,

                        0 last_tmp

                FROM    V$SQL_PLAN vp,

                        v$sql

                WHERE   vp.SQL_ID             = :1

                        AND vp.child_number   =1

                        AND vp.SQL_ID         = v$sql.SQL_ID

                        AND v$sql.is_obsolete = 'N'

                        AND v$sql.address     = vp.address

                        AND v$sql.child_number= :2

        )

ORDER BY id

The stats indicate an average 0.55s elapsed per execution. This query is not part of my application; rather, it looks like a standard query for getting an explain plan. I don't know why APEX is running explain plan, but I'm guessing it's doing something in debug mode to analyse my LOV queries before it executes them?

Oracle APEX 19.1.0.00.15

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Tagged:
Sign In or Register to comment.