Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Re: Performance issues under constant high load

Rajeshwaran JeyabalNov 12 2019 — edited Apr 16 2020

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

LA County APEX

Isn't it the current record based on the row selected on the report or based on other pre-selected value from some where?

van Baal HR

yes. And that is exactly where the problem is located. There seems to be NO row selected.

LA County APEX

Supposed, your report region a static id is MYREP, create a dynamic action.

Event: After Refresh

Selection Type: Region

Region: your region

----

Add a true action.

Action: Execute JavaScript Code

Code:

$("#MYREP_ir tr").click(function() {

      $x('PX_REC_ID').value = this.cells[1].innerHTML;  //supposed the first column is the record id

      apex.widget.waitPopup();

      document.body.style.cursor = 'wait';

      apex.submit('BTN'); //use the button to redirect to popup a page

});

----

Create a branch.

Page or URL (Redirect)

When Button Pressed: BTN

1 - 3

Post Details

Added on Nov 12 2019
2 comments
571 views