Oracle8
... 2 0.... 3 1.WINDOWS NT... 4 1.1.CPU...4 1.2....8 W INDOWS NT...9 2.ORACLE... 10 2.1.SHARED_POOL_SIZE...10 2.2.DB_BLOCK_BUFFERS...11 2.3.SORT_AREA_SIZE...14 2.4.DB_FILE_MULTIBLOCK_READ_COUNT...15 2.5....17 ORACLE...18 3. ORACLE... 19 3.1....19 3.2....22 ORACLE...25 4.... 26 4.1....26 4.2....26 4.3....28...31 SHARED_POOL_SIZE... 32... 35 1
Windows NT Oracle Oracle Oracle DMS Windows NT Server 4.0 SP4 Oracle8 Enterprise Edition R8.0.5 Oracle Oracle7 2
0. 1. OS 2. Oracle 3. 4. OS OS Windows NT Oracle Windows NT Oracle Oracle Oracle 2 Oracle 2 3 Windows NT Oracle SQL 3
1. Windows NT Oracle Windows NT 1.1. CPU OS CPU Oracle Oracle Oracle Oracle 1.1 4
Oracle 3 OracleServiceSID OracleStartSID OracleTNSListener80 OracleWebAssistant Web OracleWebAssistant 5
1.2 OS CPU Oracle Oracle Oracle Oracle 6
Windows NT Workstation Oracle CPU 1.3 Windows NT Server 7
1.2. Windows NT Oracle Oracle Oracle Oracle Windows NT Oracle Oracle Windows NT Server Windows NT Server 1.4 Windows NT Workstation 8
Windows NT CPU Oracle Windows NT Server 9
2. Oracle Oracle init.ora 4 SHARED_POOL_SIZE DB_BLOCK_BUFFERS SORT_AREA_SIZE DB_FILE_MULTIBLOCK_READ_COUNT 2.1. SHARED_POOL_SIZE SQL PL/SQL DB_BLOCK_BUFFERS Oracle8 R8.0.5 SHARED_POOL_SIZE 10MB 15MB 5MB SHARED_POOL_SIZE 15,000,000 INIT.ORA Oracle Oracle SHARED_POOL_SIZE 3 10
2.2. DB_BLOCK_BUFFERS Oracle8 R8.0.5 DB_BLOCK_BUFFERS 200 I/O SHARED_POOL_SIZE DB_BLOCK_BUFFERS Oracle CREATE TABLE ALTER TABLE CACHE SQL CACHE Oracle8 Oracle7 DB_BLOCK_BUFFERS CACHE_SIZE_THRESHOLD CACHE 11
DB_BLOCK_BUFFERS DB_BLOCK_BUFFERS. A A. Windows NT MB MB MB. A 20 SHARED_POOL_SIZE 20MB 4. A 102MB 128MB. DB_BLOCK_BUFFERS 16MB 14MB DB_BLOCK_SIZE 2KB 12
DB_BLOCK_BUFFERS INIT.ORA Oracle Oracle DB_BLOCK_BUFFERS 3 13
2.3. SORT_AREA_SIZE SORT_AREA_SIZE CREATE INDEX SORT_AREA_SIZE Oracle8 R8.0.5 SORT_AREA_SIZE 65,536 256KB 512KB 1MB 2MB 3 SORT_AREA_SIZE INIT.ORA Oracle SORT_AREA_SIZE 256KB Oracle SORT_AREA_SIZE MTS SORT_AREA_SIZE SHARED_POOL_SIZE MTS SHARED_POOL_SIZE 14
2.4. DB_FILE_MULTIBLOCK_READ_COUNT Full Table Scan Index Fast Full Scan I/O Oracle DB_BLOCK_SIZE 2KB DB_FILE_MULTIBLOCK_READ_COUNT I/O 16KB OLTP 8 DSS Oracle8 R8.0.5 DB_BLOCK_SIZE 2KB DB_FILE_MULTIBLOCK_READ_COUNT DB_FILE_MULTIBLOCK_READ_COUNT 16 DB_FILE_MULTIBLOCK_READ_COUNT init.ora Oracle 15
Oracle DB_FILE_MULTIBLOCK_READ_COUNT Oracle I/O DB_BLOCK_SIZE DB_FILE_MULTIBLOCK_READ_COUNT I/O I/O DB_FILE_MULTIBLOCK_READ_COUNT 16
2.5. OLTP DSS Oracle 3 OLTP SHARED_POOL_SIZE 15M 5M DB_BLOCK_BUFFERS 5,000 10,000 SORT_AREA_SIZE 256K DB_FILE_MULTIBLOCK_ READ_COUNT 8 OLTP DSS SHARED_POOL_SIZE 20M DB_BLOCK_BUFFERS 5,000 10,000 SORT_AREA_SIZE 1M 2M DB_FILE_MULTIBLOCK_ READ_COUNT 16 17
Oracle Oracle SHARED_POOL_SIZE DB_BLOCK_BUFFERS SORT_AREA_SIZE DB_FILE_MULTIBLOCK_READ_COUNT 18
3. Oracle 3.1. 90% SQL PL/SQL SQL SHARED_POOL_SIZE SHARED_POOL_SIZE V$LIBRARYCACHE 19
99.416% 90% SHARED_POOL_SIZE SQL SHARED_POOL_SIZE 90% SHARED_POOL_SIZE SHARED_POOL_SIZE V$ROWCACHE 99.634% 90% SHARED_POOL_SIZE SHARED_POOL_SIZE 2.1 20
90% 2.2 DB_BLOCK_BUFFERS DB_BLOCK_BUFFERS V$SYSSTAT 95.81% 90% DB_BLOCK_BUFFERS SHARED_POOL_SIZE DB_BLOCK_BUFFERS 2.2 21
3.2. SHARED_POOL_SIZE DB_BLOCK_BUFFERS Oracle Free Bytes Perc_Free 30% Free Bytes SHARED_POOL_SIZE SHARED_POOL_SIZE 2 22
Windows NT 1. 2. 3. Process 4. oracle80 5. Page Faults/sec 6. 3.1 Page Faults/sec Oracle 23
3.2 Page Faults/sec 3.2 Page Faults/sec DB_BLOCK_BUFFERS SHARED_POOL_SIZE SORT_AREA_SIZE 24
Oracle 90% 25
4. SQL 4.1. Oracle SQL Oracle ANALYZE 4.2. CHOOSE Oracle OPTIMIZER_MODE Oracle 26
CHOOSE init.ora OPTIMIZER_MODE Oracle OPTIMIZER_MODE FIRST_ROWS SQL 27
4.3. SQL ANALYZE ANALYZE COMPUTE STATISTICS 5% INDEX 10 SCOTT EMP 28
ANALYZE ANALYZE ANALYZE ANALYZE.SQL SCOTT 29
ANALYZE_SCHEMA Oracle DBMS_UTILITY ANALYZE_SCHEMA ANALYZE_SCHEMA( schema VARCHAR2, method VARCHAR2, estimate_rows NUMBER DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL ); schema VARCHAR2 method VARCHAR2 NULL ESTIMATE ESTIMATE estimate_rows estimate_percent estimate_rows NUMBER estimate_percent NUMBER estimate_rows SCOTT 30
OPTIMIZER_MODE = CHOOSE ANALYZE ANALYZE_SCHEMA 31
SHARED_POOL_SIZE SHARED_POOL_SIZE MTS SQL Minimum 30% --------------------------------------------------------- Minimum 1. DMS DMS 250 SQL 32
2. MTS MTS DMS DMS 20KB SQL 3. SQL SQL 4MB SQL SQL 33
4. 5.5MB 5. 30% 100 MTS 1 3 250 100 4M 5.5M 0.3 3MB 6. 4 13MB 34
Windows NT Oracle Windows NT Oracle Oracle DMS Oracle DMS 1999 6 Copyright ORACLE CORPORATION JAPAN 1999 35