| 3rd December 2018 |
How Not To Build A Data Warehouse
How Not To Build A Data Warehouse
| Presented to
UKOUG Tech 18
DOAG 2018 OUG Ireland 2019
OUG Scotland 2019 |
| There are features in the Oracle database that are specifically designed to optimize the performance of data warehouse queries. If you get your data model correct, and therefore you build your data warehouse according to to the generally accepted principles of good data warehouse design, then you should naturally take advantage of them. If you don't, then the database is likely to have to do a lot more work to execute your queries and you will not achieve expected performance.
This session looks at star-schemas, star transformation and bloom filters, how they work, and
takes examples from real life to show what sort of thing is often done that stop the database using these optimisations.
|
| 3rd
December 2018 |
Making Materialised Views Great Again
Making Materialised Views Great Again | Presented to
UKOUG Tech 18 DOAG 2018 OUGN 2017
OUG Ireland 2019 |
|
The old mature technology still works! This is a war story
that tells of how logical standby didn't deliver a reporting solution but materialized views did. On the way I will look at some of the newer features added 11g and 12c and some ideas for managing the refresh. |
| 1st
July 2009 | Graphing AWR Data in Excel | Presented to UK Oracle User Group |
| A picture is worth a thousand metrics. This short presentation will demonstrate how to use nothing more sophisticated than Excel to graph sets of data extracted from the AWR repository database in a simple query. Blog Entry Graphing AWR Data in Excel 2.12.2009: A Picture is Worth 1000 Metrics is the combination of Graphing AWR Data in Excel and Row Migration can Aggravate Contention on Cache Buffer Chains Latch given at UKOUG 2009. |
| 1st July 2009 | Row Migration can Aggravate Contention on Cache Buffer Chains Latch | Presented to UK Oracle User Group |
| This is presentation follows on from the previous session. This is a real-life example of a situation where having graphs of AWR data enabled me to correlate database behaviour to user behaviour on a system, and ultimately to identify row migration as a probable root cause Updated 4.7.2009: One of the benefits of presenting to a SIG is that sometimes an interesting question at the end of the session prompts a discussion. On this occasion it was sufficient for me to add and adjust some slides." see Blog entry Row Migration can Aggravate Contention on Cache Buffers Chains Latch see also Are you “Chained” to your Tables? by Joel Goodman |
| 3rd December 2008 | Obtaining and Interpreting Execution Plans using DBMS_XPLAN | Presented to UK Oracle User Group UKOUG 2008 |
| If you are developing an Oracle based application you will be writing SQL. Sometimes that take a long time to respond. 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 DBMS_XPLAN to obtain an execution plan (in SQLDeveloper or any other development tool). We will also look at how to interpret an execution plan in order to find out how that SQL is being executed. |
| 3rd December 2008 | An introduction to (BEA) Tuxedo | Presented to UK Oracle User Group UKOUG 2008 |
This provide an architectural overview of BEA Tuxedo. Oracle's purchase of BEA has made Tuxedo an Oracle product. Tuxedo is a very mature and robust transaction processing monitor. It can be used to develop a three-tier application, and PeopleSoft have used in their technology since 1997 (long before the Oracle take over). It can also be used to coordinate a distributed XA transaction. |
| 6th December 2008 | An introduction to SQL*Trace, TKPROF and Execution Plans | Presented to 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.
This session also include
* graphical techniques to make sense of the execution plan
* limitations of TKPROF
* TKPROF traps |
| 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. Demonstration Files |
| 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. 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.ion. |
| 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. |