We have built a CDS view and exposed it as an Odata service. We ran the Odata URL and the runtime in the gateway is about 12 sec. To analyze further we captured the SQL trace in ST05. Below is the execution time we see. The OPEN operation in SQL trace took 9.45 sec.
I took the plan (using the option in the black box in the above figure) with the "with session variables" option. The plan below shows that execution time is only 658.01 ms. I also double-checked this with the Plan Cache and Max Execution time is 673.531 ms.
Also, this is from my S4HANA sandbox where the load on the system is very low. How can there be such a huge difference? I did refer to the below thread and could not find a clue. Can somebody throw some light on this?