Oracle Performance Tuning and optimization (II.)

In order to be successful, I'll involve some preliminary steps that will be needed to see what happens, including running Plustrce SQL scripts, creating a Explain_plan table, granting roles, configuring Sql*plus environments to view execution plans. All of these steps include "use automatic tracking in Sql*plus" In Oracle 9i R2 Database Performance Tuning guide and reference, and for Oracle 10g These steps include "Tuning Sql*plus" in "Sql*plus User's Guide and reference version 10.2".

Preliminary steps

If the role Plustrace does not exist, it is created using the Plustrce SQL script in the Oracle_home\sqlplus\admin directory, which is fairly straightforward:

drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

Check role usage:

SQL> select role from dba_roles where role = 'PLUSTRACE';
ROLE
---------- ------
PLUSTRACE

The user must have (or have access to) a plan_table (it can be named another name, but the default name is very good), the table is created with the Utlxplan SQL script in the Oracle_home\rdbms\admin directory.

SQL> show user
USER is "SYSTEM"
SQL> @?\rdbms\admin\utlxplan
Table created.
SQL> create public synonym plan_table for system.plan_table;
Synonym created.
SQL> grant select, update, insert, delete on plan_table to <你的用户名>;
Grant succeeded.
SQL> grant plustrace to <你的用户名>;
Grant succeeded.

The user used in our example is HR (which can be found in the sample solution provided by Oracle).

SQL> conn hr/hr
Connected.
SQL> set autotrace on
SQL> select * from dual;
D
-
X