Forum Stats

  • 3,728,160 Users
  • 2,245,561 Discussions
  • 7,853,362 Comments

Discussions

Re: Performance issues under constant high load

Rajeshwaran, Jeyabal
Rajeshwaran, Jeyabal Member Posts: 86 Bronze Badge

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?

Comments

Sign In or Register to comment.