PGA 튜닝 테스트

create table t5 (id varchar2(5) primary key ,
                 ord_dt date ,
                 c3 varchar2(20) ,
                 c4 varchar2(200) ) ;


drop table t5 purge;   
truncate table t5 ;

insert into t5
select * from (
  select object_id -1 , last_ddl_time, object_type, object_name
  from all_objects
  order by object_id )
where rownum <= 50000 ;

commit;
create index t5_x1 on t5(ord_dt) ;

select * from t5;

update t5 set ord_dt = to_date('2017/05/08', 'YY/MM/DD')
where id between 48000 and 49000 ;

select * from t5;
update t5 set ord_dt = to_date('2017/05/09', 'YY/MM/DD')
where id between 49001 and 50000 ;

commit;

select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4
from t5
where ord_dt > '2017/05/08'
and ord_dt <= '2017/05/09'
order by ord_dt, id, c3, c4 ;

select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4
from t5
where ord_dt > '2017/05/08'
and ord_dt <= '2017/05/09'
order by ord_dt, id;



select * from bulk_emp
where empno = 7369 ;


create table bulk_emp
as
select rownum rn, empno, ename, job, to_date('20100101', 'yyyymmdd')+lv as hiredate, sal, deptno
from emp
, (select mod(level-1, 5000) lv from dual connect by level <= 100000) ;

alter table bulk_emp add (constraint pk_bulk_emp primary key(rn));
create index ix1_bulk_emp on bulk_emp(hiredate);




select /*+ gather_plan_statistics */
hiredate, empno, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, empno, ename, job ;

select /*+ gather_plan_statistics */
hiredate, empno, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, empno ;



select /*+ gather_plan_statistics */
hiredate, rn, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, rn, ename, job ;

select /*+ gather_plan_statistics */
hiredate, rn, ename, job
from bulk_emp
where hiredate >= to_date('20150101', 'yyyymmdd')
and hiredate < to_date('20150104', 'yyyymmdd')
order by hiredate, rn ;





select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));



 

SQL_ID  7r72vumqu8n7n, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4 from t5 where 
ord_dt > '2017/05/08' and ord_dt <= '2017/05/09' order by ord_dt, id, 
c3, c4
 
Plan hash value: 1711542383
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT ORDER BY                        |       |      1 |   1000 |   1000 |00:00:00.01 |      16 |   106K|   106K|96256  (0)|
|*  2 |   FILTER                              |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T5    |      1 |   1000 |   1000 |00:00:00.01 |      16 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T5_X1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('2017/05/09')>TO_DATE('2017/05/08'))
   4 - access("ORD_DT">'2017/05/08' AND "ORD_DT"<='2017/05/09')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

SQL_ID  3pjr8972swmvm, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ord_dt, id, c3, c4 from t5 where 
ord_dt > '2017/05/08' and ord_dt <= '2017/05/09' order by ord_dt, id
 
Plan hash value: 1711542383
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT ORDER BY                        |       |      1 |   1000 |   1000 |00:00:00.01 |      16 | 99328 | 99328 |88064  (0)|
|*  2 |   FILTER                              |       |      1 |        |   1000 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T5    |      1 |   1000 |   1000 |00:00:00.01 |      16 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | T5_X1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('2017/05/09')>TO_DATE('2017/05/08'))
   4 - access("ORD_DT">'2017/05/08' AND "ORD_DT"<='2017/05/09')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |      8 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 |      8 | 52224 | 52224 |47104  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |      8 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |      8 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 52224 | 52224 |47104  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("HIREDATE">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIREDATE"<TO_DATE(' 2015-01-04 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
              
              
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------   

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |    840 |00:00:00.01 |      69 |       |       |          |
|   1 |  SORT ORDER BY                       |              |      1 |   1120 |    840 |00:00:00.01 |      69 | 46080 | 46080 |40960  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BULK_EMP     |      1 |   1120 |    840 |00:00:00.01 |      69 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IX1_BULK_EMP |      1 |   1120 |    840 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------