I am testing a huge application with a sophisticated database middleware against Oracle database 12. Up to now, it works well with Pervasive SQL, MSSQL and Oracle database 8...11.
There are many issues about changed execution plans from Version 11 to 12. Oracle will say they all are improvements (or at least due to improvements), and I cannot really deny it. So that is not the subject.
But I encountered some SQLs with horrible execution times, especially connected to Crystal Reports. Whenever I tried to verify them, the queries were executed fastly. As far as I can see, it has to do with the user/schema that executes the SQL statement in the first time. Here are the details:
• DDL=ADMIN, DML=GUI: All data are stored within an ADMIN schema. The ADMIN user creates tables, views and so on. He grants access to and creates synonyms for real users, but he cannot change data using DML, since triggers hinder him. End-users, named "GUI" users, are allowed to use DML, but they do not have DDL privileges.
• Database reports (on paper) are usually rather complex. They present only a limited set of data (must fit on the paper sheets!), and the Oracle optimizer often optimizes against this goal. Because Crystal Reports does not generate hints, most reports are based on a view ADMIN.<xyz> (bearing the necessary hints) and are accessed through a GUI.<xyz> synonym from user GUI.
• Crystal Reports works slowly (~5 minutes) logged in as GUI. I export it, get the SQL statement from the export, log in as ADMIN, and execute the query, it works fastly (~2 seconds). Well — I probably changed some white spaces, line endings and the like during copy & paste, didn't I?
• I tried a lot, logging in as GUI and ADMIN, simplifying the query, executing, and so on. Then I got confused of a version of that that query: when ADMIN added an extra space character, it was fast, removed again, and it was slow again. Do whitespaces have an influence on the execution plan? Surely not, and if so, my view of the world would collapse.
• Later, I found out: In Version 12, it depends on the user which first executes that query. Let us nail the source of the query to the synonym GUI.<xyz>. Let us make a new version of it (coded by white spaces - smile), and if you execute it as GUI the first time, it is slow. Even if you re-login as ADMIN, the very version of that query remains slow. (That is a bug, definitely!)
Question 1: When I prefix the SQL with "EXPLAIN PLAN FOR", I seem to modify it. Since GUI has no plan_table and no privileges to explain a plan, I cannot spy on the "wrong" execution plan. I do not want to grant too much to GUI, and I fear it might modify the execution plan. Is there a trick to get the execution plan from an SQL statement found in v$sql or v$sqlarea? (Meant is: Execute the query as GUI, and explain it as ADMIN)
Question 2: Is it about privileges of GUI? What do you think, which direction shall I investigate further?