Re: Performance metrics

From: kevin jernigan <kevin.jernigan_at_oracle.com>
Date: Thu, 12 Apr 2012 10:06:24 -0700
Message-ID: <4F870B90.4040708_at_oracle.com>

You could also use Resource Manager to limit the amount of CPU available to low priority consumer groups, and also to control or kill runaway queries:

http://docs.oracle.com/cd/E11882_01/server.112/e16638/os.htm#PFGRF95151

Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS), Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information Lifecycle Management (ILM)
(650) 607-0392 (o)
(415) 710-8828 (m)

On 4/12/2012 9:53 AM, Karl Arao wrote:
> "When things go wrong they point the finger at the database or OS or
> hardware"<-- yes, and when this happens it's just a matter of getting the
> facts, numbers, figures and we can point the finger back to them. Take a
> look at the screenshots below, they are all different scenarios where
> things gone bad
>
> PGA reaching 30GB when developers fire up new reports that's doing
> tremendous hash joins eating up the server memory causing the kswapd to
> kick in and swapping at a high rate which translates to CPU wait IO and
> high load average.. basically killing the server
> https://lh4.googleusercontent.com/-AB22fuuzLwE/T4b53Ris6QI/AAAAAAAABio/mu_dIx3A3uE/s2048/20120412-template-PGA.png
>
>
> New batch of reports were introduced, and we found out that the developers
> are testing stuff in the PROD environment
> https://lh4.googleusercontent.com/-rPe_EbL4Md0/T4b53Zf81hI/AAAAAAAABik/g_rZFYUgj68/s2048/20120412-template-aas1-correct-detail.png
>
>
> Load average spike
> https://lh3.googleusercontent.com/-Pnw29AKWRSk/T4b53UOjXwI/AAAAAAAABig/LaPnAsru3rg/s2048/20120412-template-cpu-detail.png
>
>
> Sudden 15GB/s read caused by just two SQLs
> https://lh4.googleusercontent.com/-7rTUJIR-Rh0/T4b53qAvoqI/AAAAAAAABi8/lSMhGsF7OEc/s2048/20120412-template-iopsrw-15GBs-detail.png
>
>
> And since the data points are based in AWR you can drill down on
> snap_ids, generate ASH at that time period, pick the SQLs.. and regroup
> with the developers ;)
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 12 2012 - 12:06:24 CDT