10053 Event Test

• scott.big_table생성 및 scott 유저에게 trace 실행 권한 부여
  
  

conn scott/loveora

create table big_table as select rownum id,a.* from all_objects a where 1=0;
alter table big_table nologging;

declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */ into big_table select rownum, a.* from all_objects a;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ append */ into big_table
select rownum+l_cnt,owner,object_name,subobject_name,object_id,data_object_id,object_type,
created,last_ddl_time,timestamp,status,temporary,generated,secondary
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table big_table add constraint big_table_pk primary key(id);

begin
dbms_stats.gather_table_stats
(ownname => user,
tabname => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade => TRUE);
end;
/

select count(*) from big_table;

create index big_table_owner_idx
on big_table(owner)
compute statistics;

begin 
  	  	DBMS_STATS.GATHER_SCHEMA_STATS (
  	  	  ownname => 'SCOTT',
          estimate_percent => 10
          );
          end;
/

conn sys/loveora as sysdba

Rem NAME : UTLXPLAN.SQL
create table PLAN_TABLE (
        statement_id       varchar2(30)  , plan_id           number        , timestamp          date,
        remarks            varchar2(4000), operation         varchar2(30)  , options            varchar2(255),
        object_node        varchar2(128) , object_owner      varchar2(30)  , object_name        varchar2(30),
        object_alias       varchar2(65)  , object_instance   numeric       , object_type        varchar2(30),
        optimizer          varchar2(255) , search_columns    number        , id                 numeric,
        parent_id          numeric       , depth             numeric       , position           numeric,
        cost               numeric       , cardinality       numeric       , bytes              numeric,
        other_tag          varchar2(255) , partition_start   varchar2(255) , partition_stop     varchar2(255),
        partition_id       numeric       , other             long          , distribution       varchar2(30),
        cpu_cost           numeric       , io_cost           numeric       , temp_space         numeric,
        access_predicates  varchar2(4000), filter_predicates varchar2(4000), projection         varchar2(4000),
        time               numeric       , qblock_name       varchar2(30)  , other_xml          clob
);

Rem NAME : plustrce.sql
set echo on
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;
set echo off
grant alter session, alter system, PLUSTRACE to scott;
commit;

conn scott/loveora

• Session 1. 첫 번째 session에서 스크립트 실행

conn scott/loveora
spool 10053_CBO
prompt 1. CBO
set timing on
set time on
alter system flush shared_pool;
set autotrace on
alter session set events '10053 trace name context forever, level 1';
alter session set tracefile_identifier='10053_CBO';
select t1.object_name, t2.object_name from scott.big_table t1, scott.big_table t2
  where t1.object_id= t2.object_id and t1.owner='WMSYS' and rownum < 1000;
alter session set events '10053 trace name context off';
spool off

• Session 2. 첫 번째 스크립트 실행 후 두 번째 session에서 스크립트 실행

conn scott/loveora
spool 10053_RBO
prompt 2. RBO
set timing on
set time on
alter system flush shared_pool;
set autotrace on
alter session set events '10053 trace name context forever, level 1';
alter session set tracefile_identifier='10053_RBO';
select /*+ RULE */ t1.object_name, t2.object_name 
from scott.big_table t1, scott.big_table t2
  where t1.object_id= t2.object_id and t1.owner='WMSYS' and rownum < 1000;
alter session set events '10053 trace name context off';
spool off

문서에 대하여

• 최초작성자 : 박혜은
• 최초작성일 : 2009년 11월 19일
• 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.