topic Re: Giant Increase in Load Times from Oracle DB in Administration and Deployment

Giant Increase in Load Times from Oracle DB

titan31 — Thu, 05 Apr 2018 12:57:11 GMT

Hi,

Our production system's load times have gone through the roof over the last week, and can't seem to find any explanation on how it's happened at all. For months, it's been going at 1.5 hours for loading in all the tables we need from our Oracle database but last Thursday this suddenly increased up to 9 hours, and been like that since bar Tuesday night where we got it down to 2.5 hours, but was then back to its 9 hours timescale last night.

As this is a production system, it's having a huge effect on our users so I've been trying everything I can think of to see what the issue is (is it DB related, network related, hardware related or SAS related), but it seems to be like finding a needle in a haystack.

Going through logs (and I enabled FULLSTIMER to get extra stats), the CPU time seems to be consistent from the good runs to the bad runs, but the real time is the one that increased.

Would anyone have encountered any similar issues, or know of anything worth looking into to reduce the real time of the jobs.

Thanks

Re: Giant Increase in Load Times from Oracle DB

Kurt_Bremser — Thu, 05 Apr 2018 13:01:04 GMT

Make sure that your disks on the SAS server are not the culprit (compare "local" jobs from 2 weeks ago with now).
But that just needs to be checked, my main avenue of investigation would be the network. Then the network, and after that the network.

Re: Giant Increase in Load Times from Oracle DB

titan31 — Thu, 05 Apr 2018 13:50:13 GMT

Hi,

Thanks for that. Anything in particular I should be looking at. I've been looking at the trends in the Environment Manager and can see a few things are up but don't know if they're causes or effects of any issues.

Like from a network point of view, I can see that the packets transmitted per minute has dropped significantly since like the 22nd March, but that doesn't really coincide with the days we're having issues. CPU usage has also increased since Thursday from about 10% average to 30% but that could just be as a result of the jobs taking much longer.

Anything else I'm looking at within the Environment Manager isn't really giving me any indicator that something is wrong

Re: Giant Increase in Load Times from Oracle DB

LinusH — Thu, 05 Apr 2018 13:56:08 GMT

Try to isolate the Oracle query, so you can narrow down the problem. Like running the query but not storing the result if possible.

Also, try run the same query local in Oracle. If you haven't already done so, involve Oracle DBA's in the work.

FULLSTIMER will not tell you anything what's going on outside SAS.

Re: Giant Increase in Load Times from Oracle DB

titan31 — Thu, 05 Apr 2018 15:15:45 GMT

Yup,

I've involved the Oracle DBAs we have here but they can't see anything on their side as to the cause of it. Query is a normal Select * from table and we're looking at about 14m rows and 5 columns on it. We've had a DBA run it off multiple different environments but he's not really spotting anything that's causing any slowness. I'd have assumed (can correct me with if it's a wrong assumption) that with the CPU time being so much slower than the Real Time, that the issue is on SAS or the infrastructure.

Re: Giant Increase in Load Times from Oracle DB

AhmedAl_Attar — Thu, 05 Apr 2018 15:33:29 GMT

Hi,

Typically, drastic changes like these would pin down to infrastructure changes outside the control of the software!

It's either a Network Bandwidth changes, or Disk I/O threshold changes or Worse Case Scenario - BOTH!!

If your Oracle Database didn't have version changes nor patches applied, that means there were no software changes on the Oracle Part.

IF your SAS programs and scripts didn't have any modifications applied to them, that means there were no software changes on the SAS part.

That leaves you with your Storage & Network Admins to talk to and find out if there has been any recent changes .

Hope this helps,

Ahmed

Re: Giant Increase in Load Times from Oracle DB

ccaulkins9 — Thu, 05 Apr 2018 18:38:16 GMT

Always the network!
I like that mantra, @AHMED_alAttar, eh?

Re: Giant Increase in Load Times from Oracle DB

ccaulkins9 — Thu, 05 Apr 2018 18:41:10 GMT

Could be there are too many users at those peak times, @AhmedAl_Attar.
I now know this is neither here nor there -

v/r and with Regards,

Chad Caulkins

ex-dMiner, always wSeeker

Re: Giant Increase in Load Times from Oracle DB

ccaulkins912 — Tue, 10 Apr 2018 13:08:27 GMT

I meant @AhmedAl_Attar

Re: Giant Increase in Load Times from Oracle DB

titan31 — Tue, 10 Apr 2018 13:11:27 GMT

Doesn't seem to be Network or IO related anyway from what we've seen. We thought it might have been network but tried a few things there and after every time we reboot the server, it seems fine, but each successive load gets a lot slower then.

Once we reboot and do a load, it takes it's normal time, next load takes twice as long, and then 3rd load takes 3 times as long and stays like that.

Anything that might be getting filled up somewhere that could explain this

Re: Giant Increase in Load Times from Oracle DB

AhmedAl_Attar — Tue, 10 Apr 2018 13:39:47 GMT

Hi,

Not knowing how your Load Process is launched nor what it's involves, but have you checked if there were any Processes/Temporary Files left over after your load is "done"?

Rebooting a server should never be a go-to solution!! What kind of a Production System you are running?

You'll need to have your ETL developers re-examine their routines and steps to ensure they are not implementing techniques causing such behavior.

Ahmed

Re: Giant Increase in Load Times from Oracle DB

titan31 — Tue, 10 Apr 2018 13:42:43 GMT

Would there be a good way to check if there is stuff left over after a run?

Re: Giant Increase in Load Times from Oracle DB

AhmedAl_Attar — Tue, 10 Apr 2018 14:03:13 GMT

What's your platform, Linux/Windows?

Re: Giant Increase in Load Times from Oracle DB

titan31 — Tue, 10 Apr 2018 14:08:39 GMT

I'm on Windows, SAS 9.4

Re: Giant Increase in Load Times from Oracle DB

AhmedAl_Attar — Tue, 10 Apr 2018 14:26:06 GMT

Checkout the Cleanwork utility documentation, and this link for details. Not sure if this SAS Tech Support Note helps in any way, but it might shed some light.

And the use the Windows Task Manager to search for sas processes

With all that said, You still need to look into

- How your load Process launched - Batch/Interactive

- How your ETL developers developed the processes - Full re-load/Incremental update?

You'll need to understand the full picture before taking actions that could make things worse!

Ahmed

Re: Giant Increase in Load Times from Oracle DB

titan31 — Tue, 10 Apr 2018 14:33:39 GMT

Ya, I've got the Cleanwork utility scripted to run and clear out anything in the workspace drive.

Will look into the processes part, it might make sense as after a full reboot, it goes fine.

For the loading, it's a batch load as it's scheduled to run at 1am each night, but even when we're trying it manually, it runs in a similar enough time. Some of the processes are a full reload and some are SCD loads. Differing jobs have different requirements there and nothing I can see that only sort of load is slowing down, it seems to be pretty uniform that everything just ends up tripling in time.

Re: Giant Increase in Load Times from Oracle DB

AhmedAl_Attar — Tue, 10 Apr 2018 15:32:56 GMT

Here is SAS paper you may want to look into for some ideas for performance boosting

- For Debugging Query Perfromance use the following SAS options

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

There are more useful tips in the paper to look at,

Hope it helps,

Ahmed

Re: Giant Increase in Load Times from Oracle DB

titan31 — Mon, 16 Apr 2018 14:10:05 GMT

Nothing seems to have worked so far anyway, still have same issue. One of our service partners thinks that installing all the latest hotfixes will solve the issue, but I can't see anything addressed by the hotfixes that looks like it would fix it.

Re: Giant Increase in Load Times from Oracle DB

titan31 — Mon, 23 Apr 2018 09:42:03 GMT

Turns out it looks like it's McAfee and anti virus scanning that's causing it.

No idea how (yet), but just incase anyone else has this issue in the future.

Re: Giant Increase in Load Times from Oracle DB

Kurt_Bremser — Mon, 23 Apr 2018 09:50:12 GMT

Then you should consider switching to a real operating system (which does not need anti-virus software) for your SAS server. Or your complete server infrastructure as such.