1 / 16

Efficient Database Space Allocation Strategy

Understand important aspects of sizing tables and indexes, including PCTFREE and PCTUSED settings, to optimize disk space usage. Learn how to determine and adjust storage requirements for effective database management.

nara
Download Presentation

Efficient Database Space Allocation Strategy

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ISYS366 – Week 5-6 Database Tuning - User and Rollback Data Spaces, Recovery, Backup ISYS366 - Week05

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

  3. Disk Space Requirements – Sizing tables • CREATE TABLE employee (…) • TABLESPACE user_work • PCTFREE 20 • PCTUSED 60 • STORAGE(INITIAL 10M NEXT 5K PCTINCREASE 0); ISYS366 - Week05

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

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

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

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

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

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

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

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

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

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

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

  15. Disk Space Requirements – Sizing indexes • N.B. deleted index space is rarely reused, so indexes may grow even if tables don't! ISYS366 - Week05

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

More Related