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

On Feb 22, 10:31 am, vrit..._at_gmail.com wrote:
> On 22 fév, 16:11, vrit..._at_gmail.com wrote:
>
>
>
> > > 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
>
> > Hello Steve,
> > Thanks for your prompt reply
>
> > I don't have access to the oracle server at the moment, the admin
> > seems to have disappeared in a hole somewhere; however, i may have
> > some difficulty having them relaunch the procedure today -
> > understandably since it forces them to stop and restart the server
> > each time. Can i view the explain plan for the query in toad without
> > launching it? If so, i'll try to do it asap.
>
> > I'll inform you as soon as i have the explain plan for the query
> >From what i could gather, the access plan doesn't get into any details
>
> beyond "access full from dual". We're apparently not able to go beyond
> that at the moment... Are there directions pointing out how to analyse
> this correctly (and with minimum tries, because of the impact?) out
> there on the web?
>
> Sorry for the lack of useful info, i am not a dba guy and am a bit at
> a loss seeing that the would-be ones are as clueless as i am...
>
> Samy, hounding for clues

Hi Samy,

I'm not an XMLDB expert, so I'm afraid I can't help much. I would suggest, if you can do it on the box without having an impact on everything else, you start "chunking" up the query to see where it is slow. Just take the "INTO..." clause at the bottom of the statement out, and see which piece is slow. In other words, try getting an explain plan (or at least timings) for something like below...

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%';

You can start building it back up until you get to the slow part. It may be a combination of things rather than just the change to the REP% literal value. Are statistics (used by the optimizer to build a good query execution plan) calculated on the table?

HTH, Steve Received on Thu Feb 22 2007 - 10:39:29 CST