I think you could do many performant things without any involvement of software. For example you could do AVX on RAM. The CPU would recognize PID RAM and offload AVX instructions to the module.
Then, by simply asking for a special memory address you could have access to registers/regions within PID RAM that serve as a result region.
Let's say you would need to run a sum over megabytes of data like for accounting. You could just ask RAM to do it and load just the result. The bandwidth would could be 8x higher and software could stay the same.
Doing scalar operations, frequent dereferencing and similar would not get much peformance benefit in many cases, as loading and accessing CPU cache is often much faster. But simple vector operations over large data could be massive.
Having accelerators on RAM like for jpeg compression, audio decoding or mass data operations could be beneficial but you would need to be careful with heat dissipation.
Personally I'm a big fan of the "in-ram accelerator" idea especially for server space. Doing fuzzy search in RAM could be massive performance improvement.