Home » RDBMS Server » Performance Tuning » 10g Advisor
10g Advisor [message #65807] Thu, 30 December 2004 10:24 Go to next message
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;
/
Re: 10g Advisor [message #65809 is a reply to message #65807] Thu, 30 December 2004 20:51 Go to previous message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Try to use DBMS_ADVISOR.SQLACCESS_OLTP instead of DBMS_ADVISOR.SQLACCESS_WAREHOUSE.

Best regards.

Frank
Previous Topic: cache memory
Next Topic: Orale Hints?
Goto Forum:
  


Current Time: Thu Oct 01 03:04:03 CDT 2020