| Riyaj Shamsudeen
OraInternals |
About Riyaj Shamsudeen is an OakTable Network member, with fifteen years of experience using Oracle products. He has spent more than fourteen years as an Oracle DBA/Oracle APPS DBA. He is certified in DBA versions 7.0,7.3,8,8i & 9i. Riyaj specializes in performance tuning, internals, and E-Business suite. He is currently consulting for Cinugular (the new AT&T) and is an adjunt member at Northlake College. |
| Website/Blog |
Video |
| Track: ORA | |
Session title |
Session description |
Why Does Optimizer Hate My SQL? |
This presentation is to explore various SQL performance issues encountered in the real world and to discuss the root cause for those SQL performance issues. 1. Review of cardinality, selectivity, and histograms. A must for every performance tuner to completely understand these concepts. 2. What to look for in a 10053 trace. How to effectively use 10053 trace files to identify the root cause of a SQL performance issue. 3. Bind peeking issues. 4. Effective use of system statistics to combat optimizer discrepencies. 5. Index efficiency and indices on non-selective columns. 6. Nulls and indices. 7. Issues with few hints such as ORDERED, INDEX, etc. Use of profiles and outlines to resolve these issues. 8. Subquery transformation and few optimizer features related to sub query transformation. 9. Predicate based first_k_rows mode. 10. Correlation issues. |
Advanced RAC Troubleshooting |
This presentation is to explore and troubleshoot various customer performance issues encountered in RAC environment. A few discussion points: 1. Review and understanding of AWR reports/statspack reports to troubleshoot global cache performance issues. Trend analysis for global cache traffic and performance using AWR/statspack tables. Also review of simple techniques to probe interconnect performance. 2. Understanding wait events in SQL trace files related to RAC such as gc cr grant 2-way, gc cr grant 3-way, gc multi block reads, etc. 3. Global cache object re-mastering: object re-mastering, various parameters influencing object re-mastering, and how to use it to tune performance. Wait events associated with object re-mastering. 4. Global cache waits and LGWR performance. LGWR performance is important in single instance databases and LGWR performance is even more critical in RAC instances. Discussion about GC performance issues due to LGWR performance issues. 5. Interconnect issues, lost packets, and AWR statistics. Identifying interconnect issues with database and OS statistics. 6. Troubleshooting performance issues with MTU/jumbo frames and jumbo frames mis-configuration issues. 7. Troubleshooting RAC background processes such as LMS/LMD etc. 8. Troubleshooting parallel query, interconnect, and PQ setup related performance issues. 9. Concurrency-induced global cache issues and few time-tested simple work-arounds. 10. Troubleshooting locking issues in RAC environments. 11. Bind peeking issues and RAC instances.
|