High global cache waits on tab$ ?

Yes, you read it correct. That shocked me too.

I was trying to understand global cache waits for a client. Naturally, I queried statspack tables and analyzed the data for just one day using a script. Surprisingly, tab$ came as top consumer of global cache waits. I was shocked by the revelations and couldn’t believe it! If something doesn’t make sense, look more closely, right?

Global cache waits

Database version is 9i. Statspack table stats$seg_stat is the source for this script and that table is populated from v$segment_stats. So, these column values (global_cache_cu_blocks_served and global_cache_cr_blocks_served) are cumulative. To find statistics for a specific period, we need to subtract column value of prior row from current row. Analytic function lag can be useful for this.

In this analytic function printed below, partitioning clause uses instance_number, startup_time and dataobj#. All rows with same value for these three columns will be considered in one data partition and then rows ordered by snap_id within that data partition. Lag will pull the row from prior snap_id in that partition. Then, we subtract from current value to get the difference. Please refer to this paper: Performance tuning with SQL new features – paper for more information about analytic functions.

  ...
   global_cache_cu_blocks_served -
   lag(global_cache_cu_blocks_served,1,0) over (partition by instance_number,startup_time, dataobj#, obj#
              order by  snap_id ) global_cache_cu_blocks_served,
...

Script and output

Complete script printed below and running that against a database.

RMOUG Training Days 2009

I had a privilege of presenting two papers in RMOUG Training days 2009 for the past couple of days. It is always been a valuable conference to attend considering quality of presentations. This proved to be true and was an excellent conference this year too.

During my presentation, I promised that I will upload recent copy of my papers and presentations in my blog and here they are:

1. Performance features 11g ppt
2. Performance features 11g paper
3. Battle of the nodes RAC performance myths presentation
4. Battle of the nodes RAC performance myths paper

I also met many awesome presenters such as mogens nørgaard , Stephen Haisley, Jeff Needham, Gaja , Cary Millsapp , Daniel Fink , Danial Morgan , Joze Senegacnik , Carol Dacko, Dan Norris, Tanel , Jeremiah Wilton, Kevin Closson to name a few. Apologies, if I missed anybody, many valuable presentations and highly knowledgeable presenters attended this conference.

I attended few presentations:
A valuable presentation by Jeremiah Wilton, on breaking Oracle. Especially, when he created ORA-600 errors with “Ouch” and other funny remarks as arguments, whole room went in to laughter. This presentation can be downloaded from his blog .

Tanel had an interesting presentation about ‘How execution plan works?”. He explained, internally, how various row sources are simply calls to various function calls etc. Very valuable presentation indeed. Tanel said that he will upload his presentations to his blog soon.

Joze, from wonderful Slovenia, gave a presentation about SQL Plan Management. This is a very important new feature and will be extremely useful for plan stability.

Of course, I had few interesting offline and thought provoking discussions with many others. Stephen Haisley shared some new features about streams and it will be great to see that one day. I had a length conversation with Kevin Closson about NUMA, CPU cache line and his days with NUMA engineering and development (in sequent).