Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Xml query in Oracle: stored proc (apparently) killing the Oracle server

Re: Xml query in Oracle: stored proc (apparently) killing the Oracle server

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 22 Feb 2007 06:28:07 -0800
Message-ID: <1172154487.001870.185700@a75g2000cwd.googlegroups.com>


On Feb 22, 9:12 am, vrit..._at_gmail.com wrote:
> Hello everyone,
>
> We are using Oracle as a database, and have started to develop a
> website in .net designed to present data leveraged from Oracle to
> users.
>
> At one point, we have to get a bunch of indicators regarding the
> current status of a user advancement. We decided, since that data may
> be used on many platforms (user interface, website, project leader) to
> expose that data as xml coming from a webservice.
> So far so good. We wrote the first draft of a stored proc in Oracle
> that would bring back the data as a hierarchical xml, neatly organized
> in relevant sections. Here is the first draft of the stored proc. We
> bring back the result in a CLOB, as you can see:
>
> PROCEDURE P_XML_INDICATEUR (
> ARG_NUMSEQ_AYD_N IN AYD.NUMSEQ_AYD_N%TYPE,
> XML_CLOB OUT CLOB
> ) IS
> BEGIN
>
> select TO_CLOB(XMLELEMENT("INDICATEURS",
> XMLELEMENT( "CLIENTS",
> (select SYS_XMLAGG(
>
> XMLELEMENT( "CLIENT",XMLAttributes('GLOBAL'
> "CODE"),
> XMLELEMENT( "PHONOGRAMMES",
> (select SYS_XMLAGG(
>
> XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
> )
>
> )
> FROM INDICATEUR_SUIVI IDS2,
> INDICATEUR_META IDM2
> WHERE NUMSEQ_AYD_N is null
> AND
> IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
> AND IDM2.NOM_METADONNEE_C
> like 'PHO%'
> )
> ),
> XMLELEMENT( "VENTES",
> (select
> SYS_XMLAGG(
>
> XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
> )
>
> )
> FROM INDICATEUR_SUIVI IDS2,
> INDICATEUR_META IDM2
> WHERE IDS2.NUMSEQ_AYD_N =
> IDS1.NUMSEQ_AYD_N
> AND
> IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
> AND IDM2.NOM_METADONNEE_C
> like 'VTE%'
> )
> ),
> XMLELEMENT( "REPARTITIONS",
> (select
> SYS_XMLAGG(
>
> XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
> )
>
> )
> FROM INDICATEUR_SUIVI IDS2,
> INDICATEUR_META IDM2
> WHERE IDS2.NUMSEQ_AYD_N =
> IDS1.NUMSEQ_AYD_N
> AND
> IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
> AND IDM2.NOM_METADONNEE_C
> like 'VTE%'
> )
> )
> )
> )
> FROM INDICATEUR_SUIVI IDS1
> WHERE IDS1.NUMSEQ_AYD_N is null
> ),
> (select SYS_XMLAGG(
>
> XMLELEMENT( "CLIENT",XMLAttributes(NUMSEQ_AYD_N
> "CODE"),
> XMLELEMENT( "PHONOGRAMMES",
> (select SYS_XMLAGG(
>
> XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
> )
>
> )
> FROM INDICATEUR_SUIVI IDS2,
> INDICATEUR_META IDM2
> WHERE IDS2.NUMSEQ_AYD_N =
> IDS1.NUMSEQ_AYD_N
> AND
> IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
> AND IDM2.NOM_METADONNEE_C
> like 'PHO%'
> ) ),
> XMLELEMENT( "VENTES",
> (select
> SYS_XMLAGG(
>
> XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
> )
>
> )
> FROM INDICATEUR_SUIVI IDS2,
> INDICATEUR_META IDM2
> WHERE IDS2.NUMSEQ_AYD_N =
> IDS1.NUMSEQ_AYD_N
> AND
> IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
> AND IDM2.NOM_METADONNEE_C
> like 'VTE%'
> )
> ),
> XMLELEMENT( "REPARTITIONS",
> (select
> SYS_XMLAGG(
>
> XMLELEMENT("INDICATEUR",XMLAttributes(NOM_METADONNEE_C),VALEUR_METADONNEE_C
> )
>
> )
> FROM INDICATEUR_SUIVI IDS2,
> INDICATEUR_META IDM2
> WHERE IDS2.NUMSEQ_AYD_N =
> IDS1.NUMSEQ_AYD_N
> AND
> IDS2.ID_INDICATEUR_SUIVI_N = IDM2.ID_INDICATEUR_SUIVI_N
> AND IDM2.NOM_METADONNEE_C
> like 'VTE%'
> )
> )
> )
> )
> FROM INDICATEUR_SUIVI IDS1
> WHERE IDS1.NUMSEQ_AYD_N =
> ARG_NUMSEQ_AYD_N
> )
> )
> ) INTO XML_CLOB
> FROM DUAL;
> Exception
> --
> WHEN OTHERS THEN
> RAISE_APPLICATION_ERROR (-20002, 'Erreur dans la procedure
> P_XML_INDICATEUR' || TO_CHAR(SQLCODE) || ' ' || SQLERRM);
> --
> END P_XML_INDICATEUR;
>
> This function worked correctly the first time around. The xml came
> back to our webservice, and we consumed it without any problem.
> However, one of us noticed the presence of a copy-paste bug in the
> procedure and changed the second occurence of AND
> IDM2.NOM_METADONNEE_C like 'VTE%' into AND IDM2.NOM_METADONNEE_C like
> 'REP%'
> What seemed like a harmless change at the time turned out to never
> return at all. We first realized the webservice timed out, then
> launched the proc in toad to see where the culprit was hiding. It
> turned out that the process was running on the oracle server,
> unkillable, doing something (actually, to be precise, the procedure
> wasn't even fetching the results yet, but was being analysed/run) ,
> and never returned while we were waiting to see what was happenning.
> The memory consumed by Oracle was slowly growing, the cpu was less and
> less responsive, until eventually the machine was rebooted manually
> since nothing was running on it anymore.
>
> We tried replicating the problem by making sure the data was correct
> (i suggested perhaps an empty recordset in one of the inner select was
> causing the block) but encountered the same scenario so far. I don't
> have access yet to the error logs, and will go through them as soon as
> i can, but i wanted if this problem was already encountered by someone
> else.
>
> For information, we are using Oracle 9.2, and both tables mentioned in
> the queries have a primary key, a not null constraint, and
> INDICATEUR_META has a foreign key reference to the primary key of
> INDICATEUR_SUIVI
>
> Thank you for reading this lenghty text. Thank you even more if you
> are a light at the end of the tunnel :)
>
> Samy

Hi Samy,

I would start by tracing the session running the procedure to get an explain plan for the query.

Also, how many rows (and what percentage) have the NOM_METADONNEE_C starting with REP?

HTH, Steve Received on Thu Feb 22 2007 - 08:28:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US