Forum Stats

  • 3,734,281 Users
  • 2,246,937 Discussions
  • 7,857,218 Comments

Discussions

Performance Issues Using APEX_ITEM in SQL?

paulgall
paulgall Member Posts: 103
edited Sep 9, 2008 5:45AM in APEX Discussions
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.

Best Answer

  • Tyler-Oracle
    Tyler-Oracle Member Posts: 1,713
    Accepted Answer
    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?

Answers

  • Denes Kubicek
    Denes Kubicek Member Posts: 6,163 Bronze Crown
    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
    Tyler-Oracle Member Posts: 1,713
    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
    Tyler-Oracle
  • paulgall
    paulgall Member Posts: 103
    Hi Denes,

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

    Cheers,
    Paul.
  • Denes Kubicek
    Denes Kubicek Member Posts: 6,163 Bronze Crown
    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
    paulgall Member Posts: 103
    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
    Tyler-Oracle Member Posts: 1,713
    Accepted Answer
    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
    Denes Kubicek Member Posts: 6,163 Bronze Crown
    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
    -------------------------------------------------------------------
    Denes Kubicek
  • Tyler-Oracle
    Tyler-Oracle Member Posts: 1,713
    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
    Tyler-Oracle
  • Denes Kubicek
    Denes Kubicek Member Posts: 6,163 Bronze Crown
    edited Sep 4, 2008 11:22AM
    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
    paulgall Member Posts: 103
    Thanks Denes and Tyler. I've revisted my SQL and done the suggestions and its working alot better.

    Thanks.
  • paulgall
    paulgall Member Posts: 103
    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
    John Edward Scott Member Posts: 5,475 Gold Badge
    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
    paulgall Member Posts: 103
    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.
This discussion has been closed.