We recently upgraded from 10G to 11G and the existing query started running really slow....5 minutes to more than 3 hours.
Check the attached live sql file for sample details about the issue.
With SECUREFILE BINARY XML, EXTRACT method is running slow while XMLTABLE is running fast
With BASICFILE CLOB, EXTRACT method is running fast while XMLTABLE is running slow
Appreciate any help that can make the performance of the query independent of the XMLTYPE storage type
Thanks for your help in advance.
Your examples all come out with similar times for me. And none are anywhere near 3 hours...
Using your setup (being explicit about securefile binary xml!):
CREATE TABLE XML_STORAGE_TEST (COL1 NUMBER, COL2 XMLTYPE)
XMLTYPE COLUMN COL2 STORE AS SECUREFILE BINARY XML ;
CREATE TABLE XML_STORAGE_TEST1 (COL1 NUMBER, COL2 XMLTYPE)
XMLTYPE COLUMN COL2 STORE AS BASICFILE CLOB ;
DECLARE
VAL1 XMLTYPE := XMLTYPE(
'<a>
<b><Name>AB_C</Name><Value>something</Value></b>
<b><Name>DEF</Name><Value>something else</Value></b>
<b><Name>GHI</Name><Value>anything</Value></b>
</a>');
VAL2 XMLTYPE := XMLTYPE(
'<a>
<b><Name>DEF</Name><Value>whatever</Value></b>
<b><Name>AB C</Name><Value>what else</Value></b>
</a>');
BEGIN
--notice that the particular Name" tag position is dynamic
--notice also that the "Name" tag values are different but should be considered same
FOR i IN 1..1200 LOOP
INSERT INTO XML_STORAGE_TEST VALUES (i, VAL1);
INSERT INTO XML_STORAGE_TEST VALUES (i, VAL2);
INSERT INTO XML_STORAGE_TEST1 VALUES (i, VAL1);
INSERT INTO XML_STORAGE_TEST1 VALUES (i, VAL2);
END LOOP;
COMMIT;
END;
/
CREATE OR REPLACE FUNCTION GET_RQSTD_NAMES_VALUE1(
IN_XML IN XMLTYPE,
IN_NAME IN VARCHAR2) RETURN VARCHAR2
IS
V_COUNT NUMBER := 1;
V_NAME VARCHAR2(100);
OUT_VALUE VARCHAR2(100);
BEGIN
WHILE IN_XML.EXISTSNODE('//a/b[' || V_COUNT || ']') = 1 LOOP
V_NAME := UPPER(REPLACE(IN_XML.EXTRACT('/a/b' || '[' || V_COUNT || ']/Name/text()').GETSTRINGVAL(),' ','_'));
IF V_NAME = UPPER(REPLACE(IN_NAME,' ','_'))
AND (IN_XML.EXISTSNODE('/a/b[' || V_COUNT || ']/Value/text()') = 1) THEN
OUT_VALUE := IN_XML.EXTRACT('/a/b[' || V_COUNT || ']/Value/text()').GETSTRINGVAL();
EXIT;
END IF;
V_COUNT := V_COUNT + 1;
END LOOP;
RETURN OUT_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN OUT_VALUE;
END GET_RQSTD_NAMES_VALUE1;
/
CREATE OR REPLACE FUNCTION GET_RQSTD_NAMES_VALUE2(
IN_XML IN XMLTYPE,
IN_NAME IN VARCHAR2) RETURN VARCHAR2
IS
V_COUNT NUMBER := 1;
V_NAME VARCHAR2(100);
OUT_VALUE VARCHAR2(100);
BEGIN
SELECT Val
INTO OUT_VALUE
FROM XMLTABLE('/a/b'
PASSING IN_XML
COLUMNS Val VARCHAR2(100) PATH 'Value',
Nm VARCHAR2(100) PATH 'Name')
WHERE UPPER(REPLACE(Nm,' ','_')) = UPPER(REPLACE(IN_NAME,' ','_'));
RETURN OUT_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN OUT_VALUE;
END GET_RQSTD_NAMES_VALUE2;
/
Gives the following stats:
SQL> set timing on
SQL> set autotrace trace stat
SQL>
SQL> SELECT COL1,
2 GET_RQSTD_NAMES_VALUE1(COL2, 'AB_C') A,
3 GET_RQSTD_NAMES_VALUE1(COL2, 'DEF') B,
4 GET_RQSTD_NAMES_VALUE1(COL2, 'GHI') C
5 FROM XML_STORAGE_TEST;
2400 rows selected.
Elapsed: 00:00:16.79
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
83829 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2400 rows processed
SQL>
SQL> SELECT COL1,
2 GET_RQSTD_NAMES_VALUE2(COL2, 'AB_C') A,
3 GET_RQSTD_NAMES_VALUE2(COL2, 'DEF') B,
4 GET_RQSTD_NAMES_VALUE2(COL2, 'GHI') C
5 FROM XML_STORAGE_TEST;
2400 rows selected.
Elapsed: 00:00:10.88
Statistics
----------------------------------------------------------
8400 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
83829 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2400 rows processed
SQL>
SQL> SELECT COL1,
2 GET_RQSTD_NAMES_VALUE1(COL2, 'AB_C') A,
3 GET_RQSTD_NAMES_VALUE1(COL2, 'DEF') B,
4 GET_RQSTD_NAMES_VALUE1(COL2, 'GHI') C
5 FROM XML_STORAGE_TEST1;
2400 rows selected.
Elapsed: 00:00:07.92
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
92 consistent gets
0 physical reads
0 redo size
83975 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2400 rows processed
SQL>
SQL> SELECT COL1,
2 GET_RQSTD_NAMES_VALUE2(COL2, 'AB_C') A,
3 GET_RQSTD_NAMES_VALUE2(COL2, 'DEF') B,
4 GET_RQSTD_NAMES_VALUE2(COL2, 'GHI') C
5 FROM XML_STORAGE_TEST1;
2400 rows selected.
Elapsed: 00:00:09.76
Statistics
----------------------------------------------------------
8400 recursive calls
0 db block gets
92 consistent gets
0 physical reads
0 redo size
83975 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2400 rows processed
Extract on the securefile binary xml is slightly slower, but all are around 10s.
So to help we need more details! If your queries are single table selects calling functions, then presumably your query is waiting on "something".
Find out what this with a script like:
https://oracle-base.com/dba/script?category=10g&file=session_waits.sql If your real queries are more complicated, then share the execution plans too! Ideally capturing wait info. For details on how to do this, read:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution Also note: you could do away with the function calls and process everything in SQL using XMLTable. This avoids SQL <> PL/SQL context switches, so should help your query.
This will give you all the extracted elements as rows. To get your desired output all you need to do is pivot! To do this, generate a sequence column using the "for ordinality" clause. This assigns row numbers.
Unfortunately this is per root element. To get this by attribute, pass the sequence to row_number, partitioned by col1 and your name replacement to "fix" the space/underscores.
This gives you something like the following. This gives a big boost to binary XML storage:
SQL> with rws as (
2 select /*+ no_merge */col1, replace(nm,' ','_') nm, val,
3 row_number() over (partition by col1, replace(nm,' ','_') order by seq) rn
4 from xml_storage_test1,
5 xmltable('/a/b'
6 passing col2
7 columns seq for ordinality,
8 val varchar2(100) path 'Value',
9 nm varchar2(100) path 'Name')
10 )
11 select col1, a, b, c
12 from rws
13 pivot (min(val) for nm in ('AB_C' a, 'DEF' b, 'GHI' c));
2400 rows selected.
Elapsed: 00:00:07.98
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
68 consistent gets
0 physical reads
0 redo size
57791 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2400 rows processed
SQL>
SQL> with rws as (
2 select /*+ no_merge */col1, replace(nm,' ','_') nm, val,
3 row_number() over (partition by col1, replace(nm,' ','_') order by seq) rn
4 from xml_storage_test,
5 xmltable('/a/b'
6 passing col2
7 columns seq for ordinality,
8 val varchar2(100) path 'Value',
9 nm varchar2(100) path 'Name')
10 )
11 select col1, a, b, c
12 from rws
13 pivot (min(val) for nm in ('AB_C' a, 'DEF' b, 'GHI' c));
2400 rows selected.
Elapsed: 00:00:05.26
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
57791 bytes sent via SQL*Net to client
724 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2400 rows processed
Note: the no_merge hint is there because there appears to be a bug with pivot/with/ XMLTable in 11.2 that this prevents (works in 12.1).