Are Indexes Unnecessary in Exadata ?

1. Are Indexes Unnecessary in Exadata? Arup Nanda Longtime Oracle DBA (and now DMA)

2. Disclaimer If you downloaded this slide deck, please note: These slides are designed merely as props to help my presentation. They are not intended to stand as independent sources of information. Therefore the contents of the slide deck are not meant to be exhaustive in any way of the content delivered at the session. Are Indexes Unnecessary in Exadata

3. Quotes “ You don’t need indexes on Exadata. “ ” Drop all the indexes and reclaim space. “ ” Why? Because there is a storage index. ” Are Indexes Unnecessary in Exadata

4. 3 Questions for “Best Practices” • Why it is better than the rest? • What happens if it is not followed? • When are they not applicable? Are Indexes Unnecessary in Exadata

5. Storage Index Are Indexes Unnecessary in Exadata

6. Instances and Databases Server Instance Combination of • Memory Areas • Background Processes Buffer cache SELECT NAME FROM CUSTOMERS WHERE STATUS ='ANGRY' DBWR PMON Storage datafile1 datafile2 Are Indexes Unnecessary in Exadata

7. Query Processing CUSTID NAME STATUS 1 JOHN HAPPY Server 2 JILL ANGRY JILL Buffer cache 3 JOE HAPPY 500 JIM HAPPY SELECT NAME FROM CUSTOMERS WHERE STATUS ='ANGRY' DBWR PMON Storage Database Block datafile1 datafile2 Are Indexes Unnecessary in Exadata

8. Index ANGRY ANGRY CUSTID CUSTID CUSTID NAME NAME NAME STATUS STATUS STATUS CUSTID NAME STATUS Block 11 HAPPY 1 11 JOHN JOHN HAPPY HAPPY 1 JOHN HAPPY 111 JOHN 2 2 JILL JILL ANGRY ANGRY 211 JILL HAPPY 2 JILL ANGRY HAPPY 332 JOE 3 JOE HAPPY 3 3 JOE JOE HAPPY HAPPY Block 42 Table Block 31 5010 JIM HAPPY 500 JIM HAPPY 500 500 JIM JIM HAPPY HAPPY Block 24 Are Indexes Unnecessary in Exadata

9. Components for Performance CPU Memory Less I/O = better performance Network I/O Controller Disk Are Indexes Unnecessary in Exadata

10. The Solution • A typical query may: • Select 10% of the entire storage • Use only 1% of the data it gets • To gain performance, the DB needs to shed weight • It has to get less from the storage  Filtering at the storage level  The storage must be cognizant of the data CPU Memory Network I/O Controller SELECT NAME FROM CUSTOMERS WHERE STATUS ='ANGRY' Filtering should be Applied Here Disk Are Indexes Unnecessary in Exadata

11. CPU iDB Memory Get NAME … STATUS = ‘ANGRY’ Network I/O Controller Disk Are Indexes Unnecessary in Exadata

12. iDB Cell Server ESS Disk1 Disk2 Disk3 Are Indexes Unnecessary in Exadata

13. Cell Server ESS Disk1 Disk2 Disk3 MIN = 3 MAX = 5 MIN = 4 MAX = 5 MIN = 1 MAX = 3 MIN = 3 MAX = 5 SELECT … FROM TABLE WHERE COL1 = 2 1 2 3 4 Storage Index Disk4 Are Indexes Unnecessary in Exadata

14. Storage Indexes • Do not point to the database blocks • Merely stores for a Storage “Unit” • Max/Min Values • Whether nulls are present • For some columns • Is on Memory of Cells; not disk • Disappears when the cell is down Are Indexes Unnecessary in Exadata

15. Checking Storage Index Use select name, value/1024/1024 as stat_value from v$mystat s, v$statname n where s.statistic# = n.statistic# and n.name in ( 'cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan’) • Output STAT_NAME STAT_VALUE ---------- ---------- SI Savings 5120.45 Smart Scan 1034.00 Are Indexes Unnecessary in Exadata

16. Offloading and Smart Scan Offloading Processing to storage cells Smart Scan Reduction in I/O Are Indexes Unnecessary in Exadata

17. Offloading • Column Projection select cust_id, sale_amt from sales • Predicate Filtering where status = ‘ANGRY’ • Function Offloading select min(sale_amt) • Virtual Columns Are Indexes Unnecessary in Exadata

18. Smart Scan Benefits • Less I/O means • Faster disk access time • Less data from storage to DB • Less buffers • Less CPU • Less data between compute nodes Are Indexes Unnecessary in Exadata

19. Why Not? • Pre-requisite for Smart Scan • Direct Path • Full Table or Full Index Scan • > 0 Predicates • Simple Comparison Operators • Other Reasons • Cell is not offload capable • The diskgroup attribute cell.smart_scan_capable set to FALSE; • Not on clustered tables, IOTs, etc. Are Indexes Unnecessary in Exadata

20. Impact of Data Distribution MIN = 1 MAX = 5 MIN = 1 MAX = 5 MIN = 1 MAX = 5 MIN = 1 MAX = 5 MIN = 3 MAX = 5 MIN = 4 MAX = 5 MIN = 1 MAX = 2 MIN = 3 MAX = 5 1 2 3 4 SELECT … FROM TABLE WHERE COL1 = 2 Disk4 Are Indexes Unnecessary in Exadata

21. 8 Columns C1 C2 C3 C4 C5 C6 C7 C8 C2 C3 C4 C5 C6 C7 C8 C9 C3 C4 C5 C6 C7 C8 C9 C10 3 1 2 Table T1 Are Indexes Unnecessary in Exadata

22. No Predicate • Aggregations select sum(sale_amt) from sales • Sorting select ... from sales order by sale_amt; Index on SALE_AMT Are Indexes Unnecessary in Exadata

23. Function Based Indexes • Traditional Indexes can’t work select … from sales where to_char(sale_dt,’YY’) = ‘13’ • Function Based Indexes help • SI indexes will not be useful Are Indexes Unnecessary in Exadata

24. IOTs • Index Organized Tables • PK-based rows • Secondary Indexes built on the other columns CUSTID NAME STATUS Index 1 JOHN HAPPY 2 JILL ANGRY 3 JOE HAPPY 500 JIM HAPPY Index PK Are Indexes Unnecessary in Exadata

25. Clustered Tables CUSTID NAME STATUS 1 JOHN HAPPY ADDRESSES CUSTOMERS 2 JILL ANGRY ADDR3 ADDR2 ADRR1 3 JOE HAPPY Anytown Main St 500 JIM HAPPY 123 Smallville ElmSt 234 Are Indexes Unnecessary in Exadata

26. Exclusion for SIs • Not for non-equality select sale_amt from sales where status != ‘SHIPPED’ • No Wildcards select sale_amt from sales where city like ‘NEW YORK%’ Are Indexes Unnecessary in Exadata

27. Virtual Columns • Example alter table EMP add ( tot_sal number(13) generated always as sal+comm) ) • Implication • Do not actually exists in the table • Computed at runtime Are Indexes Unnecessary in Exadata

28. Indexes on Small Tables • Small table • Parameter _small_table_threshold • Indexes still help small table http://richardfoote.wordpress.com/2009/04/16/indexes-on-small-tables-part-i-one-of-the-few/ • Less latching Are Indexes Unnecessary in Exadata

29. Summary of SI Limitations • Direct Path not used • No Predicate ► No SI • No Inequality (!=) • ≤ 8 columns • No Virtual Columns • No wildcard match (LIKE ‘..%’) • No IOT, Clustered Table • Latching on small tables • First-timer Penalty • Only subsequent queries benefit Are Indexes Unnecessary in Exadata

30. Flash Cache These are flash cards presented as disks; not memory to the Storage Cells. They are similar to SAN cache; but Oracle controls what goes on there and how long it stays. Server Buffer cache DBWR alter table person storage (cell_flash_cache keep) PMON Flash Storage datafile1 datafile2 Are Indexes Unnecessary in Exadata

31. Flash Trick for Indexes • Pin Oft-Used Objects in Flash SQL> alter index in_t2 storage (cell_flash_cache keep); • Check flash CellCLI> list flashcachecontent attributes – > cachedKeepSize, cachedSize, hitCount, - > hoursToExpiration, missCount- > where objectnumber = 382380; • Or, partitions Are Indexes Unnecessary in Exadata

32. Drop the Index? • Make the indexes invisible SQL> alter index i1 invisible; • Maintains the index; but optimizer ignores it • See the performance impact. • Selectively see the impact SQL> alter session set optimizer_use_invisible_indexes = true; • See the performance impact. Are Indexes Unnecessary in Exadata

33. Disable • Two parameters • Could be session level • To disable offloading cell_offload_processing = false; • To disable storage indexes alone _kcfis_storageidx_disabled = true; Are Indexes Unnecessary in Exadata

34. In Conclusion • Full table scans in Exadata • may be faster compared to non-Exadata • may not be faster than index scans in Exadata • may benefit from Storage Indexes • Storage Indexes are not same as DB Indexes • No DB Indexes helps in some cases • But not all • Test by making DB Indexes invisible • Force FTS in those cases where index hurts Are Indexes Unnecessary in Exadata

35. Thank You! Blog: arup.blogspot.com | Twitter: arupnanda Are Indexes Unnecessary in Exadata