PowerPoint 프레젠테이션

Save this PDF as:
 WORD  PNG  TXT  JPG

Size: px
Start display at page:

Download "PowerPoint 프레젠테이션"

Transcription

1

2

3

4

5

6 Reasons for Poor Performance Programs 60% Design 20% System 2.5% Database 17.5% Source: ORACLE Performance Tuning 1

7

8

9

10 SMS TOOL DBA Monitoring TOOL Administration TOOL Performance Insight Backup SQL TUNING DBA Capacity Planning Job Control System Job Center POPSQL Console

11 Tool Tuning * * * Response * *

12 System DataBase Application Memory Disk I/O CPU

13

14

15 Tuning Tool Tool Methodology : Tuning Area Application(SQL) DBMS System Design Tuning Activity

16 Implementation Support Feed-back Performance SQL Tuner Support Reporting Supplied Utilities Customization Open Architecture Itself Easy Operation Work Load

17

18

19

20

21 Performance System

22

23

24

25

26

27

28

29

30

31

32 System Analysis Report; -., Oracle configuration, system resources (CPU, Memory, Disk I/O, Network I/O), SQL and Database design -Performance (tuning hint) - -, Ver4 XML,.

33

34

35 XML - ( ) - (tuning hint)

36

37 REPORT TOOL

38 REPORT TOOL

39 Bottleneck analysis ( 4 ) Disk I/O CPU Good Balance Network I/O ) Memory Memory OR IST-02002:System overhead by paging increases Paging influences response more than normal condition. CPU Available!! Disk I/O Available!! Network I/O Available!! Memory Full!! IST-02005:Use multi-thread server connections It's possible to reduce paging by using multi-thread server(shared server) which is suitable for many users. IST-02007:Reduce DB_BLOCK_BUFFERS The hit rate of database buffer in peak time is maintained more than 90 %. Taking away paging overhead by reducing DB_BLOCK_BUFFERS(INIT.ORA) takes priority.

40

41 - -, Report Capacity Planning Tool Bottleneck Tuning

42 .!!

43 Number of SQLs executed and response Number of SQLs executed Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Sep Sep Response coefficien Executed SQL Max. Connection Response ratio ( )

44 !! Bottleneck chart Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Aug Sep Sep-00 ratio

45 Average ratio of CPU usage User CPU System CPU I/O, IDLE.

46

47 Job Class Job Job Class priority Job Job Class =

48 Message queuing system

49 Job Console Trouble.

50

51 Job Control Schedule Job (Daily, weekly, monthly) Job - Calendar - Calendar ( :, )

52

53

54 SQeeL GUI Web HTML. HTML SQeeL. GUI, CGI. Demand Compile, SQeel Compile. eelvm,. GUI (Web),

55 Example of SQeeL <eel" arguments resultval "> <eel" global i, j, col_name "> <eel" col_name=keys(resultval[0]) "> <HTML> <HAED> <TITLE>SQeeL demo(result)</title> </HAED> <BODY> <eel" if count(resultval) < 0 "> ERROR = ${resultval} <eel" endif "> <TABLE border="1"> <eel" if count(resultval) > 0 "> <TR> <eel" loop i=0; i<count(col_name); i++ "> <TH> ${col_name[i]} </TH> <eel" endloop "> </TR> <eel" loop j=0; j<count(resultval); j++ "> <TR> <eel" loop i=0; i<count(col_name); i++ "> <TD> ${resultval[j][col_name[i]]}

56

57 Console

58 SQL SCOPE TABLE SCOPE INDEX SCOPE TABLESPACE SCOPE

59 SQL 3 ( ) 1. SQL 2. SQL ( HEAVY SQL SQL ) SQL --- SQL --- SQL

60 WHERE Full Scan Before SQL tuning

61 Before SQL tuning Physical read = 2865 blocks

62 Before SQL tuning Index not used

63 Edit SQL Index scan After SQL tuning

64 After SQL tuning Physical read = 4 blocks (2865 blocks before tuning)

65 After SQL tuning Index in use

66

67 migrated row

68

69

70

71 Fragment Coalesce fragment Coalesce

72

73 <mode 1> <mode 2> (1) CPU/Memory Usage (1) CPU/Memory usage (2) Oracle system I/O (2) Oracle system I/O (3) Top five sessions with heavy physical reads (3) Top five sessions with heavy physical read (4) Oracle datafile I/O (5) SQL statement with the heaviest load

74 <mode 3> <mode 4> (3) Top five sessions with heavy physical read (5) SQL statement with the heaviest load (4) Oracle datafile I/O

75 Technical Support & Consulting

76

77 Oracle database server environment