| 10g Advisor [message #65807] |
Thu, 30 December 2004 10:24  |
Tracy
Messages: 43
Registered: January 2000
|
Member |
|
|
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.
DECLARE
task_desc VARCHAR2(100);
task_id NUMBER;
task_name VARCHAR2(30);
workload_name VARCHAR2(30);
BEGIN
task_name := 'Task_mag';
dbms_advisor.create_task (DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_id, task_name, 'My Advisor Task', DBMS_ADVISOR.SQLACCESS_WAREHOUSE);
dbms_advisor.set_task_parameter ('Task_mag', 'EVALUATION_ONLY', 'FALSE');
DBMS_OUTPUT.PUT_LINE('test3');
dbms_advisor.set_task_parameter ('Task_mag', 'EXECUTION_TYPE', 'FULL');
DBMS_OUTPUT.PUT_LINE('test4');
-- create the workload
workload_name :='Workload_mv';
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)');
DBMS_ADVISOR.EXECUTE_TASK(task_name);
DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(task_name),
'MY_DIR','script66.sql');
END;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
