| 6th December 2007 | An introduction to SQL*Trace, TKPROF and Execution Plans | Presented to UK Oracle User Group UKOUG 2007 UKOUG 2004, UKOUG Scottish SIG and Collaborate 08 |
| If you are developing an Oracle based application you will be writing SQL. Sometimes that application will take a long time to respond, and the time may be spent on SQL. How do you know if it is SQL? How do you find out what Oracle is doing? This presentation, orientated towards developers (but DBAs are also welcome), will show you how to use SQL*Trace and TKPROF to get Oracle to tell you how much time is spent on what SQL, and how to obtain and interpret an execution plan in order to find out how that SQL is being executed. XXX If you are trying to resolve performance problems, then you need to work out what your application is spending its time doing, and why. If your problem is SQL related then you need to know how Oracle is executing that SQL and why. | ||
| 17th July 2007 | Materialized Views: Simple Replication? | Presented to UK Oracle User Group UKOUG DBMS SIG |
| Materialized Views are a mature stable technology in the Oracle database that still provide an alternative to Streams. However, they are not without their idiosyncrasies. | ||
| 8th December 2003 | tkprof: Who? What? How? When? Where? Why? -or- SQL Trace: All of the Questions and None of the Answers | Presented to UK Oracle User Group UKOUG 2003 |
| This presentation is aimed at new and intermediate level DBAs and Developers. It starts by discussing some fundamental concepts of performance tuning. How to think about performance issues. How to decide what is important and what is not. Then we will move into more technical territory. When SQL*Trace and TKPROF can be employed? What does it tell you? How to read an execution plan? When not to trust what TKPROF says? There are no silver bullets, no golden rules and therefore no quick answers that are guaranteed work in every situation. But there are rigorous scientific techniques that can be reliably be applied to any situation. Those techniques are the subject of this presentation. | ||
| 8th December 2003 | tkprof: Who? What? How? When? Where? Why? -or- SQL Trace: All of the Questions and None of the Answers | Presented to UK Oracle User Group UKOUG 2003 |
| This presentation is aimed at new and intermediate level DBAs and Developers. It starts by discussing some fundamental concepts of performance tuning. How to think about performance issues. How to decide what is important and what is not. Then we will move into more technical territory. When SQL*Trace and TKPROF can be employed? What does it tell you? How to read an execution plan? When not to trust what TKPROF says? There are no silver bullets, no golden rules and therefore no quick answers that are guaranteed work in every situation. But there are rigorous scientific techniques that can be reliably be applied to any situation. Those techniques are the subject of this presentation. | ||
| 6th January 2002 | Aphorisms | |
| A not entirely serious presentation that is used to discuss how you might consider performance problems. Companion Document: Aphorisms | ||
| 11th December 2002 | Packaged Application Performance Tuning | Presented to UK Oracle User Group UKOUG 2002 |
| There are many packaged applications on the market today. Some of them (SAP, PeopleSoft, Siebel etc) are designed to run on any database platform. I've head salesmen call this 'Platform Agnostic'. Other people think that this leads to equally bad performance on all platforms! The application, if it can be customised at all, may be hidden away in an vendor specific design tool, the SQL will conform to a lowest-common-denominator standard (no outer-joins, no UNION ALL, no special features that Oracle developed to improve performance). If you capture a piece of SQL from a trace, it may not be coded explicitly, but generated by some part of the application from meta-code. Even if it is explicitly code it can be extremely difficult for a developer to find. Even adding an index is a form of application upgrade when the application maintains its own version of the database catalogue. As a DBA working with PeopleSoft this is the challenge that I face daily. If this sounds familiar to you come and listen this presentation. I will discuss the method and techniques that I use to monitor, trace and then improve performance. The key to solving this problem lies in combining database knowledge with application technical knowledge. | ||
| 11th December 2002 | Further Experiences of Global Temporary Tables in Oracle 8.1 | Presented to UK Oracle User Group DBMS SIG and UKOUG 2002 |
| Global Temporary Tables are a new feature in Oracle 8.1 that can be used to significantly reduce redo logging and so improve performance. It is also available as a zip archive with exhibited demonstration files. Companion Document: Experiences of Global Temporary Table in Oracle 8.1 | ||
| 13th February 2001 | I/O Analysis with SAR | Presented to UKOUG 2001 and UK Oracle User Group Unix SIG |
| How to use SAR, SQL*Loader and Excel to produce a graphical I/O analysis tool. | ||
| 7th July 2000 | PeopleSoft for the DBA | |
| This presentation discusses some of the issues that a DBA faces when administering a PeopleSoft database. It answers many of the questions frequently asked by DBAs. Companion book: PeopleSoft for the Oracle DBA | ||
| 30th October 2001 | Help! I have far too many extents (What is smon doing?) | Presented to UK Oracle User Group DBMS SIG |
| This presentation looks at what happens when the free space in a tablespace is coalesced, and why it can be a problem if you are not careful. | ||
| 6th March 2001 | Single Table Clusters, An alternative to partitioning? | Presented to UK Oracle User Group DBMS SIG |
| A discussion of the potential benefits and pitfalls of using a single table cluster and as an alternative to partitioning. | ||
| 14th March 2000 | Experiences of Global Temporary Tables in Oracle 8.1 | Presented to UK Oracle User Group DBMS SIG |
| Global Temporary Tables are a new feature in Oracle 8.1 that can be used to significantly reduce redo logging and so improve performance. It is also available as a zip archive with exhibited demonstration files. Companion Document: Experiences of Global Temporary Table in Oracle 8.1 | ||
| 13th June 2000 | Tuning with Oracle's SQL Trace Utility | Presented to UK Oracle User Group DBMS SIG and Unix SIG |
| This presentation was designed to give non-DBAs a simple guide to using Oracle's SQL Trace utilities as an aid to writing effective SQL. It gives the DBA some techniques for tracing the various types of component in a PeopleSoft system. | ||
Caveat: There is no guarantee that any of the content of this website is error free, rather that there are certainly errors and inconsistencies!
© Go-Faster Consultancy Ltd. 2004