PowerPoint 프레젠테이션

Size: px

Start display at page:

Download "PowerPoint 프레젠테이션"

영인 양
6 years ago
Views:

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

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

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

21 Performance System

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,.

35 XML - ( ) - (tuning hint)

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.

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.

47 Job Class Job Job Class priority Job Job Class =

48 Message queuing system

49 Job Console Trouble.

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

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

$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$

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]]}

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

67 migrated row

71 Fragment Coalesce fragment Coalesce

with heavy physical reads (3) Top five sessions with heavy

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

77 Oracle database server environment

The Self-Managing Database : Automatic Health Monitoring and Alerting

The Self-Managing Database : Automatic Health Monitoring and Alerting Agenda Oracle 10g Enterpirse Manager Oracle 10g 3 rd Party PL/SQL API Summary (Self-Managing Database) ? 6% 6% 12% 55% 6% Source: IOUG