New post
Question
· Apr 27, 2021

How and what to delete data after changing configuration in SYSTEM EXPLORER -> TOOLS -> SQL PERFORMANCE TOOLS -> SQL RUNTIME STATISTIC

#Performance #SQL #System Administration #Caché

Hello everyone!

Some time ago, I changed the configuration in SQL Runtime Statistic to "Turn on Stats code generation to gather stats at the Open and Close of a query". With this change, the CACHE base (cache/mgr/cache/) has grown a lot to reach 198GB.

Yesterday, I returned the configuration of SQL Runtime Statistic to the default which is "Turn off Stats code generation" and the cache base is no longer growing.

My question is?

How to delete this data that was created in the CACHE base (cache/mgr/cache/) and what data I can delete without affecting the system.

Thanks,
Fábio Louly.

Product version: Caché 2018.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2018.1.2 (Build 309_5)
Discussion (3)0
Log in or sign up to continue
Vic Sun · Apr 27, 2021

Hello Fábio,

In IRIS there is a convenient SQL Runtime Statistics page where you can purge those stats.

Using the SQL Runtime Statistics Tool

On your version, the equivalent tool only offers the option to purge cached queries, so I think that's what you'll need to do. %SYS.PTools.SQLStats.Purge will purge some of the SQL stats data, though not all.

"Purging a cached query purges any related SQL Stats data. Dropping a table or view purges any related SQL Stats data."

edit: corrected, CE 2018.1.5 does not have the purge stats option that IRIS does.

0 0
Fábio Campos · Apr 27, 2021

Hi Vic!

Thanks for answering me.

Using% SYS.PTools.SQLStats.Purge you said that it clears some data but not everything. How would I go about clearing this other data that is not cleared with the command? Because not serving there and they will serve no purpose, just consuming unnecessary space in the cache base, correct?

Using this command it only excludes as collected statistics, does it exclude any other data from the global?

Thanks again.

0 0
Vic Sun · Apr 27, 2021

Hello Fábio,

I'm just quoting the class reference I linked:

"This method is called to remove data from the %SYS.PTools.SQLStats table. It does not remove data from %SYS_PTools.SQLQuery, those rows are cleaned up when a query is compiled"

You can take a look at what's actually filling up your CACHE database to determine if this will work for you. For a complete purging, I'd go with purging cached queries, as that's the "full" solution.

0 0