possible regression - slow performance showing view content

[Maxim4711]

I think, i have an issue with possible bug - if i select fully qualified view name in the sql window (maybe either a view name in own schema) - and click on the menu point - view "...my view..." -> view - then by some views it loads extremely long time the content of view into sql window. I started plsql developer in sessionmode single and traced the session - it showed, plsql developer tries to execute twice the view - 1st time - "select * from <my_view>", 2nd time - "select <underlying query from my view>" - both times without any predicates (like rownum <1 or similar). From my perspective - this should be absolutely not necessary to obtain the source code of the view, besides that - some views are not designed at all to be queried without any predicates. As result - plsql developer freezes for long time (in cases where such query is time consuming/ineffizient).
Second issue - i think this could be a regression after sql window got support for bind variables - in the previous versions it was possible in the sql window to execute something like
"explain plan for select * from dual where dummy=:b1". Now it results in prompt for bind (whereas in this case the bind value is mostly not necessary) - and if i disable bind interpretation - it results in ORA-01036. This is very inconvienient though workarounds exists - in command window and explain plan window it works as expected.
Tested in 16.07 and 16.08 (x86-64).

Regards

Maxim

 

[Marco Kalter]

The view SQL is not executed. It is merely described to obtain the field list. To prevent this you can go to Preferences > Oracle > Options and set "Add column alias list for View DDL" to "Always" or "Never". In case of "Auto", the SQL of the view will be described.

We'll fix the explain plan issue with bind variables.

 

[Maxim4711]

Thanks for fast reply. Your suggested setting for view ddl - changes (in our setup) load of the (problematic) view content to happen instantly, again - thanks a lot!

Regards

Maxim

 

[Maxim4711]

Marco, i need to reiterate on this topic. I have attached a Frida script to plsql developer instance and traced the execution. What i see in the trace - for each of my authorized query plsql developer executes "unauthorized" version. It means, i have following sequence of steps:
[OCIStmtPrepare] #5
SQL: SELECT slow_function(empno) AS empno, ename
FROM emp
WHERE empno = 7369

[OCIStmtExecute]
Stmt #5
SQL: SELECT slow_function(empno) AS empno, ename
FROM emp
WHERE empno = 7369

Iterations: 0
Mode: 0x0
Status: SUCCESS
Elapsed: 2 ms

[OCIStmtFetch2] Stmt #5
Rows requested: 100
Status: 100
[OCIStmtPrepare] #6
SQL: SELECT * FROM emp

[OCIStmtExecute]
Stmt #6
SQL: SELECT * FROM emp

Iterations: 0
Mode: 0x0
Status: SUCCESS
Elapsed: 3 ms

Now, my original query was with particular column list and predicates - the plsqldeveloper version is without any predicate and with all columns. I have disabled all possible related to this behaviour settings

| Setting | Result |
|-------------------------------------|--------------|
| Show dictionary info in result grid | No effect |
| Automatic statistics | No effect |
| Code Assistant | No effect |
| All describe settings | No effect |
| AutoExecute queries | No effect |

Additionally - set Add column alias list for View DDL" to "Never".
So, if this behaviour (OCIStmtPrepare + OCIStmtExecute) on modified version of each query (probably to obtain metadata) - can not be disabled via user interface - i would consider it as a critical bug. Here is the reasoning:
1) use case: - consider a query with extremely long parse time which can only be parsed in reasonable time with some hints - like dynamic_sampling(0) hint - without this hint user experience will be like plsql developer freezes
2) use case; - in our environment there are some very complex views involving 20-30 objects with partitioned tables and possible database links - such views are not designed to be queried without any predicates, with predicates - result is coming pretty fast (due to partition pruning, index based access via nested loops etc.), without predicates - the execution takes ages (even though no OCIStmtFetch2 is performed).

There can be more use cases where it would be absolutely undesired. So, i need a clarification, is there any setting i can use to disable this behaviour?

Regards

Maxim

 

[Marco Kalter]

There are 3 options:

Auto: The View is parsed to find the column names, so that the View DDL includes the column alias list of necessary.
Always: The View is not parsed, and the View DDL always includes the column alias list.
Never: The View is not parsed, and the View DDL never includes the column alias list.

 

[Maxim4711]

Hi, Marco, sorry - probably i was unclear. We observe following behaviour with plsql developer:
If user supplies any query (regardless - based on views, tables, or whatever) - for user supplied version we see 3 OCI Calls in a sequence:
OCIStmtPrepare
OCIStmtExecute
OCIStmtFetch2

Immediately after these steps we see additionally (generated from plsql developer) following 2 OCI Calls on a modified version of initial query:
OCIStmtPrepare
OCIStmtExecute

The modification of the query (which i noticed) are
1) original column list is replaced with *
2) all (evtl.) comments/hints are removed
3) all (evtl.) predicates are removed.

Thus, it leads to the parsing of mentioned modified version of the query at oracle side, which is undesired behaviour in our environment.
I was not able to find any Setting which eliminates this (second) parse call. If you need some evidence (traces, reproducible test case, etc.) - i would be glad to provide it.

Regards

Maxim