Good Afternoon TOAD Team,
I've been trying for several days to get TOAD to tune a long running SQL statement. It refuses to start giving me an Oracle ORA-013039: insufficient privileges on underlying objects of the view, even when I totally remove all views.
Larry
Hi Larry,
SQL Optimizer is actually a separate product installed with Toad. I've asked that team internally. Hopefully I'll have an answer for you soon.
-John
In SQL Optimizer, we need to access some views for parsing, which is included the views from SYS.
We can rewrite the SQL because we know every words of SQL. Even a simple SQL ‘SELECT X from Y’. X could be a column, a function, or even a bind variable. To figure out this, we have to access the views such as ALL_TAB_COLUMNS or ALL_OBJECTS. It could explain why you remove all the views from SQL and it still hit the error ORA-013039.
Tony Ng
By the ways, it is a good try for a simple SQL such as "SELECT 1 FROM DUAL". It is really helpful for me to define the problem as my speculation.
Thanks,
Tony Ng
Good Morning Tony and John,
Thank you!
It does not get much simpler that then tuning SELECT 1 FROM DUAL: TOAD had no issue with tuning SELECT 1 FROM DUAL in each of the three schema in the problem
SQL (WIARPT, EGRANTS and INDIAN). I have done SQL tuning in the past, so I assumed the error was talking about my views, a more specific error report would be nice. On double check I have one more view to replace with its source SQL to eliminate all views.
I recall having gone through the steps to replace it, but it is still in the select, saying I did not complete the job.
The question I have is, are select rights on a source tables sufficient for SQL tuning, all the source tables for the view are in a different schema, EGRANTS,
than where the SQL is being run, WIARPT.
All three schema have select access to the SYS views you mentioned:
Hi Larry,
Go back to that original statement you had in your first post - can you do an explain plan on it in the editor without getting an error? I've seen that ORA-01039 with explain plans before.
-John
Good Morning John and Tony,
Thank you!
That is exactly where the error was coming from. SQL Tuning first creates the explain plan; the error message appeared creating the explain plan. The schema I run from did not have rights to the underlying tables in EGRANTS; I granted select rights to all those tables and a couple functions. Now my issue has been resolved. 
Sorry to have drug you into my education.
Larry
Hi Larry,
Not a problem, we're happy to help.
-John
Good Afternoon John and Tony,
The SQL tuning is excellent!! My query went from 63 seconds to 2.6 seconds. I have a slow VM doing the tuning so tuning has been running more than a day; but currently says running 4 hours 50 minutes when it has really been running 28 hours 50 minutes. A nit not worth fixing. Only SQL code change for that improvement, so no outside cost, like indexing. 
Thank you again!!
Larry
