We observe very poor performance of XMLAgg() function in Oracle SQL:
1. Background
We try to generate XML pages directly in the SQL query in the database.
They include a reference to a XSL Stylesheet and are sent to the browser for web display.
The base data are pure relational, but a complex viewing of the data implies generating hierarchical XML,
far beyond a normal ROWSET/ROW schema. To achieve this, we have to use XMLAgg, often in
combination of grouping, subqueries etc.
2. Test Case
To abstract from our data model, we have constructed a simple testcase easyly reproducable on every recent
Oracle database providing XMLAgg (e.g. 9i or 10g).
The example here runs on 10.2.0.3 on Vista 32bit.
We create a simple test table with a reasonable amount of rows.
Then we try to generate on XMLType from all the rows.
3. The Problem
We show 3 test cases along with the corresponding TKPROF output:
case1 : the XMLAgg is commented out, the query returns many rows of XMLType Elements
case2 : Using XMLAgg to aggregate the elements into on XMLType object -> SLOW !!!!!!
case3 : Here we show a different method of aggregation by using collect and
convertiung this nested Table to an XMLType using XMLElement, wich returns
1 XMLtype comparable to case 2, but the XML Structure is different (added VARCHAR2 tags)
Both cases 2 and 3 produce 1 aggregated XML, but while case3 uses 60 ms, case 2 uses about 2200 ms !!
In more complex situations, case3 is not practical because of added XML-tags when using object types,
no grouping etc.
Any confirmation / suggestion / workaraound would be much appreciated
Below the SQL to construnct the testcases and the reulting TKPROF
create or replace type temp_obj_name_type as table of varchar2 (30);
/
drop table temp_all_obj;
commit;
CREATE --GLOBAL TEMPORARY
TABLE TEMP_ALL_OBJ
-- ON COMMIT PRESERVE ROWS
AS SELECT * FROM all_objects
;
commit;
-- case0: simple count
select count(*) as case0 from temp_all_obj;
/***
alter session set SQL_TRACE=TRUE;
alter session set SQL_TRACE=FALSE;
***/
-- ---------------------- Performance Test cases ---------------
-- case1: without aggregate -> fast
select count(*) as case1 from (
select
XMLElement ("OBJL",
--XMLAgg(
XMLElement ("N", object_name)
--) -- Agg
)
from
temp_all_obj
)
;
-- case2: with XMLAgg -> slow
select count(*) as case2 from (
select
XMLElement ("OBJL",
XMLAgg(
XMLElement ("N", object_name)
) -- Agg
)
from
temp_all_obj
)
;
-- case3: with XMLElement (collect...) -> about same as case1
select count(*) as case3 from (
select
XMLElement ("OBJL",
XMLElement ("L",cast(collect(object_name) as temp_obj_name_type) as "N")
)
from
temp_all_obj
)
;
TKPROF: Release 10.2.0.3.0 - Production on Fr Mai 11 12:20:30 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: ekat_ora_3716.trc
Sort options: default
... CASE 1 ############################################################################
select count(*) as case1 from (
select
XMLElement ("OBJL",
--XMLAgg(
XMLElement ("N", object_name)
--) -- Agg
)
from
temp_all_obj
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 573 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 573 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=573 pr=0 pw=0 time=8936 us)
40776 TABLE ACCESS FULL TEMP_ALL_OBJ (cr=573 pr=0 pw=0 time=163167 us)
********************************************************************************
... CASE 2 (2 runs) #####################################################################
select count(*) as case2 from (
select
XMLElement ("OBJL",
XMLAgg(
XMLElement ("N", object_name)
) -- Agg
)
from
temp_all_obj
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.23 2.25 0 24272 131423 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.23 2.25 0 24272 131423 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=24272 pr=0 pw=0 time=2256091 us)
1 VIEW (cr=24272 pr=0 pw=0 time=2256058 us)
1 SORT AGGREGATE (cr=24272 pr=0 pw=0 time=2256038 us)
40776 TABLE ACCESS FULL TEMP_ALL_OBJ (cr=573 pr=0 pw=0 time=163170 us)
********************************************************************************
select count(*) as case2 from (
select
XMLElement ("OBJL",
XMLAgg(
XMLElement ("N", object_name)
) -- Agg
)
from
temp_all_obj
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.18 2.28 0 24272 131423 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.18 2.28 0 24272 131423 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=24272 pr=0 pw=0 time=2284282 us)
1 VIEW (cr=24272 pr=0 pw=0 time=2284249 us)
1 SORT AGGREGATE (cr=24272 pr=0 pw=0 time=2284227 us)
40776 TABLE ACCESS FULL TEMP_ALL_OBJ (cr=573 pr=0 pw=0 time=163186 us)
********************************************************************************
... CASE 3 ############################################################################
select count(*) as case3 from (
select
XMLElement ("OBJL",
XMLElement ("L",cast(collect(object_name) as temp_obj_name_type) as "N")
)
from
temp_all_obj
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.05 0 573 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.05 0 573 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=573 pr=0 pw=0 time=56582 us)
1 VIEW (cr=573 pr=0 pw=0 time=56545 us)
1 SORT AGGREGATE (cr=573 pr=0 pw=0 time=56526 us)
40776 TABLE ACCESS FULL TEMP_ALL_OBJ (cr=573 pr=0 pw=0 time=163170 us)
********************************************************************************
Message was edited by:
mdrake