3 Replies Latest reply on Jan 23, 2014 9:08 PM by User730734-Oracle

    Clear SQL Command Cache




      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

          No I'm afraid not, sorry.



          • 2. Re: Clear SQL Command Cache

            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 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.



            1 person found this helpful
            • 3. Re: Clear SQL Command Cache


              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




              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".