Friday Jan 27, 2006

HOTSOS PD101 Course - more than just technical

A colleague and myself just returned from the "Diagnosing Oracle Performance Problems" course with HOTSOS given by Cary Millsap. This course was definitely top-notch with not only the technical aspect but the "people" aspect of solving performance problems. In my own experience I too have found that by partnering across the organization, you are better able to solve performance problems. Often times when a consultant is brought into an organization they can serve as a focal point. A good consultant can help provide a bridge between the user, developer, DBAs, and sysadmins. This course puts all of these aspects into perspective along with method-R.

I was also excited to be able to use the new version of HOTSOS. The new version is far superior to the share-ware versions on the web. I particularly like the ability to show skew in the various events... very useful for determining root cause.

Finally, I was happy to learn that HOTSOS is creating a new products which will use trace application data to help model and size systems for growth. This is a huge development. Other tools which try to model based on system resources completely miss the fact that all aspects of the application do not grow at the same rate. Using this new methodology, predicting grow should be much easier. Hats off once again to HOTSOS.

Tuesday Jun 21, 2005

"orasrp" - Free Oracle response time based trace profiler.

Ever since I started reading the "Optimizing Oracle Performance" book by Cary Millsap, I have been salivating over getting a copy of the Hotsos profiler - but alas it is too expensive. Recently I tried to find a copy of their free version "Sparky" but it had been pulled due to support issues.

Finally, I stumbled upon orasrp. This analyzer is written in python and can be used standalone to produce html, or you can browse a directory of trace files via your web browser.

Monday Apr 11, 2005

SUPerG conference - Paper and Presentation

I am going to be presenting at the spring SUPerG next week 4/21 - 4/23. IMHO, this is the best conference Sun offers - A great forum for techies to meet techies. Since I typically, update my presentation a few times before the conference, I thought it best to post LAG copies.

Hope to see you there,
Glenn

Demystifying High-End Sun Fire Behavior
when Scaling Database Applications

by Glenn Fawcett and Marcus Heckel
Paper rev1.02 updated 4/19/05 after blog comments!!
Presentation rev1.03 updated 4/21/05 prior to pres :)

This paper attempts to explain the difference in the Sun Fire Server line. After examining the inherent differences, scaling topics will be discussed. The effects of large user, memory, and cpu count will be discussed along with Tips on how to best manage and scale applications.

Monday Aug 30, 2004

Session Wait History in Oracle 10g

In the past you could only sample Oracle Wait Events and often missed interesting information. In 10g Oracle introduced a new view, V$ACTIVE_SESSION_HISTORY, which keeps a historical view of wait events. I ran across a great article in the Database Journal which describes how to use this new feature... Pretty cool!

Oracle performance on RAW or VxFS with QIO/ODM.

The RAW vs COOKED debate that has been going on for some time. You will find little argument which is more efficient at caching DB buffers. If and Oracle process can get a block by doing a block get, this uses much less code than issuing an IO and getting it from the UFS cache. If this is the case, why have I ran into numerous DBAs that have tried Raw once but saw worse or no performance improvement from RAW?

Further investigation usually reveals that the FS cache was providing caching benefit, that was not able to be realized within Oracle. Without modifying the SGA size and possibly storage parameters on active tables/indexes, it is hard to realize any benefit with Raw.

A good place to start is by looking at your largest objects. If your statistics are current, you can use the following SQL. OEM also does a good job of showing table sizes.
SQL> select table_name, CACHE, num_rows, BLOCKS/128 MB 
     from all_tables
     order by MB desc
/
TABLE_NAME                     CACHE   NUM_ROWS         MB
------------------------------ ----- ---------- ----------
BIGACTIVETABLE                     N   20000000 13258.2891
LOOKUP                             Y      50000   150.5703
...
Also, You need to know the index sizes so..
SQL> select index_name,
            num_rows,
            leaf_blocks/128 MB ,
            BUFFER_POOL
     from all_indexes
     where owner = 'STSC'
     order by MB desc
/
INDEX_NAME                       NUM_ROWS         MB BUFFER_
------------------------------ ---------- ---------- -------
BIGACTIVETABLE_PK                20000000  9245.6875 DEFAULT
....
Since we now know what the largest tables and indexes are, we should expect to see them in the buffer cache. This can be done by querying the v$bh table.
SQL> SELECT object_name, count(\*)/128 MB 
     FROM v$bh, all_objects
     WHERE object_id=objd
     GROUP BY object_name
     ORDER BY MB desc
/

OBJECT_NAME                            MB
------------------------------ ----------
LOOKUP                          120.09375
BIGACTIVETABLE                  100.98438
BIGACTIVETABLE_PK                75.23438
....
Notice that "BIGACTIVETABLE" and it's index are not being cached very well. This is due to the fact that the storage parameter on the table and index are set to "NO CACHE". This causes Oracle to not actively try to cache these blocks. By alter these storage parameters
"alter table BIGACTIVETABLE cache;"
and increasing the SGA, these tables and indexes can use more Oracle buffer cache. Note, that you can also separate objects into the DEFAULT, KEEP, and RECYCLE buffer pools. Statspack reports calculate hit ratios for the DEFAULT, KEEP, are RECYCLE buffer pools separately. I tend to put all objects I am actively trying to KEEP into the KEEP pool so I can figure my caching efficiency via statspack.

I hope this helps inspire you to give this a try. It is not black magic, give it a try.

Take Care,
Glenn
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder