Performance Visualisation made easy

Today I found a blog entry by Tanel Poder, he has also discovered the way of visual performance analysis without expensive tools. He describes that it’s better to show a graph to non-DBA people than a bunch of statspack numbers. Not only that, a human brain can interpret much more numbers by looking at a graph than at a table full of numbers.

Here’s the link to the post:

Update v1.19, RAC support and new views

I uploaded the new version of Phat4Oracle, v1.19. Version 1.18 has never been uploaded. I added a few new views and RAC support by adding INSTANCE_NUMBER column to most views.


v1.18 12-12-08

  • Added INSTANCE_NUMBER and DBID in some views for RAC support
  • New view: PHAT$OSSTAT

v1.19 28-04-08

  • Added ‘PARTITION BY’ in phat$snapshot to avoid delta values between instance 1 and 2, is now null
  • New view: PHAT$UNDOSTAT

More statspack queries

I checked out some Hotsos 2008 downloadable files here, in one PDF I found a URL to Tim Gorman’s Statspack delta views.  It’s a bit similar to the Phat4Oracle view. Check it out if you are into querying Statspack.

Phat on RAC ?

I always had the wish to do some investigation about Statspack and Phat on RAC. I have tried a few times to get RAC running on Vmware on my 2GB laptop, but I haven’t managed to get it working yet. But sometimes a interesting chance comes along. An important client is having some performance trouble on a RAC cluster, so this is a good moment to do some R&D, and be productive immediatly. I figured out how Statspack works on RAC, I have created 2 snapshot jobs, each with instance affinity. Now I’ll see if Phat can be of any use.

Strange choice of execution plan

I haven’t made any improvements lately on Phat. I mostly do this when I’m working on performance problems, I add a few new features here and there which come in handy right away. But I did not have to solve performance problems the last few months. Last week I found one problem which started as a little performance issue, but it turned out to be something completely else.

Complaints were that a batch was not performing well, and one thing led to another. Queries which were done were of the most simple form:

  • select <columns> from <table> where unique_id = <value>
  • delete from <table> where unique_id = <value>

Explain plan showed that a full table scan was performed while a unique index existed on the one column which was used as search predicate. Investigation on the size of table and index showed that an index should be used, reading a few block in an index and one block from the table was much more efficient than reading the entire 20+ MB table. So, just to make sure:

  • Index rebuild
  • Refrefh statistics

No change. Putting an index hint on the queries showed two strange things:

  • Cost was lower, why did the optimizer not choose for the index plan ?
  • Query used an ‘index full scan’, hello ? It’s a one column index, and were are not doing a count or so.

I decided to make a copy of the table in another schema, and I created a similar index on it. It gave the same behaviour, this did not give much clues. I tried a 10053 trace, despite the fact that I’m not very familiar with it yet. It showed all used variables which the optimizer used, and quite a short analysis of possible queries. It looked like it only calculated one plan, this was strange.

Next experiment I thought of got this problem in a different direction. I decided to export the table to my own test database. Running the export gave this error:

Connected to: Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 6552 encountered
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
EXP-00000: Export terminated unsuccessfully

This looked strange, I checked out invalid objects immediatly. I found 1000+ invalid SYS objects. I tried to fix it:

  • utlrp.sql left dozens of invalid KU$ views
  • catalog.sql, catproc.sql, utlrp.sql still left them invalid
  • compiling an individual KU$ view gave ORA-06552, with a message like ‘type CHAR should be CHAR’
  • select on dba_registry showed that catalog was invalid

What has happened ? A ‘grep ORA-‘ on the alert log showed the cause: a ‘CREATE DATABASE’ was done on this instance, the environment must have been set wrong when creating a new database. It has run on this existing database, and must have corrupted the catalog. Strange that a rerun of ‘catalog.sql’ did not fix the problem.

Anyway, a restore of the database was decided. After this the queries used the good old ‘index range scan’, and the exp worked again.

Phat4Oracle on 11g

I finally checked Statspack and Phat4Oracle on 11g after installing the latest Oracle version. Statspack still exists, it even has improvements. Check out the excerpt below taken from the 11g ?/rdbms/admin/spdoc.txt.

I have installed the PERFSTAT schema, and the Phat4Oracle on top of it using the create10g.sql script. No problems found yet. Maybe I will do some more testing later on, but first I have to add some 10g features which are still missing.

9.  New and Changed Features

9.1.  Changes between 10.2  and 11.1

Changes on the Summary Page of the Instance Report 
  o  Host
     - Platform name has been added to the Host information.
     - The number of CPU cores and sockets is displayed, where available.
     - Physical Memory is now shown in GB rather than MB.
  o  Snapshot information
     - The DB time and DB CPU in seconds, is now printed close to the 
       snapshot Elapsed time.
  o  Load Profile
     - DB time and DB CPU have been added to Load Profile.  Units are
       Per Second, Per Transaction, Per Execute and Per Call
       The addtion of this normalized data assists when examinig
       to reports to see whether the load is comparable.
     - The number of 'Sorts' has been replaced with 'W/A MB processed'.  
       Displaying workarea statistics more accurately reflects not
       only sorts, but also other workarea operations such as hash
       joins.  Additionally, using MB processed rather than the number
       of workarea operations indicates the quantity of workarea work
     - The following statistics have been removed from the front page,
       as they are no longer considered as important as they once were:
         % Blocks changed per Read
         Recursive Call %
         Rollback per transaction %
         Rows per Sort

  o Instance Efficiency
    - This section has been renamed from 'Instance Efficiency Percentages'
      to 'Instance Efficiency Indicators', as this more accurately
      represents that these values are simply indicators of possible
      areas to consider, rather than conclusive evidence.
   - 'In-memory Sort %' has been replaced with 'Optimal W/A Exec %'
     as the old statistic only showed sorts, and not all workarea

Modified sections of the Instance Report
  o  Wait Events and Background Wait Event
     The % of Total Call Time has been added to these sections.  Rows which
     have null for the % of Call Time are Idle Events, and so do not
     contribute to the % Total Call time.

  o  The PGA Memory Advisory now shows the Estimated Time to process the

  o  The init.ora parameters section now shows the entire parameter value
     rather than truncating the output

  o  SQL sections
     SQL statements which have not completed execution (e.g. have a 0
     execution count as the statement was still running when the End
     snapshot was taken) can now appear in the SQL reports.

New Data captured/reported on - Level 1
  o  Foreground Wait Events
     A new wait event section has been added.  This shows wait events
     for foreground processes only.

  o  OS Statistics - detail (from v$osstat)
     This is a new section which shows the Load, %Busy, %User, %System
     %WIO and %WCPU for each snapshot between the Begin and End snapshot
     Ids specified.

  o  IO Stat by Function - summary (from v$iostat_function)
     This section shows IO statistics broken down by Oracle function.
     A by-function breakdown can help identify where the IO bandwith 
     is being consumed (e.g. by RMAN, DBWR, recovery, direct IO).
     The summary section summarizes IO requests and IO rates.

  o  IO Stat by Function - detail (from v$iostat_function)
     Similar to above, however this section shows details how the
     IO requests and IO rates break down by small requests and
     large requests (requests > 128k).

  o  Memory Target Advice (from v$memory_target_advice)
     Shows potential values for memory_target parameter, and
     their estimated cost, in DB time.

  o  Memory Dynamic Components
     Shows infomration about the last resize operation for each
     Memory Component.

  o  Memory Resize Operations
     Shows all known Memory resize operations which occured between
     the Begin and End snapshots.

Obsoleted data
  o  v$temp_histogram view and the corresponding Statspack table have
     been dropped.

Back from vacation

My 3 week holiday is over, unfortunatly. I have noticed that 11g can be downloaded, which I did immediately. I haven’t installed it yet. I’m interested if Phat can be of any good on this release, and if there still is a Statspack. I will keep you posted. I also have some minor improvements in my local Phat version here, I will post them soon. An online page with release notes would also be handy, I will put some time in this.