1 / 38

Chap 5. Disk IO Distribution Chap 6. Index Architecture

Chap 5. Disk IO Distribution Chap 6. Index Architecture. Written by Yong-soon Kwon Summerized By Sungchan Park @ IDS Lab. 2008-07-11. Overview. Chap 5. Disk IO Distribution Oracle Storage Architecture Tuning by Disk IO Distribution Chap 6. Index Architecture Introduction to Index

danae
Télécharger la présentation

Chap 5. Disk IO Distribution Chap 6. Index Architecture

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. Chap 5. Disk IO DistributionChap 6. Index Architecture Written by Yong-soon Kwon Summerized By Sungchan Park @ IDS Lab. 2008-07-11

  2. Overview • Chap 5. Disk IO Distribution • Oracle Storage Architecture • Tuning by Disk IO Distribution • Chap 6. Index Architecture • Introduction to Index • B+-Tree Index • Bitmap Index • Reverse Key Index • Function Base Index • Random Access Tuning Center for E-Business Technology

  3. Chap 5. Disk IO Distribution

  4. Disk IO Distribution • Pros • Improving Performance • Cons • Probability of error increase • Problem on one disk affects whole table • Hard to manage Center for E-Business Technology

  5. Disk IO Distribution Rule • Disk IO Distribution Rule • We should increase the probability that all disks are accessed. • Data in each table should be distributed to all disks • How to satisfy the rule? • Optimizing Disk Organization • Segment Extent Striping • Applying Table Archtecture • Partition table, IOT table Center for E-Business Technology

  6. Optimizing Disk Organization: Volume Group • Volume Groups(VG) • Groups of Physical Disks • One physical disk cannot belong to multiple VG • Checking VG(HP UX) • “vgdisplay –v vg01” • Important Info • “Cur PV : 4” : This VG consists of four physical disks • “Act PV : 4” : Four physical disks are active in this VG Center for E-Business Technology

  7. Optimizing Disk Organization: Logical Volume • Concept of Logical Volume • Space for file system in VG • Logical Volume Usage • File System • Raw Device Center for E-Business Technology

  8. Optimizing Disk Organization: Logical Volume Striping • Logical Volume Striping Center for E-Business Technology

  9. Optimizing Disk Organization: Logical Volume Striping • Good Point of Logical Volume Striping • Achieve Disk IO distribution rule for each VG • Checking Logical Volume • “ldisplay –v /dev/vg01/lvol1” • Important Info • “Stripes 4” : This logical volume is striped with 4 disks • “Stripe Size(Kbytes) 64” : The striping size is 64KB Center for E-Business Technology

  10. Oracle Storage Architecture Center for E-Business Technology

  11. Oracle Storage Architecture • OS Block • OS Level IO unit • DB Block • n OS Block. DB IO unit • Extent • Contiguous DB Blocks. Building unit of Segment • Segment • Group of Extents(table, index) • Tablespace • Group of Segments • Database • Group of Tablespaces • Datafile • Stores Segments. n Datafile make one Tablespace Center for E-Business Technology

  12. Segment Extent Striping: Create Tablespace Center for E-Business Technology

  13. Segment Extent Striping: Create Table on Tablespace Center for E-Business Technology

  14. Segment Extent Striping: Create Table on Tablespace Center for E-Business Technology

  15. Segment Extent Striping • Good Points • Storing data evenly over all disks for all tables • Disk IO distribution rule optimization • Checking Exntent Striping Center for E-Business Technology

  16. Partition Table • Example • Data about 20~30 year old people is frequently accessed • If we just store all data in a table, there can be some disks which do not contain any data about 20~30s • Data are not properly distributed! • Partition table • Create partition w.r.t. age! Center for E-Business Technology

  17. Partition Table: Example • Result • Data about 20~30s are distributed to all 96 disks! Center for E-Business Technology

  18. Wrong IO Distrubution #1 Center for E-Business Technology

  19. Wrong IO Distribution #2 • Incrementally adding datafile to tablespace Center for E-Business Technology

  20. Chap 6. Index Architecture

  21. Pros. and Cons. ofIndexing • Pros. • Improving Retrieving Performance • Cons. • Degrading Data Manipulation Performance • Insert, Delete, … • Storage Center for E-Business Technology

  22. ROWID • ROWID • ID given to all rows in database Center for E-Business Technology

  23. Example of ROWID Center for E-Business Technology

  24. Usage of ROWID • Index Creation • Data Access • SQL Tuning Center for E-Business Technology

  25. Index • B+-Tree Index • Bitmap Index • Reverse Key Index • Function Based Index Center for E-Business Technology

  26. B+-Tree Index • B+-Tree Index • Support efficient search, delete, and insert Center for E-Business Technology

  27. B+-Tree Index Access Center for E-Business Technology

  28. B+-Tree Index: Pros. and Cons. • Pros. • Balanced • Range scan using linked list • Good for OLTP small number row access • Cons. • Bad for column with low cardinality • Index scanning 3%~5%of Table row: Worse than table full scan • Possibility of full scan on OR operation Center for E-Business Technology

  29. Bitmap Index • Bitmap Index • Good for column with low cardinality Center for E-Business Technology

  30. Bitmap Index structure Center for E-Business Technology

  31. Bitmap Index: Pros. and Cons. • Pros. • Small space • Good for column with low cardinality • Cons. • Bad for table with frequent DML • Bitmap is need to be decompressed before DML operation • Need block level lock • Bad for column with high cardinality Center for E-Business Technology

  32. Reversed Key Index • Reversed Key Index • B+-Tree is bad for sequentially increasing index key column • All insert operation need to access left block • Competetion for left block occur! • Reversed Key Index using reversed key to avoid above situation Center for E-Business Technology

  33. Reversed Key Index: Pros. and Cons. • Pros. • Avoid competetion on left block • Cons. • Range scan is impossible Center for E-Business Technology

  34. Function Based Index • Function Based Index • Index for transformed value • Example • SELECT employee_id, ceil(salary/12)*(grade-5) FROM emp WHERE mod(grade, 10)*1.2 > 10 • Creating Function Based Index • SQL> CREATE INDEX bonus_index on emp(mod(grade, 10)*1.2); Center for E-Business Technology

  35. Function Based Index: Pros. and Cons. • Pros. • Index scan for transformed value is possible • Cons. • DML overhead • Flexibility of index is low • Only used for where cluases in predefined form Center for E-Business Technology

  36. Random Access • Random Access • IO single Block • Should be reduced • Table Full Scan • Multi Block IO • Not Random Access • Index Scan • Random Access occurs Center for E-Business Technology

  37. Reducing Random Access • Reducing Random Access • Optimizing Cluster Factor • Using Optimized Index • Ex) SELECT name, date, item FROM trans WHERE id = ‘10’ AND date BETWEEN ‘200401’ AND ‘20040406’ Center for E-Business Technology

  38. Random Access and Index Scan • Random Access and Index Scan • Table Access via Index Scan is Random Access • Index Scan itself is NOT Random Access • Index Scan below 3% of data in table is effective • Over 3%, Full table Scan is effective Center for E-Business Technology

More Related