0 Replies Latest reply on Nov 12, 2019 1:30 PM by Rajeshwaran, Jeyabal Branched from an earlier discussion.

    Re: Performance issues under constant high load

    Rajeshwaran, Jeyabal

      This particular SQL was executed most in our workload.

       

      SQL ordered by Elapsed Time                  DB/Inst: CPES/CPES1  Snaps: 42-43

      -> Resources reported for PL/SQL code includes the resources used by all SQL

        statements called by the code.

      -> % Total DB Time is the Elapsed Time of the SQL statement divided

        into the Total Database Time multiplied by 100

      -> %Total - Elapsed Time  as a percentage of Total DB time

      -> %CPU  - CPU Time      as a percentage of Elapsed Time

      -> %IO    - User I/O Time as a percentage of Elapsed Time

      -> Captured SQL account for    7.3% of Total DB Time (s):          1,505

      -> Captured PL/SQL account for  87.9% of Total DB Time (s):          1,505

       

       

              Elapsed                  Elapsed Time

              Time (s)    Executions  per Exec (s)  %Total  %CPU    %IO    SQL Id

      ---------------- -------------- ------------- ------ ------ ------ -------------

              1,302.5          9,471          0.14  86.5    .7    .0 fx7afb95h42bj

      Module: /provider/prov

        PDB: PRODES

       

      SQL> select sql_text

        2  from dba_hist_sqltext

        3  where sql_id ='fx7afb95h42bj'

        4  and rownum = 1 ;

       

       

      SQL_TEXT

      -------------------------------------------------------------------------

      declare

      nm owa.vc_arr := :1 ;

      vl owa.vc_arr := :2 ;

      l_patch varchar2(10) := 'N';

      begin

      owa.init_cgi_env( :3 , nm, vl );

      htp.init;

      htp.HTBUF_LEN := 63;

      :4  := sys_context('USERENV','SID');

      declare

        l_sql varchar(200) := 'select patch_applied from apex_release';

      begin

        execute immediate l_sql into l_patch;

      exception

        when others then

         null;

      end;

      :5  := l_patch;

      end;

       

       

       

       

      SQL> col owner format a15

      SQL> col object_name format a15

      SQL> col object_type format a10

      SQL> select version from dba_registry where comp_id ='APEX';

       

       

      VERSION

      ------------------------------

      5.1.3.00.05

       

       

      SQL> select owner,object_name,object_type

        2  from dba_objects

        3  where object_name like upper('apex_release%' );

       

       

      OWNER           OBJECT_NAME     OBJECT_TYP

      --------------- --------------- ----------

      APEX_050100     APEX_RELEASE    VIEW

      PUBLIC          APEX_RELEASE    SYNONYM

       

       

      SQL>

       

      so any inputs /directions to avoid multiple execution of this anonymous block?