[DB Study Season2] 00.환경설정

 

환경설정.7z

demobld_11g.sql

big_table.sql

demobld.sql

login.sql

mystat.sql

mystat2.sql

runstats.sql

show_space.sql

tk.sql

DB study Season1은 없습니다. ㅡ_ㅡ;;;
농담이고 Season1은 가장 기초인 SQL, Admin1,2입니다.
Performance Tuning는 제 실력이 무리라 제외입니다.

여튼 Season2는 기초가 어느정도 있는 분들에게 맞혀 나갈 예정입니다.
우선 환경설정 하겠습니다.
scott/tiger 스키마 설정입니다.

공유되는 DB에서 수행할 경우 특정 계정에 각자의 테이블 복사본을 설치 해서 사용하자.
DB를 혼자 사용하는거라면 걍 써도 무방하다는 것이다.

그럼 계정을 살펴 보자. 기본으로 scott계정은 잠겨 있다.
그러므로 unlock해 줘야 한다.

SYS:AS SYSDBA on 16-AUG-11>conn scott/tiger
ERROR:
ORA-28000: the account is locked
(잠겨있다고 에러가 뜬다.)
Warning: You are no longer connected to ORACLE.
: on 16-AUG-11>conn /as sysdba
Connected.
(sys로 접속해서 풀어준다.)
SYS:AS SYSDBA on 16-AUG-11>alter user scott identified by tiger account unlock;
User altered.
SYS:AS SYSDBA on 16-AUG-11>conn scott/tiger
Connected.
(정상적으로 접속이 되었다.)

SCOTT: on 16-AUG-11>show user
USER is "SCOTT"
SCOTT: on 16-AUG-11>select * from tab;

TNAME                            TABTYPE  CLUSTERID
------------------------------ -------------------------- ----------------------
DEPT                                  TABLE
EMP                                    TABLE
BONUS                               TABLE
SALGRADE                        TABLE

딱 보니 테이블이 4개 뿐이 없다.(5개 여야 하는데;;;)
테이블이 존재하지 않는다면 계정만들고 데이타를 넣어보자.

[solaris10g@test:/u01/app/oracle/product/10.2.0/db_1/sqlplus]ls
admin  bin    doc    lib    log    mesg   opmn

위에 보면 demo라는 폴더가 안보인다. 스크립트가 없다고 해서 좌절하지 말고
직접 넣어보자 우선 계정을 만들고~

create user big_table identified by oracle
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant connect, resource to big_table;
여기까지는 계정을 만들고 권한을 부여하는 과정이다.
10g에서는 demobld.sql이라는 파일이 안보인다. 그래서 11g로 대체 했다.
첨부된 파일 demobld_11g.sql을 다운해서 실행을 하자.
테이블을 조회해보면 5개의 테이블이 나온다.

제약 조건까지 모두 생성했으면 다음으로 넘어가자.

DBMS_output을 사용하기 위한 설정
SQL prompt상에서 set serveroutput on으로 설정하면 되나
매번 설정해야 하는 번거로움이 있다.
그래서 glogin.sql에 이 부분을 추가 해주면 된다.

파일 위치는 $ORACLE_HOME/sqlplus/admin에 위치해 있다.
이 파일을 열어 다음과 같이 입력해주었다.
(11g같은 경우 glogin.sql파일에 아무런 내용이 입력되어있지 않았다.)
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

다음으로 sqlplus에서 autotrace에 대한 설정을 해보자.
cd $ORACLE_HOME/rdbms/admin으로 이동하고
system으로 접속을 한다.
utlxplan.sql을 실행하자.
create public synonym plan_table for plan_table;
grant all on plan_table to public;
위 두문장을 실행해 준다.
public이라는것은 공용화를 의미하기 때문에 모든 유저가 사용할수 있다.

plustrace role를 부여하자
cd $ORACLE_HOME/sqlplus/admin으로 이동을 하고
sys로 접속한후 plustrce.sql을 실행한다.
grant plustrace to public;

statspack 설정
설치는 sys로 접속해서 spcreate.sql을 실행하면 된다.
perfstat schema의 password 입력
perfstat의 default tablespace 지정
perfstat의 temporary tablespace 지정

삭제는 spdrop.sql을 실행하면 된다. statspack 설치 시에 spcpkg.lis파일을
생성하며 이 파일을 보면 오류 여부를 확인할 수 있다.

사용자 정의 스크립트
runstats
한 유저가 사용하는 소규모 데이타베이스가 유용하다.
통계정보를 보관하기 위해 패키지를 생성한다. 4개의 v$테이블을 엑세스 해야 한다.
(v$statname, v$mystat, v$timer, v$latch)

create or replace view stats
as select 'STAT...'||a.name name, b.value
from v$statname a, v$sysstat b
where a.statistic#=b.statistic#
union all
select 'LATCH.'||name, gets from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer

통계정보 수집을 위한 작은 테이블 생성
create global temporary table run_stats(
run_id varchar2(15),
name varchar2(80),
val int)
on commit preserve rows;

API 콜이 포함된 runstats 패키지를 생성

create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop(p_difference_threshold in number default 0);
end;
/

runstats를 사용할 준비를 하자
첨부파일에 runstats.sql파일을 실행하면 된다.

1백만 row를 insert 할 두 개의 테이블을 설정에 앞서
big_table을 만들자
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
where rownum <-&1;
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;
/

위 실행문을 그대로 실행하면 에러가 뜬다. all_objects의 테이블에는 namespace,edition_name
이 두 컬럼이 없다. 그러므로 없는 컬럼을 삭제하고 실행하면 된다.

1,000,000 row를 입력할 두개의 테이블을 생성

create table &table_name
as select * from big_table.big_table
where 1=0
테이블만 만들었지 행은 하나도 없다. 두가지 방법으로 행을 넣어보자.
위 스크립트를 실행하면 테이블 이름을 물어본다. 바인드 변수를 써보았다.(ㅋㅋㅋ)
테이블 이름을 t1이라고 입력하고 엔터치고 다시 /(재실행)하면 또 물어본다.
그럼 t2라고 입력하면 된다.(배운걸 써먹어 보았다.)

--첫 번째 방법--
runstats_pkg, rs_start를 호출
exec runstats_pkg.rs_start;

insert into t1
select * from big_table.big_table
where rownum <= 1000000;

commit;
--두 번째 방법--
exec runstats_pkg.rs_middle;

begin
  for x in (select * from big_table.big_table
                                where rownum <=1000000)
                 loop
                        insert into t2 values x;
                 end loop;
                 commit;
end;
/
report 생성
big_table유저에서 실행하면 에러가 발생한다. sys에서 실행하자.
exec runstats_pkg.rs_stop(1000000);

Mystat : 단순히 특정 통계정보의 시작값
--- mystat.sql ---
set echo off
set verify off
column value new_val V
define S="&1"
column name format a45

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
-- and lower(a.name) = lower('&S')
/
set echo on

mystat2.sql은 차이를 보여준다. &V는 mystat.sql쿼리에서 조회한 마지막 값이다.
--- mystat2.sql ---
set echo off
set verify off
column diff format a18
select a.name, b.value Value, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

예를 들면 update문에 의해 생성되는 redo크기를 볼수 있다.
@mystat "redo size"
set echo off

update big_table set owner = lower(owner)
where rownum <= 1000;

@mystat2
set echo off

NAME                                                                VALUE                    DIFF
-------------------------------------------------------------- ----------------------- ---------------------------
redo size                                                  893290216      893,235,168
redo size for lost write detection                          0               -55,048
redo size for direct writes                             55048                          0
전후의 리두 사이즈의 차이이다.(11g에서는 이렇게 나온다.)

SHOW_SPACE
이 부분은 아무리 해도 에러가 발생한다. 어디가 문제인지 발견하지 못해서 걍 넘긴다.;;;
차후에 원인을 찾으면 수정하겠다.

sys유저든 big_table유저든 show_space.sql파일을 실행하고
exec show_space('BIG_TABLE');
위 문장을 실행하면 다음과 같은 에러 메세지가 뜬다.
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "BIG_TABLE.SHOW_SPACE", line 44
ORA-06512: at line 1
에러 해결은 다음으로 넘긴다.(해결방법 아시는분은 제보(?) 바란다.ㅋ)
이로써 환경설정은 모두 마친다.
시간 관계상 1장은 넘기고 2장으로 간다. 그럼 2장에서 보자.ㅎㅎㅎ