| Presentation Title: | Cost based Subquery transformation - Concepts & Analysis |
| Speaker: | Mr Riyaj Shamsudeen |
| Company: | OraInternals |
| Co-presenter: | |
| Presentation abstract: | Sub-queries were transformed heuristically prior to Oracle version 10g. In 10gR2 onwards, sub-queries are rewritten and transformed using various techniques, cost calculated and transformation cost compared before a final plan is chosen. This transformation and subsequent cost computation is highly mathematical in nature. Combinations of transformations includes techniques such as predicate pushing, view merging, predicate pull up, subquery unnesting, predicate move around etc. Optimization techniques to reduce the parsing time also discussed. This paper is to explore the concepts behind this new feature and show 10053 traces for various test cases. Performance analysts will be able to understand how the queries are transformed and follow 10053 traces to see cost calculations using this paper. |
| Presentation begins: | 04/12/2008 13:00 |
| Presentation duration: | 45 |
| Presentation content level: | 1 (1 = indepth, 5 = strategic overview) |
| Audience experience: | Expert (3+ years) |
| Audience function: | DBA's, Developers |
| Speaker biography: | Riyaj Shamsudeen has 15+ years of experience in Oracle and 14+ years as an Oracle DBA/ERP Financials DBA. He is the principal engineer with OraInternals, specializes in performance tuning and database internals. He has authored many articles such as internals of locks, internals of hot backups, redo internals, tuning with SQL new features etc. He is a frequent presenter in UKOUG, HOTSOS, RMOUG etc. He also taught classes in community colleges in Dallas such as North lake college. He is an OakTable Member |
| Related topics: | Server Technology: Performance Tuning and Diagnostics, Server Technology: General - Technology |
| Hall: | 9 |
