.SAS7butl Extension Files

[GMS_Georges]

Hi Everyone,

I have recently encountered a significant decrease in performance when running codes in SAS Enterprise Guide. This issue started after renewing our SAS license. I am using a SAS server with an Oracle connection.

While monitoring the SAS local temp folder, I noticed that .SAS7butl files are being generated specifically during the process of joining tables. These files are taking a considerable amount of time and space. After their completion, another file with the .SAS7bdat.lck extension is created, followed by the final creation of the dataset (physical table) on the local server. Additionally, the size of the two tables remains unchanged until the physical table is finalized.

Is this a standard practice? I am familiar with .SAS7bdat.lck files, but I have never come across .SAS7butl files before.

Prior to this issue, the same query (proc SQL) would take 5 minutes to run, but now it is taking around 30 minutes.

Could someone please explain how SAS manages this process, and do you have any recommendations for resolving this issue? It's worth noting that there have been no changes made to the Oracle database or the server.

Thank you.

[Tom]

Has the size of the data changed?

Check whether the setting of the COMPRESS option has changed. If before you were using COMPRESS=YES or COMPRESS=BINARY but now you are using COMPRESS=NO then the amount of space needed to store the same data might have increased by a lot.

Also there are options that might impact how Oracle data is transferred to SAS datasets that could impact space used.  I think there is one that sets the default maximum length for character variables (or perhaps just CLOB variables) when transferred to SAS datasets.  Changes to that might make the files much larger.

[ballardw]

The files are utility files such as used for temporary storage for some operation.

I would not say that there presence is likely to have anything to do with longer run times unless the code was changed in some manner. It may be that you can see them because the code is running longer as typically these are removed after not needed by the SAS system.

I suggest setting diagnostics on the Oracle connection to get details to see if the behavior of the connection is the cause of the longer run time. That assumes that the data file(s) involved have not changed size noticeably.

[SASKiwi]

Licence renewals have no connection with SAS Server performance. SAS servers can be restarted after licence updates have been applied. Perhaps there has been a change in setting after the restart. Talk to your SAS administrator regarding your performance concerns to see if they are able to shed light on this.

[LinusH]

If these sas7butl files are big, it can potentially point to the fact that part of the SQL processing is taking place in SAS, not Oracle.

Set these options and analyse the log:

options fullstimer msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;