{"id":2706,"date":"2018-02-02T22:31:07","date_gmt":"2018-02-02T13:31:07","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2706"},"modified":"2018-02-02T22:31:07","modified_gmt":"2018-02-02T13:31:07","slug":"clinet-server-%ed%99%98%ea%b2%bd%ec%97%90%ec%84%9c%ec%9d%98-performance-tuning","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2706","title":{"rendered":"CLINET\/SERVER \ud658\uacbd\uc5d0\uc11c\uc758 PERFORMANCE TUNING"},"content":{"rendered":"
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n

PERFORMANCE TUNING\uc5d0\ub294 3\uac00\uc9c0\uba74\uc5d0\uc11c\uc758 APPROACH\uac00 \uac00\ub2a5\ud558\ub2e4.<\/p>\n

———————————————————–<\/p>\n

. SYSTEM LEVEL : HARDWARE AND SOFTWARE<\/p>\n

. DATABASE CONFIGURATION LEVEL : INIT.ORA AND STRUCTURE<\/p>\n

. APPLICATION LEVEL : APPLICATION SPECIFIC DEVELOPMENT<\/p>\n

 <\/p>\n

\ub2e4\uc74c\uc5d0\uc11c\ub294 \uc704\uc758 3\uac00\uc9c0 \uc811\uadfc\ubc29\ubc95\uc744 \ud63c\ud569\ud558\uc5ec PERFORMANCE TUNING\uc5d0 \uad00\ud558\uc5ec \uc0b4\ud3b4\ubcf8\ub2e4.<\/p>\n

 <\/p>\n

INIT.ORA<\/p>\n

——–<\/p>\n

INTERNAL PARAMETER\uc911\uc5d0\uc11c ORACLE PERFORMANCE\uc5d0 \uc911\uc694\ud55c \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 3\uac00\uc9c0\uc758 PARAMETER\ub97c \uc0b4\ud3b4\ubcf8\ub2e4.<\/p>\n

. DB_BLOCK_BUFFERS<\/p>\n

. SHARED_POOL_SIZE<\/p>\n

. SORT_AREA_SIZE<\/p>\n

 <\/p>\n

< DB_BLOCK_BUFFERS ><\/p>\n

DB_BLOCK_BUFFERS\ub294 DATA CACHING\uc744 \uc704\ud574 SGA\ub85c \ud560\ub2f9\ub418\ub294 MEMORY\ub97c \uc9c0\uc815\ud55c\ub2e4.<\/p>\n

\uc774 \uc22b\uc790\uac00 \uc99d\uac00 \ub420\uc218\ub85d PERFORMANCE\ub294 \ud5a5\uc0c1\ub41c\ub2e4. SQL \ubb38\uc7a5\uc774 \uc2e4\ud589\ub420\ub54c, \ub9cc\uc57d CACHE \ub0b4\uc5d0 RECORD\uac00 \uc5c6\ub2e4\uba74 \uc2e4DATA\ub97c \uc77d\uc5b4\ub4e4\uc5ec CACHE\uc5d0 \uc62c\ub824\ub193\ub294\ub2e4.<\/p>\n

CACHE\ub0b4\uc5d0 \uc62c\ub824\uc9c4 DATA\ub4e4\uc740 LRU \uc54c\uace0\ub9ac\uc998\uc5d0 \ub530\ub77c \uc0ad\uc81c\ub420\ub54c\uae4c\uc9c0 SGA\ub0b4\uc5d0 \uc704\uce58\ud558\uac8c \ub41c\ub2e4. \ud544\uc694\ud55c \uc790\ub8cc\ub97c DISK\ub85c\ubd80\ud130 \uc77d\uc5b4\uc624\uc9c0 \uc54a\uace0 MEMORY\ub0b4\uc5d0\uc11c \ucc3e\uc544\uc624\ub294 \ube44\uc728\uc774 \ub192\uc744\uc218\ub85d PERFORMANCE\uac00 \ub192\ub2e4. DB_BLOCK_BUFFERS\ub294 \uc2e4\uc81c\uc758 \ud560\ub2f9\ub418\ub294 MEMORY\ub97c \uc9c0\uc815\ud558\ub294 \uac83\uc774 \uc544\ub2c8\ub77c, BLOCK\uc218\ub97c \uc9c0\uc815\ud55c\ub2e4. \uc2e4\uc81c\ub85c \uc9c0\uc815\ub418\ub294 MEMORY\uc758 \uc591\uc740 DB_BLOCK_BUFFERS * DB_BLOCK_SIZE\uc758 \uac12\uc774\ub41c\ub2e4. DB_BLOCK_BUFFERS\uc758 DEFAULT \uac12\uc740 HARDWARE\uc5d0 \ub530\ub77c \ub2e4\ub974\uac8c \uc9c0\uc815\ub41c\ub2e4.<\/p>\n

\uc77c\ubc18\uc801\uc73c\ub85c \uc2e4\uc81c MAIN MEMORY\uc758 25%\uc774\ud558\ub85c \uc7a1\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n

< SHARED_POOL_SIZE>(Data Dictionary Cache, Shared Sql Area)<\/p>\n

SHARED_POOL_SIZE\ub294 SGA\ub0b4\uc5d0 DATA DICTIONARY CACHING\uacfc STORED SQL\ubb38\uc7a5\uc744 \uc704\ud55c MEMORY\uc758 \ud560\ub2f9\ub7c9\uc744 \uc9c0\uc815\ud55c\ub2e4. DATA DICTIONARY CACHE\ub294 DATA DICTIONARY \uc694\uc18c\uc758 BUFFERING\uacfc \uad00\ub828\ub418\uc5b4 \uc6b0 \uc911\uc694\ud558\ub2e4.<\/p>\n

\ud558\ub098\uc758 SQL\ubb38\uc7a5\uc774 \uc2e4\ud589\ub420\ub54c ORACLE\uc774 DATA DICTIONARY\uc790\ub8cc\ub97c \uc5ec\ub7ec\ubc88 \ucc38\uc870\ud558\uae30 \ub54c\ubb38\uc5d0 DATABASE\uc640 APPLICATION SCHEMA\uc640 STRUCTURE\uac00 MEMORY\uc5d0 \ub9ce\uc774 \uc62c\ub77c\uc640 \uc788\uc744\uc218\ub85d, DISK\ub85c \ubd80\ud130 \uc77d\uc5b4\ub4e4\uc5ec\uc624\ub294 \uc790\ub8cc\uac00 \uc801\uc5b4\uc9c0\uac8c\ub41c\ub2e4.<\/p>\n

DATA DICTIONARY\ub3c4 DB_BLOCK_BUFFERS\uc758 \uacbd\uc6b0\uc640 \ub9c8\ucc2c\uac00\uc9c0\uc758 \ubc29\ubc95\uc73c\ub85c CACHING \ub41c\ub2e4. \uac00\uc7a5 \uc774\uc0c1\uc801\uc778 \uac83\uc740 \ubaa8\ub4e0 DATA DICTIONARY\uc815\ubcf4\uac00 MEMORY\uc5d0 \uc62c\ub77c\uc640 \uc788\ub294 \uac83\uc774\ub2e4. SHAED SQL AREA\ub294 ORACLE INTERNAL\uc774 \uc2e4\ud589\ud558\ub294 \uac83\uc774\ub098 USER\uc758 \ud2b9\ubcc4\ud55c \uc2e4\ud589 \ubb38\uc7a5\uc744 \uac16\uace0\uc788\ub2e4. SQL\ubb38\uc7a5\uc774 \uc2e4\ud589\ub420\ub54c, SHARED SQL STATEMENT AREA\uc5d0 \uac19\uc740 \ubb38\uc7a5\uc774 \uc788\ub294\uc9c0\ub97c \ud655\uc778\ud558\uc5ec \ubcf4\uc544 \uc788\uc73c\uba74 \uadf8 \ubb38\uc7a5\uc774 \uc2e4\ud589\ub41c\ub2e4. \uc774\uac83\uc740 PARSING TIME\uc744 \uc904\uc5ec \uc804\uccb4\uc801\uc778 PERFORMANCE\ub97c \ud5a5\uc0c1\uc2dc\ud0a8\ub2e4. \uc774\ub54c \ubb38\uc7a5\uc774 \uac19\ub2e4\ub294 \uac83\uc740 \ubb38\uc790\uc5f4\uacfc SPACE\uae4c\uc9c0\ub3c4 \uac19\uc544\uc57c \ud558\ub294\uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \ub9cc\uc57d, \ubb38\uc7a5\uc774 \ub2e4\ub974\ub2e4\uba74 \uc0c8\ub85c\uc6b4 \ubb38\uc7a5\uc774 PARSING\ub418\uc5b4 \uc2e4\ud589\ub418\uace0 SHARED SQL AREA\uc5d0 \ub2e4\uc2dc \uc800\uc7a5\ub41c\ub2e4.<\/p>\n

<\uc608><\/p>\n

SQL> select name,customer<\/p>\n

2 from customer_information;<\/p>\n

SQL> select name,customer<\/p>\n

2 FROM customer_information;<\/p>\n

\uc704\uc758 \ub450 \ubb38\uc7a5\uc740 \uacb0\uacfc\ub97c RETURN\ud558\uc9c0\ub9cc, ‘FROM’\uc808\uc758 \ub300\uc18c\ubb38\uc790 \ub54c\ubb38\uc5d0 ORACLE \uc5d0\uc11c\ub294 \ub2e4\ub978 \ubb38\uc7a5\uc73c\ub85c \uc778\uc2dd\ud558\uc5ec SHARED SQL AREA\ub0b4\uc758 \uc788\ub294 CURSOR\ub97c \uc7ac\uc0ac\uc6a9\ud558\uc9c0 \uc54a\uac8c\ub41c\ub2e4. \uc774\uc640\uac19\uc774 SHARED SQL AREA\ub0b4\uc758 \uc790\ub8cc\uc5d0 \ub300\ud55c \uc7ac \uc774\uc6a9\uc728\uc744 \ub192\uc774\ub824\uba74, STORED PROCEDURE\ub97c \uac00\ub2a5\ud55c \ub9ce\uc774 \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \uc88b\ub2e4. SHARED_POOL_SIZE PATAMETER\uc758 \uac12\uc740 BYTE \ub2e8\uc704\ub85c \uc9c0\uc815\ub41c\ub2e4. DEFAULT\uac12\uc740 SYSTEM\uc5d0 \ub530\ub77c \ub2e4\uc591\ud558\ub2e4.<\/p>\n

 <\/p>\n

< SORT_AREA_SIZE ><\/p>\n

SORT_AREA_SIZE\ub294 USER\ub2f9 SORTING DATA\uc5d0 \ud544\uc694\ud55c MEMORY\uc758 \uc591\uc744 \uc9c0\uc815\ud55c\ub2e4. \uc774\uac83\uc740 SGA \uc678\ubd80\uc5d0 \uc874\uc7ac\ud558\uba70, \ud544\uc694\uc2dc\uc5d0\ub9cc \uc0ac\uc6a9\ub41c\ub2e4. SORT_AREA_SIZE PARAMETER\uc758 \uac12\uc740 BYTE\ub2e8\uc704\ub85c \uc9c0\uc815\ud558\uba70, USER\ub2f9\uc758 PROCESS\uc5d0 \ud574\ub2f9\ud558\ub294 \uac12\uc774\ub2e4.<\/p>\n

 <\/p>\n

< DATABASE STRUCTURE ANALYSIS ><\/p>\n

ORACLE KERNAL\uc758 \uae30\ub2a5\uacfc \uc800\uc7a5 \ub2a5\ub825\uc774 \uc99d\uac00\ub428\uc5d0 \ub530\ub77c \uac01\uae30 \ub2e4\ub978 TABLESPACE\ub85c ORACLE STRUCTURE\ub97c \ub098\ub204\uace0, \ub2e4\uc911 DISK\uc640 CONTROLLER\ub97c \ud1b5\ud558\uc5ec \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0a4\uace0, TABLESPACE\uc758 FRAGMENTATION\uc744 \uac10\uc18c\ud558\ub294\uac83\uc774 \uc911\uc694\ud558\uac8c \ub300\ub450\ub418\uace0 \uc788\ub2e4.<\/p>\n

ORACLE\uc744 INSTALL\ud558\uba74 DEFAULT\ub85c ROLLBACK SEGMENT<\/p>\n

TABLESPACE(ROLLBACK_DATA), TEMPORARY TABLESPACE(TEMPORARY_DATA),USER TABLESPACE(USER_DATA)\uac00 \uc0dd\uc131\ub41c\ub2e4. \uac00\ub2a5\ud558\uba74 \uac01\uae30 \ub2e4\ub978 DISK\uc640 CONTROLLER\ub85c \ubd84\uc0b0 \uc2dc\ucf1c\ub193\ub294 \uac83\uc774 \uc88b\ub2e4. \uc5ec\uae30\uc5d0 \ub354\ud558\uc5ec USER\uac00 \uc0dd\uc131\ud558\ub294 APPLICATION\uc774\ub098 OBJECT\ub4e4\ub3c4 \ubd84\uc0b0\ud558\uc5ec TABLESPACE\uac04\uc758 DISK I\/O\ub97c \ubd84\uc0b0\uc2dc\ud0a4\ub294 \uac83\uc774 \uc88b\ub2e4.<\/p>\n

 <\/p>\n

< CLIENT-SERVER ANALYSIS ><\/p>\n

. Database Communication Protocol<\/p>\n

\ud604\uc7ac ORACLE\uacfc \uc811\uc18d\ud558\uae30 \uc704\ud574\uc11c\ub294 ODBC\ub97c \ud1b5\ud558\uac70\ub098 ORACLE\uc758 SQL*NET PRODUCT\ub97c \ud1b5\ud558\uc5ec \uac00\ub2a5\ud558\ub2e4. PERFORMANCE \ubfd0\uc544\ub2c8\ub77c ERROR TRAPPING, MESSAGE HANDLING\uc744 \uc704\ud574\uc11c ORACLE\uc758 SQL*NET\uc744 \uc4f0\ub294\uac83\uc744 \uad8c\uc7a5\ud55c\ub2e4.<\/p>\n

. Client Versus Server Processing<\/p>\n

CLIENT-SERVER\ud658\uacbd\uc744 \uc0dd\uac01\ud560\ub54c PC\ub098 MACINTOSH WORKSTATION\uc744 SERVER\uc5d0 CONNECT \ud558\ub294 \uac83 \ubfd0\uc544\ub2c8\ub77c UNIX SYSTEM\uc744 SQL*NET\uc744 \ud1b5\ud558\uc5ec \ub2e4\ub978 UNIX\uc640 CONNECT \ud558\ub294 \uac83\uae4c\uc9c0\ub3c4 \ud3ec\ud568\ud558\uc5ec \uc0dd\uac01\ud558\uc5ec\uc57c \ud55c\ub2e4. EXECUTION\uc5d0 \ub300\ud558\uc5ec CLINET \ub300 SERVER PERFORMANCE\uc758 \ucc28\uc774\uc810\uc744 TEST\ud558\ub824\uba74, QUERY\ubb38\uc744 CLIENT SQL*PLUS\uc5d0\uc11c (SET TIMING ON \uc0c1\ud0dc\uc5d0\uc11c) \uc2e4\ud589\ud558\uc5ec \ubcf4\uace0, \uadf8 \ubb38\uc7a5\uc744 SERVER\uc5d0 \uc804\uc1a1\ud558\uc5ec SERVER SQL *PLUS\uc5d0\uc11c (SET TIMING ON \uc0c1\ud0dc\uc5d0\uc11c) \uc2e4\ud589\ud558\uc5ec \ubcf8\ub2e4. SERVER SIZE\uc5d0\uc11c \uc218\ud589\ud558\ub294 \uac83\uc758 \uc8fc\uc694\ud55c \uc787\uc810\uc740 \uc2e4\ud589\uc2dc\uac04\uc774 \uc9e7\uc544\uc9c0\ub294 \uac83\uc774\uace0, \uacb0\uad6d \uadf8\uac83\uc740 SYSTEM\uc758 PERFORMANCE\ub97c \ud5a5\uc0c1\uc2dc\ud0a4\ub294 \uacb0\uacfc\ub97c \uac16\uac8c\ub41c\ub2e4.<\/p>\n

. Client System Configuration<\/p>\n

CLIENT \uc5d0\uc11c DML \uc18d\ub3c4\ub294 \ub9e4\uc6b0 \uc911\uc694\ud558\ub2e4. \ub354 \ube60\ub978 PROCESSOR\uac00 \ubd80\uac00\uc801\uc778 \ucc28\uc774\ub97c \uac00\uc838\uc624\uac8c\ub41c\ub2e4. \uadf8\ub7ec\ub098, \ud070 \ucc28\uc774\uc810\uc740 RAM\uc758 \uc99d\uac00\uc5d0\uc11c \uc628\ub2e4.<\/p>\n

. Location Of Products And Source Code<\/p>\n

\ub300\ubd80\ubd84\uc758 CLIENT-SERVER \uad6c\uc131\uc740 NOVELL\uacfc \uac19\uc740 NETWORK O\/S\ub97c FILE SERVER\ub85c \uc774\uc6a9\ud55c\ub2e4. \uc774 \uad6c\uc131\uc740 PRODUCT\uc640 SOURCE CODE\ub97c NETWORK DRIVE\uc704\uc5d0 \ub193\uace0, CLIENT \uc5d0\uc11c\ub294 \ucc38\uc870\ub9cc \ud558\ub3c4\ub85d \ud55c\ub2e4. \uc774\ub7ec\ud55c \uad6c\uc131\uc740 \uc81c\ud488\uc758 UPGRADE\uc2dc\uac04\uc758 \uc808\uc57d\uc774\ub098 VERSION \uad00\ub9ac\uc5d0 \uc6a9\uc774\ud558\ub2e4. \uadf8\ub7ec\ub098 SOURCE CODE\ub97c \ud3ec\ud568\ud558\uc5ec \uc81c\ud488\uc744 PC CLIENT\uc5d0 INSTALL\ud558\ub294 \uac83\uc774 \uc18d \ub294 \ub354 \ube60\ub974\ub2e4. NETWORK I\/O\uac00 \uac10\uc18c\ub418\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n

. Windows Swap File<\/p>\n

WINDOWS SWAP FILE\uc740 \ub2e4\ub978 \uac83\uc5d0\uc758\ud574 \ucc38\uc870\ub418\ub294 FILE\ub4e4\uc774\ubbc0\ub85c, CLIENT WORKSTATION \uc5d0\uc11c \uac00\uc7a5 \ube60\ub978 DRIVE\uc5d0 \uc704\uce58\ud558\uac8c \ud558\ub294 \uac83\uc744 \uad8c\uc7a5\ud55c\ub2e4. \ub2e4\ub978 \ubc29\ubc95\uc740 \ubcc4\ub3c4\uc758 MEMORY\ub97c \ucd94\uac00\ud558\uc5ec (16M\ucd94 \ub97c \uad8c\uc7a5\ud55c\ub2e4) 16M VIRTUAL DRIVE\ub97c \uc0dd\uc131\ud558\uc5ec WINDOWS SWAP FILE\uc744 \uc774 DRIVE\uc5d0 \uc0dd\uc131\ud558\ub294 \uac83\ub3c4 \uc88b\ub2e4. \uc774 \ubc29\ubc95\uc740 DISK I\/O\ub97c \uc904\uc774\uace0 MEMORY\ub97c ACCESS\ud558\uac8c \ud55c\ub2e4. \uc774\ub7ec\ud55c \ubc29\ubc95\uc740 \uacfc\uac70\uc5d0 \ub9ce\uc774 \uc0ac\uc6a9\ud558\uc600\uc73c\ub098, \uba87\uba87 \ubb38\uc81c\ub97c \uc57c\uae30\ud558\uae30\ub3c4 \ud55c\ub2e4.<\/p>\n

. Stored Procedures, Functions And Database Triggers vs. Pl\/Sql In Source<\/p>\n

STORED PROCEDURE\ub098 DATABASE TRIGER\ub294 \uc774\ubbf8 PARSING\ub418\uc5b4 \uc788\uc5b4 SQL\ubb38\uc7a5\uc774\ub098 PL\/SQL BLOCK\uc744 \uc9c1\uc811 \ud638\ucd9c\ud558\ub294 \uac83\ubcf4\ub2e4 \uc2e4\ud589\uc5d0 \ud544\uc694\ud55c OVERHEAD\ub97c \uc904\uc5ec\uc900\ub2e4.<\/p>\n

. Eliminating Network I\/O And Repetitiveness<\/p>\n

CLIENT-SERVER \uad6c\uc131\uc5d0\uc11c PERFORMANCE\ub97c \ud5a5\uc0c1\uc2dc\ud0a4\ub294 \ub610 \ub2e4\ub978 \ubc29\ubc95\uc740 DATABASE\ub97c \ud638\ucd9c\ud558\uc5ec NETWORK OVERHEAD\ub97c \ucd08\ub798\ud558\ub294 \ubc18\ubcf5\uc801\uc778 PROGRAM\uc758 LOGIC\uc744 \uc7ac\uad6c\uc131\ud558\ub294 \ubc29\ubc95\uc774 \uc788\ub2e4. \uc774 \ubc29\ubc95\uc740 \uac01 RECORD\ub97c \uc870\uc791\ud558\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \ud55c \ubb38\uc7a5\uc5d0 \uc758\ud558\uc5ec DATA\ub97c \uc870\uc791\ud558\ub294 \ubc29\ubc95\uc744 \uac04\uad6c\ud558\ub294 \uac83\uc774 . \uc774\uac83\uc740 DATABASE CALL\uc758 \uc22b\uc790\ub3c4 \uc904\uc774\ub294 \uacb0\uacfc\ub97c \uac00\uc838\uc628\ub2e4.<\/p>\n

\ub2e4\uc74c\uc758 \ubc29\ubc95\uc5d0\uc11c \ubcf4\uba74<\/p>\n

Method A<\/p>\n

1) Start;<\/p>\n

2) Fetch Invoice Record;<\/p>\n

3) Print Invoice Record;<\/p>\n

4) Update Invoice Record To Printed;<\/p>\n

5) Loop To Start;<\/p>\n

Method B<\/p>\n

1) Update all invoices not Printed to In Process;<\/p>\n

2) Start;<\/p>\n

3) Fetch Invoice record;<\/p>\n

4) Print Invoice Record;<\/p>\n

5) Loop To Start;<\/p>\n

6) Update Invoice Record To Printed;<\/p>\n

1000\uac74\uc758 \ud574\ub2f9 DATA\uac00 \uc788\ub2e4\uba74 , METHOD A\uc5d0 \uc758\ud574\uc11c\ub294 DATA\uac00 1\uac74\uc529 FETCH\ub418\uc5b4 1000\ubc88\uc758 UPDATE\uac00 \ubc1c\uc0dd\ud558\uac8c \ub2e4. \ubc18\uba74 METHOD B\uc758 \uacbd\uc6b0, \ud55c\ubc88\uc758 UPDATE\uc5d0 \uc758\ud574 1000\uac74\uc758 DATA\uac00 UPDATE\ub418\uac8c\ub41c\ub2e4.<\/p>\n

. RULE BASED vs. COST BASED OPTIMIZATION<\/p>\n

ORACLE 7\uc5d0 \ub4e4\uc5b4\uc624\uba74\uc11c, COSE BASED OPTIMIZER\ub77c\ub294 \uc0c8\ub85c\uc6b4 ORACLE QUERY OPTIMIZER \uac1c\ub150\uc774 \ub3c4\uc785\ub418\uc5c8\ub2e4. ORACLE 7\uc774\uc804\uc5d0\ub294 \uc2e4\ud589 PLAN\uc744 \uacb0\uc815\ud558\ub294\ub370 14\uac00\uc9c0 \uc815\ub3c4\uc758 RULE\uc5d0 \ub530\ub974\ub294 RULE BASED OPTIMIZER\uac00 \uc0ac\uc6a9\ub418\uc5b4\uc838 \uc654\ub2e4. \uc774 \ubc29\ubc95\uc740 SQL OPTIMIZATION\uc5d0 \uc911\uc694\ud55c \uc694\uc18c\uac00 \ub418\ub294 DATA\uc758 \ubd84\uc0b0\uc815\ub3c4\uc5d0 \ub300\ud574\uc11c\ub294 \uc804\ud600 \uc778\uc2dd\ud558\uc9c0 \ubabb\ud558\ub294 \ubc29\ubc95\uc774\uc5c8\ub2e4. DATA\uc758 \ubd84\uc0b0\uc815\ub3c4\ub294 QUERY \uc2e4\ud589\uc758 \ubc29\ubc95\uc744 \uacb0\uc815\ud558\ub294\ub370 \uc911\uc694\ud55c \uc694\uc18c\uc774 . \uacb0\uad6d, ORACLE7 \uc774\uc804\uc5d0 \uc788\uc5b4\uc11c\uc758 PERFORMANCE TUNING\uc740 SQL\ubb38\uc7a5\uc744 \uc4f0\ub294 \uac1c\ubc1c\uc790\uc758 \ubc29\ubc95 \uc124\uc815\uc5d0 \uc758\ud574 \uacb0\uc815 \ub418\uc5b4\uc838 \uc654\ub2e4. COST BASED OPTIMIZER\ub97c \uc0ac\uc6a9\ud558\ub294 ORACLE7 \uc5d0\uc11c\ub3c4 OPTIMIZER HINT\ub97c \ucd94\uac00\ud568\uc73c\ub85c\uc368 RULE BASED OPTIOMIZER\uc5d0 \uc758\ud55c \ubc29\ubc95\uc758 \uc0ac\uc6a9\uc774 \uac00\ub2a5\ud558\ub2e4.<\/p>\n

ORACLE RULE BASED OPTIMIZER\ub294 INIT.ORA FILE\uc758 OPTIMIZER_MODE PARAMETER\ub97c ‘RULE’\ub85c SETTING \ud568\uc73c\ub85c\uc368\ub3c4 \uc0ac\uc6a9\uac00\ub2a5\ud558\ub2e4. \uc774 PARAMERER\uc758 DEFAULT \uac12\uc740 ‘CHOOSE’\uc774\ub2e4. COST BASED OPTIMIZER\ub294 DATA DICTIONARY\uc5d0 \uc800\uc7a5\ub418\uc5b4 \uc788\ub294 DATA \ubd84\uc0b0 \ud604\ud669\uc744 \uc0ac\uc6a9\ud55c\ub2e4. \ud604\ud669\uc740 \ub2e4\uc74c\uc758 TABLE\uc5d0\uc11c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n

USER_TABLES ALL_TABLES DBA_TABLES<\/p>\n

USER_TAB_COLUMNS ALL_TAB_COLUMNS DBA_TAB_COLUMNS<\/p>\n

USER_INDEXES ALL_INDEXES DBA_INDEXES<\/p>\n

USER_CLUSTERS DBA_CLUSTERS<\/p>\n

\ud604\ud669 \uc815\ubcf4\ub294 ‘ANALYZE’ COMMAND\uc5d0 \uc758\ud558\uc5ec \uc0dd\uc131\ub41c\ub2e4. \uc774 COMMAND\ub294 \uac01\uac01\uc758 TABLE \uc5d0 \ub300\ud558\uc5ec \uc2e4\ud589 \ub418\uc5b4\uc57c \ud55c\ub2e4. ANALYZE COMMAND\uc5d0\ub294 DATA DICTIONARY\uc800\uc7a5\ub418\ub294 \ud604\ud669\uc758 \uc885\ub958\ub97c \uacb0\uc815 \ud560 \uc218 \uc788\ub294 \uba87\uac1c\uc758 OPTION\uc774 \uc788\ub2e4.<\/p>\n

 <\/p>\n

< ANALYZE COMMAND\uc758 \uc608 : OPTION\ud3ec\ud568 ><\/p>\n

SQL*PLUS\uc5d0\uc11c \uc2e4\ud589\ud55c\ub2e4.<\/p>\n

1. TABLE \ub300\ud55c ESTIMATED STATISTICS\uc758 \uc0dd\uc131<\/p>\n

: 1064 \uac1c\uc758 RECORD\ub97c RANDOM\ud558\uac8c SAMPLING\ud55c\ub2e4.<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS;<\/p>\n

: DATA\uc758 SAMPLING\uc744 \uc704\ud55c 2\uac00\uc9c0\uc758 OPTION\uc774 \uc788\ub2e4.<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE 5000 ROWS;<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE 25 PERCENT;<\/p>\n

2. TABLE\uc5d0 \ub300\ud55c COMPLETE STATISTICS\uc758 \uc0dd\uc131<\/p>\n

: TABLE\uc758 \ubaa8\ub4e0 ROW\uc5d0 \ub300\ud55c TABLE\uacfc INDEX\uc758 \uc790\ub8cc\ud604\ud669\uc744 \uc0dd\uc131\ud55c\ub2e4.<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;<\/p>\n

3. TABLE\uc5d0 \ub300\ud55c STATISTICS\uc758 DELETE<\/p>\n

: TABLE\uacfc INDEX\uc758 \uc790\ub8cc\ud604\ud669\uc744 \uc0ad\uc81c\ud55c\ub2e4.<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME DELETE STATISTICS;<\/p>\n

4. CHAINED_ROWS TABLE\uc5d0 CHAINED ROW\ub97c \ub4f1\ub85d<\/p>\n

: UTLCHAIN.SQL SCRIPT(CHAINED_ROWS TABLE\uc744 \uc0dd\uc131\ud55c\ub2e4)\ub97c \uc544\ub798\uc758 COMMAND\ub97c \uc2e4\ud589 \ud558\uae30\uc804\uc5d0 \uc218\ud589\ud558\uc5ec\uc57c \ud55c\ub2e4. CHAINED_ROWS TABLE\uc5d0\ub294 CHAINED ROW\uc758 ROWID\ub97c \ud3ec\ud568\ud55c \uac01\uac01\uc758 ROW\ub4e4\uc774 INSERT \ub41c\ub2e4. \uc774 ROWID\ub97c \uc774\uc6a9\ud558\uc5ec \uc6d0\ub798\uc758 TABLE\ub85c \ubd80\ud130 CHAINED\ub41c ROW\ub4e4\uc744 DELETE\ud55c \ud6c4, CHAINED_ROWS TABLE\uc758 ROW\ub97c \uc774\uc6a9\ud558\uc5ec \uc0c8\ub85c\uc6b4 ROW\ub97c \uc0dd\uc131\ud558\uc5ec CHAIN\uc744 \uc5c6\uc568 \uc218 \uc788\ub2e4.<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS;<\/p>\n

5. TABLE\uacfc INDEX\uc5d0 CORRUPTION\uc774 \uc788\ub294\uc9c0\ub97c VALIDATE<\/p>\n

: \ub9cc\uc57d CORRUPTION\uc774 \uc874\uc7ac\ud55c\ub2e4\uba74, \ub2e4\uc74c COMMAND\ub97c \uc2e4\ud589 \ud558\uc600\uc744\ub54c ‘DROP AND RECREATE’ MESSAGE\uac00 \ud45c\uc2dc\ub41c\ub2e4.<\/p>\n

SQL> ANALYZE TABLE TABLE_NAME VALID STRUCTURE CASCADE;<\/p>\n

ANALYZE COMMAND\uac00 \uc2e4\ud589\ub418\uba74, SHARED SQL AREA\uc5d0 \uc874\uc7ac\ud558\uba74\uc11c \ucc38\uc870\ub418\ub358 OBJECT \ub4e4\uc740 \ub2e4\uc74c\uc5d0 \uc0c8\ub85c\uc6b4 \ud604\ud669\uc73c\ub85c \ucc38\uc870\ub418\uae30 \uc704\ud574 SHARED SQL AREA\uc5d0\uc11c \uc0ad\uc81c\ub41c\ub2e4.<\/p>\n

ANALYZE COMMAND\ub294 \uc704\uc5d0\uc11c\uc640 \uac19\uc774 \ud55c TABLE\uc529\uc5d0 \ub300\ud558\uc5ec \uc2e4\ud589\ub420 \uc218\ub3c4 \uc788\uc73c\ub098, ORACLE\uc5d0\uc11c \uacf5\ud558\ub294 PACKAGE PROCEDURE\uc778 DBMS_UTILITY.ANALYZE_SCHEMA\ub97c \ud638\ucd9c\ud558\uc5ec \ud55c USER\uc758 \uc804\uccb4 OBJECT\uc5d0 \ub300\ud574\uc11c \uc804\uccb4\uc801\uc73c\ub85c \uc2e4\ud589 \ud560 \uc218\ub3c4 \uc788\ub2e4.<\/p>\n

SQL> BEGIN<\/p>\n

2 DBMS_UTILITY.ANALYZE_SCHEMA(‘USER_NAME’,’COMPUTE’);<\/p>\n

3 END;<\/p>\n

4 \/<\/p>\n

ANALYZE\ub418\ub2c8 \uc54a\uc740 DATA DICTIONAY TABLE\uc5d0 \ub300\ud574\uc11c\ub294 RULE BASED OPTIMIZER\uac00 \uc0ac\uc6a9\ub41c\ub2e4. COST BASED OPTIMIZER\ub97c \uc0ac\uc6a9\ud558\uac8c \ud558\uae30 \uc704\ud574\uc11c\ub294, \uc704\uc758 PACKAGED PROCEDURE\ub97c SYS\uc758 SCHEMA\uc5d0 \ub300\ud574 \uc2e4\ud589 \ub418\uc5b4\uc838\uc57c \ud55c\ub2e4.<\/p>\n

. THE ORACLE MONITOR AND ORACLE V$TABLES<\/p>\n

ORACLE MONITOR\ub294 SQLDBA UTILITY\uc0c1 \uc11c \uac00\ub2a5\ud558\ub2e4. MONITOR UTILITY\ub294 ORACLE\uc758 V$VIEW\uc774\ub098 \uc5ec\ub7ec\uac1c\uc758 V$VIEW\ub97c JOIN\ud558\uc5ec \uc815\ubcf4\ub97c \uac00\uc838\uc628\ub2e4.<\/p>\n

. INDEXING STRATEGIES AND REVIEW<\/p>\n

TABLE\uc5d0 \ub300\ud55c INDEXING\uc740 SYSTEM PERFORMANCE\uc5d0 \uc911\uc694\ud55c KEY\uac00 \ub418\uba70, INDEXING\uc758 \uc0dd\uc131\uc5d0\ub294 \uc8fc\uc758\ub97c \uc694\ud55c\ub2e4. \ud55c TABLE\uc5d0 \ub300\ud558\uc5ec INDEX\uc758 \uac2f\uc218\ub97c 3\uac1c\uae4c\uc9c0\ub85c \uc81c\ud55c\ud558\ub294 \uac83\uc774 \uc88b\uc73c\uba70, \uac00\ub2a5\ud55c\ud55c 5\uac1c \uc774\uc0c1\uc740 \ub9cc\ub4e4\uc9c0 \uc54a\ub294 \uac83\uc774 \uc88b\ub2e4.<\/p>\n

3\uac00\uc9c0 TYPE\uc758 TABLE\uc5d0 \ub300\ud558\uc5ec \uac01\uae30 \uc77c\ubc18\uc801\uc778 INDEX\uae30\ubc95\uc5d0 \ub300\ud558\uc5ec \ub2e4\uc74c\uc5d0\uc11c \uc0b4\ud3b4\ubcf8\ub2e4.<\/p>\n

1. \ub9cc\uc57d TABLE\uc774 QUERY\ub9cc\uc744 \uc704\ud55c \uac83\uc774\ub77c\uba74, \uc704\uc5d0\uc11c\uc758 \uacbd\uc6b0\ubcf4\ub2e4 \ub9ce\uc740 \uc218\uc758 INDEX \ub97c \uc0dd\uc131\ud558\uc5ec\ub3c4 \uc88b\ub2e4.<\/p>\n

2. \ub9cc\uc57d TABLE\uc5d0 \ub300\ud55c QUERY\ub294 \uc801\uace0, INSERT\ub098 UPDATE\uac00 \uc8fc\ub85c \uc774\ub8e8\uc5b4 \uc9c4\ub2e4\uba74 INDEX\ub97c \uc801\uac8c \uac00\uc838\uac00<\/p>\n

\ub3c4\ub85d \uad8c\uc7a5\ud55c\ub2e4. (DATA\uc758 \uc6b4\uc6a9\uc740 TABLE\uc758 DATA\ubfd0\ub9cc\uc544\ub2c8\ub77c TABLE\uc5d0 \ub300\ud55c INDEX\uae4c\uc9c0 UPDATE\uac00 \uc774\ub8e8\uc5b4<\/p>\n

\uc838\uc57c \ud558\uae30\ub54c\ubb38\uc774\ub2e4)<\/p>\n

3. \ub9cc\uc57d TABLE\uc774 INSERT,UPDATE,QUERY\uac00 \ubaa8\ub450 \ud544\uc694\ud558\ub2e4\uba74, INDEXING\uc5d0 \ub9e4\uc6b0 \uc8fc\uc758\ub97c \uc694\ud558\ub098, INDEX\uc740<\/p>\n

\uaf2d \ud544\uc694\ud558\ub2e4.<\/p>\n

. EXPLAIN PLAN<\/p>\n

EXPLAIN PLAN COMMAND\ub294 ORACLE OPTIMIZER\uac00 SQL\ubb38\uc758 \uc2e4\ud589\uc5d0 \uc0ac\uc6a9\ud560 EXECUTION PLAN\uc744 \uac1c\ubc1c\uc790\uc5d0\uac8c \uc81c\uacf5\ud574\uc900\ub2e4. \uc774 COMMAND\ub294 SQL\ubb38\uc7a5\uc774 \uc2e4\uc81c\ub85c \uc2e4\ud589 \ub418\uc9c0\ub294 \uc54a\uace0, \uc2e4\ud589 PLAN\uc774\ub098 OUTLINE\ub9cc\uc774 ORACLE TABLE\uc5d0 \uc800\uc7a5\ub418\ubbc0\ub85c SQL\ubb38\uc7a5\uc758 PERFORMANCE\ub97c \ub192\uc774\ub294\ub370 \ub9e4\uc6b0 \ub3c4\uc6c0\uc774\ub41c\ub2e4. EXPLAIN PLAN COMMAND\ub97c \uc2e4\ud589\ud558\uae30 \uc804\uc5d0 COMMAND\ub97c \uc218\ud589\ud560 ORACLE USER\uc5d0 \uc758\ud574 UTLPLAN.SQL\uc774 \uba3c\uc800 \uc218\ud589\ub418\uc5b4\uc838\uc57c \ud55c\ub2e4. \uc774 SCRIPT\ub294 PLAN_TABLE \uc0dd\uc131\ud55c\ub2e4.<\/p>\n

SQL> EXPLAIN PLAN<\/p>\n

2 SET STATEMENT_ID = ‘QUERY1’ FOR<\/p>\n

3 SELECT CUST_ID, NAME FROM CUST_INFO WHERE CUST_ID = ‘199’;<\/p>\n

\uc774 TABLE\uc758 \uc790\ub8cc\ub97c \uc870\ud68c\ud558\uae30 \uc704\ud574\uc11c\ub294 SQL\ubb38\uc744 \uc0ac\uc6a9\ud558\uc5ec\uc57c \ud55c\ub2e4.<\/p>\n

SQL> SELECT Operation, Options, Object_Name, Id, Parent_Id, Position<\/p>\n

2 FROM PLAN_TABLE<\/p>\n

3 WHERE STATEMENT_ID = ‘QUERY1’<\/p>\n

4 ORDER BY Id;<\/p>\n

< OUTPUT ><\/p>\n

OPERATION OPT OBJECT_NAME ID P_ID POS<\/p>\n

————– —- ————– — —- —<\/p>\n

SELECT STATEMENT 0<\/p>\n

TABLE ACCESS FULL CUST_INFO 1 0<\/p>\n

SQL> SELECT Lpad(‘ ‘,2*(Level-1))||<\/p>\n

2 Operation||’ ‘||<\/p>\n

3 Options||’ ‘||<\/p>\n

4 Object_Name||’ ‘||<\/p>\n

5 Decode(Id,0,’Cost = ‘||Position) ‘Query Plan’<\/p>\n

6 FROM PLAN_TABLE<\/p>\n

7 START WITH ID = 0<\/p>\n

8 AND STATEMENT_ID = ‘QUERY1’<\/p>\n

9 CONNECT BY PRIOR_ID = PARENT_ID<\/p>\n

10 AND STATEMENT_ID = ‘QUERY1’;<\/p>\n

< OUTPUT ><\/p>\n

QUERY PLAN<\/p>\n

——————————————<\/p>\n

SELECT STATEMENT Cost =<\/p>\n

TABLE ACCESS FULL CUST_INFO<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

PERFORMANCE TUNING\uc5d0\ub294 3\uac00\uc9c0\uba74\uc5d0\uc11c\uc758 APPROACH\uac00 \uac00\ub2a5\ud558\ub2e4. ———————————————————– . SYSTEM LEVEL : HARDWARE AND SOFTWARE . DATABASE CONFIGURATION LEVEL : INIT.ORA AND STRUCTURE . APPLICATION LEVEL : APPLICATION SPECIFIC DEVELOPMENT   \ub2e4\uc74c\uc5d0\uc11c\ub294 \uc704\uc758 3\uac00\uc9c0 \uc811\uadfc\ubc29\ubc95\uc744 \ud63c\ud569\ud558\uc5ec PERFORMANCE TUNING\uc5d0 \uad00\ud558\uc5ec \uc0b4\ud3b4\ubcf8\ub2e4.   INIT.ORA ——– INTERNAL PARAMETER\uc911\uc5d0\uc11c ORACLE PERFORMANCE\uc5d0 \uc911\uc694\ud55c \uc601\ud5a5\uc744 \ubbf8\uce58\ub294 3\uac00\uc9c0\uc758 PARAMETER\ub97c \uc0b4\ud3b4\ubcf8\ub2e4. . DB_BLOCK_BUFFERS . SHARED_POOL_SIZE […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[896,895],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2706"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2706"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2706\/revisions"}],"predecessor-version":[{"id":2707,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2706\/revisions\/2707"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2706"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}