DevStarSJ.github.io 으로 이사했습니다.: Oracle

레이블이 Oracle인 게시물을 표시합니다. 모든 게시물 표시

2016년 2월 21일 일요일

Oracle Plan, Trace 읽는 법

SQL Tuning을 하기 위해서 가장 기본으로 알아야 하는 것이 Plan과 Trce를 읽는 방법입니다.
그래야 어느 곳이 비효율적인지를 알아내서 그 부분을 중심으로 Tuning 전략을 세울 수 있습니다.

아래 내용중 SELECT Tuning시 중요하게 봐야할 사항은 다음과 같습니다.

실행 순서 : Plan, Trace 동일
Trace 결과 : Row Source Operation의 각 수치들의 의미 (rows, cr만 알아도 됩니다.)

1. Plan 읽는 법

Plan은 SQL을 실행하기 전에 Optimizer에 의해 선택된 최적의 실행 경로 및 계산되어진 예상 Cost를 보여줍니다.

*************************[Explain Plan Time: 2016/02/21 14:01:15]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=5 Bytes=325)
   1    0   SORT (ORDER BY) (Cost=9 Card=5 Bytes=325)
   2    1     UNION-ALL
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=5 Card=1 Bytes=65)
   5    4           SORT (ORDER BY STOPKEY) (Card=1 Bytes=14)
   6    5             FILTER
   7    6               TABLE ACCESS (FULL) OF 'BBS' (TABLE) (Cost=3 Card=10 Bytes=140)
   8    2       VIEW (Cost=4 Card=4 Bytes=260)
   9    8         SORT (ORDER BY) (Cost=4 Card=4 Bytes=56)
  10    9           COUNT (STOPKEY)
  11   10             TABLE ACCESS (FULL) OF 'BBS' (TABLE) (Cost=3 Card=5 Bytes=70)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   3 - filter(ROWNUM<=4)
   5 - filter(ROWNUM<=4)
   6 - filter(NULL IS NOT NULL)
   7 - filter("NUM"<10)
  10 - filter(ROWNUM<=4)
  11 - filter("NUM">10)
-----------------------------------------------------------

1.1 실행순서 (access path)

sibling 사이에서는 먼저 나온 것을 먼저 처리
child가 있는 경우 child부터 다 처리하고 parent 처리하기

이 두가지만 기억하면 됩니다.

위 Plan을 기준으로 처리 순서는 다음과 같습니다.
(0 ~ 11까지 있는 왼쪽의 Index를 사용하겠습니다.)

7 -> 6 -> 5 -> 4 -> 3 -> 11 -> 10 -> 9 -> 8 -> 2 -> 1 -> 0

(4) UNION-ALL 아래에 2개의 child(3,8)가 있습니다.
이 둘중 위에 있는 (3)부터 처리를 해야하는데 (3)은 child가 있으므로 가장 안쪽부터 처리합니다.
(3)의 child를 다 처리한 후에 자신의 sibling인 (8)을 처리해야하는데, (8)도 child가 있으므로 안쪽부터 처리합니다.
(3, 8)이 모두 처리된 후에 (2)부터 쭉 처리하면 됩니다.

1.2 예상 성능지표 (Cost-based Optimizer Mode에서만 표시)

Cost : Cost 예상 지수. 클수록 성능상 (CPU 점유, Disk I/O, 수행시간 등...) 안좋다는 의미입니다.
Card : (Computed Cardinality) : CBO상 계산된 예상되는 return row 입니다.
Bytes : return row의 byte수 입니다.

1.3 Predicate information

각 단계별 filter 조건이 어떻게 적용되었다는 정보를 보여줍니다.

2. Trace 읽는 법

Trace는 실제 실행된 경로와 그 성능상 중요 수치들을 보여줍니다.

Compile Time  : 2015/07/09 13:31:22
Trace File    : c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_11048.trc
Trace Version : 11.2.0.1.0
********************************************************************************

SELECT * FROM SCOTT.EMP
 WHERE DEPTNO = :"SYS_B_0"

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.001          0          0          0          0
Fetch        1    0.000        0.000          0          3          0          0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        3    0.000        0.001          0          3          0          0

Misses in library cache during parse   : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYS (ID=0)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
      0  TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=0 us cost=2 size=190 card=5

2.1 Row Source Operation 읽는법

상대적으로 중요한 Row Source Operation 읽는 법부터 소개해드리겠습니다.
Plan과 같은 형식으로 실행 경로를 보여 줍니다.
읽는 법은 Plan과 같으며 괄호 안에 나오는 성능지표 값을 해석하는 것이 중요합니다.

Rows (왼쪽) : return row 수 입니다. 해당 단계에서 몇건의 row가 결과로 return 되었는지 알려줍니다.
cr : (Consistent Mode Block Read) 총 읽은 block수 입니다. 의미상은 DB Buffer Cache에서 읽은 block수 이지만, disk상에서 읽은 경우에도 buffer로 먼저 올린 후에 읽어야 하므로 사실상 disk + cache에서 읽은 총 수라고 해석하면 됩니다.
pr : (Physical Disk Block Read) disk에서 읽은 block수 입니다.
pw : (Physical Disk Block Write) disk에 저장한 block수 입니다.
time : 소요시간 (microsecond 단위)
cost : cost (성능상)
size : data size
card : (cardinality)

2.2 Call Table

Tunning시 사실상 크게 볼 필요 없습니다.

Call
- Parse : Cursor를 Parsing하고 Execution Plan을 생성하는 단계
- Execute : Cursor를 실행하는 단계
- Fetch : 결과 Record를 Fetch하는 단계
Count : 수행 회수
CPU : CPU 사용시간
Elapsed : 수행시간 ( CPU time + Wait time )
Disk : Disk에서 읽은 Block수
Query : Consistent Mode에서 읽은 Block수 (Query 수행 시점에 읽은 읽기 전용 Block)
Current : Current Mode에서 읽은 Block수 (Table을 액세스하는 시점에 읽은 수정할 Block)
Rows : 읽거나 갱신한 처리 건수

참조 Slide

좀 더 자세한 사항을 알고 싶으시면 아래 Slide를 참고해주세요.

[Oracle Architecture][2015 04-17] Consistent vs Current : http://www.slideshare.net/seokjoonyun9/oracle-architecture2015-0417-consistent-vs-current

[Oracle Architecture][2015 04-17] Consistent vs Current from Seok-joon Yun

[2015-06-12] Oracle 성능 최적화 및 품질 고도화 1 : http://www.slideshare.net/seokjoonyun9/20150612-oracle-1

[2015-06-12] Oracle 성능 최적화 및 품질 고도화 1 from Seok-joon Yun

[2015-07-10-윤석준] Oracle 성능 관리 & v$sysstat : http://www.slideshare.net/seokjoonyun9/20150710-oracle-vsysstat

[2015-07-10-윤석준] Oracle 성능 관리 & v$sysstat from Seok-joon Yun

2015년 9월 5일 토요일

Python 과 Oracle 연동 #04 cx_Oracle : Bind Variable 사용하기

cx_Oracle.Cursor.execute( ) 함수에서 bind variable을 사용하는 방법에 대해 알아보겠습니다.
역시 Python 답게 아주 쉽습니다.
parameters 로 Dictionary를 전달할 경우에는 bind variable 이름과 값을 전달해 주면 되며,
parameters 로 List를 전달할 경우에는 bind variable의 순서대로 값을 전달해 주면 됩니다.

아래에 설명할 예제에 앞서 먼저 DB에 연결을 합니다.

import cx_Oracle

conn = cx_Oracle.connect('sys/orange@localhost:1521/orcl', mode = cx_Oracle.SYSDBA)

cursor = conn.cursor()

1. List를 이용한 bind variable 구현

>>> cursor.execute('SELECT * FROM SCOTT.EMP WHERE SAL >= :SAL AND DEPTNO = :DEPTNO' , [ 2000, 20 ])
<cx_Oracle.Cursor on <cx_Oracle.Connection to sys@localhost:1521/orcl>>

>>> print(cursor.fetchall())
[(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20),
(7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20),
(7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)]

2. Dictionary를 이용한 bind variable 구현

>>> cursor.execute('SELECT * FROM SCOTT.EMP WHERE SAL >= :SAL AND DEPTNO = :DEPTNO' , { 'SAL' : 2000 , 'DEPTNO' : 20 })

<cx_Oracle.Cursor on <cx_Oracle.Connection to sys@localhost:1521/orcl>>

>>> print(cursor.fetchall())
[(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20),
(7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20),
(7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)]

3. Bulk insert

cx_Oracle.Cursor.executemany( ) 함수를 이용하여 bulk insert 구현이 가능합니다.
하나의 statement에 bind variable을 List에 담아서 전달하면 됩니다.
각각의 row는 Tuple로 만들어서 List에 담습니다.

먼저 Test로 사용할 Table을 생성합니다.

CREATE_SCRIPT = """
CREATE TABLE SCOTT.TESTPY (
    ID    INT,
    NAME  VARCHAR(32)
)
"""
cursor.execute(CREATE_SCRIPT)

Bind varialbe을 List로 생성합니다.

ARRAY = []
for i in range(0,10): ARRAY.append((i, 'TEST' + str(i)))

이제 bulk insert를 실행 후 결과를 확인해보겠습니다.

cursor.prepare('INSERT INTO SCOTT.TESTPY VALUES ( :1, :2 )')
cursor.executemany(None, ARRAY)
cursor.execute('SELECT * FROM SCOTT.TESTPY')

>>> print(cursor.fetchall())
[(0, 'TEST0'), (1, 'TEST1'), (2, 'TEST2'), (3, 'TEST3'), (4, 'TEST4'), (5, 'TEST5'), (6, 'TEST6'), (7, 'TEST7'), (8, 'TEST8'), (9, 'TEST9')]

cx_Oracle.Cursor.executemany( )에 statement도 같이 전달하는 방법도 있습니다.
일단 값들을 삭제 한 후 시도해 보겠습니다.

>>> cursor.execute('DELETE SCOTT.TESTPY')
>>> print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
[]

>>> cursor.executemany('INSERT INTO SCOTT.TESTPY VALUES ( :1, :2 )', ARRAY)
>>> print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
[(0, 'TEST0'), (1, 'TEST1'), (2, 'TEST2'), (3, 'TEST3'), (4, 'TEST4'), (5, 'TEST5'), (6, 'TEST6'), (7, 'TEST7'), (8, 'TEST8'), (9, 'TEST9')]

* 예제 Code

2015년 9월 4일 금요일

Python 과 Oracle 연동 #03 cx_Oracle : Cursor Object 및 SQL 실행하기

앞 장에서 cx_Oracle.connect() 함수를 이용하여 Oracle에 접속하는 방법에 대해서 살펴보았습니다.
그 결과 Connection Object를 생성하였습니다.

Connection Object에서 SQL문을 실행하기 위해서는 Cursor Object를 생성해야 합니다.
Connection.cursor() 함수를 실행하여 얻을 수 있습니다.

import cx_Oracle

conn = cx_Oracle.connect('sys/orange@localhost:1521/orcl', mode = cx_Oracle.SYSDBA)

cursor = conn.cursor()

순서상 Connection Object에 대한 설명이 먼저 와야 하지만, 기본적인 활용 단계에서는 자세히 살펴보지 않아도 됩니다.

Transaction을 제어, logon password 변경, shutdown database, cache size 변경 등의 작업을 할 경우에는 Connection Object에 대해서 아셔야 합니다만,
SQL 실행에 대해서는 몰라도 상관없습니다.

Connection Object에 대한 설명은 아래 Link에서 문서를 참조하시기 바랍니다.

http://cx-oracle.readthedocs.org/en/latest/connection.html

* Cursor Object

Connection Object 에서 cursor() 함수를 이용하여 선언이 가능합니다.
일반적으로는 1개의 cursor를 계속 해서 반복 사용하는게 보통입니다.
큰 project에서는 여러개의 cursor를 각각 선언하여 사용하기도 합니다.
cursor 별로 다른 query 결과를 담아두고 필요에 따라 fetch 하면서 사용하는 식의 process를 생각하시면 여러 cursor 의 필요성을 충분히 생각하실 수 있을 것입니다.

Cursor Object 의 기능 중 다음 3가지 가장 중요합니다.
일단 저정도만 알아도 Oracle에 대한 작업에는 큰 지장이 없습니다.

1. Parse

cx_Oracle.Cursor.parse([statement])

중요하다고 바로 위에서 얘기했지만, 솔직히 몰라도 상관없는 기능이긴 합니다.
통상적으로는 execute() 실행시 자동으로 parse 단계를 거치기 때문에 잘 사용되지 않습니다만,
실행전에 statement를 validate 할 용도로 사용 할 수 있습니다.
만약 statement에 error가 있다면

ORA-00900 : invalid SQL statement,
ORA-01301 : insufficient privileges
ORA-00921 : unexpected end of SQL command

등의 DatabaseError exception을 야기시킵니다.

2. Execute

cx_Oracle.Cursor.execute(statement, parameters)

statement 하나만 인자로 받은 경우에는 해당 statement를 실행합니다.
binding variable을 사용할 경우에는 parameters도 같이 입력을 해 주어야 합니다.
parameters 로 Dictionary를 전달할 경우에는 bind variable 이름과 값을 전달해 주면 되며,
parameters 로 List를 전달할 경우에는 bind variable의 순서대로 값을 전달해 주면 됩니다.
return 값이 있는 경우에는 List로 결과값들을 받습니다.
(binding variable 에 대해서는 다음 post에서 설명드리겠습니다.)

3. Fetch

Query 에서만 사용가능한 기능입니다. 왜냐면 DDL 과 DCL은 return 값이 없기 때문입니다.
cursor 가 query를 실행시키지 않은 경우에 사용하게 되면 InterfaceError exception 을 발생시킵니다.

cx_Oracle.Cursor.fetchall()
남아있는 모든 Row 들을 Tuple 들의 List로 return 합니다.

cx_Oracle.Cursor.fetchmany([rows_no])
인자로 전달한 수 만큼의 row를 return 합니다.

cx_Oracle.Cursor.fetchone()
하나의 row를 tuple로 return 합니다.

Cursor Object에 대해서 좀 더 자세히 알고 싶으시면 아래 Link를 참고하세요.

http://cx-oracle.readthedocs.org/en/latest/cursor.html

* 예제

* 결과

>>> print(cursor.fetchone())
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)

>>> print(cursor.fetchone())
(7499, 'ALLEN', 'SALESMAN', 7698, datetime.datetime(1981, 2, 20, 0, 0), 1600.0, 300.0, 30)

>>> print(cursor.fetchmany(2))
[(7521, 'WARD', 'SALESMAN', 7698, datetime.datetime(1981, 2, 22, 0, 0), 1250.0, 500.0, 30), (7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20)]

>>> print(cursor.fetchall())
[(7654, 'MARTIN', 'SALESMAN', 7698, datetime.datetime(1981, 9, 28, 0, 0), 1250.0, 1400.0, 30), (7698, 'BLAKE', 'MANAGER', 7839, datetime.datetime(1981, 5, 1, 0, 0), 2850.0, None, 30), (7782, 'CLARK', 'MANAGER', 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None, 10), (7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20), (7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0, None, 10), (7844, 'TURNER', 'SALESMAN', 7698, datetime.datetime(1981, 9, 8, 0, 0), 1500.0, 0.0, 30), (7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1983, 1, 12, 0, 0), 1100.0, None, 20), (7900, 'JAMES', 'CLERK', 7698, datetime.datetime(1981, 12, 3, 0, 0), 950.0, None, 30), (7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20), (7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0), 1300.0, None, 10)]

>>> for record in cursor: print(record)
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)
(7499, 'ALLEN', 'SALESMAN', 7698, datetime.datetime(1981, 2, 20, 0, 0), 1600.0, 300.0, 30)
(7521, 'WARD', 'SALESMAN', 7698, datetime.datetime(1981, 2, 22, 0, 0), 1250.0, 500.0, 30)
(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20)
(7654, 'MARTIN', 'SALESMAN', 7698, datetime.datetime(1981, 9, 28, 0, 0), 1250.0, 1400.0, 30)
(7698, 'BLAKE', 'MANAGER', 7839, datetime.datetime(1981, 5, 1, 0, 0), 2850.0, None, 30)
(7782, 'CLARK', 'MANAGER', 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None, 10)
(7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20)
(7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0, None, 10)
(7844, 'TURNER', 'SALESMAN', 7698, datetime.datetime(1981, 9, 8, 0, 0), 1500.0, 0.0, 30)
(7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1983, 1, 12, 0, 0), 1100.0, None, 20)
(7900, 'JAMES', 'CLERK', 7698, datetime.datetime(1981, 12, 3, 0, 0), 950.0, None, 30)
(7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)
(7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0), 1300.0, None, 10)

>>> print(cursor.execute('SELECT * FROM SCOTT.TESTPY').fetchall())
[(10, 'LUNA')]

DevStarSJ.github.io 으로 이사했습니다.

페이지

Post List

2016년 2월 21일 일요일

Oracle Plan, Trace 읽는 법

Oracle Plan, Trace 읽는 법

1. Plan 읽는 법

1.1 실행순서 (access path)

1.2 예상 성능지표 (Cost-based Optimizer Mode에서만 표시)

1.3 Predicate information

2. Trace 읽는 법

2.1 Row Source Operation 읽는법

2.2 Call Table

참조 Slide

2015년 9월 5일 토요일

Python 과 Oracle 연동 #04 cx_Oracle : Bind Variable 사용하기

2015년 9월 4일 금요일

Python 과 Oracle 연동 #03 cx_Oracle : Cursor Object 및 SQL 실행하기