Performance Issues Using APEX_ITEM in SQL?

paulgall

Hello,

I was wondering if using APEX_ITEM.* in your SQL source for a Report would give some performance issues? I would expect the report to bring back just over 3000 records. When I developed this it was running fine but we only had 150 records or so but now that we have migrated the application over to our test system the page will take about 2 minutes to load. Here is my SQL to create the report:

select distinct
initcap(mpa.pa_name) || ' (' || sd.DESIGNATION_CODE || ')' site,
frc.REPORT_DESCRIPTION report_category,
mf.FEATURE_DESC feature,
decode(cmf.SELECTED_FOR_QA, 'Y', 'X', 'N', ' ') QA,
apex_item.select_list_from_query (
21,
cpf.ASSIGN_TO,
'select ss.firstname || '' '' || ss.surname d, ss.STAFF_NUMBER r
from snh_staff ss,
snh_management_units smu,
m_pa_snh_area psa
where ss.MU_UNIT_ID = smu.UNIT_ID
and smu.UNIT_ID = psa.UNIT_ID
and ss.CURRENTLY_EMPLOYED = ''Y''
and psa.SCM_LEAD = ''Y''
and psa.MAIN_AREA = ''P''
and psa.PA_CODE = ' || mpa.pa_code,
null,
'YES',
NULL,
' ') assign_to,
apex_item.select_list_from_query (
22,
decode(to_char(cpf.planned_fieldwork, 'DD/MM/'),
'30/06/', 'Q1 ' || to_char(planned_fieldwork, 'YYYY'),
'30/09/', 'Q2 ' || to_char(planned_fieldwork, 'YYYY'),
'31/12/', 'Q3 ' || to_char(planned_fieldwork, 'YYYY'),
'31/03/', 'Q4 ' || to_char(planned_fieldwork-365, 'YYYY'),
to_char(cpf.planned_fieldwork, 'YYYY')),
'select d, r
from CM_CYCLE_Q_YEARS') planned_fieldwork,
apex_item.select_list_from_query (
23,
decode(to_char(cpf.planned_cmf, 'DD/MM/'),
'30/06/', 'Q1 ' || to_char(planned_cmf, 'YYYY'),
'30/09/', 'Q2 ' || to_char(planned_cmf, 'YYYY'),
'31/12/', 'Q3 ' || to_char(planned_cmf, 'YYYY'),
'31/03/', 'Q4 ' || to_char(planned_cmf-365, 'YYYY'),
to_char(cpf.planned_cmf, 'YYYY')),
'select d, r
from CM_CYCLE_Q_YEARS') planned_cmf,
apex_item.select_list_from_query (
24,
cpf.monitoring_method_id,
'select METHOD, MONITORING_METHOD_ID from cm_monitoring_methods where active_flag = ''Y''') monitoring_method,
apex_item.text (
25,
cpf.pre_cycle_comments,
15,
255,
'title="'||cpf.pre_cycle_comments||'"',
'annualPlanningComments'
||to_char(cpf.PLAN_MON_FEATURE_ID)) comments,
apex_item.text (
26,
to_char(cpf.CONTRACT_LET,'DD-MON-YYYY'),
11,
11) contract_let,
apex_item.text (
27,
to_char(cpf.CONTRACT_REPORT_PLANNED,'DD-MON-YYYY'),
11,
11) contract_report,
apex_item.text (
28,
cpf.ADVISOR_DATA_ENTRY,
11,
11) advisor_entry,
cms.complete_percentage || ' ' || cms.description status,
apex_item.text (
29,
to_char(cpf.RESULT_SENT_TO_OO,'DD-MON-YYYY'),
11,
11) result_to_oo,
cpf.PLAN_MON_FEATURE_ID,
cmf.MONITORED_FEATURE_ID,
mpa.PA_CODE,
mpf.SITE_FEATURE_ID
from fm_report_category frc,
m_feature mf,
m_pa_features mpf,
m_protected_area mpa,
snh_designations sd,
cm_monitored_features cmf,
cm_plan_mon_features cpf,
cm_monitoring_status cms,
cm_cycles cc,
m_pa_snh_area msa,
snh_management_units smu,
snh_sub_areas ssa
where frc.REPORT_CATEGORY_ID = mf.REPORT_CATEGORY_ID
and mf.FEATURE_CODE = mpf.FEATURE_CODE
and mpa.PA_CODE = mpf.PA_CODE
and mpa.DESIGNATION_ID = sd.DESIGNATION_ID
and mpf.SITE_FEATURE_ID = cmf.SITE_FEATURE_ID
and cmf.MONITORED_FEATURE_ID = cpf.MONITORED_FEATURE_ID
and cms.MONITORING_STATUS_ID = cmf.MONITORING_STATUS_ID
and cc.CYCLE# = cmf.CYCLE#
and msa.PA_CODE = mpa.PA_CODE
and msa.UNIT_ID = smu.UNIT_ID
and msa.SUB_AREA_ID = ssa.SUB_AREA_ID
and cc.CURRENT_CYCLE = 'Y'
and msa.MAIN_AREA = 'P'
and msa.SCM_LEAD = 'Y'
and mpf.INTEREST_CODE in (1,2,3,9)
and ((nvl(:P6_REPORTING_CATEGORY, 'ALL') = 'ALL'
and to_char(frc.FCA_FEATURE_CATEGORY_ID) = case nvl(:P6_BROAD_CATEGORY, 'ALL') when 'ALL' then to_char(frc.FCA_FEATURE_CATEGORY_ID) else :P6_BROAD_CATEGORY end)
or (nvl(:P6_REPORTING_CATEGORY, 'ALL') != 'ALL'
and to_char(mf.REPORT_CATEGORY_ID) = case nvl(:P6_REPORTING_CATEGORY, 'ALL') when 'ALL' then to_char(mf.REPORT_CATEGORY_ID) else :P6_REPORTING_CATEGORY end))
and ((nvl(:P6_SNH_SUB_AREA, 'ALL') = 'ALL'
and to_char(msa.UNIT_ID) = case nvl(:P6_SNH_AREA, 'ALL') when 'ALL' then to_char(msa.UNIT_ID) else :P6_SNH_AREA end)
or (nvl(:P6_SNH_SUB_AREA, 'ALL') != 'ALL'
and to_char(msa.SUB_AREA_ID) = case nvl(:P6_SNH_SUB_AREA, 'ALL') when 'ALL' then to_char(msa.SUB_AREA_ID) else :P6_SNH_SUB_AREA end ))
and ((nvl(:P6_SITE, 'ALL') != 'ALL'
and mpa.PA_CODE = :P6_SITE)
or nvl(:P6_SITE, 'ALL') = 'ALL')

As you can see I have 9 calls to the APEX_ITEM API and when I take them out the report works as I would expect it to.

Has anybody else came across this issue?

We are currently on APEX: 3.0.1.00.08 and using Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production Database.

Thanks in advance,
Paul.

Tyler-Oracle

Try removing all but one of the apex_item.select_list_from_query calls, then rewrite that one to use subquery factoring. Now compare the time for the same query with and without subquery factoring. Also, is 500 rows a realistic number that you would expect someone to edit?

Denes Kubicek

How long does it take if you run the same SQL from SQL Developer or Toad?

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://apex.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

Tyler-Oracle

First, your code is a lot easier to read if you wrap it in code tags. More info here:
http://wiki.oracle.com/page/Oracle+Discussion+Forums+FAQ?t=anon

Now on to your query. How many rows does your query return? Keep in mind that the queries in apex_item.select_list_from_query will be executed once FOR EVERY ROW in your query. This is yet another example of the Tom Kyte mantra of "row by row = slow by slow". Here are some strategies to mitigate this problem:
* Return the rows you want to edit in an inner query, then wrap that in an outter query that includes the apex_item calls.
* Use [subquery factoring|http://www.psoug.org/reference/with.html] for each of the queries used in your apex_item.select_list_from_query calls such that they are only executed once, then the results are reused for each row.
* Use predicates that limit the rows to a reasonable number, say 15-50 rows instead of 1000+.
Tyler

paulgall

Hi Denes,

When i run the above code in Toad then it takes about 10 seconds or so.

Cheers,
Paul.

Denes Kubicek

Toad returns 500 records I think. Try limiting your query running in apex by adding where rownum <= 500 to get a good comparisson. I think the performance problem may be caused by all those case and nvl and to_char functions.

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://apex.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

paulgall

Hi Tyler,

Thanks for you reply I will look into what you have said and see if I can modify the SQL in the way you mentioned.

Hi Denes,

You are correct Toad only brings back 500 records. I did what you asked by adding the rownum clause to the SQL in my report region. Yes it was comparable with how long TOAD was taking.

I also took out the decodes, NVLs and to_char functions and it didn't make much of a difference.

Cheers,
Paul.

Tyler-Oracle

Try removing all but one of the apex_item.select_list_from_query calls, then rewrite that one to use subquery factoring. Now compare the time for the same query with and without subquery factoring. Also, is 500 rows a realistic number that you would expect someone to edit?

Denes Kubicek

Tyler,

good point. That will probably be the one causing the problem. It will be run once for each record.

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://apex.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

Tyler-Oracle

Denes,

And it's not just "the one", since there are 4 apex_item.select_list_from_query. This means that for 500 rows, those calls will add 2,000 additional queries!!!

Tyler

Denes Kubicek

Tyler,

Sure . That one 4x.

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://apex.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

paulgall

Thanks Denes and Tyler. I've revisted my SQL and done the suggestions and its working alot better.

Thanks.

paulgall

Sorry for getting back on this but I have been reading up on this and subquery factoring is not supported in apex_item.select_list_from_query calls.

John Edward Scott

Hello,

subquery factoring is not supported in apex_item.select_list_from_query calls.

What do you mean by 'not supported'?

John.
--------------------------------------------
http://jes.blogs.shellprompt.net
http://www.apex-evangelists.com

paulgall

Hello,

Well I am still not 100% sure about this but I have tried to this in two different ways. When i do it this way

select distinct
    initcap(mpa.pa_name) || ' (' || sd.DESIGNATION_CODE || ')' site,
    frc.REPORT_DESCRIPTION report_category,
    mf.FEATURE_DESC feature,    
    decode(cmf.SELECTED_FOR_QA, 'Y', 'X', 'N', ' ') QA,    
    cpf.ASSIGN_TO,    
    cpf.planned_fieldwork planned_fieldwork,
    cpf.planned_cmf planned_cmf,
    *apex_item.select_list_from_query (*
      *24,*
      *cpf.monitoring_method_id,*
      *'with method_values AS*
        *(  select METHOD, MONITORING_METHOD_ID*
 
           *from cm_monitoring_methods*
 
           *where active_flag = ''Y'')*      select method_values.method, method_values.MONITORING_METHOD_ID from method_values') monitoring_method,
    apex_item.text (
      25,
      cpf.pre_cycle_comments,
      15,
      255,
      'title="'||cpf.pre_cycle_comments||'"',
      'annualPlanningComments'
              ||to_char(cpf.PLAN_MON_FEATURE_ID)) comments,
    apex_item.text (
      26,      
      to_char(cpf.CONTRACT_LET,'DD-MON-YYYY'),
      11,
      11) contract_let,
    apex_item.text (
      27,      
      to_char(cpf.CONTRACT_REPORT_PLANNED,'DD-MON-YYYY'),
      11,
      11) contract_report,
    apex_item.text (
      28,
      cpf.ADVISOR_DATA_ENTRY,
      11,
      11) advisor_entry,
    cms.complete_percentage || ' ' || cms.description status,
   apex_item.text (
     29,
     to_char(cpf.RESULT_SENT_TO_OO,'DD-MON-YYYY'),
     11,
     11) result_to_oo,
   cpf.PLAN_MON_FEATURE_ID,
   cmf.MONITORED_FEATURE_ID,
   mpa.PA_CODE,
   mpf.SITE_FEATURE_ID         
from fm_report_category frc,
     m_feature mf,
     m_pa_features mpf,
     m_protected_area mpa,
     snh_designations sd,
     cm_monitored_features cmf,
     cm_plan_mon_features cpf,
     cm_monitoring_status cms,
     cm_cycles cc,
     m_pa_snh_area msa,
     snh_management_units smu,
     snh_sub_areas ssa
where frc.REPORT_CATEGORY_ID = mf.REPORT_CATEGORY_ID
and   mf.FEATURE_CODE = mpf.FEATURE_CODE
and   mpa.PA_CODE = mpf.PA_CODE
and   mpa.DESIGNATION_ID = sd.DESIGNATION_ID
and   mpf.SITE_FEATURE_ID = cmf.SITE_FEATURE_ID
and   cmf.MONITORED_FEATURE_ID = cpf.MONITORED_FEATURE_ID
and   cms.MONITORING_STATUS_ID = cmf.MONITORING_STATUS_ID
and   cc.CYCLE# = cmf.CYCLE#
and   msa.PA_CODE = mpa.PA_CODE
and   msa.UNIT_ID = smu.UNIT_ID
and   msa.SUB_AREA_ID = ssa.SUB_AREA_ID
and   cc.CURRENT_CYCLE = 'Y'
and   msa.MAIN_AREA = 'P'
and   mpf.INTEREST_CODE in (1,2,3,9)
and ((nvl(:P6_REPORTING_CATEGORY, 'ALL') = 'ALL'     
     and to_char(frc.FCA_FEATURE_CATEGORY_ID) = case nvl(:P6_BROAD_CATEGORY, 'ALL') when 'ALL' then to_char(frc.FCA_FEATURE_CATEGORY_ID) else :P6_BROAD_CATEGORY end)
  or (nvl(:P6_REPORTING_CATEGORY, 'ALL') != 'ALL'
   and to_char(mf.REPORT_CATEGORY_ID) = case nvl(:P6_REPORTING_CATEGORY, 'ALL') when 'ALL' then to_char(mf.REPORT_CATEGORY_ID) else :P6_REPORTING_CATEGORY end))
and ((nvl(:P6_SNH_SUB_AREA, 'ALL') = 'ALL'
     and to_char(msa.UNIT_ID) = case nvl(:P6_SNH_AREA, 'ALL') when 'ALL' then to_char(msa.UNIT_ID) else :P6_SNH_AREA end)
  or (nvl(:P6_SNH_SUB_AREA, 'ALL') != 'ALL'
    and to_char(msa.SUB_AREA_ID) = case nvl(:P6_SNH_SUB_AREA, 'ALL') when 'ALL' then to_char(msa.SUB_AREA_ID) else :P6_SNH_SUB_AREA end ))
and ((nvl(:P6_SITE, 'ALL') != 'ALL'
        and mpa.PA_CODE = :P6_SITE)
     or nvl(:P6_SITE, 'ALL') = 'ALL')
and rownum <= 50

And when I put this in my report I get an error of:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "FLOWS_030000.WWV_FLOW", line 7050
ORA-06512: at "FLOWS_030000.WWV_FLOW_UTILITIES", line 248
ORA-01403: no data found
ORA-06512: at "FLOWS_030000.WWV_FLOW_UTILITIES", line 6230
ORA-06512: at "FLOWS_030000.WWV_FLOW_ITEM", line 683
ORA-06512: at "FLOWS_030000.HTMLDB_ITEM", line 214
ORA-06512: at line 1

so that didn't seem right.

So I triead another approach and did my query like this:

*WITH method_values AS*
*(  select METHOD, MONITORING_METHOD_ID*
 
    *from cm_monitoring_methods*
 
    *where active_flag = 'Y')*select distinct
    initcap(mpa.pa_name) || ' (' || sd.DESIGNATION_CODE || ')' site,
    frc.REPORT_DESCRIPTION report_category,
    mf.FEATURE_DESC feature,    
    decode(cmf.SELECTED_FOR_QA, 'Y', 'X', 'N', ' ') QA,    
    cpf.ASSIGN_TO,    
    cpf.planned_fieldwork planned_fieldwork,
    cpf.planned_cmf planned_cmf,
    *apex_item.select_list_from_query (*
      *24,*
      *cpf.monitoring_method_id,*
      *'select method_values.method, method_values.MONITORING_METHOD_ID from method_values') monitoring_method,*    apex_item.text (
      25,
      cpf.pre_cycle_comments,
      15,
      255,
      'title="'||cpf.pre_cycle_comments||'"',
      'annualPlanningComments'
              ||to_char(cpf.PLAN_MON_FEATURE_ID)) comments,
    apex_item.text (
      26,      
      to_char(cpf.CONTRACT_LET,'DD-MON-YYYY'),
      11,
      11) contract_let,
    apex_item.text (
      27,      
      to_char(cpf.CONTRACT_REPORT_PLANNED,'DD-MON-YYYY'),
      11,
      11) contract_report,
    apex_item.text (
      28,
      cpf.ADVISOR_DATA_ENTRY,
      11,
      11) advisor_entry,
    cms.complete_percentage || ' ' || cms.description status,
   apex_item.text (
     29,
     to_char(cpf.RESULT_SENT_TO_OO,'DD-MON-YYYY'),
     11,
     11) result_to_oo,
   cpf.PLAN_MON_FEATURE_ID,
   cmf.MONITORED_FEATURE_ID,
   mpa.PA_CODE,
   mpf.SITE_FEATURE_ID         
from fm_report_category frc,
     m_feature mf,
     m_pa_features mpf,
     m_protected_area mpa,
     snh_designations sd,
     cm_monitored_features cmf,
     cm_plan_mon_features cpf,
     cm_monitoring_status cms,
     cm_cycles cc,
     m_pa_snh_area msa,
     snh_management_units smu,
     snh_sub_areas ssa,     
     method_values mv
where frc.REPORT_CATEGORY_ID = mf.REPORT_CATEGORY_ID
and   mf.FEATURE_CODE = mpf.FEATURE_CODE
and   mpa.PA_CODE = mpf.PA_CODE
and   mpa.DESIGNATION_ID = sd.DESIGNATION_ID
and   mpf.SITE_FEATURE_ID = cmf.SITE_FEATURE_ID
and   cmf.MONITORED_FEATURE_ID = cpf.MONITORED_FEATURE_ID
and   cms.MONITORING_STATUS_ID = cmf.MONITORING_STATUS_ID
and   cc.CYCLE# = cmf.CYCLE#
and   msa.PA_CODE = mpa.PA_CODE
and   msa.UNIT_ID = smu.UNIT_ID
and   msa.SUB_AREA_ID = ssa.SUB_AREA_ID
and   cc.CURRENT_CYCLE = 'Y'
and   msa.MAIN_AREA = 'P'
and   mpf.INTEREST_CODE in (1,2,3,9)
and   mv.MONITORING_METHOD_ID (+) = cpf.monitoring_method_id 
and ((nvl(:P6_REPORTING_CATEGORY, 'ALL') = 'ALL'     
     and to_char(frc.FCA_FEATURE_CATEGORY_ID) = case nvl(:P6_BROAD_CATEGORY, 'ALL') when 'ALL' then to_char(frc.FCA_FEATURE_CATEGORY_ID) else :P6_BROAD_CATEGORY end)
  or (nvl(:P6_REPORTING_CATEGORY, 'ALL') != 'ALL'
   and to_char(mf.REPORT_CATEGORY_ID) = case nvl(:P6_REPORTING_CATEGORY, 'ALL') when 'ALL' then to_char(mf.REPORT_CATEGORY_ID) else :P6_REPORTING_CATEGORY end))
and ((nvl(:P6_SNH_SUB_AREA, 'ALL') = 'ALL'
     and to_char(msa.UNIT_ID) = case nvl(:P6_SNH_AREA, 'ALL') when 'ALL' then to_char(msa.UNIT_ID) else :P6_SNH_AREA end)
  or (nvl(:P6_SNH_SUB_AREA, 'ALL') != 'ALL'
    and to_char(msa.SUB_AREA_ID) = case nvl(:P6_SNH_SUB_AREA, 'ALL') when 'ALL' then to_char(msa.SUB_AREA_ID) else :P6_SNH_SUB_AREA end ))
and ((nvl(:P6_SITE, 'ALL') != 'ALL'
        and mpa.PA_CODE = :P6_SITE)
     or nvl(:P6_SITE, 'ALL') = 'ALL')
and rownum <= 50

When I run the above code i get:

report error:
ORA-06550: line 1, column 151:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored

I am understanding subquery factoring but I can't seem to get my head around how you would use it in a query for a apex_item.select_list_from_query.

Thanks in advance,
Paul.