160 likes | 227 Views
ISYS366 – Week 5-6. Database Tuning - User and Rollback Data Spaces, Recovery, Backup. Database Deliverables of a System Development Process. ERD Relational model Disk space requirements Tuning goals – response time, etc. Security requirements Data requirements Data entry and retrieval
E N D
ISYS366 – Week 5-6 Database Tuning - User and Rollback Data Spaces, Recovery, Backup ISYS366 - Week05
Database Deliverables of a System Development Process • ERD • Relational model • Disk space requirements • Tuning goals – response time, etc. • Security requirements • Data requirements • Data entry and retrieval • Backup and recovery • Execution plans – EXPLAIN PLAN • Acceptance testing ISYS366 - Week05
Disk Space Requirements – Sizing tables • CREATE TABLE employee (…) • TABLESPACE user_work • PCTFREE 20 • PCTUSED 60 • STORAGE(INITIAL 10M NEXT 5K PCTINCREASE 0); ISYS366 - Week05
Disk Space Requirements – Sizing tables • Blocks • Block header = 90 bytes • 2K block size: 2048-90 = 1958 bytes • 4K block size: 4096-90 = 4006 bytes ISYS366 - Week05
Disk Space Requirements – Sizing tables • Blocks • Block header = 90 bytes • 2K block size: 2048-90 = 1958 bytes • 4K block size: 4096-90 = 4006 bytes • pctfree • the amount of space reserved in the block for updates that will expand the row • the purpose is to minimize disk I/O by reducing row chaining and row migration • row chaining occurs when you have to go to another block to get the rest of the row • row migration occurs when you have to write a row to another data block (can be cause by too low pctfree) ISYS366 - Week05
Disk Space Requirements – Sizing tables • pctfree • used for both tables and indexes • Default is 10% • The more volatile you data, the higher pctfree • For DW and OLAP, pctfree can be 0 • 1958*(10/100) = 196 bytes (round up) ISYS366 - Week05
Disk Space Requirements – Sizing tables • pctused • Determine when used blocks can be readded to the list of blocks into which rows can be added • Default is 40, which is too low for frequent deletes • For frequent deletes, try: 95 - pctfree ISYS366 - Week05
Disk Space Requirements – Sizing tables • Free list space • Each table has associated with it lists of data blocks that have been allocated for that table • These lists contain free spaces for inserting rows called "free lists“ • Blocks in the free list is less than PCTUSED full • 2048 – 90 – 196 = 1762 bytes for data ISYS366 - Week05
Disk Space Requirements – Sizing tables • Space used per row • Bytes per row • When data is available • Average bytes per row • SELECT AVG(NVL(VSIZE(col1),0)) + • AVG(NVL(VSIZE(col2),0)) + • AVG(NVL(VSIZE(col3),0)) + • AVG(NVL(VSIZE(col4),0)) • FROM table; ISYS366 - Week05
Disk Space Requirements – Sizing tables • Space used per row • When data is available • Maximum bytes per row • SELECT column_name, data_type, data_length • FROM table • WHERE table_name = 'table_name'; • 3 bytes for row header • 1 byte for each column • 1 byte for each long column • 10 bytes for each ROWID (Oracle 8) • Rows per block • Free space/bytes per row ISYS366 - Week05
Disk Space Requirements – Sizing tables • Determining if pctfree is correct • ANALYZE TABLE table COMPUTE STATISTICS; • RETRIEVE DATA SELECT Num_rows, Blocks, Num_rows/Blocks FROM User_Tables WHERE Table_name = 'TABLE_NAME'; • Perform an update • Run ANALYZE again • If the number of rows is fewer, then that means they have been moved to a new data block. Thus, the pctfree is NOT high enough. • If, however, the Avg_space (also generated by ANALYZE) is large, then the pctfree is TOO high ISYS366 - Week05
Determining actual storage • SELECT COUNT(*) FROM mytable; • SELECT COUNT (DISTINCT (SUBSTR (ROWID, 1, 8)) || (SUBSTR (ROWID, 15,4))) FROM mytable; • #rows per block = #rows/#blocks OR • ANALYZE TABLE table COMPUTE STATISTICS; • RETRIEVE DATA • SELECT Num_rows, Blocks, Num_rows/Blocks FROM User_Tables WHERE Table_name = 'TABLE_NAME'; ISYS366 - Week05
Disk Space Requirements – Sizing indexes • Blocks • Block header = 161 bytes • 2K block size: 2048-161 = 1887 bytes • 4K block size: 4096-161 = 3935 bytes • pctfree • Same as for tables ISYS366 - Week05
Disk Space Requirements – Sizing indexes • Space used per index row • Average bytes per indexed column • 8 bytes for index row header • 1 byte for each column • 1 byte for each long column • 1 byte if index is UNIQUE • 6-10 bytes for each ROWID (Oracle 8) • Blocking Factor (rows per block) • Free space/bytes per row ISYS366 - Week05
Disk Space Requirements – Sizing indexes • N.B. deleted index space is rarely reused, so indexes may grow even if tables don't! ISYS366 - Week05
Sizing Tablespaces SQL> select * From user_tablespaces; TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE --------------- -------------- ----------- ----------- ----------- ------------ MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO ---------- --------- --------- --------- ---------- --------- SYSTEM 106496 106496 1 300 1 0 ONLINE PERMANENT LOGGING DICTIONARY USER RBS 1048576 1048576 2 121 0 0 ONLINE PERMANENT LOGGING DICTIONARY USER USERS 57344 57344 1 121 1 0 ONLINE PERMANENT LOGGING DICTIONARY USER TEMP 106496 106496 1 121 0 0 ONLINE TEMPORARY LOGGING DICTIONARY USER INDX 57344 57344 1 121 1 0 ONLINE PERMANENT LOGGING DICTIONARY USER OEM_REPOSITORY 131072 131072 1 2.147E+09 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER DRSYS 40960 40960 1 505 50 0 ONLINE PERMANENT LOGGING DICTIONARY USER WEBBOARD 40960 40960 1 505 50 0 ONLINE PERMANENT LOGGING DICTIONARY USER 8 rows selected. ISYS366 - Week05