Archives of the TeradataForum

Message Posted: Wed, 18 Sep 2013 @ 15:32:23 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Global temporary space usage

From:		Dieter Noeth

Daniel Rewalt wrote:

You may be able to find that information in DBQL. This query should provide the "bones":

          > select querytext, min(starttime)
          > from dbc.qrylog
          > where sessionid in (sel sessionid
          >       from dbc.qrylog
          >       where querytext = 'CALL MyDb.MyMasterSp ();') and statementtype
          > = 'CALL'
          > group by 1
          > order by 2 desc

Hey, that's a nice idea :-)

2 - The session could call other SPs. That would mess up the results. You may want to execute a controlled run of the "master" SP on a dev/test system and pull the results from there.

This will be no problem, when a SP is CALLed the "RequestNum" will not change for all statements submitted within this CALL, only the InternalRequestNumber increases, so this works event if multiple SPs are called:

     SELECT
         querytext,
         MIN(starttime)
     FROM dbc.qrylog
     WHERE (sessionid, RequestNum)
     IN (SEL sessionid, RequestNum
           FROM dbc.qrylog
           WHERE querytext LIKE '%CALL nestedproc%'
           AND statementtype = 'CALL')
     AND statementtype = 'CALL'
     AND RequestNum <> InternalRequestNum -- to exclude the callin SP GROUP BY 1 ORDER BY 2 DESC

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference