Introduction
I talked with two APEX experts at KSCOPE23 regarding the use of APEX Collections. We all agreed that APEX Collections should be used cautiously and that performance issues were likely if you overused collections, especially in a large-scale APEX environment.
💡
The primary concern is that under the covers of an APEX Collection is a single table shared across all Sessions in all Workspaces in the APEX instance.
When I got back from KSCOPE23, I decided to perform some empirical testing to find out at what point performance starts to degrade when using collections.
Test Environment
I ran my tests on an Oracle OCI Base Database:
There was nothing else going on in the instance when I ran these tests.
Methodology
Generating Collections
I have a table of EV sales by year in California. The table has 15,591 rows. I used the following script to create 200 collections in 200 different APEX sessions, each collection containing 15,591 rows.
DECLARE
l_query CONSTANT VARCHAR2(4000) := 'SELECT data_year, county, fuel_type, make, model, number_of_vehicles FROM ev_sales_by_year';
BEGIN
FOR i IN 1..200 LOOP
apex_session.create_session (p_app_id => 201, p_page_id => 1, p_username => 'COLLECTION_PERFORMANCE');
apex_collection.create_collection_from_query_b
(p_collection_name => 'COLLECTION_PERFORMANCE',
p_query => l_query);
apex_session.detach;
END LOOP;
END;
Counting Collection Members
After each execution of the above script, I ran the following SQL as the SYSTEM user. This gave me a total count of collections, sessions, and collection members across all APEX Sessions in the database instance.
SELECT COUNT(1) count_members
, COUNT(DISTINCT c.id) count_collections
, COUNT(DISTINCT c.session_id) count_sessions
FROM apex_230100.wwv_flow_collection_members$ m
, apex_230100.wwv_flow_collections$ c
WHERE c.id = m.collection_id
AND c.collection_name = 'COLLECTION_PERFORMANCE';
Assessing the Impact
Finally, I created a simple APEX Page, which creates a collection before each page view. To avoid confusion, the collection has a different name APEX_PAGE from the one in the previous section, COLLECTION_PERFORMANCE. The APEX_PAGE Collection contains 15,591 records.
I used abex_debug.error to get a timing for the collection generation for each page view. This avoided enabling debug to get the timing (which incurs an overhead).
The page also contains a simple Interactive Report on the collection APEX_PAGE and includes a #TIMING# substitution string in the Footer Text. The #TIMING# substitution string displays the number of seconds it takes APEX to run the report on 15,591 rows.
The report output looks like this:
The SQL APEX Runs
At this point, it is worth noting that APEX wraps any SQL statement provided in a report region to get the total row count and limit the number of records returned. After checking APEX Debug, I could see the SQL being run by APEX for this report was as follows:
select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
from (select "C001","C002","C003","C004"
from(select d."C001",d."C002",d."C003",d."C004" from(SELECT c001
, c002
, c003
, c004
FROM apex_collections
WHERE collection_name = 'APEX_PAGE'
AND c001 = '2013'
)d
)i
)i where 1=1 and rownum<=1000001
Results
I took initial timings with no collection records at all. I then generated five sets of 200 collections in 200 different APEX Sessions. The results are as follows:
- Columns in Grey represent the generation of 15.5 million records in the 'COLLECTION_PERFORMANCE' collection across 1,000 APEX sessions.
Columns in Orange represent the timings to generate records in the collection called APEX_PAGE in the APEX Page Process.
Columns in Green represent the timings to run the Interactive report on 15,591 records in the collection called APEX_PAGE.
I took the screenshot below to prove that over 1,000 APEX Sessions were generated from these tests.
and more than 15.5 million collection records:
Considerations
I did not test the impact of contention on the collections table. I am sure performance will be negatively impacted if hundreds of sessions are constantly adding/deleting records.
I cleared down the 15 million collection records by running the following script.
DECLARE
CURSOR cr_sessions IS
SELECT apex_session_id
FROM apex_workspace_sessions
WHERE user_name = 'COLLECTION_PERFORMANCE';
BEGIN
FOR r_session IN cr_sessions LOOP
apex_session.attach (p_app_id => 201, p_page_id => 1, p_session_id => r_session.apex_session_id);
IF apex_collection.collection_exists (p_collection_name => 'COLLECTION_PERFORMANCE') THEN
apex_collection.truncate_collection (p_collection_name => 'COLLECTION_PERFORMANCE');
END IF;
apex_session.detach;
END LOOP;
END;
This script took 744 seconds (12.4 minutes) to complete. This gives you an idea of the impact of deleting records from collections when many records are in the collection table.
Conclusion
I must say that I was pretty surprised by the results. The final test showed almost no performance degradation with more than 15 million collection records.
💡
I should add that I am not a DBA, and I did not assess the impact on the database as a whole. I welcome any feedback you have on my testing methods.
Read More
