Scribd
Upload
127 views3 pages

SQL Tuning for Database Admins

This document shows PL/SQL code to run the SQL Access Advisor, which analyzes SQL statements from the cursor cache and makes suggestions about additional indexes and materialized views which might improve system performance. The code creates an advisor task, loads the SQL statements from the cursor cache as a workload, sets various task parameters, executes the task, and then can view the results.

Uploaded by

Mushtaq Niazi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
127 views3 pages

SQL Tuning for Database Admins

This document shows PL/SQL code to run the SQL Access Advisor, which analyzes SQL statements from the cursor cache and makes suggestions about additional indexes and materialized views which might improve system performance. The code creates an advisor task, loads the SQL statements from the cursor cache as a workload, sets various task parameters, executes the task, and then can view the results.

Uploaded by

Mushtaq Niazi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL Access Advisor

make suggestions about additional indexes and materialized views which might
improve system performance.

=================

DECLARE
taskname varchar2(30) := 'SQLACCESS3638195';
task_desc varchar2(256) := 'SQL Access Advisor';
task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
task_id number := 0;
num_found number;
sts_name varchar2(256) := 'SQLACCESS3638195_sts';
sts_cursor dbms_sqltune.sqlset_cursor;
BEGIN
/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_id,
taskname,
task_desc,
task_or_template);

/* Reset Task */
dbms_advisor.reset_task(taskname);

/* Delete Previous STS Workload Task Link */


select count(*)
into num_found
from user_advisor_sqla_wk_map
where task_name = taskname
and workload_name = sts_name;
IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld_ref(taskname,sts_name,1);
END IF;

/* Delete Previous STS */


select count(*)
into num_found
from user_advisor_sqlw_sum
where workload_name = sts_name;
IF num_found > 0 THEN
dbms_sqltune.delete_sqlset(sts_name);
END IF;

/* Create STS */
dbms_sqltune.create_sqlset(sts_name, 'Obtain workload from cursor cache');

/* Select all statements in the cursor cache. */


OPEN sts_cursor FOR
SELECT VALUE(P)
FROM TABLE(dbms_sqltune.select_cursor_cache) P;

/* Load the statements into STS. */


dbms_sqltune.load_sqlset(sts_name, sts_cursor);
CLOSE sts_cursor;

/* Link STS Workload to Task */


dbms_advisor.add_sqlwkld_ref(taskname,sts_name,1);
/* Set STS Workload Parameters */

dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_U
NUSED);

dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_U
NUSED);
dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT','25');

dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR
_UNUSED);

dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UN
USED);

dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_
UNUSED);

dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR
_UNUSED);

dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVIS
OR_UNUSED);

dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR
_UNUSED);

dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISO
R_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');

/* Set Task Parameters */


dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');

dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST
');

dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.AD
VISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');

dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLI
MITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');

dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISO
R_UNUSED);

dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNU
SED);

dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISO
R_UNUSED);

dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNU
SED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISO
R_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');

/* Execute Task */
dbms_advisor.execute_task(taskname);
END;
/

========================

select * from DBA_ADVISOR_TASKS;

select * from DBA_ADVISOR_LOG ;

select * from DBA_ADVISOR_FINDINGS ;

========================================

You might also like