SQLAccess Advisor

The SQLAccess Advisor tool produces intelligent recommendations for the proper usage of indexes, and materialized views, and the SQLAccess Advisor can recommend both bitmap and B-tree indexes to optimize access to data. It can also produce suggestions about how to tune with materialized views to take advantage of fast refresh and query rewrite features.

As we noted, materialized views (MV) are a blessing for poorly-designed schemas and an MV can be used to create a denormalized schema by pre-joining tables, and commonly referenced tasks can be pre-aggregated to eliminate redundant queries.

Generally, the SQLAccess Advisor is accessible through the Oracle dbms_advisor package, and this package has a set of advisory and analysis functions and procedures that can be used to access the SQLAccess Advisor tool. The following information contains guidance on how to use dbms_advisor API to get recommendations from this advisor.

Through the dbms_advisor, the SQLAccess Advisor API supports the DBA by recommending new indexes or MVs:

§ A real world or hypothetical workload can be used.

§ It can assist in making a materialized view fast refreshable.

§ It helps edit and change existing materialized views to improve query rewrite.

§ It can save and manage historical workload data.

§ It can update and remove recommendations.

§ It can perform a quick tune operation using a single SQL statement as input.

Working with SQLAccess Advisor is very similar to working with the ADDM Advisor, and the SQLAccess Advisor can be invoked using the following four steps:

§ Create an advisor task.

§ Configure advisor task parameters.

§ Run the analysis for the task.

§ Review and implement the recommendations.

The dbms_advisor.create_task procedure can be used to create an initial SQLAccess Advisor task. In the example below, a new SQLAccess Advisor task named SQL_ACC_TASK1 is created:

execute dbms_advisor.create_task ( ‘SQL Access Advisor’, ‘SQL_ACC_TASK1’ , ‘New SQLAccess Task’ );

The next step is to configure the workload parameters for your task. In the case of the SQLAccess Advisor task, this step would involve the creation of a workload that will form the grounds for subsequent advisor analysis with ADDM. The workload consists of one or more SQL statements along with associated statistics and attributes that describe each statement. The dbms_advisor.create_sqlwkld procedure is used to create a new workload object that is independent from any tasks created earlier:

DECLARE

W_name VARCHAR2(100);

BEGIN

W_name := ‘SQL_WRKLD_1’;

dbms_advisor.create_sqlwkld (W_name);

END;

After the workload object is created, it must be “linked in” to the SQLAccess Advisor task that was just created. The dbms_advisor.add_sqlwkld_ref procedure is used to link a workload with a task:

exec dbms_advisor. add_sqlwkld_ref(‘SQL_ACC_TASK1’, ‘SQL_WRKLD_1’ );

The new workload object must now be populated with the SQL or DML statements to be analyzed. The following options are available for adding statements to the workload:

§ Use the dbms_advisor.import_sqlwkld_sqlcache procedure to import data into a workload from the current SQL cache in the shared pool.

§ Use the dbms_advisor.add_sqlwkld_statement procedure to add a single SQL statement to a workload.

§ Use the dbms_advisor.import_sqlwkld_sts procedure to import SQL statements from an existing SQL Tuning Set.

§ Use the dbms_advisor.import_sqlwkld_user procedure to import SQL statements from the specified user table.

For example, the call below imports all the SQL statements to the workload from the current contents of the library cache:

DECLARE

Saved_rows number;

Failed_rows number;

BEGIN

Dbms_advisor.import_sqlwkld_sqlcache (‘SQL_WRKLD_1’, ‘NEW’, 2, Saved_rows, Failed_rows);

END;

The next step is setting up the SQLAccess Advisor task parameters. The query below can be used to produce the list of parameters that can be configured:

SELECT

p.parameter_name,

p.parameter_value,

p.parameter_type,

p.is_output

FROM

dba_advisor_tasks t,

dba_advisor_parameters p

WHERE

t.owner = 'DABR'

AND

t.task_name = ‘SQL_ACC_TASK1’