SQL Tuning Advisor

This practice is based on http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php#sql_tuning_advisor.

SQL> conn / as sysdba
SQL> DECLARE
2 l_sql VARCHAR2(500);
3 l_sql_tune_task_id VARCHAR2(100);
4 BEGIN
5 l_sql := 'SELECT e.*, d.* ' ||
6 'FROM hr.employees e JOIN hr.departments d ON e.department_id = d.department_id ' ||
7 'WHERE NVL(employee_id, ''0'') = :empno';
8 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
9 sql_text => l_sql,
10 bind_list => sql_binds(anydata.ConvertNumber(100)),
11 scope => DBMS_SQLTUNE.scope_comprehensive,
12 time_limit => 60,
13 task_name => 'emp_dept_tuning_task',
14 description => 'Tuning task for an EMP to DEPT join query.');
15 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
16 END;
17 /

SQL> EXEC DBMS_STATS.delete_table_stats('HR', 'EMPLOYEES');

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

In Enterprise Manager, click Advisor Central, you will see your sql tuning task completed. Click that name. In SQL Tuning result page, you can click View Recommendations button to view all of recommendations based on the query.

SQLAccess Advisor

SQL> conn / as sysdba
SQL> BEGIN
2 DBMS_ADVISOR.quick_tune(
3 advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
4 task_name => 'test_quick_tune',
5 attr1 => 'SELECT * FROM hr.employees WHERE upper(last_name) = ''SMITH''');
6 END;
7 /

From Enterprise Manager, click Advisor Central. From the page, you can see test_quick_tune. Click that task name. In the page Result for Task, click the Recommendation tab. You will see a recommendation for tuning your index based on your query.

Using Segment Advisor

For each segment, Oracle will put a boundary (i.e. Highwater Mark) that will separate between used blocks and free blocks. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. In normal DB operations, the high water mark only moves upwards, not downwards. If there is a lot of free space below the high water mark, one might consider to use alter table move statements. In another words, we have to shrink or compact the segment to move HWM downward. This condition will make full scan segment more faster.
We will try to use segment advisor to make our segment can be optimized based on the statistical data of our segment.

SQL> create table uji2 (id number(4), nilai varchar(1000));

Table created.

SQL> begin
2 for i in 1..10000 loop
3 insert into uji2 values (i, lpad('*', 1000, '*'));
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> update uji2 set data = null where mod(id, 5) = 0;

200 rows updated.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly
(if you have any problems with this command, please refer to http://www.samoratech.com/tips/swenableautotrace.htm)

SQL> select count(*) from uji2;
(Summary)

SQL> alter table uji2 enable row movement;

Table altered.

SQL> alter table uji2 shrink space;

Table altered.

SQL> select count(*) from uji2;
(Summary)

Based on our practice, you can see that after shrink space, the amount of blocks that was reading have decreased, because the HWM was downwarded.

Now, we will use Segment Advisor from Enterprise Manager to get an advice about our segment.

SQL> create table uji2 (id int, data char(1000));

Table created.

SQL> insert into uji2 select object_id, lpad('*', 1000, '*')
2 from all_objects;

SQL> update uji2 set data = null where mod(id, 5) = 0;

Go to Enterprise Manager, and click Performance tab. In bottom section, click Advisor Central, and then click Segment Advisor. In Segment Advisor page, click Schema Objects, and click Next. Click Add button to add our table, i.e. uji2.

Click Next, and you can click Submit button directly. Here is example of the result :
Click the name of latest Segment Advisor task. In the page of Segment Advisor task, click Recomendataion Details.


Now, you can click Shrink button to make segment compact.