trace and mview

cd C:\app\owner\diag\rdbms\orcl\orcl\trace
sqlplus scott/oracle
set linesize 132
set timing on
set pages 3000
alter session set tracefile_identifier='test28';
alter session set sql_trace=true;

select a.empno, a.ename, b.dname
from big_emp1 a, big_dept1 b
where a.deptno = b.deptno;

alter session set sql_trace=false;

C:\app\owner\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_1220_test28.trc test28.txt sys=no

 

------------------------------
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

desc dba_tablespaces;
select tablespace_name, retention from dba_tablespaces;
alter tablespace undotbs1 retention guarantee;

 

 

create materialized view emp_sumsal
build immediate refresh complete on commit
enable query rewrite
as
select sum(a.sal) as sum_sal, a.deptno
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno;

select * from all_objects where object_name = 'EMP_SUMSAL';

drop materialized view emp_sumsal;

 

create materialized view emp_sumsal
build deferred  -- 추후 data  생성
as
select sum(a.sal) as sum_sal, a.deptno
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno;

--? 알아서 data 가져온다.
SQL> exec dbms_mview.refresh('EMP_SUMSAL','?');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from emp_sumsal;

   SUM_SAL     DEPTNO
---------- ----------
      9400         30
     10075         20
      8750         10

- C : COMPLETE
CREATE 명령의 모든 QUERY 정의를 재실행하여 기졲 데이터를 잘라내는 작업과 마스터 테이블을 기반으로 하는 모든 데이터를 재구축하는 작업이 포함
- F : FAST
마지막 REFRESH 이후의 변경 사항을 적용하며 젂체 재구축을 수행하지는 않음
MIVEW LOG를 사용한 REFRESH : 마스터 테이블의 모든 변경사항이 로그에 기록된 후 MVIEW에 적용
ROWID 범위를 이용한 REFRESH : 싞규 행의 ROWID를 이용하여 DIRECT PATH LOAD 후 MVIEW에 적용
- ? : FORCE
COMPLETE + FAST

EXEC DBMS_MVIEW.REFRESH('EMP_SUMSAL','?');

CREATE MATERIALIZED VIEW dept_sal
BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 선택.
REFRESH COMPLETE -- FORCE, COMPLETE, ?
ON COMMIT -- ON DEMAND, ON COMMIT 선택.
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;

 

INSERT INTO EMP
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO )
VALUES
( 7935, 'KIM', 'MANAGER', 7839,
TO_Date( '12/17/1980 12:00:00 오전', 'MM/DD/YYYY HH:MI:SS AM'),3000, NULL, 20);

 

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

 

grant query rewrite to scott;
grant create materialized view to scott;

SQL> SELECT SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    30 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     3 |    30 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   140 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

-- OPTIMIZER_MODE가 CHOOSE인 상태에서 Analyze를 실행하지 않아 QueryRewrite가 실행되지 않음
-- OPTIMIZER_MODE가 CHOOSE일 경우는 모든 테이블을 ANALYZE
-- 또한 MVIEW를 ENABLE QUERY REWRITE 옵션으로 생성해야 함
SQL> SET AUTOTRACE OFF;
SQL> drop MATERIALIZED VIEW EMP_SUMSAL;
SQL> CREATE MATERIALIZED VIEW EMP_SUMSAL
ENABLE QUERY REWRITE
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;

 

SQL> set autot trace
SQL> SELECT SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 503417823

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     3 |    78 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_SUMSAL |     3 |    78 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

 

SQL> SELECT /*+ NOREWRITE */ SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    30 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     3 |    30 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   140 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>