[Performance Tuning]Performance Tuning

Performance Tuning

실행계획 확인하는 방법

• explain plan  : plan_table에 저장  

SCOTT@orcl2> explain plan set statement_id = 'test01'

             into plan_table for

             select * from emp

             where job = 'CLERK';

            

-- plan 테이블을 식별하기 위한 표시 test01

-- plan_table은 temporary table로 만들어져 있다

SCOTT@orcl2> select * from table(dbms_xplan.display);

-- temporary table을 보기 좋게 보여준다.

<<temporary table을 영구적으로 만들기>>

SCOTT@orcl2> @$ORACLE_HOME/rdbms/admin/utlxplan

-- SCOTT 유저에 plan_table이 만들어진다. 단 이 테이블은 SCOTT의 소유의 temporary table이 아닌 영구적인 테이블이다.

SCOTT@orcl2> explain plan set statement_id = 'test01'

             into plan_table for

             select * from emp

             where deptno = 10;

SCOTT@orcl2> explain plan set statement_id = 'test02'

             into plan_table for

             select * from emp

             where job = 'CLERK';

SCOTT@orcl2> select * from table(dbms_xplan.display(null, 'test01', 'basic'));

-- 'advanced' = 'all' + outline

-- 'all' = 'typical' + Query Block Name / Object Alias + Column Projection Information

-- 'typical' = 'basic' + Predicate Information +(Rows, Bytes, Cost, Time)

-- 'basic' = 아주 기초

• autotrace
• sql 실행
• 실행계획
• statistics 
• plustrace role 이 필요하다
• @$ORACLE_HOME/sqlplus/admin/plustrce.sql
• 위의 세개를 전부 보기 위해서는 set autot on 명령어 사용
• 실행계획만을 보려면 set autot traceonly explain 사용
• statistics만을 보려면 set autot traceonly statistics 사용

SYS@orcl2> create user u01 identified by u01;

SYS@orcl2> grant connect, resource to u01;

                    

U01@orcl2> set autot on

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

-- statistics가 필요하므로 에러가 발생

                    

U01@orcl2> set autot traceonly explain                    

-- 에러가 발생하지 않는다.

-- 실행계획만 보여준다 (statistics는 없어도 된다.)                    

-- plustrace ROLE이 필요없다.

U01@orcl2> select * from dual;

Execution Plan

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

-- plustrace 권한을 만들어준다.

SYS@orcl2> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SYS@orcl2> grant plustrace to scott, u01;

Grant succeeded.

U01@orcl2> set autot on

U01@orcl2> select * from dual;

D

-

X

Execution Plan

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        418  bytes sent via SQL*Net to client

        420  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SCOTT@orcl2> select * from emp;

0  sorts (memory)

SCOTT@orcl2> select * from emp order by empno;

1  sorts (memory)

• v$sql_plan
• LC의 plan
• alter session set statistics_level =  '   ' : 세션레벨에서 실행
• alter system set statistics_level = '   ' : 시스템레벨에서 실행
• 전체 성능문제 발생 가능성 있으므로 추천하지 않음.
• /*+ gather_plan_statistics  */ : 문장 level에서 실행 
• all : 실행통계(time, buffers...)
• typical(기본값) : 모든 주요통계, advisor의 사용
• 권한
• grant select on v_$session to hr
• grant select on v_$sql to hr
• grant select on v_$sql_plan_statistics_all to hr
• 실행 계획 보기
• dbms_xplan.display_cursor('sql_id', 'child_number', 'option')
• sql_id : v$sql
• child_number : 버전이라고 생각하자.
• option : 어떠한 format으로 데이터를 볼것인가?
• 'advanced' = 'all' + outline
• 'all' = 'typical' + Query Block Name / Object Alias + Column Projection Information
• 'typical' = 'basic' + Predicate Information +(Rows, Bytes, Cost, Time) =>DEFAULT
• 'basic' = 아주 기초
• 'lostats' + 'memstats' => allstats
• sql_id와 child_number가 null 일경우 직전에 실행된 sql 문장이 분석됨
• dbms_xplan.display_cursor('null', 'null', 'allstats last -rows +partition +cost +predicate')
• AWR(Automatic Workload Repository)
• dbms_xplan.display_awr

1. SQL 실행

USER01@orcl2> select  /* example */ * from employees join departments using(department_id);

2. V$SQL_TEXT를 query하여 SQL_ID를 얻습니다.

USER01@orcl2> select sql_id, sql_text from v$SQL

              where sql_text

              like '%example%';

              

SQL_ID        SQL_TEXT

------------- --------------------------------------------------------------------------------

abpjv3k3f4vzc select  /* example */ * from employees join departments using(department_id)

3. SQL_ID를 사용하여 이 명령문이 DBA_HIST_SQLTEXT 딕셔너리 뷰에 캡처되었는지확인합니다.

Query 시 행이 반환되지 않는다면 명령문이 아직 AWR에 로드되지 않은것입니다.

USER01@orcl2> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID ='abpjv3k3f4vzc';

no rows selected         

                

4. 매시간마다 발생하는 다음 번 스냅샷을 기다리지 않고 수동으로 AWR 스냅샷을 생성할수 있습니다.

그런 다음 DBA_HIST_SQLTEXT에서 캡처되었는지 확인합니다.

USER01@orcl2> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

USER01@orcl2> select snap_id, dbid, instance_number, startup_time

              from dba_hist_snapshot

              order by snap_id desc, startup_time desc ;

-- 히스토리 이력을 확인              

              

USER01@orcl2> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_ID = 'abpjv3k3f4vzc';

SQL_ID SQL_TEXT

-------------- -------------------------------

abpjv3k3f4vzc select /* example */ * from …

              

              

USER01@orcl2> SELECT SQL_ID, SQL_TEXT FROM dba_hist_sqltext WHERE SQL_TEXT like '%example%';

5. DBMS_XPLAN.DISPLAY_AWR () 함수를 사용하여 실행 계획을 검색합니다.

USER01@orcl2> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('abpjv3k3f4vzc'));

• SQL  Monitoring --11g
• 1초 간격으로 병렬로 실행되는 SQL 문장 및 5초 이상 CPU, I/o 시간을 소모하는 SQL 문장 자동 수집, 또는 명시적으로 Monitor 힌트

SER01@orcl2> show parameter control_management

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_management_pack_access       string      DIAGNOSTIC+TUNING

#session1

USER01@orcl2> select /*+ monitor */ * from sales s natural join customers c;

#session2(위 문장을 실행중)

SYS@orcl2> show long

long 80

-- long이 충분한 크기로 있어야 볼 수 있다.

SYS@orcl> set long 10000

SYS@orcl2> select dbms_sqltune.report_sql_monitor from dual;

Global Information

------------------------------

Status              :  EXECUTING

Instance ID         :  1

Session             :  USER01 (144:43)

SQL ID              :  fyg8uk9uqpyps

SQL Execution ID    :  16777217

Execution Started   :  05/17/2019 14:49:21

First Refresh Time  :  05/17/2019 14:49:21

Last Refresh Time   :  05/17/2019 14:49:59

Duration            :  53s

Module/Action       :  SQL*Plus/-

Service             :  SYS$USERS

Program             :  sqlplus@edydr1p0.us.oracle.com (TNS V1-V3)

Fetch Calls         :  25496

Global Stats

===========================================================================

| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |

| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |

===========================================================================

|    4.56 |    3.77 |     0.00 |     0.78 | 25496 |  28680 |   38 |  15MB |

===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=2056508761)

================================================================================

======================================================================

| Id   |      Operation       |   Name    |  Rows   | Cost |   Time    | Start

| Execs |   Rows   | Read | Read  | Mem | Activity | Activity Detail |

|      |                      |           | (Estim) |      | Active(s) | Active

|       | (Actual) | Reqs | Bytes |     |   (%)    |   (# samples)   |

================================================================================

======================================================================

| -> 0 | SELECT STATEMENT     |           |         |      |        39 |     +0

|     1 |     382K |      |       |     |    33.33 | Cpu (2)         |

| -> 1 |   HASH JOIN          |           |    919K | 3931 |        39 |     +0

|     1 |     382K |      |       | 14M |    66.67 | Cpu (4)         |

|    2 |    TABLE ACCESS FULL | CUSTOMERS |   55500 |  405 |         1 |     +0

|     1 |    55500 |      |       |     |          |                 |

| -> 3 |    TABLE ACCESS FULL | SALES     |    919K | 1232 |        39 |     +0

|     1 |     382K |   38 |  15MB |     |          |                 |

================================================================================

======================================================================

• sql trace
• 기본적으로 sp의 id로 이름이 정해진다.
• v$diag_info에서 경로를 찾을 수 있다

USER01@orcl2> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')

----------------------------------------------------------------------

144

--자신의 세션을 알아낼 수 있다.

         

          

USER01@orcl2> alter session set sql_trace=true;

-- trace 활성화 시작점

또는

USER01@orcl2> exec dbms_session.set_sql_trace(true);

PL/SQL procedure successfully completed.

-- 패키지를 이용해서 trace 생성 시작점

USER01@orcl2> select * from v$diag_info;

   INST_ID NAME                  VALUE

---------- --------------------- -----------------------------------------------------------------

         1 Diag Enabled          TRUE

         1 ADR Base              /u01/app/oracle

         1 ADR Home              /u01/app/oracle/diag/rdbms/orcl2/orcl2

         1 Diag Trace            /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace

         1 Diag Alert            /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert

         1 Diag Incident         /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident

         1 Diag Cdump            /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump

         1 Health Monitor        /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm

         1 Default Trace File    /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_9193.trc

         1 Active Problem Count  0

         1 Active Incident Count 0

USER01@orcl2> select * from emp;

USER01@orcl2> /

USER01@orcl2> /

USER01@orcl2> /

USER01@orcl2> /

USER01@orcl2> select * from emp where deptno = 10;

USER01@orcl2> select count(*) from sales s, customers c where s.cust_id = c.cust_id;

USER01@orcl2> alter sessionset sql_trace=false;

-- trace 비활성화 시점

또는 패키지를 사용했다면

USER01@orcl2> exec dbms_session.set_sql_trace(false);

-- 종료지점

USER01@orcl2> !cp -av /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_9193.trc test.trc

USER01@orcl2> !tkprof test.trc test.out sys=no aggregate=no sort=execpu print=10 explain=user01/oracle

USER01@orcl2> !vi test.out

--볼 수 있다.