3 Replies Latest reply: Jan 23, 2014 3:08 PM by user730734 RSS

    Clear SQL Command Cache

    3387d036-f9f3-412c-aeea-0ef070461952

      Hi,

       

      is it possible to clear the SQL command cache which you can query with ttSQLCmdCacheInfo?

       

      I do some testing and it would be easier for me to find the queries i want to debug in an almost empty cache.

        • 1. Re: Clear SQL Command Cache
          Chrisjenkins-Oracle

          No I'm afraid not, sorry.

           

          Chris

          • 2. Re: Clear SQL Command Cache
            Slaw-Oracle

            Currently, the only way to clear the SQL command cache is by unloading and reloading the database into memory.

             

            Which release of TimesTen are you using? In TimesTen 11.2.2.5.0 and later, there is a new built-in called ttSQLCmdCacheInfo2. It returns timing information such as the timestamp when a given SQL was last executed, and how long it took to execute. These information may help you to identify the ones that you want to debug.

             

            Simon

            • 3. Re: Clear SQL Command Cache
              user730734

              Hi.

              If you are using ttIsql, you can use the "cmdcache" ttIsql command.

               

              Command> help cmdcache;

               

              Arguments in <> are required.

              Arguments in [] are optional.

               

               

              Command Usage: cmdcache [ [ by {cmdid | querytext | owner} ] <query substring>]

              Command Aliases: (none)

              Description: Displays the contents of the TimesTen SQL Command Cache.

              Optionally specify the column to search (cmdid, querytext or owner) and

              substring to filter the SQL statements that are returned.  The default field

              to search is the querytext.

              Requires an active connection: YES

              Requires autocommit turned off: NO

              Reports elapsed execution time: NO

              Works only with a TimesTen data source: YES

              Example: cmdcache; -or- cmdcache  mytable -or- cmdcache by owner SYS

              Command>

               

               

              To elaborate on the examples, if you know the statement you are looking for has "mytable" in the query text, then try running "cmdcache mytable".

              If you provide the <query substring> argument to the cmdcache command, the cmdcache command acts like a limited version of grep (without the regular expressions).

               

              Or if you know that the user that prepared the query is user SCOTT, then try running "cmdcache by owner SCOTT".

               

              -Bill