Skip to Main Content
  • Questions

  • Generating XML doc seems slow & Cursor Results Sets

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jon.

Asked: July 08, 2002 - 11:03 pm UTC

Last updated: May 24, 2010 - 2:25 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom,

2 questions:

1) We have the latest version of the XDK for PL/SQL installed into an 8.1.7 database on Solaris. Generating an XML doc using the supplied XMLGEN package (or DBMS_XMLQUERY) appears slow compared with the speed I can generated an XML doc "manually" using BULK COLLECT. For example:

-- Using XMLGEN
declare
vXML clob;
vBuff varchar2(4002);
vTime number;
begin
vTime := dbms_utility.get_time;
vBuff := 'select OWNER, TABLE_NAME, TABLESPACE_NAME,
CLUSTER_NAME, IOT_NAME, PCT_FREE, PCT_USED, INI_TRANS,
MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
LOGGING, BACKED_UP, NUM_ROWS, BLOCKS
from all_tables';
XMLGen.setRaiseException(true);
vXML := XMLGen.getXML(vBuff);
vTime := 10*(dbms_utility.get_time-vTime);
dbms_output.put_line('XMLGEN: '||vTime||' msecs');
dbms_lob.freeTemporary(vXML);
end;

XMLGEN: 3200 msecs


-- Using BULK COLLECT
declare
NL constant char(1) := chr(10);
type TXMLRows is table of varchar2(4000);
vXMLRows TXMLRows;
vXML clob;
vRow varchar2(4002);
vTime number;
begin
vTime := dbms_utility.get_time;
select '<OWNER>'||OWNER||'</OWNER>'
||'<TABLE_NAME>'||TABLE_NAME||'</TABLE_NAME>'
||'<TABLESPACE_NAME>'||TABLESPACE_NAME||'</TABLESPACE_NAME>'
||'<CLUSTER_NAME>'||CLUSTER_NAME||'</CLUSTER_NAME>'
||'<IOT_NAME>'||IOT_NAME||'</IOT_NAME>'
||'<PCT_FREE>'||PCT_FREE||'</PCT_FREE>'
||'<PCT_USED>'||PCT_USED||'</PCT_USED>'
||'<INI_TRANS>'||INI_TRANS||'</INI_TRANS>'
||'<MAX_TRANS>'||MAX_TRANS||'</MAX_TRANS>'
||'<INITIAL_EXTENT>'||INITIAL_EXTENT||'</INITIAL_EXTENT>'
||'<NEXT_EXTENT>'||NEXT_EXTENT||'</NEXT_EXTENT>'
||'<MIN_EXTENTS>'||MIN_EXTENTS||'</MIN_EXTENTS>'
||'<MAX_EXTENTS>'||MAX_EXTENTS||'</MAX_EXTENTS>'
||'<PCT_INCREASE>'||PCT_INCREASE||'</PCT_INCREASE>'
||'<FREELISTS>'||FREELISTS||'</FREELISTS>'
||'<FREELIST_GROUPS>'||FREELIST_GROUPS||'</FREELIST_GROUPS>'
||'<LOGGING>'||LOGGING||'</LOGGING>'
||'<BACKED_UP>'||BACKED_UP||'</BACKED_UP>'
||'<NUM_ROWS>'||NUM_ROWS||'</NUM_ROWS>'
||'<BLOCKS>'||BLOCKS||'</BLOCKS>'
bulk collect into vXMLRows
from all_tables;
dbms_lob.createTemporary(vXML, true, dbms_lob.call);
for i in 1..nvl(vXMLRows.Last,0) loop
vRow := vXMLRows(i)||NL;
dbms_lob.writeappend(vXML, length(vRow), vRow);
end loop;
dbms_lob.freeTemporary(vXML);
vTime := 10*(dbms_utility.get_time-vTime);
dbms_output.put_line('Bulk Collect: '||vTime||' msecs');
end;

Bulk Collect: 1270 msecs

With the system I am working on, generating XML docs quickly is important. The supplied XDK packages appear to get exponentially slower as the number of rows and/or columns increase. Using bulk collect, however, results in a linear performance drop off as rows/columns increase. The difference between these two approaches in favour of bulk collect can be huge (20+ secs) with some queries. The problem with using bulk collect is that it is tedious for developers to manually construct XML docs like this, and it doesn't work if an XML "row" is > 4000 bytes.

Is the something wrong with XMLGEN implementation to be this slow? Is it better in 9i (unfortunatley upgrading is not an option at the moment)? Is there some way of getting this faster? And btw, are we suppose to be using XMLGEN or DBMS_XMLQUERY?

2) When a ref cursor is passed back to a client application (VB, SQLPlus etc.) the tool in some way has access to the columns and their datatypes when displaying the result set. Within PL/SQL, when a ref cursor is passed to a procedure, can we access that same information - essentially the structure of the cursor? You can probably see where I am going with this in light of question 1...

Thanks for your assistance.
Regards,
Jon Waldron

and Tom said...

Specific code is almost certainly ALWAYS going to be faster then generic code -- almost certainly. As you point out also, your "optimal" approach will vary by the data -- you cannot always do the concatenation. It is not a generic approach but rather very specific to the data itself.

I did run a test in 9iR1 and and then R2 and this was the output:

ops$tkyte@ORA9I.WORLD> declare
2 vXML clob;
3 vBuff varchar2(4002);
4 vTime number;
5 vCtx number;
6 begin
7 vTime := dbms_utility.get_time;
8 vBuff := 'select OWNER, TABLE_NAME, TABLESPACE_NAME,
9 CLUSTER_NAME, IOT_NAME, PCT_FREE, PCT_USED, INI_TRANS,
10 MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
11 MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
12 LOGGING, BACKED_UP, NUM_ROWS, BLOCKS
13 from all_tables';
14
15 vCtx := dbms_xmlgen.NEWCONTEXT( vBuff );
16 vXML := dbms_XMLGen.getXML(vCtx);
17 vTime := (dbms_utility.get_time-vTime)/100;
18 dbms_output.put_line('XMLGEN: '||vTime||' secs, ' || dbms_lob.getlength(vXML) || ' bytes');
19 dbms_lob.freeTemporary(vXML);
20 end;
21 /
XMLGEN: 1.64 secs, 325847 bytes

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> declare
2 NL constant char(1) := chr(10);
3 type TXMLRows is table of varchar2(4000);
4 vXMLRows TXMLRows;
5 vXML clob;
6 vRow varchar2(4002);
7 vTime number;
8 begin
9 vTime := dbms_utility.get_time;
10 select '<OWNER>'||OWNER||'</OWNER>'
11 ||'<TABLE_NAME>'||TABLE_NAME||'</TABLE_NAME>'
12 ||'<TABLESPACE_NAME>'||TABLESPACE_NAME||'</TABLESPACE_NAME>'
13 ||'<CLUSTER_NAME>'||CLUSTER_NAME||'</CLUSTER_NAME>'
14 ||'<IOT_NAME>'||IOT_NAME||'</IOT_NAME>'
15 ||'<PCT_FREE>'||PCT_FREE||'</PCT_FREE>'
16 ||'<PCT_USED>'||PCT_USED||'</PCT_USED>'
17 ||'<INI_TRANS>'||INI_TRANS||'</INI_TRANS>'
18 ||'<MAX_TRANS>'||MAX_TRANS||'</MAX_TRANS>'
19 ||'<INITIAL_EXTENT>'||INITIAL_EXTENT||'</INITIAL_EXTENT>'
20 ||'<NEXT_EXTENT>'||NEXT_EXTENT||'</NEXT_EXTENT>'
21 ||'<MIN_EXTENTS>'||MIN_EXTENTS||'</MIN_EXTENTS>'
22 ||'<MAX_EXTENTS>'||MAX_EXTENTS||'</MAX_EXTENTS>'
23 ||'<PCT_INCREASE>'||PCT_INCREASE||'</PCT_INCREASE>'
24 ||'<FREELISTS>'||FREELISTS||'</FREELISTS>'
25 ||'<FREELIST_GROUPS>'||FREELIST_GROUPS||'</FREELIST_GROUPS>'
26 ||'<LOGGING>'||LOGGING||'</LOGGING>'
27 ||'<BACKED_UP>'||BACKED_UP||'</BACKED_UP>'
28 ||'<NUM_ROWS>'||NUM_ROWS||'</NUM_ROWS>'
29 ||'<BLOCKS>'||BLOCKS||'</BLOCKS>'
30 bulk collect into vXMLRows
31 from all_tables;
32 dbms_lob.createTemporary(vXML, true, dbms_lob.call);
33 for i in 1..nvl(vXMLRows.Last,0) loop
34 vRow := vXMLRows(i)||NL;
35 dbms_lob.writeappend(vXML, length(vRow), vRow);
36 end loop;
37 vTime := (dbms_utility.get_time-vTime)/100;
38 dbms_output.put_line('Bulk Collect: '||vTime||' secs, ' || dbms_lob.getlength(vXML) || ' bytes');
39 dbms_lob.freeTemporary(vXML);
40 end;
41 /
Bulk Collect: 1.71 secs, 350442 bytes

PL/SQL procedure successfully completed.



ops$tkyte@ORA920> declare
2 vXML clob;
3 vBuff varchar2(4002);
4 vTime number;
5 begin
6 vTime := dbms_utility.get_time;
7 vBuff := 'select OWNER, TABLE_NAME, TABLESPACE_NAME,
8 CLUSTER_NAME, IOT_NAME, PCT_FREE, PCT_USED, INI_TRANS,
9 MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
10 MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
11 LOGGING, BACKED_UP, NUM_ROWS, BLOCKS
12 from all_tables';
13
14 vXML := dbms_XMLGen.getXML(vBuff);
15 vTime := (dbms_utility.get_time-vTime)/100;
16 dbms_output.put_line('XMLGEN: '||vTime||' secs, ' || dbms_lob.getlength(vXML) || ' bytes');
17 dbms_lob.freeTemporary(vXML);
18 end;
19 /
XMLGEN: 1.29 secs, 342798 bytes

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 NL constant char(1) := chr(10);
3 type TXMLRows is table of varchar2(4000);
4 vXMLRows TXMLRows;
5 vXML clob;
6 vRow varchar2(4002);
7 vTime number;
8 begin
9 vTime := dbms_utility.get_time;
10 select '<OWNER>'||OWNER||'</OWNER>'
11 ||'<TABLE_NAME>'||TABLE_NAME||'</TABLE_NAME>'
12 ||'<TABLESPACE_NAME>'||TABLESPACE_NAME||'</TABLESPACE_NAME>'
13 ||'<CLUSTER_NAME>'||CLUSTER_NAME||'</CLUSTER_NAME>'
14 ||'<IOT_NAME>'||IOT_NAME||'</IOT_NAME>'
15 ||'<PCT_FREE>'||PCT_FREE||'</PCT_FREE>'
16 ||'<PCT_USED>'||PCT_USED||'</PCT_USED>'
17 ||'<INI_TRANS>'||INI_TRANS||'</INI_TRANS>'
18 ||'<MAX_TRANS>'||MAX_TRANS||'</MAX_TRANS>'
19 ||'<INITIAL_EXTENT>'||INITIAL_EXTENT||'</INITIAL_EXTENT>'
20 ||'<NEXT_EXTENT>'||NEXT_EXTENT||'</NEXT_EXTENT>'
21 ||'<MIN_EXTENTS>'||MIN_EXTENTS||'</MIN_EXTENTS>'
22 ||'<MAX_EXTENTS>'||MAX_EXTENTS||'</MAX_EXTENTS>'
23 ||'<PCT_INCREASE>'||PCT_INCREASE||'</PCT_INCREASE>'
24 ||'<FREELISTS>'||FREELISTS||'</FREELISTS>'
25 ||'<FREELIST_GROUPS>'||FREELIST_GROUPS||'</FREELIST_GROUPS>'
26 ||'<LOGGING>'||LOGGING||'</LOGGING>'
27 ||'<BACKED_UP>'||BACKED_UP||'</BACKED_UP>'
28 ||'<NUM_ROWS>'||NUM_ROWS||'</NUM_ROWS>'
29 ||'<BLOCKS>'||BLOCKS||'</BLOCKS>'
30 bulk collect into vXMLRows
31 from all_tables;
32 dbms_lob.createTemporary(vXML, true, dbms_lob.call);
33 for i in 1..nvl(vXMLRows.Last,0) loop
34 vRow := vXMLRows(i)||NL;
35 dbms_lob.writeappend(vXML, length(vRow), vRow);
36 end loop;
37 vTime := (dbms_utility.get_time-vTime)/100;
38 dbms_output.put_line('Bulk Collect: '||vTime||' secs, ' || dbms_lob.getlength(vXML) || ' bytes');
39 dbms_lob.freeTemporary(vXML);
40 end;
41 /
Bulk Collect: 1.46 secs, 462291 bytes

PL/SQL procedure successfully completed.


(hey, maybe generic code isn't so bad after all). The big difference here is that in 9i, the XML layer is written natively in C under the covers. That can lend a huge performance boost.

2) You need to use a DBMS_SQL cursor with that. DBMS_SQL has the ability to describe a dbms_sql cursor. A ref cursor is not describable in PLSQL and there is no method to dynamic fetch from it with different numbers of columns (eg: fetch cursor_name into variable_list is well known at COMPILE time - variable_list is an explicit list).

That and you cannot bulk collect from a dynamically opened ref cursor in 8i.

So, you would need to use dbms_sql.




Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can we use the C version?

Jon Waldron, July 09, 2002 - 5:59 pm UTC

Tom, thanks for your prompt response. Is there some way we can link in the 9i C library used by XMLGEN?

Tom Kyte
July 10, 2002 - 7:19 am UTC

only by using a DBLINK from 8i to 9i ;)

It is part of the kernel, you either are using 9i or you are not.


DBMS_XMLGEN parsing speed and bind variables

Jack Wells, August 23, 2005 - 12:28 pm UTC

Tom,

With regards the DBMS_XMLGEN being compiled in C and integrated directly into the database engine, it is indeed much faster! In my SQL*Plus code below (connect as SCOTT/TIGER), I think I have made it as efficient as possible, but the REF CURSOR's select is still parsed everytime this procedure is run, correct? To tweak the performance even more, I have a few questions:

1) Is there a way to make DBMS_XMLGEN use an already parsed, static SQL statement rather than it having to be parsed everytime? In my case, the SQL is already known at design time and will not be dynamically determined at runtime. I just want to spit out XML from the same table with the same WHERE clause, just different bind values.

2) Am I opening the REF CURSOR and using the bind variable properly?

3) (Misc) Is it necessary to use the "DBMS_LOB.freetemporary (lc_xml);" or will PL/SQL automatically free up resources used by that CLOB variable?

Thanks!
Jack

--------- beg code --------------------------
SET SERVEROUTPUT ON SIZE 1000000
PROMPT
DECLARE
PROCEDURE get_usertables_xml (
fv_like_string IN VARCHAR2 DEFAULT '%'
)
IS
lr_refcur sys_refcursor;
lo_ctx DBMS_XMLGEN.ctxhandle;
lc_xml CLOB;
ln_pos PLS_INTEGER := 1;
ln_offset PLS_INTEGER := 1;
ln_count PLS_INTEGER := 0;
BEGIN
--
-- Create a Ref Cursor that uses bind variables
--
OPEN lr_refcur
FOR 'SELECT table_name tn, tablespace_name tsn FROM user_tables WHERE table_name LIKE :x'
USING UPPER (fv_like_string);
--
-- Create an XMLGEN context
--
lo_ctx := DBMS_XMLGEN.newcontext (lr_refcur);
--
-- Run the cursor and return rows in XML format
--
lc_xml := DBMS_XMLGEN.getxml (lo_ctx);
--
-- Print each line returned.
--
IF DBMS_XMLGEN.getnumrowsprocessed (lo_ctx) > 0 THEN
LOOP
ln_pos := DBMS_LOB.INSTR (lc_xml, CHR (10), ln_offset);
EXIT WHEN ln_pos = 0;
DBMS_OUTPUT.put_line (DBMS_LOB.SUBSTR (lc_xml, ln_pos - ln_offset, ln_offset));
ln_offset := ln_pos + 1;
ln_count := ln_count + 1;
END LOOP;
DBMS_LOB.freetemporary (lc_xml);
END IF;
DBMS_OUTPUT.put_line ('Rows processed....: ' || DBMS_XMLGEN.getnumrowsprocessed (lo_ctx));
DBMS_OUTPUT.put_line ('XML output lines..: ' || ln_count);
--
-- Free up resources.
--
DBMS_XMLGEN.closecontext (lo_ctx);
END get_usertables_xml;
BEGIN
get_usertables_xml ('e%');
get_usertables_xml ('EMP');
get_usertables_xml;
END;
/
--------- end code --------------------------


Tom Kyte
August 24, 2005 - 8:37 am UTC

1) all sql has to be parsed at least ONCE in the session regardless of whether it is static or not, that would not change that fact.

2) yes.

3) best to free the lob yourself. If they give you a lob and it is temporary, they cannot tell when you are "done" with it entirely. it would tend to stick around for the session.

re: DBMS_LOB.createtemporary

Jack Wells, November 15, 2005 - 12:09 pm UTC

Tom,

Further to the last DBMS_LOB.createtemporary question, in the following code, is the explicit creation of the temporary LOB at the beginning still best practice, even though I cannot release it at the end (since I am RETURNing it)?

FUNCTION get_clob (
fv_code IN VARCHAR2
)
RETURN CLOB
IS
lo_clob CLOB;
BEGIN
DBMS_LOB.createtemporary (lob_loc => lo_clob, CACHE => TRUE, dur => DBMS_LOB.CALL);
BEGIN
SELECT l.text_value
INTO lo_clob
FROM ngs_lobs l
WHERE l.lob_code = fv_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lo_clob := NULL;
END;
RETURN lo_clob;
END get_clob;

Thanks,
Jack

Tom Kyte
November 15, 2005 - 12:20 pm UTC

if l.text_value is already a clob - you actually have a "leak". you have a temporary lob locator and a permanent one - the temporary one you've just lost the handle to it.

Leaking LOBs Confirmation

Jack Wells, November 15, 2005 - 12:57 pm UTC

Ok, this is starting to make a little more sense... yes, "l.text_value" is a CLOB column in the ngs_lobs table. So, for clarification:

1) If I remove the DBMS_LOB.createtemporary line, the routine will be fine (i.e. efficient), even though I'm assigning NULL to the local CLOB variable in the EXCEPTION?

2) Would the "dur => DBMS_LOB.CALL" argument to the DBMS_LOB.createtemporary line have covered me anyway since the temporary LOB locator only exists "during the call" to this function?

Thanks,
Jack


Tom Kyte
November 15, 2005 - 2:38 pm UTC

1) yes.

2) I not 100% sure that the duration is totally implemented...


... 1 of 2 predefined duration values (SESSION or CALL) which specifies a hint as to whether the temporary LOB is cleaned up at the end of the session or call. ....

I would not rely on that to clean up, no.

performance issue using dbms_xmlgen.getxml

Rohit Bansal, February 22, 2006 - 4:59 pm UTC

Hi Tom,
You're great!
I am having a performance issue (I guess).
If I run a query on sqlplus it is taking 16 secs but if I generate it in XML format it is taking more than 75 secs.
Do you think it is happening because converting into XML?
It is just returning 2000 rows with 20 columns
Your help is greatly appreciated.
Thanks,
Rohit

Here is the query.

'select '||
'/*+ driving_site(o) ordered index(o ord_pk$idord) index(cs pk_cust_serviceS) */ '||
'rownum, '||
'cs.ch_account_number "account", '||
'o.ship_name "name", '||
'o.id_order "order_num", '||
'qwe.create_date "create_date", '||
'qwe.last_update "last_update", '||
'qwe.description "description", '||
'qwe.issue "issue", '||
'nvl(o.sro_date,o.wo_date) "install_date", '||
'qwe.job_id "job_id", '||
'qwe.queue_id "queue_id" , '||
'qwe.queue_name, '||
'qwe.job_client_name "job_client_name", '||
'qwe.prio_description, '||
'qwe.ACQUIRED_BY , '||
'qwe.COMPLETED , '||
'qwe.COMPLETED_DATE , '||
'qwe.TERMINATED , '||
'qwe.TERMINATED_DATE , '||
'qwe.REFERENCE_ID, '||
'qwe.REFERENCE_DATE, '||
'qwe.ERROR_DESC, '||
'qwe.ch_login, '||
'to_char(nvl(o.sro_date,o.wo_date),''j'')||'' ''||GET_QUOTA_SLOT_END_TIME(cs.id_cd_corp,cs.ch_cd_house,cs.ch_cd_cust,''TIME'') "install_date_sort", '||
'GET_QUOTA_SLOT_END_TIME(cs.id_cd_corp,cs.ch_cd_house,cs.ch_cd_cust,''DESC'') "time_slot_desc", '||
'qwe.Buffered "Buffered", '||
'lec.name||'' ''||lec_sla.state "LEC_name", '||
'lec.name||'' ''||cs.ch_account_number||'' ''||lec_sla.state "LEC_sort" '||
'from ( '||
'select '||
'rownum, '||
'j.created_date create_date, '||
'j.last_modified_date last_update, '||
'j.description description, '||
'job_long_des issue, '||
'j.id_job job_id, '||
'j.id_job_queue queue_id, '||
'jq.name queue_name, '||
'client.name job_client_name, '||
'prio.description prio_description , '||
'acquired_by, '||
'completed, '||
'completed_date, '||
'terminated, '||
'terminated_date, '||
'reference_id, '||
'reference_date , '||
'error_desc , '||
'ch_login , '||
'decode(j.description,''NOT_DONE_BUFFERED'',''B'', NULL) Buffered '||
'from '||
'jobs j, '||
'job_clients client, '||
'job_priority_types prio, '||
'job_queues jq, '||
'job_except_description je, '||
'crc_details cd '||
'where '||
'j.id_job_client = client.id_job_client and '||
'j.id_job_priority_type = prio.id_job_priority_type and '||
'j.id_job_queue =jq.id_job_queue and '||
'completed is null and '||
'terminated is null and '||
'je.job_short_desc(+) =j.description and '||
'j.acquired_by =cd.id_crc(+) and '||
'upper(jq.name)= ''' || lv_queue_name || '''' ||
') qwe, '||
'orders o, '||
'lnp_details lnp, '||
'cust_services cs, '||
'lec, '||
'lec_sla '||
'where qwe.reference_id = o.id_order '||
'and o.id_order = lnp.id_order (+) '||
'and o.id_svc_rec = cs.id_service_rec '||
'and lec.id_lec (+) = lec_sla.id_lec '||
'and lec_sla.id_lec_sla (+) = lnp.id_lec_sla ' ;

and code that is generating the XML is

PROCEDURE get_job_queues(pi_queue_type IN VARCHAR2,
pi_queue_name IN VARCHAR2,
po_xml OUT CLOB,
po_status OUT NUMBER,
po_error_message OUT VARCHAR2)
IS
querycontext dbms_xmlgen.ctxhandle;
v_temp varchar2(4000);
BEGIN

get_job_queues_sql(pi_queue_type, pi_queue_name, v_temp, po_status, po_error_message);
IF po_status = 0 THEN
dbms_output.put_line('ERROR: po_status='||po_status||', '||po_error_message);
RETURN;
END IF;

querycontext := dbms_xmlgen.newcontext(v_temp);
dbms_xmlgen.setrowtag(querycontext, 'queue_detail');
dbms_xmlgen.setrowsettag(querycontext, 'queue');
po_xml := dbms_xmlgen.getxml(querycontext);
dbms_xmlgen.closecontext(querycontext);
po_status := 1;
po_error_message := 'Successfully completed';

EXCEPTION
WHEN OTHERS THEN
--return error message
po_status := 0;
po_error_message := sqlerrm;
dbms_output.put_line(substr(po_error_message,1,255));
END get_job_queues;


Tom Kyte
February 22, 2006 - 5:35 pm UTC

sql trace it and see that the "sqlplus plan" and the "plsql plan" were the same, that'd be my first place to start.

Need more info

Rohit bansal, February 23, 2006 - 12:40 pm UTC

what is plsql plan never heard of it.
How can I see it for this query.

Thanks.

Tom Kyte
February 23, 2006 - 7:03 pm UTC

the plan arrived at when you run it in plsql vs the plan arrived at when you run it in sqlplus as straight sql.


alter session set sql_trace=true;
REM this will get the plsql plan:
exec procedure_with_query
REM this will get the "sqlplus" plan:
select .....
exit



Rohit, February 27, 2006 - 9:50 am UTC

Query is using the hints provided but whenever I tring to execute it is not using hints? Is it because DBMS_XMLGEN is executed locally? I have to use hint here Query is taking >3 without hints and with hints <15 secs.

Your help is greatly appreciated.

Thanks,
Rohit

Tom Kyte
February 27, 2006 - 10:59 am UTC

er?

...
Query is using the hints provided but whenever I tring to execute it is not
using hints?

.....

how can the query be both using and not using something?

Rohit, February 27, 2006 - 5:04 pm UTC

Sorry...
Query is using the hints provided but whenever I try to execute with DBMS_XMLGEN it is NOT using hints. Is it because DBMS_XMLGEN is executed locally? Moreover I have to use hint here since query is taking >3 Without hints and With hints <15 secs.

Thanks,
Rohit



Tom Kyte
February 27, 2006 - 5:08 pm UTC

trace it, show us the real query from the trace.

Rohit, March 02, 2006 - 2:40 pm UTC

SELECT --+ driving_site(o) ordered index(o ord_pk$idord) index(cs pk_cust_services)
xmlconcat(
xmlelement("ROWNUM",ROWNUM),
xmlelement("account",cs.ch_account_number) ,
xmlelement( "name",o.ship_name),
xmlelement("order_num",o.id_order ),
xmlelement("create_date",qwe.create_date) ,
xmlelement("last_update",qwe.last_update) ,
xmlelement("description",qwe.description) ,
xmlelement("issue",qwe.issue),
xmlelement( "install_date",NVL(o.sro_date, o.wo_date)),
xmlelement("job_id",qwe.job_id) ,
xmlelement("queue_id",qwe.queue_id),
xmlelement("queue_name",qwe.queue_name),
xmlelement("job_client_name",qwe.job_client_name),
xmlelement("prio_description",qwe.prio_description),
xmlelement("ACQUIRED_BY",qwe.ACQUIRED_BY),
xmlelement("COMPLETED",qwe.COMPLETED),
xmlelement("COMPLETED_DATE",qwe.COMPLETED_DATE),
xmlelement("TERMINATED",qwe.TERMINATED),
xmlelement("TERMINATED_DATE",qwe.TERMINATED_DATE),
xmlelement("REFERENCE_ID",qwe.REFERENCE_ID),
xmlelement("REFERENCE_DATE",qwe.REFERENCE_DATE),
xmlelement("ERROR_DESC",qwe.ERROR_DESC),
xmlelement("ch_login",qwe.ch_login),
xmlelement("install_date_sort",TO_CHAR(NVL(o.sro_date, o.wo_date), 'j')||' '||GET_QUOTA_SLOT_END_TIME(cs.id_cd_corp,cs.ch_cd_house, cs.ch_cd_cust, 'TIME')) ,
xmlelement("time_slot_desc",GET_QUOTA_SLOT_END_TIME(cs.id_cd_corp, cs.ch_cd_house, cs.ch_cd_cust, 'DESC')) ,
xmlelement("Buffered",qwe.Buffered) ,
xmlelement("LEC_name",lec.name||' '||lec_sla.state) ,
xmlelement("LEC_sort",lec.name||' '||cs.ch_account_number||' '||lec_sla.state)
).getclobval()
FROM (
SELECT ROWNUM,
j.created_date create_date,
j.last_modified_date last_update,
j.description description,
job_long_des issue,
j.id_job job_id,
j.id_job_queue queue_id,
jq.name queue_name,
client.name job_client_name,
prio.description prio_description,
acquired_by,
completed,
completed_date,
terminated,
terminated_date,
reference_id,
reference_date,
error_desc,
ch_login,
DECODE(j.description, 'NOT_DONE_BUFFERED', 'B', NULL) Buffered
FROM jobs j, job_clients client, job_priority_types prio, job_queues jq,
job_except_description je, crc_details cd
WHERE j.id_job_client = client.id_job_client
AND j.id_job_priority_type = prio.id_job_priority_type
AND j.id_job_queue = jq.id_job_queue
AND completed IS NULL
AND terminated IS NULL
AND je.job_short_desc(+) = j.description
AND j.acquired_by = cd.id_crc(+)
AND UPPER(jq.name) = 'NOT DONE') qwe,
orders o,
lnp_details lnp,
cust_services cs,
lec,
lec_sla
WHERE qwe.reference_id = o.id_order
AND o.id_order = lnp.id_order (+)
AND o.id_svc_rec = cs.id_service_rec
AND lec.id_lec (+) = lec_sla.id_lec
AND lec_sla.id_lec_sla (+) = lnp.id_lec_sla
--and rownum<2
/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3042 | 4494K| | 13793 |
| 1 | COUNT | | | | | |
|* 2 | HASH JOIN OUTER | | 3042 | 4494K| 4496K| 13793 |
|* 3 | HASH JOIN OUTER | | 3042 | 4450K| 4480K| 13734 |
| 4 | NESTED LOOPS | | 3042 | 4432K| | 13675 |
|* 5 | HASH JOIN OUTER | | 3042 | 4295K| 4256K| 7591 |
| 6 | NESTED LOOPS | | 3042 | 4218K| | 6632 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | VIEW | | 3042 | 3933K| | 548 |
| 8 | COUNT | | | | | |
|* 9 | HASH JOIN OUTER | | 3042 | 528K| | 548 |
|* 10 | HASH JOIN | | 3042 | 362K| | 543 |
| 11 | TABLE ACCESS FULL | JOB_PRIORITY_TYPES | 3 | 21 | | 2 |
|* 12 | HASH JOIN OUTER | | 3042 | 341K| | 540 |
|* 13 | HASH JOIN | | 3042 | 285K| | 523 |
| 14 | TABLE ACCESS FULL | JOB_CLIENTS | 2 | 20 | | 2 |
|* 15 | HASH JOIN | | 3042 | 255K| | 520 |
|* 16 | TABLE ACCESS FULL| JOB_QUEUES | 1 | 14 | | 2 |
|* 17 | TABLE ACCESS FULL| JOBS | 260K| 17M| | 513 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 18 | REMOTE | | 10177 | 188K| | 13 |
| 19 | TABLE ACCESS FULL | JOB_EXCEPT_DESCRIPTION | 66 | 3696 | | 2 |
| 20 | REMOTE | | 1 | 96 | | 2 |
| 21 | REMOTE | | 409K| 10M| | 715 |
| 22 | REMOTE | | 1 | 46 | | 2 |
| 23 | TABLE ACCESS FULL | LEC_SLA | 34 | 204 | | 2 |
| 24 | TABLE ACCESS FULL | LEC | 18 | 270 | | 2 |
--------------------------------------------------------------------------------------------------


Query without XMLelement
select --+ driving_site(o) ordered index(o ord_pk$idord) index(cs pk_cust_serviceS)
rownum,
cs.ch_account_number "account",
o.ship_name "name",
o.id_order "order_num",
qwe.create_date "create_date",
qwe.last_update "last_update",
qwe.description "description",
qwe.issue "issue",
nvl(o.sro_date,o.wo_date) "install_date",
qwe.job_id "job_id",
qwe.queue_id "queue_id" ,
qwe.queue_name,
qwe.job_client_name "job_client_name",
qwe.prio_description,
qwe.ACQUIRED_BY ,
qwe.COMPLETED ,
qwe.COMPLETED_DATE ,
qwe.TERMINATED ,
qwe.TERMINATED_DATE ,
qwe.REFERENCE_ID ,
qwe.REFERENCE_DATE ,
qwe.ERROR_DESC ,
qwe.ch_login ,
to_char(nvl(o.sro_date,o.wo_date),'j')||GET_QUOTA_SLOT_END_TIME(cs.id_cd_corp,cs.ch_cd_house,cs.ch_cd_cust,'TIME') "install_date_sort",
GET_QUOTA_SLOT_END_TIME(cs.id_cd_corp,cs.ch_cd_house,cs.ch_cd_cust,'DESC') "time_slot_desc",
decode(qwe.description, 'NOT_DONE_BUFFERED', 'B', NULL) "Buffered",
lec.name || lec_sla.state "LEC_name",
lec.name||cs.ch_account_number|| lec_sla.state "LEC_sort"
from (
select
rownum,
j.created_date create_date,
j.last_modified_date last_update,
j.description description,
job_long_des issue,
j.id_job job_id,
j.id_job_queue queue_id ,
jq.name queue_name ,
client.name job_client_name,
prio.description prio_description ,
acquired_by ,
completed ,
completed_date ,
terminated ,
terminated_date ,
reference_id ,
reference_date ,
error_desc ,
ch_login ,
decode(j.description,'NOT_DONE_BUFFERED','B', NULL) "Buffered"
from
jobs j,
job_clients client,
job_priority_types prio,
job_queues jq,
job_except_description je,
crc_details cd
where
j.id_job_client = client.id_job_client and
j.id_job_priority_type = prio.id_job_priority_type and
j.id_job_queue =jq.id_job_queue and
completed is null and
terminated is null and
je.job_short_desc(+) =j.description and
j.acquired_by =cd.id_crc(+) and
upper(jq.name) ='NOT DONE'
) qwe,
orders o,
lnp_details lnp,
cust_services cs,
lec,
lec_sla
where qwe.reference_id = o.id_order
and o.id_order = lnp.id_order (+)
and o.id_svc_rec = cs.id_service_rec
and lec.id_lec (+) = lec_sla.id_lec
and lec_sla.id_lec_sla (+) = lnp.id_lec_sla
/

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 1472K| 2017M| | 3039K|
| 1 | COUNT | | | | | |
|* 2 | HASH JOIN OUTER | | 1472K| 2017M| 2003M| 3039K|
|* 3 | HASH JOIN OUTER | | 1472K| 1986M| 1962M| 3007K|
| 4 | NESTED LOOPS | | 1472K| 1945M| | 2976K|
|* 5 | HASH JOIN OUTER | | 1472K| 1905M| 1912M| 31989 |
| 6 | NESTED LOOPS | | 1472K| 1895M| | 200 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | VIEW | | 100 | 129K| | 5 |
| 8 | REMOTE | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| ORDERS | 14722 | 402K| | 2 |
|* 10 | INDEX UNIQUE SCAN | ORD_PK$IDORD | 1 | | | 1 |
| 11 | TABLE ACCESS FULL | LNP_DETAILS | 409K| 2796K| | 715 |
| 12 | TABLE ACCESS BY INDEX ROWID | CUST_SERVICES | 1 | 29 | | 2 |
|* 13 | INDEX UNIQUE SCAN | PK_CUST_SERVICES | 1 | | | 1 |
| 14 | REMOTE | | 34 | 986 | | 2 |
| 15 | REMOTE | | 18 | 396 | | 2 |
-----------------------------------------------------------------------------------------------


Tom Kyte
March 03, 2006 - 7:39 am UTC

ahh, xmlelement is likely preventing the driving site.

try

a) using a view (put the query at the remote site, select from view)
b) using an inline viwe without xmlelement - but with the driving site

Rohit, March 20, 2006 - 12:55 pm UTC

Thanks Tom. Another thing I notices that if I use XMLGEN performance is still same but if I use XMLELEMENT then there is performance improvement. Can you shed some light on it?

Thanks

XMLGEN or XMLELEMENT

V.Hariharaputhran, May 24, 2010 - 1:22 pm UTC

Mr.Oracle,

Which one of these will have to used for better performance XMLGEN or XMLELEMENT. Can you thrown some light on this.

Regards
V.Hari
Tom Kyte
May 24, 2010 - 1:30 pm UTC

frequent readers - infrequent ones even - know what I would do to evaluate that...

What approach do you think I would take? Can you give it a go and let us know what you turn up?

(that is: benchmark it, like I show you over and over...)

XMLGEN OR XMLELEMENT

V.Hariharaputhran, May 24, 2010 - 1:42 pm UTC

Mr.Oracle

Appreciate your spelindid service to oracle community,

YES do agree with you,Iam not able to trace produce trace at the moment, will come to you only with bench mark results. But What i would like to know is, Are there any difference in the methodology(Like Parsing etc) used by XMLGEN and XMLELEMENT to generate the XML Doc's which would boost the performance.

THANKS FOR YOUR TIME.

Regards
V.Hari
Tom Kyte
May 24, 2010 - 2:25 pm UTC

I actually do not use XML often enough to be able to make an informed answer... sorry.

otn.oracle.com -> discussion forums -> XML

there is a good forum on otn.oracle.com however, you might post there.

XMLGEN or XMLELEMENT

V.Hariharaputhran, May 24, 2010 - 2:41 pm UTC

Thanks for your time and response Mr.Oracle


Regards
V.Hari
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here