Skip to Main Content

Java Development Tools

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!

Render Response Phase taking a very long time

User_Q9TKTMay 1 2018 — edited May 4 2018

I am using JDEV version 11.1.2.4.0.

I have an ADF Search page (it is NOT using querypanel) with some search fields and a results table.

We are NOT using querypanel so as to achieve the layout that business desires.

After I enter a value in the search field and click on Search it is taking a very long time to display search results.

I started debugging (by implementing PhaseListener) and noticed that till the end of Invoke Application phase (including the execution of the VO query and fetching of 26 rows) it is going quite very fast - no hiccups till this point - Please see below for sample output from application.

However, Render Response phase is taking a very long time (4+ minutes).

I also noticed that NO application code is executing in the Render Response phase - for example, no getters/setters on the managed bean are called and no other application code is invoked still the Render Response phase is taking 4+ minutes to complete.  After the completion of the Render Response phase I finally see the results displayed in the Search Results table.

Note: When I scroll thru the search results past the 26th record, another 26 records are quickly fetched and displayed without any latency.

What could be the root cause of this behavior of Render Response phase?

How to see what (ADF) code is being executed in Render Response phase that is taking so much time?  How to debug/resolve this?  Any known ADF/JSF bugs?

Sample Output from Application on Clicking Search Button:

START PHASE RESTORE_VIEW 1

setCreatedBy - Begin

setResultsTable - Begin

setReasonPopUpBind - Begin

<<<other Managed Bean setters are also called - for brevity, removed those SOPs>>>

END PHASE RESTORE_VIEW 1. Time Taken: 0.016 seconds.

START PHASE APPLY_REQUEST_VALUES 2

END PHASE APPLY_REQUEST_VALUES 2. Time Taken: 0.0010 seconds.

START PHASE PROCESS_VALIDATIONS 3

END PHASE PROCESS_VALIDATIONS 3. Time Taken: 0.0 seconds.

START PHASE UPDATE_MODEL_VALUES 4

END PHASE UPDATE_MODEL_VALUES 4. Time Taken: 0.029 seconds.

START PHASE INVOKE_APPLICATION 5

ActionListener: SearchBean.onSearch() - Begin

VO Query Execution Total Time: 0.071 seconds.

Creating 1 rows of VO

Creating 2 rows of VO

Creating 3 rows of VO

Creating 4 rows of VO

Creating 5 rows of VO

Creating 6 rows of VO

Creating 7 rows of VO

Creating 8 rows of VO

Creating 9 rows of VO

Creating 10 rows of VO

Creating 11 rows of VO

Creating 12 rows of VO

Creating 13 rows of VO

Creating 14 rows of VO

Creating 15 rows of VO

Creating 16 rows of VO

Creating 17 rows of VO

Creating 18 rows of VO

Creating 19 rows of VO

Creating 20 rows of VO

Creating 21 rows of VO

Creating 22 rows of VO

Creating 23 rows of VO

Creating 24 rows of VO

Creating 25 rows of VO

Creating 26 rows of VO

ActionListener: SearchBean.onSearch() - End

END PHASE INVOKE_APPLICATION 5. Time Taken: 0.131 seconds.

START PHASE RENDER_RESPONSE 6

END PHASE RENDER_RESPONSE 6. Time Taken: 252.823 seconds.

This post has been answered by User_Q9TKT on May 4 2018
Jump to Answer

Comments

105967
You have to code it yourself. Check if there is no row returned and if yes set the variable to your desired value.
(just think about it, if there is NO ROW, how can a column be filled ie there is no column to be filled)
MichaelS
e.g.
SQL> var met number
SQL>
SQL> exec :met := 1

PL/SQL procedure successfully completed.

SQL>
SQL> with text_file as
  2  (
  3    select 1 criteria, 'some_text' text from dual
  4  )
  5  select nvl(text,'***empty') text from text_file t1, (select :met met from dual) where t1.criteria(+) = met
  6  /

TEXT
---------
some_text

SQL>
SQL> exec :met := 2

PL/SQL procedure successfully completed.

SQL>
SQL> /

TEXT
---------
***empty
cubmar
Well, I would perhaps do something like this

select text from text_file where criteria=met
UNION
select '** No data' text from dual

But of course only when no data is returned.
cubmar

Soemthing like this works, I guess:

  1  with text_file as
  2  (select 'This' text, 'met' criteria from dual)
  3  select nvl(text,dfttext) text from
  4  (select 1 lnk, text from text_file where criteria='met') qry
  5  ,
  6  (select 1 lnk, '** No data' dfttext from dual) dft
  7* where qry.lnk(+)=dft.lnk
  8  /

TEXT
----------
This
  1  with text_file as
  2  (select 'This' text, 'notmet' criteria from dual)
  3  select nvl(text,dfttext) text from
  4  (select 1 lnk, text from text_file where criteria='met') qry
  5  ,
  6  (select 1 lnk, '** No data' dfttext from dual) dft
  7* where qry.lnk(+)=dft.lnk
SQL> /

TEXT
----------
** No data

It might not look too pretty!

Keith Jamieson
As leo said, you have to code it.

if sql%rowcount = 0
<perform action>
end if;

or a local pl/sql block.

begin
<select statement >
exception
when no_data_found
then
perform action>
end;
cubmar
Keith,

There are two (very similar) examples of how to do it without the need for PL/SQL

Thanks
Keith Jamieson
Try this and see what you get:

select nvl(dummy,'A NULL') from dual where dummy='Y'

The other solutions "Cheat" in that they actually force a row to be found.

But maybe if you posted the problem you're trying to solve, ie the question behind your question we could help further.
105967
... and where you easily can end up with a full table scan, ie a disastrous performance.
BluShadow
And just for fun without using NVL or outer joins...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'fred' as nm from dual union all
  2             select 'bob' from dual union all
  3             select 'jim' from dual)
  4  -- end of test data
  5  select nm from (
  6    select rownum rn, nm from t where nm like '&name%'
  7    union
  8    select 0, '~~ NO DATA ~~' from dual
  9    order by 1 desc
 10    )
 11* where (rn > 0 or (rownum = 1 and rn = 0))
SQL> /
Enter value for name: will
old   6:   select rownum rn, nm from t where nm like '&name%'
new   6:   select rownum rn, nm from t where nm like 'will%'

NM
-------------
~~ NO DATA ~~

SQL> /
Enter value for name: fred
old   6:   select rownum rn, nm from t where nm like '&name%'
new   6:   select rownum rn, nm from t where nm like 'fred%'

NM
-------------
fred

SQL>
MichaelS
... and where you easily can end up with a full table scan, ie a disastrous performance
Why should it be so? Of course a proper Index layout is assumed:
SQL>  create table text_file as select object_id criteria, object_name text from all_objects
/
Table created.

SQL>  create unique index text_file_idx on text_file (criteria)
/
Index created.

SQL>  set autotrace on explain

SQL>  select nvl(text,'***empty') text from text_file t1, (select 15111 met from dual) where t1.criteria(+) = met
/
TEXT                          
------------------------------
/df245789_JavaToSQLMapMapClass
1 row selected.

Execution Plan
----------------------------------------------------------
           SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=30)
   1         NESTED LOOPS OUTER (Cost=3 Card=1 Bytes=30)
   2    1      FAST DUAL (Cost=2 Card=1)
   3    1      TABLE ACCESS BY INDEX ROWID MICHAEL.TEXT_FILE (Cost=1 Card=1 Bytes=30)
   4    3        INDEX UNIQUE SCAN MICHAEL.TEXT_FILE_IDX (Cost=0 Card=1)


SQL>  select nvl(text,'***empty') text from text_file t1, (select 106276 met from dual) where t1.criteria(+) = met
/
TEXT                          
------------------------------
***empty                      
1 row selected.


Execution Plan
----------------------------------------------------------
           SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=30)
   1         NESTED LOOPS OUTER (Cost=3 Card=1 Bytes=30)
   2    1      FAST DUAL (Cost=2 Card=1)
   3    1      TABLE ACCESS BY INDEX ROWID MICHAEL.TEXT_FILE (Cost=1 Card=1 Bytes=30)
   4    3        INDEX UNIQUE SCAN MICHAEL.TEXT_FILE_IDX (Cost=0 Card=1)

SQL>  drop table text_file
/
Table dropped.
105967
Yes, but I did not think of you for using it the wrong way :-)

The problem lays in your sentence: "Of course a proper Index layout is assumed". If the OP thinks, he/she has now a general solution and he/she will try with any column of the table ...
Roger D
how about something of the form ...

select c1, c2, c3 from tab where ...
union
select '*** empty' c1, null c2, null c3 from dual where not exists (select c1, c2, c3 from tab where ...)

for example:
/* when main query returns data */
select dummy from dual
union all
select '*** empty' dummy from dual where not exists (select dummy from dual)
;

/* when main query returns NO data */
select dummy from dual where 1=2
union all
select '*** empty' dummy from dual where not exists (select dummy from dual where 1=2)
;
663924
Set up your default message in a variable and then overwrite the default data when the query returns data. Try the following example...

SQL> define msg="No data found"
SQL> select '&&msg' from dual;

'NODATAFOUND'
-------------
No data found

SQL> col a new_value msg
SQL> select 'X' a from dual where rownum < 1;

no rows selected

SQL> select '&&msg' from dual;

'NODATAFOUND'
-------------
No data found

SQL> select 'X' a from dual;

A
-
X

SQL> select '&&msg' from dual;

'
-
X
Ganesh Srivatsav
you can use this,
SQL> select ename from emp where empno=7902;

ENAME
----------
FORD

SQL> SELECT ename
  2    FROM emp
  3   WHERE empno = 7902
  4  UNION ALL
  5  SELECT 'EMPTY'
  6    FROM DUAL
  7   WHERE NOT EXISTS ( SELECT ename
  8                       FROM emp
  9                      WHERE empno = 7902);

ENAME
----------
FORD

SQL> SELECT ename
  2    FROM emp
  3   WHERE empno = 7909
  4  UNION ALL
  5  SELECT 'EMPTY'
  6    FROM DUAL
  7   WHERE NOT EXISTS ( SELECT ename
  8                       FROM emp
  9                      WHERE empno = 7909);

ENAME
----------
EMPTY

SQL> 
{code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
836203
SELECT NVL(<select that may return no data>, <default value>) FROM DUAL
635471
cubmar wrote:
Keith,

There are two (very similar) examples of how to do it without the need for PL/SQL

Thanks
You usually try to use SQL rather than PL/SQL because it is more efficient to do so. In this case it is probably not.
BluShadow
user2310677 wrote:
SELECT NVL(<select that may return no data>, <default value>) FROM DUAL
Did you sign up to the forum, just to post that very basic answer to a question that's over 2 years old?
karthiklogica

select nvl(to_char (c1),'no data') from (select case  (select 'present'

       from Table WHERE column = value ) when 'present'

       then (select column

       from table  WHERE column = value )

       else null 

       end as c1   from dual )

BluShadow

Another one!

This thread is 5 years old!!!! FFS If the OP hasn't got an answer to their question yet, I'd be very concerned.

Locking this thread

1 - 19
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 1 2018
Added on May 1 2018
2 comments
476 views