|10g Advisor [message #65807]
||Thu, 30 December 2004 10:24
Registered: January 2000
I am trying to generate recommendations for indexes using Oracle's 10g advisors. |
However, I don't have access to the Enterprise Manager, so I am doing it by using the DBMS_Advisor package and SQL*Plus(Pl/Sql). I am getting recommendations, but only for materialized views and I am trying to get the advisor to recommend indexes.
The default is that it will recommend both, however I am wondering if there is a setting or parameter that I can change to force it to recommend indexes??
Below is a sample of code that is only generating materialized views. I have four tables 2 with 100,000 rows and 2 with 50,000 rows all with primary keys and foreign keys.
task_name := 'Task_mag';
task_id, task_name, 'My Advisor Task', DBMS_ADVISOR.SQLACCESS_WAREHOUSE);
dbms_advisor.set_task_parameter ('Task_mag', 'EVALUATION_ONLY', 'FALSE');
dbms_advisor.set_task_parameter ('Task_mag', 'EXECUTION_TYPE', 'FULL');
-- create the workload
dbms_advisor.create_sqlwkld(workload_name, 'MV workload' , NULL);
-- now link the two together
dbms_advisor.add_sqlwkld_ref(task_name, workload_name) ;
-- add a SQL statement
dbms_advisor.add_sqlwkld_statement (workload_name,'App','action', NULL,15,3000,423,507,60,704, 3,'16-FEB-2002',80, 'test','SELECT test.t2.fname2 as T2fname, test.t2.lname2 as T2LastN, test.t3.fname3 as T3FirstN from test.t2, test.t3 where (test.t2.primk = 84756) and (test.t3.primk = 222)');