10g Sql Advisory

grant advisor to

wasted a lot of time trying to figure out why dbms_sql.submit kept claiming that table or view does not exist.

It turned out I had created the tables while logged in as my schema but as sysdba using plsql developer!

SQL > CREATE or REPLACE PROCEDURE create_tuning_task IS
2 tuning_task VARCHAR2(30);
3 sqltext CLOB;
4 BEGIN
5 sqltext := 'select cust_name,count(*)'
6 ' from customer, cust_order'
7 ' where customer.cust_no = cust_order.cust_no'
8 ' and customer.cust_no = 8'
9 ' group by cust_name';
10 tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
11 sql_text => sqltext,
12 user_name => 'SYS',
13 scope => 'COMPREHENSIVE',
14 time_limit => 30,
15 task_name => 'CUST_ORDERS',
16 description => 'Tuning effort for counting customer orders');
17 END create_tuning_task;
18 /

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'CUST_ORDERS') FROM DUAL;