狠狠撸

1
Function ??? SQL??
??? ????
2013.03.02
DA? : ???

2
CONTENTS
? Warming Up
? Clustering Factor
? ??? ????? ??? ??
- ??? ????? ??
- ??? ??? ??
- ??? ????? ??? ??
? Function??? SQL?? ??

3
What is your DB management
level?
? Level 1
DB? ??? ???? ????.
? Level 2
????? ???? ??.
? Level 3
DB? ??? ???? ????.
- ACCESS PATH? ????.
? ????? ????.

4
Warming Up with some stretches
? ACCESS PATH
- Index Stretegy, partition
? JOIN METHOD
- NL JOIN, HASH JOIN, SORT MERGE JOIN, HASH_SJ, HASH_AJ……
? JOIN ORDER
- emp ? dept, dept ? emp
? Transformer (Logical Optimizer)
- Huristic Transformer / Cost Based Query Transformer
? ETC
- null, not null, buffer pinning, OWI, filtering optimization, function,
case, decode, nvl, pagenation, ….

5
DATA Modeling & Business Rule
INDEX?? employees : (JOB_ID)
orders : (EMPLOYEE_ID, ORDER_DATE)
? SQL? ??? ??? ?? ???

6
? ????
Employees ???? job_id=‘J04’??? ????(order_date)?
2012?01?01? ?? 2012?06?01? ?? ?? ????
(order_status)? ‘10’? employee_id? last_name? ?????.
? Data modeling? Business Rule? ??? ??? SQL ?? ??
???.

7
?????
1. Employees ????? employee_id? unique??.
2. Orders ??? ???(N?)? ?? join? ??? ??.
3. ???? ???? ??? ??? ??? Join? ????.

8
???? 11g??? Transformer? ?? ?? ??? SQL? ????? ??
?. ???? ???(??)? SQL? ? ???? ??.

9
OPTIMIZER BASICS
? Three main questions you should ask when
looking for an efficient execution plan:
1. How much data? How many rows? Volume?
2. How scattered / clustered is the data?
3. Caching?
=> Know your data!

10
OPTIMIZER BASICS
? Why are these questioins so important?
? Two main strategies:
1. One “Big Job”
=>How much data, volume?
2. Few/many “Small Jobs”
=>How many times / rows ?
=>Effort per iteration? Clustering/Caching

11
OPTIMIZER BASICS
? Optimizer’s cost estimate is based on:
? How much data? How many rows?
? How scattered / clustered ?(partially)
? (Caching?) Not at all : 11g

12
SUMMARY
? Cardinality and Clustering determine
whether the “Big Job” or “Small Job”
strategy should be preferred
? If the optimizer gets these estimates rigtht,
the resulting execution plan will be
efficient within the boundaries of the given
access paths
? Know your data and business questions
? Help your optimizer. (Oracle doesn’t know
the data the way you know it.)

13
Today’s LEMA
? Oracle doesn’t know the
data the way you know it!!
? Inefficient Execution Plan
50% Oracle Does not know the data.
50% SQL writers Do not know the
optimizer.

14
AGENDA
? Clustering Factor
? Statistics / Histograms
? Datatype issues

15
HOW SCATTERED / CLUSTERED?
? INDEX SCAN ? TABLE BLOCK
? Worst Case
1,000 rows => visit 1,000 table blocks:
1,000 * 5ms = 5s
? Good Case
1,000 rows => visit 10 table blocks: 10*5ms = 50ms

16
? There is only a single measure of clustering
in Oracle:
The index clustering factor
? The index clustering factor is represented
by a single value
? The logic measuring the clustering factor by
default does not cater for data clustered
across few blocks(ASSM!)

17
? Challenges
? Getting the index clustering factor right
? There are various reasons why the index
clustering factor measured by Oracle might not
be representative
- Multiple freelists / freelist groups
- ASSM (automatic space segment management)
- Partitioning
- SHRINK SPACE effores

19
? ASSM??? ??? ?? ??? ?? ?
clustering? ????.
? ASSM??? ??? ?? block? ???
? insert?? ?? ???.
(freelist??)
? ?? ???? clustering factor? ???
?? read block? ???? ??? ??
?? ???.(?)

20
? The CF in case of an index range scan
with table access involved represents the
largest fraction of the cost associated
with the operation. (See 10053 trace file)

21
STATISTICS
? Basic Statics:
- Table Statistics: Blocks, Rows,
AvgRowLen
- Basic Column Statistics:Low/High Value,
Num Distinct, Num Nulls

22
Statistics
? Controlling column statistics via METHOD_OPT
? FOR ALL INDEXED COLUMNS SIZE > 1:
? Nonsense, without basic column
statistics
? Default from 10g on:
FOR ALL COLUMNS SIZE AUTO:
basic column statistics for all coumns,
histograms if Oracle determines so

23
HISTOGRAMS
? Basic column statistics get generated
along with table statistics in a single pass
? Each histogram requires a separate pass
? Therefore Oracle resorts to aggressive
sampling if allowed =>AUTO_SAMPLE_SIZE
? This limits the quality of histograms and
their significance
(basic column statistics? ?? ?? row? ???? ?
?, histogram? ?? ?? row? sampling)
user_tab_col_statistics ??

24
HISTOGRAMS
? Limited resolution of 255 value pairs
maximum
? Less than 255 distinct column values =>
Frequency Histogram
? More than 255 distinct column values
=>Height Balanced Histogram
? Height Balanced is always a sampling of
data, even when computing statistics!

25
HISTOGRAMS
? Aggressive sampling
? Oracle doesn’t trust its own histogram
information when caculating estimated
cardinality.
? Very bad cardinality estimation ?
inefficient execution plan

26
Frequency Histograms
? When it consists of only a few popular
values
? Very popular and nonpopular values
? Dynamic sampling also is not
representative
? Statistics is sometimes inconsistent

27
Height Histograms
? Rounding effects
? They cannot cover all values.
? Histogram values are unstable.
(when you gather histograms, the values
can be different.)
? Oracle doesn’t know the data the way
you know it.

28
Height Histograms
5000 is considered unpopular, even though it is a very popular
value.

29
Height Balanced Histograms
INDEX range scan, height balanced histograms can be very useful.

30
SUMMARY
? Check the correctness of the CF for your
critical indexes
? Oracle does not know the questions you
ask about the data
? You may want to use FOR ALL COLUMNS
SIZE 1 as default and only generate
histograms where really necessary
? You may get better results with the old
histogram behavior, but not always

31
SUMMARY
? There are data patterns that don’t work well
with histograms
? => You may need to manullay generate
histograms using
DBMS_STATS.SET_COLUMN_STATS for
critical columns
? Don’t forget about Dynamic
Sampling/FBI/Virtual Columns/Extended
Statistics
? Know your data and business questions!

32
10053 Trace File
? SYSTEM Statistics Information
- CPU SPEED, SBRDTime, MBRDTime, MBRC
? ???/??? Statistical Information
- Base Cardinality, Density, CLUF
? Cardinality Estimation
? Cost Estimation
- Access Type, Join Type, Join Order
? ??? ?? ? ?? ??.

33
COST?
? Jonathan Lewis
? The cost represents (and has always
represented) the optimizer’s best
estimate of the time it will take to
execute the statement.
? Query? ?? ?? ??(Time)

34
Time???? Cost
? Total Time = CPU Time + I/O time + Wait Time
? Estimated Time
= Estimated CPU time + Estimated I/O time
= Estimated CPU time + Single Block I/O time
+ Multi Block I/O time

35
Time???? Cost
? Oracle? COST? ???(???? ??)? ??
? ?? Time? ?? ?? Single Block I/O Time?? ???.
? ? ???? Oracle Optimizer? ??? ?? ??? ??
? ?? System Statistics ??? ???? ??? I/O Model? Cost??? ??
Model???. ?, Cost? ? I/O Count??.
? I/O Count? Time?? ???? ?? ????? ??? ??? Time? I/O
Count???? ????? ??? ????.

36
Time???? Cost
? Total Time = CPU Time + I/O time + Wait Time
? Estimated Time
= Estimated CPU time + Estimated I/O time
= Estimated CPU time + Single Block I/O time +
Multi Block I/O time
? COST = (Estimated Time / Single Block I/O Time)
?cost? ?? = ????? ??
= Single Block I/O count + ??Multi Block I/O count + ?
?CPU count
? ?? ?? ?? ??? single block I/O time? ?? ???
? ??? count??? ??
? I/O Cost ???? ??

37
System Statistics
? 10g ?? ??? DBA? ????? ??
?? ??? CPU mode ??

38
Cost ?? ?
? Column C1 ??? fetch?? ?? C1, C2
? fetch?? ?? ??? ???.
? Predicate? ????? ????? CPU
??? ????.

39
DB_FILE_MULTIBLOCK_READ_COU
NT
? Cost ?? ??? System Statistics?
mbrc?? ????.
? ???, ?? Query? ??? ??
DFMBRC?? ????.

40
Time Model ??
? Mreadtim ? sreadtim ?? ????
? Enterprise ??? Storage? sreadtim?
mreadtim?? ? ?? ?? ?????
?? ??
? dbms_stats.set_system_stats procedure?
??? ??? ?? ??

41
CBO? ?? ??
SQL
Statement
Parse
Transformation
(logical optimization)
Optimization
(Physical Optimization)
Exexution
Plan
Result Set

42
Basic Terminalogy
? Density : ??, ???
?. Histogram? ?? ?? : 1/NDV
?. Histogram? ?? ?? : unknown
? Cardinalty : ??? ???? ??? ?
Cardinality = Base Cardinality * Selectivity
? Selectivity : ????(predicate)? ???
??
? Histogram : ???, ?????

43
Cardinality ??
-C2 ??? histogram ???
-C2 ??? density? 1/NDV?? ?? ?? ?

44
? C2 = :b ?? : Histogram(frequency
histogram)? ???? selectivity???
Density? ?? NDV?? ????.
? C2= ‘A’ ?? : ???? ????
Histogram??? ??? ????
cardinality? ? ? ??.

45
Cardinality??
C2= :b1 ????? cardinality? ??? ?????
Cardinality = base cardinality * selectivity
?, C2=:b1 ? selectivity? ?????

46
Cardinality ??
?, C2=:b1 ? selectivity? ?????
Frequency hostogram? ???? ?? selectivity ??? density? ??
NDV? ????.
??? selectivity = 1/5 = 0.2 ??.
?? C2=‘B’?? ?? cardinality? ??? ?????

47
Cardinality ??
CBO?
Frequency Histogram ??? Data? ??? ??? ?? ??.
????,
C2 like ‘%A%’ ??? ??? cardinality? ?????

48
Cardinality
????,
C2 like ‘%A%’ ??? ??? cardinality? ?????
??? ?? cardinality? 500? ??? ????
(Magic Number **% RULE)
Googling ???.

49
Index Scan Cost
? ?? Row? ? ??? ??? Index
Scan? Table Full Scan?? ??????
? CBO? Index Scan? Full Table Scan?
Cost? ????? ??
? 10053 Trace file ??
? Cost?? ?? ?? ? CBO? ? Index
Scan/Full Table Scan??? ??? ? ?
?.

50
Table Full Scan Cost ?? ??
? ??? ?? ???? ??
Blocks = 100
MBRC = 4
sreadtim = 5
mreadtim = 10
? COST = Single Block I/O Count +?? Multi Block I/O Count + ??
CPU Count
? ??Multi Block I/O Count
= MBI/OC * ???
= (Blocks/MRC) * (mreadtim / sreadtim)
= (100/4) * (10/5) = 50
? ??CPU Count = small value
? COST = 0 + 50 + <CPU> = 50.xxx.
? Table Full Scan? COST = f (Table Block?, MBRC, multi block read ?
??)
? ??? ?? ??? ???? Block?(MBRC)? ??
? Cost? ????.

51
Index Scan Cost
? ??? ???? ??? Index Leaf Block?
? ????.
? ??? ???? Index Key? ???? fetch
???
? Rowid? ??? Table Block? ??? ??
??
? ??? ???? Row? Fetch??.
? Cost = root block?? leaf block?? ???? Cost
+??? ?? Index Leaf Block? ?? Cost
+??? ?? Table Block? ?? Cost

52
Index Scan Cost
? root block?? leaf block?? ???? Cost
= Blevel = Index Height -1
? ??? ?? Index Leaf Block? ?? Cost
= ?? Leaf Block * (Index) Selectivity
? ??? ?? Table Block? ?? Cost
= Clustering Factor * (Table) Selectivity
- CF : Index Key? ???? Table Block? ?? ?? ??? ?? ??

53
Index Scan Cost
= Blevel +
Leaf Blocks * Index Selectivity +
CF * Table Selectivity +
?? CPU count
?Index Height, Selectivity, Leaf Block?, CF? ?? ??
???? data block? <=CF =< ??? row count???
CF? ??? ? ??? COST?? ???? ??? ??.

54
Index Scan Cost
= Blevel +
Leaf Blocks * Index Selectivity +
CF * Table Selectivity +
?? CPU count
??? ??(leaf block? ??)? Data? Index? ???
? ??.

55
Clustering Factor
? A measure of the orderedness of an
index in comparison to the table that it
is based upon. It is used as an indicator
for computing the estimated cost of the
table lookup following an index access.
? Index Key? Table Row? ?? ??? ?
?? ??? ???? ???

56
Clustering Factor
? Good Clustering Factor
Index = 1,2,3,4,5,6,7,8,9,10,….
Table = 1,2,3,4,5,6,7,8,9,10,….
? Bad Clustering Facotr
Index = 1,2,3,4,5,6,7,8,9,10,…
Table = 3,8,7,1,4,5,10,2,6,9,…
Index = 1,2,3,4,5,6,7,8
Table = 8,7,6,5,4,3,2,1 Good or Bad?

57
CF ?? ??
? The CF records the number of data
blocks that will be accessed when
scanning an index.
? ????
1. ? ??? Table Data Block??
Memory? Cache? ? ??.
2. CF? Physical Reads, ? Cache? ???
? ?? Disk?? ?? ??? ????.

58
CF ?? ??
1 2 3
1 2 3
INDEX
TABLE
Index
Block
Table
Block

59
CF ?? ??
1 2 3
1 3
INDEX
TABLE
Index
Block
2

60
??? CF? ?????
CF? ??? : Table? Data Block ?
CF? ???

61
CF? ???
??? CF? ?????
CF? ??? : Table? Row ?

62
CF and Performance
Histogram ?? X
???? ??

63
CF and Performance
? c2 ??? ?? index? ???? ????

64
CF and Performance
? c2 ??? ?? index? ???? ????
COST =
Blevel +
Leaf blocks * Index Selectivity +
CF * Table Selectivity + ??? CPU count
Blevel + Leaf blocks* Index Selectivity ??? Cost? ‘2’? ????
?, CF*Table Sectivity? ???? cost? 97? ???.
Table?
Selectivity
? ???
??

65
CF and Performance
? CF? Selectivity ?? ?? Index Lookup
? Cost? ???? ??? ??
? ?? ??? row count? 10000?? ??
??. Selectivity? 1%??? ????,
Index Lookup ??? ? ??.
? ?? ?? ?? ??? ??? ? ???
?????

67
? ??? logical reads ??? ?????
CF and Performance

68
Buffer Pinning
? ? ?? fetch?? ? ? ?? ???? Block? ???
Pinning? ????.
? Pinned Block? ???? cache buffers chains latch? ?
??? ??? ??? ??? ?? Memory Address? ??
? ?? ???.
? Buffer Pinning? ??? ????? ????? ??? ??
??? ???.

69
Cache Buffers Chains Latch
? ? ???? bad SQL?? ?? ? ?? ??? ??? ???? ?? ?? cache buffers chains
latch? ?? ???? ??.

70
Buffer Pinning
? ??? T_CLSF_I1 ???? ???? Table?
access?? logical reads? (4-3)1??.
? T_CLF_I2 ???? ???? Table? access
?? logical reads? 95-3=92??. CF? ?
??? ? 8? ??? buffer pinning? ??
??? ? ? ??. ??? random???, ?
? buffer?? 100?? ??? ???.

71
CF? ??? ??
? Index ??? ?? clustering factor ?? row
count? ??? ??, CF?? ?? ? ??
??? ?????
? ?? ??? Index Scan? ??? ???.
? ?? ??? Index Scan? ??? ????
??? Index Fast Full Scan? ????.

72
Controlling Index Scan Cost
? Optimizer_index_caching : default 0
? Oracle? Index? Memory? Caching??
?? ??? ???? Cost? ????.
? Cost ??? ??
? ‘0’?? ?? ?? : Index Scan? ????
? ??? ??? ?? Cost??? ??

73
Controlling Index Scan Cost
? Optimizer_index_cost_adj : default 100
? 100? Index Scan Cost? ?? ?? ??
? 100% ??
? 50? Index Cost? 50% ?????.

74
Parameter vs. System Statistics
? System Statistics ? ? sreadtim??
mreadtim?? ????? ???? ??
? ??.
System Statistics?
? mbrc?? ???
?,
DB_FILE_MULTBLOCK
_READ_COUNT ??
??? ? ?? Cost
??? ???? ?
??.

76
Index Range Scan Cost = Root Block I/O Count + Leaf Block I/O Count
Fetch Call ??? ?? ??? ?? Block? ?? ??? ??.
Buffer Read ??
Cost 20? ??? ???? ?? ????
Buffer Read ?? 118? ??? ???? ?? ????

77
Buffer Read ??
C1 ??? ???? ?? Count?? ???? ??? Logical Read? 20
??. ? ?? Index? Root Block + Leaf Block?? ????.
??? Fetch??? Leaf Block? Range Scan?? ??? Leaf Block? ?
??? Read? ????.
?, Fetch Call??? ?? ??? ?? Block? ?? ??? ??.
Fetch Call? ??,
? Fetch Array Size? Index Range Scan? Leaf Block ?? ?
?? ????.
???, Cost ???? Fetch Call??? ???? ???. Index Range
Scan? Cost? ???? ? Block? ?? ?? ????.

78
INDEX Range Scan ??
? Index Key? ??(Delete)?? Leaf Block?
?? ????.
? ??? ? Index Leaf Block? ?? ????.
(Index Key? ??? ???? ?? Leaf
Block?? ? ?? ??? ??.)
? Oracle? Leaf Block? ???? ???
? key? ?? ??? ? ? ??.
? Key? ??? ?? ?? “Fetch”? ????
???? Fetch Call ??? ????.
? Leaf Block? ???? rebuild, coalesce,
shrink ??? ????.

79
INDEX Leaf Block ???
?? ???? ???? ???? 20?? Block? ????.
???? Leaf Block ?? ???? ??? ?? ???

80
INDEX Leaf Block ???
Coalesce or Rebuild ? ????? ??? ? ??.

81
?? ??
? ??? ????
? ??? ?
? ????
Select t1.c1, t2.c2, (select fun_nm(c1) from t2)
From t1,
(select t3.c1 from t3),
Where t1.c1 = t2.c1
and t2.c2 not in (select c2
from t1);

85
??? ????? ??
??? ?? : T1 ????? c1, c2 ???? ???? 10?? ????.
?, T2???? c3??? T1 ???? c1?? T2???? c1?? ?? ?
??? ????, ??? ???? ??? NULL? ???? ??.
select rownum rnum, x.*
from ( select c1, c2, c3, (select t2.c3
from scalar_t2 t2
where t2.c1 = t1.c1) as c2_c3
from scalar_t1 t1
order by c1, c2
) x
where rownum <= 10;
? SQL? ????? ??? ? ????
?? ???? ??? ???.

86
10?? ????? T2 ???? 50?? ?? access??.
??????.
? ??? ????? ?? ? ????
??1. ??? ????? ?? ????
????? ????.

87
??????? ?? ??? ????.
Index access ?? 50???? 10??? ???,
Buffer cache access ?? 15???? 1??? ???.
?? ??? rownum<=10 ??? ??? 1???? 2??? ?? ?? ?????

88
??? ????? ??
? Deterministic ??
? ?? ?? ?? ?? ?? multi buffer?
??
? ????? filter optimization? ?? ?
?
? Multi buffer size : default 64K
_query_execution_cache_max_size

89
??? ????? ??? ??
select /*+ gather_plan_statistics */ t1.c2, t1.c1, t1.c3,
(select t2.c3
from scalar_t2 t2
where t2.c1 = t1.c1) t2_c3,
(select t3.c3
from scalar_t3 t3
where t3.c1 = t1.c1) t3_c3
from scalar_t1 t1
order by t1.c1, t1.c2;
? ????? ?? ???? ??? ??? ???.
??? ? ???? ???? ??? ??? ?? ???
??

91
??????vs???????
? ??????(?? ?????)? ?
?? ????? ???? ??.
? ????????? ????????
Outer Join?? ????, Hash Join??
????.
? ????
1) Outer Join?? ??(?? ??? ????
Outer Join?? ???? ??? ?)
2) ?? ?? ??? ?? ?? Unique?? ??.

92
?????? ?? ?
Outer join? T1? driving??? ??, ???? Right? driving???? ?
????? ???

93
??vs. ??? ????
Q: Compared to the join method, are
sacalar subqueries always inefficient?
A : No, sometimes scalar subqueries are
more desirable.
Q : When subqueries are more efficient?

94
?? vs. scalar subquery
select rownum rnum, x.*
from (select /*+ leading(T1) USE_NL(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 as t2_c3,
t3.c3 as t3_c3
from scalar_t1 t1, scalar_t2 t2, scalar_t3 t3
where t1.c1 = t2.c1(+)
and t1.c1 = t3.c1(+)
order by t1.c1, t1.c2) x
where rownum <= 10;
? ??? ????? ??? ??, ??? ???? ???? ??? ?
???, ??? ??? GURU?.

95
???? INDEX SCAN?? I/O ?? ??
? ???? INDEX SCAN ??? ?? HASH? ??

96
??? ????? HASH JOIN?? ??? ?????
?? ????? ? NL JOIN?? ???? ??? ?????.
?? ?????? ?? ???? ????

97
???? ??? Unique
T1????? 10? ?? ? T2???, T3??? ???? ??? ??
????
1) T1????? 10? ?? ?, T2? T3 ???? **?? ???? ????
? ??
2) ??? ????? ??
? ???? ??? ????? ????? ?????.

99
???????vs?? ????
? ?? ???? ?? ??(Online ????)
? ??????? OK
? ??? ??? ????? ?? ??
? ?? ???? ?? ??(Batch ????)
? ??????? NO!
? ???????? ???? ?? ?
Hash Join ???? ?? ??

100
WITH? ??? ???? SQL
? ??? ???? ????? ??? ??
SQL? ??? ???? ?? ???? ?
?
? /*+ MATERIALIZE */
? /*+ INLINE */ with?? ?? ?? 1?? ??

101
MATERIALIZE ?? ??
1. Global temporary table ??
2. Global temporary table? with??? ?
?? ??? insert
3. Global temporary table ???? ???
??

102
SQL?? with?? 1?? ????? INLINE????? ??
T_T1 with?? ????? Materialize ?? ??

103
Outer join??? ??? ?? T1 ???? ?????? T2???? ????
??? ??? T3 ???(????? ????)? HASH JOIN??
? ?????? ?? ???? ????
?? ????? ??? ????? ???? ??? ? ????
?? : T1 ???? ?? ??? ??? ??.

104
???? ??
1. T1.C1 = T3.C1(+) ??? T3 ???? ??? ????? ??.
???, T3? access ?? ?? ??? ???.
2. T1 ????? ??? ?? ??? ?? ?? ? ????T3 ?? ??? ?
???.
3. With?? ???? ??? ???? ?? ??? ?, ??? ??? ????
?.

105
T1 ???? 2? access
? ????? ???? ?????
???1

106
???2
???1 ?? ??? ???, ??? ???? 2? ???? ???? ??

107
WITH? ????
? ???? ?? ?? materialize?? ??
- DISK I/O ????? control file
sequential read Wait Event ?? ??
? ????? ???, materialize?? ??
? DISK I/O ?? ?? ??
? WITH? ??? ???? ?? ??

109
User Defined Function? ??
Is this execution plan efficient?
At the end of this workship you
will be able to raise questions.

111
FUNCTION ????
? ?? ?? ??.
? ?????? ??? ??, ??? ? ?
??? ????.
? ????? ????.
? ???? ????? ????.
? ?? ? ??? ??? ??? ????.
? WAS??? Network ??? ?? ? ??.
? ???? ???? ?? ?????.

112
FUNCTION ????
? Deterministic Function
- ?? ?? ???, ?? ?? ?? ???
???? Function
- ???? ??? Function? ?? ????
?? ??? ?? Cache? ?? ?? ?? ?
?? ?? (fetch call ???? ??)
? Not Deterministic Function
- ???? ??? ?? ?? ??
- ????? ???? ??? deterministic
???? ?? ??? ?

114
TT????
YAMHHA?? ?
??? ?? ??
??? no row?
? ??, 1 row?
???
Deterministic ?? ???
Fun_lookup ?? ??
--TT???? ???
value???? ???
Test????? NO??? ???
??? ??? ??? ‘YAMHHA’
? row? ??

115
User Defined Function vs. Bulit-In
??

116
??
?? ?? recursive call? ??? ??? ????.
Recursive call? ?? ?? ??? ??? ? ????.
USER DEFINED FUNCTION? ???? ??? ?? ??
?? slide??

117
??
I/O? ?? ???? ?? function(Dual???? I/O? ??)? ???
? ???? elapsed time? 2.56??? 18.88?? 9? ??
?? : 10046 Trace file ? DB call? 2??? ??
parse 1?, execute 1??, fetch 1??

118
??? ?? ???
? ???? ?????? ???? ???.
? ?? ??? ???? recursive??? ??
??? ?? ??? ???? ? ??? ??
?? ??? ???? ??? ???.
? ?? ?? ?? ??? ???? ??? ?
??? ???? ???? ???.
select fun_dept_nm(c1) from emp;
? ? SQL?????, dept???? ???
? ??? ???? ??? ???

119
?? ??? ?? ????
? ????, ???, ???? ?? ????
???? ???.
? ?? ???? ????.
(?? ????? ?? ??? ?? ???
?? UNDO ???? ??? ???? ?
??.)

123
select c1, fn_c1_codenm(c1), c3
from functioin_table;
? ???? fn_c1_codenm ??? ? ? ?????
Function? ?? SQL? 10046 trace file ?? ? Main SQL ????? ?
??? ???.
Function? ?? ??? ??? ??.

124
from functioin_table
where c2 = 0 and c3 =‘A’;
? ???? fn_c1_codenm ??? ? ? ?????
Function? ?? SQL? 10046 trace file ?? ? Main SQL ????? ?
??? ???.
Function? ?? ??? ??? ??.

125
? trace ?????? ?? I/O? ????? ??? ??? ??
? ??.
??? Function? ????? ???? ??.

126
????? 3846?
?? ?? ???
?.
Function? ???
?? SQL? ? ?
???? ????.

127
where c2 = 0 and c3 =‘A’
and rownum <= 1;
? SQL??? function? ? ? ?
????

128
?? : 1?
1. Select?? ???? Function? ?
?? Fetch?? ????.
2. ?? ???? ?? ????.
Select?? ?? ????
where c2 = 0 and c3 =‘A’
and rownum <= 1;

129
Where?? ?? ????
select /*+ gather_plan_statistics */
t1.*, t2.*
from function_table t1, c1_code_nm t2
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
and t2.c3 = FN_C2_CODENM(t2.c4);
???? ????? ????? ?? fn_c2_codenm ??? ? ? ??
????? ?? ????(768?)?? ?? ???????
T1????? ??? ?????(3846?)?? ?? ???????
T2????? ??? ?????(15000?)?? ?? ???????
T2???? ?? row? 5000? ??? ?? ?? ?????

130
??? ????? 10046 trace ??? ?? ??.
? ????? ?? T1 ??? ???? filtering?? 3846?? ????
?, T2???? FN_C2_CODENM?? ??? ??? filtering?? 15000
?? ?????.
??? FN_C2_CODENM??? ***? ?????? ? ? ??.
Hash Join? ?????? ?? ?? ???? ??? ??? ? ?? ?
???? hash map? ???. ? ?? ?????? ????? ?? ?
?? ???, ?? ?????? ??? ???. ? ?? ?? ???? ?
?? ?? hash map? ?? ??? ??? ??? ??? ???? ??
??.

131
select /*+ gather_plan_statistics */
t1.*, t2.*
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
and t2.c4 in (2, 4)
Where?? ?? ????
??? ??? ?? ? ??? ??? ??? ?? ???? ?? ???
? ??. 1) t2.c4 ??? ?????? ? join ??? ??????
2) ?? cardinality 2000? ??? ???? ?? ????
10046 trace?? FN_C2_CODENM?? ????? ?? ??, ??? ?
?? ??? ????? ???? ? ??. ??? ????? ??? ?
????

132
T2.C3 = FN_C2_CODENM(T2.T4)? ???? ?? ???? 4??? ?
??? ????? function? ????.
10046 trace? ?? ?? 40000? ??? ? 2.42?? ????.
?? 1?? 0.0000605???? 4??? ??? elpased time? 2.42?
? ??.
? ?? ?? ? ????
1) elpased time ????.
2) buffer cache? ?? ????.
3) buffer cache? ?????.
4) ??? soft parsing??
5) CPU?? ??
??? ???? ????.

133
???? NESTED LOOP? ??
select /*+ gather_plan_statistics USE_NL(T1 T2)*/
t1.*, t2.*
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
????? ?????. ??? ??? ? ? ???????

134
t1.*, t2.*
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
Nested Loop Join? ?????? ???? ? ?? ???? ?? ???
? ?? ???? ??? ?????, ?? ??????? ??? ????
?? ???? ??.
??? FN_CE_CODENM??? 3846? ??????, t2.c3 = ? filter??
?? ???? T2????? ?? 768?? row? ?????.

135
T2? ?? ??? ??? ??
t1.*, t2.*
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
and t2.c4 in (2, 4)
T2.c4 ?? ??? ?????? ????? ?? ??? ? slide? ???
?. ???? ?? ????? ????
?? ????? ??? ? ?????
???? ? ? ?????? 10046 trace file? ?? ??? ? ? ????

136
??
1538? ?????. 10046 trace ?? ??
Nested Loops Join? ?????? Function? ???? ???.
?? ???? ?? ??? ??????? ?????? ????? ??
?, ??? ???? ???? ??? ?? ????, Function? ????
????.
Function? ????? Function? ??? ??? ???? ?? ????
? 384?? ???? ??.

137
?? ??? ?? ??
t1.*, t2.*
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
and t2.c4 in (2, 4)
? ??? ?? slide ???? ?? ??? ?????.
??? function ?? ??? ??? ???
????? ?????, 10046 trace file? ?? ??? ? ? ??????
? ? ????

138
t1.*, t2.*
where t1.c2 = 0
and t1.c3 = 'A'
and t1.c1 = t2.c1
and t2.c4 in (2, 4)
?? ??? ???, Functioin? ???? ??? ???? ?? ???? ?? ?
??. ?, T2? ?? ??? ? T2.C4 in (2, 4) ??? ???? ???? ??? ?,
4??? ???? T2.C3 = FN_C2_CODENM(T2.C4) ??? ???? 5000?? ??
??? ???.

139
Where?? ???? function??
??
? ????(NL, HASH), ????, ????
? ??? Function ????? ???.
? SQL? Where?? Function? ???, ?
??? ? ????? ??? Function ?
???? ?????.
? ????? ?????? Function???
? ??? elapsed time, buffer block read
?? ?? ? ??.

140
???? ?? ?? ??1
Select *
From (
select rownum no, a.*
from (
select memb_nm(??????) ??????,
memb_nm(??????) ??????,
??????, ??????, ????, ????, ???
from ??
where ???? = :????
and ???? = :????
and ???? between sysdate -10/24 and sysdate
order by ???? desc
) a
where rownum <= 30
)
)
Where no between 21 and 30
)
? ????? ??? ??? ?????

141
???? ?? ?? ??1
? ????? ??? ???? row count? 10????, 10??? ????
???? sorting? ? memb_nm??? 10?? ????. Sorting? ???
?? ?? 30?? ??? 21???? 30?? row?? ????. ??? ??
????, ??? ????? ?? ?? ??? 10?? ?? ? ??.
Select memb_nm(??????) ??????,
memb_nm(??????) ??????,
??????, ??????, ???? ????, ???
From (
select rownum no, a.*
from (
select ??????, ??????
??????, ??????, ????, ????, ???
from ??
where ???? = :????
and ???? = :????
and ???? between sysdate -10/24 and sysdate
order by ???? desc
) a
where rownum <= 30
)
)
Where no between 21 and 30
)

142
? slide??? ?? ????? ???, Function ?? ??? ???? ??
?? ??? ??? ? ???. ? slide?? ?? ?? ??? ???
function ?? ??? ???? ???? ??? ???.
Main SQL?? ??? ??? ? Function? ???? ?? ?? ??
? ?? ??, ????????? Function? ????? ????,
multi buffer? ??? ??? ???? ? ??.
? ??? ????? ???? ??
? ???????? ???? ???? ? ????? ??????
?? ?? ?? ?? Query Execution Cache? ??? ??.
? ????? ??? ??? ???? ???? ???? ?? ???
??? ?? ?? ????, ??? ??? ??? ? ?? ?? ??
?? ??? ????.
? ? ??? ?? ?? ??? ???? ??
? multi buffer? size? _query_execution_cache_max_size? ??
? default 64K ? ?? ??!!
? ?? ?? ??? ??? ??? ??, ??? ?? ?? ??? CPU
??? ??
SQL? ?? ?? ??? ?? ??

143
SQL? ?? ?? ??? ?? ??
? ??? ??? ???? ????
(1??? ??? ?? ??? ???????, 2?? ????? ?????)
1????? ??? ? ? ?????
2????? ??? ? ? ?????

144
10046 trace file ??
1? ????? function? 10??,
2? ????? function? 3?
Function? ?? ??? ???? ??? NUM_DISTINCT? ?? ?,
Function??? ??? ????? ????
? ??? ??.
??? ??? ??? multi buffer? check?? ??
1) CPU??? ????.
2) Function??? ?? ????? ? I/O? ?? ??? ??
??1. ??? ?????????
????? ??

145
??2. Function? ????? ??
NUM_DISTINCT? ?? ?? ?? ??1. ? ?????? ??? ? ???
????.
?? ?? Function? ???? ???? ??? ??? ? ??.
????? ???? elapsed time? 0.2?? ???.
?? ??? elapsed time? 6?
?? dbms_xplan package?????? ?? ??, 10046 trace file ????

146
??2. Function? ????? ??
? Outer Join? Select?? Function? ???? ??? ? ???? ?? ??
? Hash Join? From??? ??? ??? ???? ?? ??(??? ??)?
???? ???? ???? ?? ?? ??
??
?? ???? ???? Function? ???? ?? ??
1. Function? ??? ????? ??
2. Function? ???? ??

147
Where?? function? select??
???
??? T1???? ??? filter?? 16667? ?????,
T2???? ??? t2.c3 in (‘A’) ? fu_c2_codenm(t2.c4) ~ ? filtering
?? 5000? ?????.
???? ??? fn_c2_codenm ??? ? ? ???????
10046 trace file? ?? ?? ? ? ???, ? ? ?????? ???? SQL
? ??? ??.

148
Where?? function? select??
???
LO? BETWEEN ??? ??? ?? ????.
T2.c3 in (‘A’) and FN_C2_CODENM(t2.c4) >= ‘A’ and FN_C2_CODENM(t2.c4)
<=‘B’
??? ?? ????? 10000???.
??? ? ???? ??? ?? T2 ???? ?? ??? filter?? 5000?
? ?????. ? FN_C2_CODENM??? ? ?? ???? ???.
Where?? ?? Function? select??? ?? ??? ?, ??? ?? ???
Function? ????, ??? ?? ???, ???? ??? ??? ????
? SQL? ????? ??.

149
?? ?? SQL? ???? ?? ??? ????
FN_C2_CODENM? ? ? ????? ??????

150
????? ?? VIEW? ???? ??? ??.
? ??? ??(??? ? ??? function? ?? ????)?? ?????
??? ???. Elapsed time? ?? ???.
???? ?????? ?? ????? 100046 trace file?? ????.

151
10046 trace file? ?? ?? ????? 20000???.
10000??? ???? SQL? ?????, ? ??? ???.
?? ?? ????? 10053 trace file? ????? ?? Logical Optimizer
? ??? ??? ??.

153
VIEW merging ??
View merging? ?????? ?? ????? ??? 20000???.
Logical Optimizer? ??? ??? ??.

154
In a nutshell
select /*+ NO_MERGE(K) */ K.c3, K.c4
from ( select /*+ gather_plan_statistics leading(t1 t2) USE_HASH(T1 T2) */
t1.c3, t2.c4,
fn_c2_codenm(t2.c4) as ft2c4
where t1.c2 = 0
and t1.c4 = 2
and t1.c1 = t2.c1
and t2.c3 in ('A')
and FN_C2_CODENM(t2.c4) >= ‘A’
and FN_C2_CODENM(t2.c4) <= ‘B’ ) K
10053 trace file ???? ? SQL? ???.
??? ??? optimizer??? ?? ?? ???? ??? Filter???
View??? ????? ? ??? ??????? ???? ??? View
??? ?? ????. (Filter Push Down)
??? ?? ?? SQL? ???? t2.c3 in(‘A’) ??? ???? ??
(5000rows)?? ??? ?? ????.
Logical Optimizer? ??? ??? ??? ????
Optimizer? ??? ?? ??? ? ??.

155
To make a long story short
HASH JOIN?? 1667row? ?
????, 1667row? ???
??? check???? ???
? ????? 1667 row? ?
????. Elapsed time ? ?
??.
??? fn_c2_codnm(t2.c4)??? ?? ? ? ???????
?? ????? 10046 trace file? ?? ?? ??? ??? ? ????

156
To make a long story short
Function? ???? ?? t2.c4? ???? ? distinct number? ???? ‘0’ ?
? 1667? ?????. ??? ???????? ?????? multi buffer? ?
??? ?? ??? 1?? ????. (10046 trace file ??? ??)
?? ?? ???? t2.c4 ??? ?? unique??? ??? ? ? ???????
??? ????, ??? ?????? ??? ?? ???.

157
1. ??? Join??? ??
2. ??? ???????? ???.
???? ?? ?? ??2
select memb_nm(??????) ??????,
memb_nm(??????) ??????,
??????, ??????, ????, ????, ???
from ??
where ???? = :????
and ???? = :????
and ???? between sysdate -10/24 and sysdate;
? SQL? ?? ??? 10?????, ??? ?? ??? ???? ? ????
Brainstorming
Presenter? ??? : ?? ?? ??

158
User Defined Funtion ??
? ??? ??? ????? ??
? ??? ????? ??????? ??
? ???? ????? ??
? ??? ?? ?? ??????? ???
??
? ??? ???? ??? ??? ????
??
? ??? ???? ??? ?? ??? ??
? ? ? ?? ??? ??

159
?? ???? ??? ??
? ??? ?? ?? ?????? ??
? Decode?? ?? Case??? ??
? View Merge ??
? ??????? ????? ??
? Deterministic??? ?? ?? ??
? Result Cache ??
? ??? ?? ??? ?? SQL? ??

160
?????? ?? SQL?
??? ????? ?
? access path, join
method, join order ?
? ?????? ??
??. ?? ??? ?
???? ???

161
?? ??? SQL? ??? ??
? ? row count
? ? function? ?? ??
? ? function ?? ?? number of distinct
value
? Function? Join?? ??
? Function ?? ? base table? ????
??? ??? : integrity ?? ???
? ………

狠狠撸

SQL performance and UDF

Recommended

More Related Content

Similar to SQL performance and UDF (20)

Recently uploaded (9)

SQL performance and UDF