$ sqlplus hr/hr

SQL> @/opt/oracle/product/9.2.0/rdbms/admin/utlxplan.sql

SQL> alter session set optimizer_mode = rule;

SQL> !cat $HOME/LABS/ptw9i/lab12_02.sql
select *
from employees
where employee_id < '215';

SQL> explain plan for
  2  @LABS/ptw9i/lab12_02.sql

Explained.

SQL>
SQL>
SQL> col id format 999
SQL>
SQL> col operation format a20
SQL> col options format a20
SQL> col object_name format a20
SQL> select id,parent_id,operation,options,object_name
  2  from plan_table;

  ID  PARENT_ID OPERATION            OPTIONS              OBJECT_NAME
---- ---------- -------------------- -------------------- --------------------
  0            SELECT STATEMENT
  1          0 TABLE ACCESS        BY INDEX ROWID      EMPLOYEES
  2          1 INDEX                RANGE SCAN          EMP_EMP_ID_PK

동일한 문장일지라도 optimizer mode 가 달라지면 실행계획이 달라진다.
rule 인 경우 emp_emp_id_pk 인덱스가 사용되어 index range scan이 사용되었지만 optimizer mode 가 all_rows로 된 경우 Full Table scan 이 사용되었다.

SQL> !cat $HOME/LABS/ptw9i/lab12_04.sql
select /*+ all_rows*/ *
from employees
where employee_id < '215';

SQL>
SQL> explain plan for
  2  @LABS/ptw9i/lab12_04.sql

Explained.

SQL>
SQL> select id,parent_id,operation,options,object_name
  2  from plan_table;

  ID  PARENT_ID OPERATION            OPTIONS              OBJECT_NAME
---- ---------- -------------------- -------------------- --------------------
  0            SELECT STATEMENT
  1          0 TABLE ACCESS        FULL                EMPLOYEES

SQL> exit

3. SQL Trace 사용

$cd $ORACLE_HOME/rdbms/log

$rm *.trc

$ sqlplus sh/sh

SQL> alter session set sql_trace = true;

SQL> !cat $HOME/LABS/ptw9i/lab12_06.sql
select c.cust_last_name, sum(s.amount_sold)
from sales s, customers c
where c.cust_id = s.cust_id
  and s.prod_id = '46520'
group by c.cust_last_name;

SQL> @$HOME/LABS/ptw9i/lab12_06.sql

SQL> alter session set sql_trace = false;

SQL> exit

$ cd $ORACLE_HOME/rdbms/log

[human@dbserver udump]$ ls
human_ora_24745.trc

4. 통계정보의 수집

-hr/hr 로 login 한 다음 hr user schema 에 있는 object 에 대한 통계정보를 저장할 table 을 생성한다.

$sqlplus hr/hr

SQL> execute dbms_stats.create_stat_table('HR','MY_STATS');

SQL> select * from my_stats;

-hr user schema 에 있는 모든 object에 대한 통계정보를 dictionary 로 부터 my_stats라는 table로 export

SQL> execute dbms_stats.export_schema_stats('HR','MY_STATS');
SQL> select * from my_stats;

-hr user schema 의 현재의 모든 object에 대해 통계를 생성하여 dictionary에 store한다.

SQL> execute dbms_stats.gather_schema_stats('HR');

-dictionary에 store된 hr user schema object에 대한 통계정보를 삭제한 후 앞에서 my_stats에 떨어 뜨린 이전 통계정보를 dirct에 restore

SQL> execute dbms_stats.delete_schema_stats('HR');

SQL> execute dbms_stats.import_schema_stats('HR','MY_STATS');

SQL> exit