[Oracle] 오라클에서 수집하는 통계정보

봄·2025년 9월 8일
0

오라클 관리

목록 보기
108/163

오라클에서 수집해야하는 통계정보 2가지

  1. 옵티마이져 통계정보: 테이블 통계정보
  2. 시스템 통계정보 : 컴퓨터의 사양과 성능 정보

테이블 통계정보를 수집하지 않는다면?

  • 옵티마이져가 좋은 실행계획을 만들어 낼 수 없음

오라클은 기본값으로 매일밤 10시에 테이블 통계정보를 자동으로 수집함. 그런데 현업 dba 중 이 기능을 끄는 사람들이 종종 있음

[실습1] scott 이 가지고 있는 테이블들에 대해서 통계정보가 언제 수집되었는지 확인하시오

select table_name, num_rows, last_analyzed
  from  dba_tables
  where owner='SCOTT';

[실습2] SCOTT 이 가지고 있는 테이블들에 대해서 통계정보를 수집하시오

exec dbms_stats.gather_schema_stats('SCOTT');

select table_name, num_rows, last_analyzed
  from  dba_tables
  where owner='SCOTT';

[실습3] 매일 밤 10시에 테이블 통계정보가 자동으로 수집되는지 확인하시오

select w.window_name, w.repeat_interval, w.duration
     , cast(w.last_start_date as timestamp with local time zone) last_start_date
     , cast(w.next_start_date as timestamp with local time zone) next_start_date
 from   dba_scheduler_wingroup_members m
     , dba_scheduler_windows w
where  m.window_group_name = 'MAINTENANCE_WINDOW_GROUP'
and    w.window_name = m.window_name;

--> 월~금 까지는 매일밤 10시에 수집, 토요일과 일요일은 아침 6시에 수집

[실습4] 오라클에 어떤 기능이 매일 자동으로 수행되는지 확인하시오

select client_name, status
 from dba_autotask_client;


💡 매일밤 10시에 테이블 통계정보를 자동으로 수집하고
비어있는 공간에 대해 인접한 빈 공간을 자동으로 합치고
하루동안 발생했던 악성 SQL 중 가장 느린 10개를 자동으로 튜닝해버림

[실습5] SQL튜닝 어드바이져를 이용해서 SQL을 튜닝하시오

■  수동 생성 방법 실습

-- scott으로 test 테이블 생성

SQL> connect scott/tiger

SQL> create table test (n number );

SQL> declare
begin
for  i  in  1 .. 10000  loop
insert into test values(i);
	commit;
end loop;
end;
/

-- 인덱스 생성
SQL> create index test_idx on test(n);


-- test 테이블 통계정보 분석

SQL> analyze table test estimate statistics;


-- NO_INDEX 힌트를 주어 풀테이블 스캔으로 수행되는 SQL확인

-- 실행계획만 보겠다고 지정합니다.
SQL> set autot traceonly explain

SQL> select /*+ NO_INDEX(test test_idx) */ *  from test  where n = 1 ;

-- 튜닝 TASK생성 후, SQL Tuning Advisor를 실행

SQL> connect / as sysdba

# 안되면 task를 drop  하고 수행합니다.
SQL> exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_1');


SQL> 
declare
         my_task_name    VARCHAR2(30);
         my_sqltext         CLOB; 
begin
         my_sqltext := 'select /*+ no_index(test test_idx) */ *
                              from test where n = 1';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                          sql_text => my_sqltext,
                          user_name => 'SCOTT',
                          scope => 'COMPREHENSIVE',
                          time_limit => 60,
                          task_name => 'my_sql_tuning_task_1',
                         description => 'Task to tune a query on a specified table' );
end;
/


SQL> 
begin
   DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'my_sql_tuning_task_1' );
end;
/


-- SQL Tuning Advisor을 통해 얻은 결과(튜닝 레포트)를 확인하고 SQL Profile을 적용

SET LONG 70000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SET PAGES 4000

select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') 
        from  DUAL;


-- SQL profile 을 받아들여서 보다 더 좋은 실행계획으로 변경을 하는 작업 

DECLARE
   my_sqlprofile_name VARCHAR2(30);
BEGIN
   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name => 'my_sql_tuning_task_1',
    name => 'my_sql_profile' );
END;
/


-- 위에서 실행한 악성 SQL의 실행계획을 확인


SQL> conn scott/tiger
SQL> set autot traceonly explain
SQL> select /*+ NO_INDEX(test test_idx) */ * from test where n = 1;


 *  sql profile drop 방법
 
exec dbms_sqltune.drop_sql_profile('test_prof', true)


 *  sql tuning task  drop 방법



select task_name
 from  user_ADVISOR_TASKS;


exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_1');

* sql tuning task  drop   프로시져


set serveroutput on
declare
          not_drop EXCEPTION;
          PRAGMA EXCEPTION_INIT(not_drop, -27365);

 cursor c1 is
select task_name
 from  user_ADVISOR_TASKS;
begin
for emprec  in c1 loop
 dbms_sqltune.drop_tuning_task(task_name=>emprec.task_name);
 end loop;
commit;
exception
when not_drop then
  dbms_output.put_line('not not not');
end;
/


scott에서 악성 sql의 실행계획을 확인해보니 자동변경됨

문제1. 아래의 환경을 만들고 아래의 SQL을 튜닝하시오

SCOTT 유져에서 수행

[oracle@ora19c oradata]$ cd
[oracle@ora19c ~]$
[oracle@ora19c ~]$ pwd
/home/oracle
[oracle@ora19c ~]$ sqlplus scott/tiger

SQL*Plus: Release 19.0.0.0.0 - Production on 월 9월 8 11:22:37 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

마지막 성공한 로그인 시간: 월 9월  08 2025 11:22:21 +09:00

다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SCOTT @ ora19dw > @demo

세션이 변경되었습니다.


테이블이 삭제되었습니다.

drop table dept
           *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다



테이블이 생성되었습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


테이블이 생성되었습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


커밋이 완료되었습니다.

SCOTT @ ora19dw >
SCOTT @ ora19dw > create index emp_empno  on emp(empno);

아래의 SQL을 튜닝 어드바이저를 쓰지 말고 튜닝하시오

SELECT  /*+ no_index(emp emp_empno) */ empno, ename, sal
  from emp
  where empno = 7788;

튜닝 후:

SELECT  /*+ index(emp emp_empno) */ empno, ename, sal
  from emp
  where empno = 7788;

문제1. SQL Gate 툴을 다운로드 받고 오라클에 접속하세요.

sql gate 다운

문제2. 위의 SQL튜닝 어드바이져로 SQL을 튜닝 하시오

문제3. 아래의 SQL을 튜닝하시오

select /*+ leading(e d) use_nl(d) */  e.ename, d.loc
 from  emp  e, dept  d
 where  e.deptno = d.deptno;

문제4. 아래의 환경을 만들고 아래의 SQL을 튜닝 어드바이져로 튜닝하고 SQL 프로파일을 적용해서 SQL은 고치지 말고 최적의 실행계획으로 실행계획을 출력하시오

@demo
create  index  emp_ename  on  emp(ename);

select /*+ no_index(emp emp_ename) */ ename, sal, deptno
  from emp
  where ename='SCOTT';

튜닝 후:

select /*+ index(emp emp_ename) */ ename, sal, deptno
  from emp
  where ename='SCOTT';

문제5. 지금까지 만들었던 SQL 프로파일을 DROP 하시오

SELECT *
 from DICTIONARY
 WHERE TABLE_NAME LIKE '%SQL%PROFILE%';
 
select *
 from dba_sql_profiles;
 
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01992742e1660000', true);


exec dbms_sqltune.drop_sql_profile('my_sql_profile', true);

[실습6] 위의 3개의 자동화 기능을 모두 끄시오

select  client_name,  status
  from  dba_autotask_client; 
    
begin 
    dbms_auto_task_admin.disable(
          client_name=> 'sql tuning advisor',
          operation => null,
          window_name=> null);
end;
/
profile
봄
이전 포스트

[Oracle] 오라클 자동화 기능 관련 용어

다음 포스트

[Oracle] 옵티마이저 통계정보 수집

0개의 댓글