Re: Performance issues under constant high load
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?
