| |
1.perfstat/perfstat로 login하고 snap.sql을 실행하여 snapshot id를 확인해 둔다.
$sqlplus 'sys/oracle as sysdba'
SQL> startup
SQL>exit
$sqlplus /nolog
SQL> @$HOME/LABS/ptw9i/snap.sql
SQL> exit
2. hr/hr로 login하여 database buffer cache에 대한 user의 작업을 simulate 한다.
$ sqlplus hr/hr
SQL> !more $HOME/LABS/ptw9i/lab04_02.sql
insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
select (employees_seq.nextval), first_name, last_name,
to_char((employees_seq.nextval)), phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id
from hr.employees
where rownum < 5;
commit;
insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
select (employees_seq.nextval), first_name, last_name,
to_char((employees_seq.nextval)), phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id
from hr.employees
where rownum < 5;
commit;
insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
select (employees_seq.nextval), first_name, last_name,
to_char((employees_seq.nextval)), phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id
from hr.employees
where rownum < 5;
commit;
insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
select (employees_seq.nextval), first_name, last_name,
to_char((employees_seq.nextval)), phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id
from hr.employees
where rownum < 5;
commit;
insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
select (employees_seq.nextval), first_name, last_name,
to_char((employees_seq.nextval)), phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id
from hr.employees
where rownum < 5;
commit;
insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
salary, commission_pct, manager_id, department_id)
select (employees_seq.nextval), first_name, last_name,
to_char((employees_seq.nextval)), phone_number,
hire_date, job_id, salary, commission_pct, manager_id,
department_id
from hr.employees
where rownum < 5;
commit;
SQL> @$HOME/LABS/ptw9i/lab04_02.sql
SQL> exit
$ sqlplus system/manager
SQL> select 1 - (phy.value - lob.value - dir.value) / ses.value "Cache Hit Ratio"
2 from v$sysstat ses,v$sysstat lob,v$sysstat dir, v$sysstat phy
3 where ses.name='session logical reads'
4 and dir.name='physical reads direct'
5 and lob.name=
6 'physical reads direct (lob)'
7 and phy.name='physical reads';
Cache Hit Ratio
---------------
.94251138
Cache Hit Ratio는 90%이상 이어야 한다.
4. perfstat/perfstat로 login한 후 이전 snapshot과 지금의 snapshots사이의 통계를 확인한다.
$ sqlplus /nolog
SQL>@$HOME/LABS/snap.sql
SQL>@$HOME/LABS/spreport.sql
Enter value for begin_snap: ??
Enter value for end_snap: ??
Enter value for report_name:
SQL> exit
$vi sp_??_??.lst
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
**--> Buffer Hit %: 97.19 In-memory Sort %: 100.00
Library Hit %: 91.63 Soft Parse %: 95.37
Execute to Parse %: 55.48 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 88.24 % Non-Parse CPU: 99.91
<-- Production DB에서 만일 Hit율이 나쁘다면 새로운 buffer를 추가하고 앞의 과정을 반복하여 hit율이 개선되었는가 확인한다.
Buffer의 크기가 너무 크다면 Buffer의 수를 줄여도 연전히 Hit율이 좋을 것이다.
6. system/manager로 login한 후 hr schema의 TEMP_EMP table의 크기를 확이한 후 그것을 KEEP buffer pool에 유지해보자
-먼저 hr.EMP_TEMP table의 size를 확인한다.
$sqlplus system/manager
SQL> analyze table hr.temp_emps compute statistics;
SQL> select table_name,blocks from dba_tables
2 where table_name in ('TEMP_EMPS');
TABLE_NAME BLOCKS
------------------------------ ----------
TEMP_EMPS 7 <-- segment header block을 제외하고 7개 block
(7+1)*4096bytes = 32K 밖에 안 되지만 buffer cache의 할당 단위는 granule이다.
============================================================
sga_max_size <= 128M 이면 4M
아니면 16M ===> 그러므로 4M로 하자
============================================================
- keep buffer pool을 구성한다.
SQL> alter system set db_keep_cache_size= 4M;
alter system set db_keep_cache_size= 4M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache <--왜 error가 날까?
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 25165824
SQL>
SQL> alter system set shared_pool_size=20M;
<-- SGA에 새로운 Memory component를 추가할 만큼의 충분한 크기를 가지고 있지 않고 다른 component 줄여도 성능에 문제가 되지 않는다면
SQL> alter system set db_keep_cache_size=4M;
SQL> select id,name,block_size,current_size,buffers
2 from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
---------- -------------------- ---------- ------------ ----------
1 KEEP 8192 4 500
3 DEFAULT 8192 4 500
--이제 hr.TEMP_EMPS table을 keep buffer pool에 cache시킨다.
SQL> alter table hr.temp_emps
2 storage(buffer_pool keep);
Table altered.
SQL> select table_name,buffer_pool
2 from dba_tables
3 where buffer_pool='KEEP';
TABLE_NAME BUFFER_
------------------------------ -------
TEMP_EMPS KEEP
<-- 이 session은 그대로 유지한다.
-별도로 hr/hr로 login하여 lab04_10.sql을 실행하여 TEMP_EMPS table에 대해 query를 실행한 후 buffer cache에 대한 hit ratio를 check 한다.
$ sqlplus hr/hr
SQL> !cat $HOME/LABS/ptw9i/lab04_10.sql
variable n number;
begin
for i in 1..40
loop
select count(*) into :n from hr.temp_emps;
end loop;
end;
/
SQL> @LABS/ptw9i/lab04_10.sql
-이전에 open된 system/manager session으로 이동
SQL> select name,physical_reads,db_block_gets,consistent_gets,
2 1 - (physical_reads / (db_block_gets + consistent_gets)) "Hits"
3 from v$buffer_pool_statistics;
NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS Hits
-------------------- -------------- ------------- --------------- ----------
KEEP 8 360 360 .988888889
DEFAULT 4286 52780 37684 .952622037
SQL>exit
|