Chap. 13  Query Processing

*Textbook: Chapter 12(6판), Chapter 15(7판)

Overview

1. Translation of a high-level SQL query

⇒ Internal ‘relational algebra’ expression

2. Query optimization: The most efficient execution plan

⇒ Performance in terms of the no. of disk accesses required

⇒ The difference between a 'bad' strategy and a 'good' strategy

3. Execution of a query: Query output

Ex) p.501 Fig.13-1

∙Query-execution plan

- Parse-tree representation

- Annotated instruction

Ex) p.539 Fig.14-1

π고객명 [σ고객지점명=‘Brooklyn'  (지점 ∞ (계좌 ∞ 예금)) ]

⇒ π고객명 [ (σ고객지점명=‘Brooklyn' 지점) ∞ (계좌 ∞ 예금)) ]  ; Optimization

▪

EX) Memory Structures and Processes of Oracle

D000    Dispatcher

$ sqlplus

① shared pool - 'program'

□Memory에 존재하는 SQL 영역

□모든 SQL문은 이곳에서 수행됨. (parsing/optimization/execution/fetch)

□User가 입력한 SQL문은 이 곳에 저장되며, Oracle server process는 memory에 있는 SQL문을 실행함.

② database buffer cache - 'data'

□Server process가 data를 이용할 수 있도록 data block을 저장하는 memory 영역.

□Oracle은 가장 최근에 사용된 block을 이 곳에 저장함.

□할당된 memory block이 이미 꽉 차 있을 때, 새로운 block이 필요하면 least recently used block을 사용함.

⇒ LRU algorithm

□SGA 영역 중 가장 큼.

③ redo log buffer

□data recovery에 이용되는 영역.

□모든 trans.은 redo log file에 기록되기 전에 이 곳에 먼저 저장됨.

□LGWR은 정기적으로 이 buffer의 내용을 redo log file에 옮김.

④ large Pool

□Backup/Restore Operation, I/O Server Process, Multi-thread Server를 위한 'Session' Memory 영역.

⑤ cursor

□Oracle program의 Statement Handler가 이용.

⑥ redo log file

□Transaction들에 의해 DB에 일어나는 모든 변경들을 기록

□이 log를 사용하여 DB의 recovery가 가능

Ex) Server process의 SQL문 처리: Parse, Execute, Fetch의 세 단계로 처리됨.

① Parse:

□SQL문장의 처음부터 끝까지 조사: SELECT, FROM, WHERE 같은 예약어, 괄호, 쉼표 등 문법적으로 맞는가를 확인.

□ 테이블명, 컬럼명 같은 오브젝트가 실제로 DB에 존재하는지 확인.

□ 실행계획: Rule-based/Cost-based Optimization

□가장 시간이 많이 걸리며, shared pool을 이용하면 중복을 피할 수 있음.

② Execution

□ 실행계획을 수행.

□ I/O가 일어나며 lock을 발생시킴.

③ Fetch

□추출 조건을 만족하는 행을 추출하여 User process에게 넘겨줌.

□정렬도 이 단계에서 수행됨. (ORDER BY)

Query Optimization

▪Overview

Step 1: Generating Equivalent Expressions

Step 2: Choosing an Optimized Query-Execution Plan

▪Generating Equivalent Expressions

- A Query can be expressed in several different ways.

⇒ Given an expression, we consider alternative and 'equivalent' expressions.

⇒ Different 'costs'

Ex) p.419, Fig12-12

- Two steps

① Generating 'logically equivalent' expressions to the given expression.

⇒ By using equivalence 'rules'.

Ex) p.420～p.422: Rule 1 ～ Rule 12

② 'Annotating' the result to generate alternative query plans.

⇒ In addition to alternative expressions, alternative 'algorithms' for each operation must also be considered.

Ex) p.426, Fig.12-15

pipelining - Execute several operations simultaneously in a pipeline fashion with the results of one operation passed on to the next without 'writing' the temporary results in temporary files.

▪Choosing an Optimized Query-Execution Plan

- How many alternative query plans can be generated?

⇒ It is not necessary to generate 'every' possible expression using the equivalence rules.

Ex) Joining n relations

⇒ (2(n-1))！/(n-1)！join orderings

e.g, When n=3, R∞S∞T, 4！/2！=12 ways

n=5, 1680 ways

n=7, 665280 ways

Ex) (R∞S∞T)∞U∞V

① Evaluate 12 join orders of (R∞S∞T), and find the optimal order, e.g., (R∞T)∞S.

② Evaluate another 12 join ordering of {(R∞T)∞S)}∞U∞V

⇒ The total no. of ordering = 12 + 12 = 24

⇒ Dynamic-Programming Algorithm: Keep on finding optimals of subsets and find an optimal of the entire set in the end.

- Two methods

① Heuristic optimization: Use the 'heuristics (rules)' to reduce the no. of choices.

② Cost-based optimization: Evaluated the costs of selected plans using 'cost estimation models' and choose the one with the 'least' cost.

⇒ Most practical optimizers in commercial DBMSs combine elements of both approaches.

Ex) Oracle optimizer

①Rule-based: Use heuristics

□Perform 'selections' as early as possible.

□Perform 'projections' early.

□etc.

②Cost-based: Then, use cost estimation models for a full analysis of cost.

▪Performance

- Even with heuristics, cost-based optimizer imposes a 'substantial overhead' on query processing.

- But, the difference in disk accesses bet. a 'good' plan and a bad one may be huge, and thus, query optimization is essential.

- When one query is optimized once, the execution plan for the query can be used on each run.

Ex) 'shared pool' in Oracle SGA

Cost Estimation

▪Optimizers make use of 'statistical' information to make a good 'estimate' of the cost of a plan ⇒ Stored in the system catalog

Ex) relation sizes, index depths and so on.

▪Relevant parameters

- NR: the no. of tuples in the relation R

- SR: the size of a tuple of R in 'bytes'

- FR: the blocking factor of R (i.e., the no. of tuples of R in one block)

- BR: the no. of blocks containing R

Ex) BR = ⌈NR/FR⌉

- V(A,R): the no. of 'distinct' values in R for attribute A (= the size of ∏AR)

Ex) V(A,R)=NR if A is a key

- SC(A,R): the selection cardinality of A of R.

i.e., the 'average' no. of tuples that satisfy an equality condition

Ex) SC(A,R)=1 if A is a key

SC(A,R)=NR/V(A,R) if A is a nonkey attribute & uniformly distributed.

▪Index parameters

- fI: the average fan-out of internal nodes of tree-structured index I

P1	K1	P2	K2	P3	……	Pn-1	Kn-1	Pn

⇒ A internal node contains m pointers. (⌈n/2⌉≤m≤n)

- HTI: the no. of levels in I (i.e., the height of I)

Ex) HTI = ⌈logfI V(A,R)⌉ for a B+-tree index

HTI = 1 for a hash index

- LBI: the no. of blocks at the leaf-level of the index I

▪Estimate the cost for various algorithms

⇒ EA: the cost estimate of algorithm A

▪Measure of the actual cost: the no. of block transfers

⇒ Significantly depends on the size of the 'buffer' in main memory

⇒ Assumption: the worst case

▪The best case: The buffer can hold all data in a relation.

⇒ The block in a relation does not need to be accessed 'again'.

The worst case: The buffer can hold only a few blocks of data, i.e., one block	per relation

Cost Estimation Models

▪Selection: Search/File Scan

1. Linear Search (A1)

- All blocks have to be read and tested to see whether they satisfy the selection condition.

- EA1 = BR

EA1 = BR/2 for a selection on a key value.

2. Binary Search (A2)

- Assumption

① The file is 'ordered' on an attr.

② The selection condition is an equality comparison on the attr.

③ The set of records for a given value start at a block boundary.

④ The blocks of a relation are stored contiguously on disk.

- EA2 = ⌈log2 BR⌉ + ⌈SC(A,R)/FR⌉- 1

locating the first tuple	one block has already been retrieved

total no. of block that satisfying the condition

EA2 = ⌈log2 BR⌉ for a selection on a key value

Ex) Estimate the query, σcity='LA' (Acnt)

given	FAcnt = 20	(i.e., 20 tuples/block)

NAcnt = 10000	(i.e., 10000 tuples/relation)

V(bran-name, Acnt) = 500	(i.e., 500 different branches)

V(city, Acnt)	= 50	(i.e., 50 different cities)

BAcnt = 10000/20 = 500 (blocks/relation)

EA1 = BAcnt = 500 blocks

EA2 = ⌈log2 500⌉ + ⌈(10000/50) / 20⌉ - 1

= 9 + 10 - 1 = 18 blocks,	SC(city, Acnt)

given Acnt is sorted on city.

3. Selection using Indices

-	Primary Index	: An index on an attr. stored in the physical order.

Secondary Index: All other indices that are not a primary index.

⇒ The use of indices imposes the 'overhead' of access to the index blocks

① Primary/Secondary index, Equality on key (A3)

∙Retrieve a single record that satisfies the corresponding equality condition.

∙EA3 = HTI + 1	the block containing the record

the no. of index levels

② Primary index, Equality on nonkey (A4)

∙⌈SC(A,R)/FR⌉ blocks must be accessed after the index scan.

∙EA4 = HTI + ⌈SC(A,R)/FR⌉

index scan	file scan

③ Secondary index, Equality on nonkey (A5)

∙Assuming the worst case, each matching record resides on a different block

∙EA5 = HTI + SC(A,R)

Ex) Estimate the query, σcity='LA' (Acnt)

given	FAcnt = 20	(i.e., 20 tuples/block)

NAcnt = 10000	(i.e., 10000 tuples/relation)

V(bran-name, Acnt) = 500	(i.e., 500 different branches)

V(city, Acnt)	= 50	(i.e., 50 different cities)

a primary, B+-tree index for attr. city (i.e, a clustering index)

fI = 20 points/node

HTI = ⌈logfI V(A,R)⌉ = ⌈log20 50⌉ = 2 blocks	; index scan

SC(city, Acnt) = 10000/50 = 200 tuples/attr.

SC(city, Acnt)/FAcnt = 200/20 = 10 blocks/attr.	; file scan

EA5 = 2 + 10 = 12 blocks

4. Selection Involving Comparisons

5. Complex Selections

▪Sorting

- For output display, internal processing (join), etc.

- Logical sorting: Reading tuples using an index on the sort key.

Physical sorting: Ordering tuples physically on the disk.

- Two types of 'physical' sorting

①Internal sorting: Data fit entirely in main memory.

⇒ insertion/bubble sort, quick sort, heap sort, etc.

②External sorting: The relation does not fit in memory.

⇒ external sort-merge

1. External sort-merge

- Let M denote the buffer size (= no. of blocks in main memory).

⇒ N = ⌈BR/M⌉  (BR= the no. of blocks of the relation R)

- Algorithm: p.395

① Stage 1: N sorted files (=runs) are created.

M blocks

R0

M blocks

R1

M blocks

RN-1

…

② Stage 2: N runs are merged by using (M-1)-way merge.

⇒ (M-1) runs are merged simultaneously.

⇒ no_of_passes

IF N≤(M-1), no_of_passes = 1

ELSE        no_of_passes	= ⌈logM-1 N⌉

= ⌈logM-1 BR/M⌉

buffer (=M blocks)

0
1		M-1 blocks
…		for merge
M-2		⇒ (M-1)-way merge
M-1	for output

Ex) M=3 blocks ⇒ 2-way merge

Assume that only one tuple fits in a block. (FR = 1)

buffer

0
1
2	for disk output

R0 = {(a,19), (d,31), (g,24)}

R1 = {(b,14), (c,33), (e,16)}

read tuples	compare&write   read next    compare&write  read next   …

0	*(a,19)
1	*(b,14)
2

0	(a,19)
1	(b,14)
2	*(a,19)

0	*(d,31)
1	(b,14)
2	(a,19)

0	(d,31)
1	(b,14)
2	*(b,14)

0	(d,31)
1	*(c,33)
2	(b,14)

- Performance

The total no. of disk block access

= stage ① + stage ②

= (read & write)․BR + (read & write)․BR․(no_of_passes) - BR (?)

= 2․BR + 2․BR․⌈logM-1 BR/M⌉ - BR	⇒ the final pass can produce

= BR․(2⌈logM-1 BR/M⌉ + 1)	the sorted output without

writing its result to disk (??)

Ex) p.396, Fig.12-3

BR = 12

M=3 ⇒ 2-way merge

⇒ no_of_passes = ⌈log2 12/3⌉ = 2

The total no. of disk block access = 12․(2․2 + 1)

= 12․5

= 60 blocks

▪Join

- To process a query which requires two or more relations.

i.e., equi-join: R ∞R.A=S.B S

- Algorithms: Nested-loop, sort-merge, hash

⇒  Depends on each 'preparation' method before comparison.

Ex) CUSTOMERS ∞ BRANCHES

⇒	SELECT Customer

FROM CUSTOMERS, BRANCHES

WHERE	CUSTOMERS.Accnt = BRANCHES.Accnt AND

BRANCHES.Branch = 'Perryridge'

Figure 1: Join operation

CUSTOMERS					BRANCHES
Accnt	Customer		Balance		Accnt	Branch
001 101 130 217 373	Kim Wortman Friesen Lively Rumpho		5900 6000 6700 7500 6500		001 417 110 130 499 373 101 404 452 318	Brighton Brighton Downtown Mianus Perryridge Perryridge Perryridge Redwood Redwood Round Hill
Accnt	Customer
001 101 130 217 373	Kim Wortman Friesen Lively Rumpho				Accnt	Branch
					499 373 101	Perryridge Perryridge Perryridge
		Accnt	Customer	Branch
		101 373	Wortman Rumpho	Perryridge Perryridge

2. Projection

1. Selection

3. Join

- Three Algorithms For Join

①Nested-loop join: Every possible pair of tuples from each relation to be joined are examined. ⇒ O(|R|2).

②Sort-merge join: Each relation is sorted by the join attribute(s), and then both relations are sequentially scanned.

Figure 2: Nested-loop join.

CUSTOMERS			BRANCHES
Accnt	Customer		Accnt	Branch
001	Kim		499 373 101	Perryridge Perryridge Perryridge
101	Wortman		499 373 101	Perryridge Perryridge Perryridge
130	Friesen		499 373 101	Perryridge Perryridge Perryridge
217	Lively		499 373 101	Perryridge Perryridge Perryridge
373	Rumpho		499 373 101	Perryridge Perryridge Perryridge

⇒ O(|R|․log|R|).

Fiqure 3: Sort-merge join

CUSTOMERS			BRANCHES
Accnt	Customer		Accnt	Branch
001 101 130 217 373	Kim Wortman Friesen Lively Rumpho		101 373 499	Perryridge Perryridge Perryridge

▪Merge join with an index on the join attribute(s). ⇒ O(|R|).

Fiqure 4: Merge join for indexed-sequential files.

CUSTOMERS						BRANCHES
Accnt	Customer					Accnt	Branch
001 101 130 217 373	Kim Wortman Friesen Lively Rumpho		101			499 373 101	Perryridge Perryridge Perryridge
			373
			499

Accnt Index

③Hash join: Each relation is first hashed on the join attribute(s) into disjoint buckets by the same hash function, and the nested-loop join is performed between two equally-addressed buckets.

⇒ ≈ O(|R|) in case of skew or O(|R|2) in the worst case.

Fiqure 5: Hash join

			Bucket 0	(130,Friesen)			Bucket 0
			Bucket 1	(101,Wortman) (001,Kim)		(101,Perryridge)	Bucket 1
			Bucket 2				Bucket 2
Accnt	Customer		Bucket 3	(373,Rumpho)		(373,Perryridge)	Bucket 3		Accnt	Branch
000 110 130 217 373	Kim Wortman Friesen Lively Rumpho								499 373 101	Perryridge Perryridge Perryridge
			Bucket 4				Bucket 4
			Bucket 5				Bucket 5
			Bucket 6				Bucket 6
			Bucket 7	(217,Lively)			Bucket 7
			Bucket 8				Bucket 8
			Bucket 9			(499,Perryridge)	Bucket 9

Bucket i = Accnt mod 10

Bucket i = Accnt mod 10

CUSTOMER

BRANCHES

Oracle Query Optimization

▪SQL Processing

① Cursor의 생성

- Cursor는 SQL을 수행하기 위해서 Oracle이 사용하는 자원.

⇒ A 'named' resource for a 'private' SQL memory area in which a parsed statement and other info. for processing SQL statements are kept.

⇒ Available to a program and can be used to parse SQL statements in the application

- Most users rely on the 'automatic' cursor handling of the Oracle utilities.

⇒ But, the application interface offer programmers more control over cursors.

⇒ Each user session can open multiple cursors explicitly up to the limit set by OPEN_CURSORS.

- OLTP application에서는 cursor가 부족하면 SQL 연산에서 문제가 발생하므로, system 자원이 충분하면 cursor의 수를 늘리는 것이 유리함.

Ex) inittune.ora file의 내용을 수정

OPEN_CURSORS = 300

② Parse

- Shared pool에 이전에 사용된 ‘같은’ SQL이 있는지 확인하고, 없으면 parse 과정을 거침.

- Shard pool size를 크게 잡아 놓고, 같은 작업을 하는 SQL문은 table 순서, 대소문자 등을 정확하게 같이 작성하면 Shared pool에 있는 SQL을 다시 사용하므로 성능이 향상됨.

Ex) inittune.ora file의 내용을 수정

SHARED_POOL_SIZE = 3000000

Ex) 아래의 SQL등은 같은 작업을 하지만 서로 다른 SQL문으로 간주됨.

SELECT	emp_no, dept_no

FROM emp, dept

WHERE	emp.emp_no = dept.emp_no;

SELECT	emp_no, dept_no

FROM dept, emp

WHERE	emp.emp_no = dept.emp_no;

SELECT	EMP_NO, DEPT_NO

FROM emp, dept

WHERE	emp.emp_no = dept.emp_no;

③ Describe (설명문) 처리

Ex)	storeddemo.sql (PL/SQL)

/* …

** …

*/

CREATE or REPLACE PROCEDURE StoredDemo(cust_name IN VARCHAR,

customer	OUT account.name%TYPE,

bal	OUT account.balance%TYPE)  IS

DECLARE

…

BEGIN

SELECT  name, balance	INTO customer, bal

FROM	account

WHERE	name = cust_name;

DBMS_OUTPUT.PUT(customer);

DBMS_OUTPUT.PUT(bal);

END;

/

④ 정의(DECLARE) 처리

⑤ Bind variables

⑥ Generate execution plans and optimize

⑦ Parallelize: 문장의 병렬화

⑧ Execute: Fetch result records

▪Execution Plan Generation

Ex)	SELECT	ename, job, sal, dname

FROM	emp, dept

WHERE	emp.deptno = dept.deptno

AND NOT EXIST (SELECT * FROM  salgrade

WHERE emp.sal BETWEEN lowsal AND highsal);

Index	⑤

(Unique Scan) pk_depno

Table Access        ④

(By Row) dept

Table Access        ③

(Pull) emp

Nested Loops        ②

Filter                ①

(AND)

Table Access        ⑥

(Pull) salgrade

▪Optimization Mode Selection

- Two methods

① Rule-based

□Use heuristics

② Cost-based

□Execution plan들을 작성.

□Table, Cluster, Index 등에 대한 통계 데이터들을 사용하여, 각 execution plan들에 대한 cost 계산.

□가장 낮은 cost를 가진 execution plan을 수행.

- 대부분의 경우, Cost-based 방식이 Rule-based 방식보다 좋은 결과를 산출.

- Set modes

Modes	Definition
RULE	Rule-based
CHOOSE	Table, Cluster, Index 등이 Optimizer를 위한 통계 데이터를 갖고 있으면 Cost-based를 사용하고, 통계 데이터가 없으면 Rule-based를 사용
ALL_ROWS	통계 데이터의 유무에 상관없이 Cost-based를 사용, Best throughput이 목적
FIRST_ROWS	Cost-based 사용, Best response time이 목적

①Oracle 초기 parameter 사용

Ex) OPTIMIZATION_MODE = RULE

②ALTER SESSION statement 사용

Ex) ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS;

▪통계 데이터 수집: ANALYZE statement

- Functions

□통계 데이터 수집: Table, Cluster, Index 등에 관련된 데이터들을 모아 Cost-based optimization에 사용되게 함.

□Data integrity 확인: Table, Cluster, Index 등의 무결성 확인 작업을 수행.

□Chained row 통계: Table, Cluster에 있는 'chained' row - physical clustering이 되어있지 않고 떨어져서 연결되어 있는 row들 - 에 대한 통계자료 수집.

Ex)	① Compute 'exact' statistics

ANALYZE TABLE table_name COMPUTE STATISTICS;

ANALYZE INDEX index_name COMPUTE STATISTICS;

ANALYZE CLUSTER cluster_name COMPUTE STATISTICS;

⇒ 전체 tables, indices, clusters를 read하므로, 커다란 temporary space가 필요.

② Compute 'estimate' statistics

ANALYZE TABLE table_name ESTIMATE STATISTICS;

ANALYZE INDEX index_name ESTIMATE STATISTICS;

ANALYZE CLUSTER cluster_name ESTIMATE STATISTICS;

⇒ Compute exact statistic와는 달리 커다란 temporary space가 필요하지는 않으나, 값은 비교적 덜 정확함.

⇒ 다음과 같이 수행 속도를 높일 수 있지만, 정확도 면에서는 떨어짐.

ANALYZE TABLE table_name ESTIMATE STATISTICS

SAMPLE 10000 ROWS;

ANALYZE TABLE table_name ESTIMATE STATISTICS

SAMPLE 40 PERCENT;

③ Check integrity

ANALYZE TABLE table_name VALIDATE STRUCTURE;

ANALYZE INDEX index_name VALIDATE STRUCTURE;

ANALYZE CLUSTER cluster_name VALIDATE STRUCTURE;

검사하고자하는 table과 관련된 모든 object(index 등)들을 검사.

ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;

④ Chained row

ANALYZE TABLE emp_dept LIST CHAINED ROWS INTO chained_rows_table;

- 확장된 기능의 optimizer를 사용하기 위한 통계자료 수집 procedure들

Ex)	DBMS_STATS.EXPORT_SCHEMA_STATS: 새로운 통계자료를 얻기 위해서 기존의 자료들을 저장(export)하기 위해서 수행.

DBMS_STATS.IMPORT_SCHEMA_STATS: Export했던 자료들을 다시 사용하기 위해서 import할 때 사용.

DBMS_STATS.GATHER_SCHEMA_STATS: 새로운 통계자료들을 얻기 위해서 수행.

GATHER_SCHEMA_STATS: export와 gather를 순차적으로 수행.

- 수행될 때 아래의 정보들을 수집함.

Table	Index
number of rows number of blocks that have been used number of blocks never used average available free space number of chained rows average row length number of distinct values per column the second smallest value per column the second largest value per column	Index level number of leaf blocks number of distinct keys average number of leaf blocks per key average number of data blocks per key clustering factor minimum key value maximum key value

* cluster의 경우는 average cluster key chain length를 수집

- 수집된 데이터는 아래의 Data Dictionary View들에 저장됨.

□USER_TABLES, ALL_TABLES, DBA_TABLES

□USER_INDEXES, ALL_INDEXES, DBA_INDEXES

□USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA__TAB_COLUMNS

- Cost-based Optimizer와 관련된 초기화 변수들

□OPTIMIZER_FEATURES_ENABLED: B_TREE_BITMAP_PLANS, FAST_FULL_ SCAN_ENABLED를 포함한 optimizer 기능들을 사용함.

□OPTIMIZER_MODE: optimizer mode를 지정.

□OPTIMIZER_PERCENT_PARALLEL: optimizer가 사용할 병렬처리 정도를 지정.

□HASH_AREA_SIZE: 값이 클수록 더 많은 hash join이 가능.

□SORT_AREA_SIZE: 값이 클수록 더 많은 sort-merge join이 가능.

□DB_FILE_MULTIBLOCK_READ_COUNT: 값이 클수록 table scan에 유리함.

DW application과 관련된 초기화 변수들

□ALWAYS_ANTI_JOIN: Anti-join type을 지정 (NESTED_LOOPS/MERGE/

HASH)

□HASH_JOIN_ENABLED: Hash join 기능 ENABLE/DISABLE, DW 환경에서는 항상 TRUE로 하는 것이 좋음.

▪SQL Tuning: SQL*Trace 이용

- 현재 SQL의 수행상태를 정확히 파악하고, tuning 작업이 의도대로 되었는지 등의 효과를 측정함.

- SQL 수행에 관한 자료 수집

□CPU time

□Total execution time

□Physical/Logical I/O

□Library cache

□Parse count

□Execute count

□Fetch count

□Result record count

- SQL*Trace 실행

① Oracle initialization parameter 초기화

TIMED_STATISTICS

USER_DUMP_DEST	/* Trace written in this destination file */

MAX_DUMP_FILE_SIZE	/* The max. size of a trace file	*/

② SQL*Trace 실행

□ALTER SESSION statement 사용

ALTER SESSION	SET SQL_TRACE = TRUE;

□Procedure 사용

EXECUTE RDBMS_SYSTEM.SET_SQL_TRACE_SESSION(2, 1, TRUE);

⇒ 2는 SID, 1은 serial no.이며, TRUE는 SQL_TRACE_ENABLE 값임.

SQL> select sid, serial#, osuser from V$session where osuser='oracle';

SID    SERIAL#   OSUSER

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

1          15	oracle

2          17	oracle

V$SESSION: This view lists session information for each current session.

Column	Datatype	Description

SADDR	RAW(4)	Session address

SID	NUMBER	Session identifier

SERIAL# NUMBER	Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and 'another' session begins with the 'same' session ID.

③ tkprof (oracle command)를 사용하여 SQL*Trace 정보를 user가 알아볼 수 있는 형식으로 변환/출력.

Ex) #tkprof input_file output_file

[explain=user/passwd]	/* 해당 trace file이 수행된 session	의 user와 password */

[table=schema.table_name]	/* Execution plan을 저장할 tkprof	temporary table의 name */

[print=integer]	/* trace file별로 출력시킬 SQL문	의 수 */

[insert=file_name]

[sys=yes/no]	/* SYS user의 SQL문의 list */

[sort=option]	/* sort options */