$ sqlplus 'sys/oracle as sysdba'
SQL> startup
SQL> show parameter shared_pool_size
SQL> conn perfstat/perfstat
SQL> @LABS/ptw9i/snap.sql
SQL> exit

2. database에 대한 user의 job을 시뮬레이트
-session 1
$sqlplus hr/hr
SQL> !cat $HOME/LABS/ptw9i/lab03_03_1.sql
connect hr/hr
CREATE TABLE temp_emps AS SELECT * FROM employees;
UPDATE temp_emps
SET    employee_id = rownum;
COMMIT;

SQL> @LABS/ptw9i/lab03_03_1.sql

-session 2 : 별도로 open 한다.

$ sqlplus hr/hr

SQL> !cat $HOME/LABS/ptw9i/lab03_03_2.sql
declare
  cursor dept_cursor is select department_id from  hr.departments;
  cursor emp_cursor is select * from hr.employees where rownum < 5;
begin

  for i in 1..5 loop
  for dept_rec in dept_cursor loop
      for emp_rec in emp_cursor loop

      insert into hr.temp_emps values (
        i+1000,
        emp_rec.FIRST_NAME,emp_rec.last_name,emp_rec.EMAIL,
        emp_rec.phone_number, emp_rec.hire_date, emp_rec.job_id,
        emp_rec.salary, emp_rec.commission_pct, emp_rec.manager_id,
        emp_rec.department_id);
      end loop;
      commit;
  end loop;
end loop;
end;
/

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

-session 1, session 2 를 close

SQL> exit
SQL> exit

3. system/manager로 login하여 v$librarycache,v$rowcache를 조회한다.

$ sqlplus system/manager

SQL> select sum(pins),sum(reloads),sum(reloads)*100/sum(pins) from v$librarycache;

sum(reloads)*100/sum(pins) 은 1%를 넘지 말아야 한다.

SQL> select sum(getmisses),sum(gets),sum(getmisses)*100/sum(gets) "Miss%" from v$rowcache;

sum(getmisses)*100/sum(gets) 는 15%를 넘지 말아야 한다.

SQL> exit

4. perfstat/perfstat 로 login한 후 snap.sql을 실행하여 statistics snapshot을 수집하고, snapshot id를 확인 spreport.sql를 실행하여 이전에 확인해 두었던 두 snapshot id간의 통계를 획득한다..

$sqlplus /nolog
SQL> @$HOME/LABS/ptw9i/snap.sql
SQL> @$HOME/LABS/ptw9i/spreport.sql

SQL> exit

5. current directory 에 생성된 report를 분석한다.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00      Redo NoWait %:  100.00
            Buffer  Hit  %:  99.92    In-memory Sort %:  100.00
            Library Hit  %:  100.00        Soft Parse %:  100.00
        Execute to Parse %:  49.71        Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  50.00    % Non-Parse CPU:  99.96

6. 'sys/oracle as sysdba' 로 login한후 v$db_object_cache를 조회하여 shared pool에 pin되어있는 package,procedure,Trigger를 확인하고 Oracle이 제공하는 package중 하나를 DBMS_SHARED_POOL.KEEP를 사용하여 shared pool에 pin시킨다.

$ sqlplus 'sys/oracle sa sysdba'

SQL> col name format a22
SQL> col type format a15
SQL>
SQL> @/opt/oracle/product/9.2.0/rdbms/admin/dbmspool.sql

SQL> col anme format a30
SQL> col type format a15
SQL>
SQL> select name,type,kept
  2  from v$db_object_cache where type in('PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY');

SQL> execute dbms_shared_pool.keep('SYS.STANDARD');

SQL>  select name,type,kept
  2  from v$db_object_cache where type in('PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY') and name like '%STANDARD%';

7. Oracle Server를 Shared Server로 구성하여 uga memory 사용을 monitering한다.

$vi $ORACLE_HOME/dbs/initDBA9i60.ora
  => 추가한다.
dispatchers="(PROTOCOL=TCP) (dis=2)"
shared_servers=1

$ sqlplus 'sys/oracle as sysdba'

SQL> startup

SQL> select name,status,circuit,idle,busy,requests from v$shared_server;

NAME STATUS          CIRCUIT        IDLE      BUSY  REQUESTS
---- ---------------- -------- ---------- ---------- ----------
S000 WAIT(COMMON)    00            25784          0          0

SQL>
SQL> select name,status from v$dispatcher;

NAME STATUS
---- ----------------
D000 WAIT
D001 WAIT

SQL> select sum(value) || 'bytes' "Total session memory"
  2  from v$sesstat s,v$statname n
  3  where n.name='session uga memory'
  4  and s.statistic# = n.statistic#;

Total session memory
---------------------------------------------
1180876bytes                              ------------(1)

<-- 이 session을 그대로 두고

$ sqlplus system/manager

SQL> select username,server from v$session
  2  where username is not null;

USERNAME                      SERVER
------------------------------ ---------
SYS                            DEDICATED
SYSTEM                        DEDICATED

SQL> select sum(value) || 'bytes' "Total session memory"
  2  from v$sesstat s,v$statname n
  3  where n.name='session uga memory'
  4  and s.statistic# = n.statistic#;

Total session memory
---------------------------------------------
1258472bytes                  -----------------(2)

(2) - (1) = ?

8. Oracle server를 다시 Dedicated Server로 구성하려면

$vi $ORACLE_HOME/dbs/initDBA9i60.ora
# shared_servers=1
# dispatchers="(pro=tcp)(dis=2)"

즉 위의 parameter를 comment처리한다..