20th Novemver 2014Practical Use of Active Session History

Practical Use of Active Session History

Presented to UK Oracle User Group, Northern Server Technology Day DOAG2014

This presentation provides a very brief overview of Active Session History (ASH) and then looks at the practicalities of how to use the ASH data to diagnose performance problems. It discusses the importance of instrumentation to make the ASH data useful, and how to query the ASH data to determine which SQL statements spent how long doing what, and therefore which need further examination.

It is very difficult to present all the detail in PowerPoint, so there is a companion document that shows the SQL and outputs in full.

1st July 2009Graphing 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 2009Row 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 interestingquestion 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 2008Obtaining 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 2008An introduction to (BEA) TuxedoPresented 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 2008An introduction to SQL*Trace, TKPROF and Execution PlansPresented 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 2007Materialized 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 2003tkprof: Who? What? How? When? Where? Why? -or- SQL Trace: All of the Questions and None of the AnswersPresented 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 2003tkprof: Who? What? How? When? Where? Why? -or- SQL Trace: All of the Questions and None of the AnswersPresented 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 2002Aphorisms
A not entirely serious presentation that is used to discuss how you might consider performance problems.

Companion Document: Aphorisms

11th December 2002Packaged Application Performance TuningPresented 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 2002Further Experiences of Global Temporary Tables in Oracle 8.1Presented 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 2001I/O Analysis with SARPresented 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 2000PeopleSoft 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 2001Help! 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 2001Single 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 2000Experiences of Global Temporary Tables in Oracle 8.1Presented 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 2000Tuning with Oracle's SQL Trace UtilityPresented 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. 2014