Total Session History

Overview

Proving performance gains of Exadata requires statistical analysis of the total session history, including charting active as well as idle sessions. Total Session History is a utility that allows administrators to capture session performance statistics over a specific time period. Total Session History can be used in conjunction with other tools including AWR, ASH, and the SQL Replay and SQL Tuning Sets features of Real Application Testing to determine exactly how Exadata will impact performance of a particular system.

Why TSH?

My team and I are focused on showing customers how Exadata will improve the performance of their database applications.  We have conducted over 500 Exadata benchmarks for customers, with the vast majority showing excellent performance increases.  Oracle’s claims of 10X performance improvements (versus current generation competitor hardware) are certainly reasonable, and even under-stated in some cases.  We have seen critical “tasks” (transactions, functions, jobs or individual SQL statements) that are 50X, 100X or even 1000X faster on Exadata.  However, there is always a RANGE of performance impact, from “tasks” that run only slightly faster (maybe 2X) as well as tasks that run much faster (10X or more).

Because there is always a range of performance gains, we obviously focus on finding “tasks” in a system that meet the following criteria:

  1. Tasks that run slow on the current system
  2. And Somebody in the business cares about
  3. And Exadata will run them faster

We first need to find things that run slow on the system, or maybe things that can’t be run due to performance issues.  In some cases, application functionality has been disabled or users simply don’t use parts of an application because of performance problems.  We can easily use AWR or ASH to find slow running SQL statements on a system, but you need to determine if someone cares about it.  If a job runs slow but runs at 3:00AM, it’s possible that nobody cares.  Un-used parts of an application won’t appear in any AWR or ASH reports, so a close dialog with application users is critical to understanding their perspective.

Performance of an interactive application should always be measured or viewed from the end-user perspective.  Performance of batch jobs is measured in total runtime or throughput of the job.  Once you understand the end-user perspective, you can evaluate how the application behaves and where time is being spent.  It’s critical to understand what happens after a user hits “enter” or clicks an “ok” button including time spent on the client or application tier, time spent in the network, and time spent in the database tier.

TSH is a tool that accelerate analysis of runtime to determine how much time the application spends inside the database versus outside.  If the application is spending a large percentage of time outside of the database, there is little value in making the database faster before you fix those external bottlenecks.  Making the database tier faster will not improve performance of an application that spends most of it’s time in the application tier.  Making the database tier faster will not improve the performance of “chatty” applications that make too many round-trips across the network.

 TSH Key Statistics

TSH gives a performance tuning specialist access to some key application performance statistics at the SESSION level inside of Oracle.  For example, we can tell when sessions are initiated, how long each session is active, how many round-trips are made across the network for each session, and how much time the application spends inside the database versus outside.  We can determine how many SQL statements are executed, and how much time is spent inside the database on average for each of those SQL statements.

The relevant statistics gathered by TSH will vary depending on the application workload profile.  There will be some common reports that are useful for all applications, but the real value of TSH will come through investigation of the statistics gathered by TSH.

2 thoughts on “Total Session History

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s