APEX

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!

Performance Issues Using APEX_ITEM in SQL?

paulgallSep 4 2008 — edited Sep 9 2008

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.

This post has been answered by Tyler-Oracle on Sep 4 2008

Jump to Answer

Hilary Farrell-Oracle

Answer

Hi JKL,
If you haven't already done so, please refer to section 20.3 Controlling Access to Applications, Pages, and Page Components in our APEX 20.2 User's Guide. You haven't stated what Authentication Scheme has been selected for your app, so I'll go with the assumption that it's the default APEX Application Account. The usernames that you list in your Access Control list, should be created as new users in the workspace. Please refer to section 2.7 Managing Users in a Workspace in our APEX 20.2 Administration Guide, specifically section 2.7.3 Creating User Accounts. Once you've created the users in the workspace, to correspond with the user names you've defined in your app's Access Control list, then those user credentials can be used to log into the app.
I hope this helps.
Regards,
Hilary

Marked as Answer by JKL · Oct 27 2020

JKL

Hilary,
Thanks for the reply. I am comparing the user from the Application specific Users area in the Administration section and the APEX Workspace user. The two lists don't match. Attached please find the screen shots between the two. One from the Application Administration Access Control User list and the other is the Workspace user. I don't think that the two lists are the same like you suggested unless I misunderstand you. The following two screen shots will show you the difference. I blur the domain name for the email address. In the Workspace user section, you can ignore GETHELP as the user. That's for another app/login. But jlam@xxxxxx.com is my actual login to the apex.oracle.com. In the specific app, I also have an user named jlam@xxxxxx.com. I can understand this one. But I have also created ds9, jl2 via the Application Administration menu link area, i.e. default Access Control List feature Admin page seeded with the App Creation wizard. But I am not finding these users in the Workspace User list. So I don't think that the two are the same here.
The following screen shot was taken from the App Administration section of the User list.
The following screen shot was taken from the Workspace Manage User section.

The two are very different. My understanding is that the APEX user is created in the Workspace. But my question is related to the Application level user list. Where do they create and how can I login as those user such as DS9 in the screen. There is no place to create the password when you add a new user via the Application Administration section.
So are you suggesting that I have to also create the same user e.g. DS9 or JL2 in the Workspace area and then they can access the app that way?
Hope I can explain my question properly. Thanks

JKL

Hilary,
Ok, I just create new user via the Manage Workspace User area and it seems to work the way it is. But how does APEX reconcile the two lists. Are they truely the same user in this case other than the user name is the same. But it is just weird that this is the method. Why can't APEX simply actually create the Workspace user directly if the user is not already created? Either way, thanks for your reply.

Hilary Farrell-Oracle

Hi JKL,
So are you suggesting that I have to also create the same user e.g. DS9 or JL2 in the Workspace area and then they can access the app that way?
Yes, that's correct. When you're using APEX Accounts authentication, the users in question must exist in the workspace in order to have access to the application. Users added to the Access Control List via the Application Administration are not automatically created as workspace users. You must use the workspace Administration -> Manage Users and Groups -> Create User wizard in order to actually create the new users.
Regards,
Hilary

JKL

Hilary,
It would be nice to automatically add this as APEX user or take you to the add user page for the app if the app has a custom application user creation process. I guess that this may be quite a complicated feature. For now, thanks for your update.

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 7 2008

Added on Sep 4 2008

#apex-discussions, #oracle, #performance, #report, #sql

13 comments

2,695 views

APEX

Performance Issues Using APEX_ITEM in SQL?

Comments

Post Details