Month: December 2012

Real-Time library cache monitoring

Again the same story : Oracle provides a useful view to check librarycache statistics (v$librarycache), but this view is a cumulative one. So how to check what’s is going on my database right now with cumulative values ?

Right : You have to substract the values between 2 measures.

So to get real-time librarycache statistics, you can use the librarycache.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the gv$librarycache view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./librarycache.pl

15:20:30   INST_NAME	NAMESPACE	RELOADS	INVALIDATIONS	GETS	GETRATIO	PINS	PINRATIO
15:20:30   BDT	        TRIGGER	        0	0	        1	100.0	        10	100.0
15:20:30   BDT	        TABLE/PROCEDURE	0	0	        2	100.0	        46	100.0
15:20:30   BDT	        BODY	        0	0 	        5	100.0	        20	100.0
15:20:30   BDT	        SQL AREA	0	0	        16	88.9	        213	100.0
--------------------------------------> NEW
15:20:31   INST_NAME	NAMESPACE	RELOADS	INVALIDATIONS	GETS	GETRATIO	PINS	PINRATIO
15:20:31   BDT	        TABLE/PROCEDURE	0	0	        1	100.0	        24	100.0
15:20:31   BDT	        TRIGGER	        0	0	        1	100.0	        10	100.0
15:20:31   BDT	        PIPE	        0	0	        1	100.0	        1	100.0
15:20:31   BDT	        SQL AREA	0	0	        12	92.3	        162	100.0

So, as you can see 12 gets occured on the SQL AREA during the last second without invalidations or reloads. The output is sorted on the GETS column but you could choose to sort on another one.

Let’s see the help:

./librarycache.pl help

Usage: ./librarycache.pl [Interval [Count]] [inst=] [top=] [namespace=] [sort_field=]
        Default Interval : 1 second.
        Default Count    : Unlimited

  Parameter      Comment                                                           Default
  ---------      -------                                                           -------
  INST=          ALL - Show all Instance(s)                                        ALL
                 CURRENT - Show Current Instance
                 INSTANCE_NAME,... - choose Instance(s) to display

                    << Instances are only displayed in a RAC DB >>

  TOP=           Number of rows to display                                         10
  NAMESPACE=     ALL - Show all NAMESPACE (wildcard allowed)                       ALL
  SORT_FIELD=    RELOADS|INVALIDATIONS|GETS|PINS                                   GETS

Example : ./librarycache.pl
Example : ./librarycache.pl sort_field='PINS'
Example : ./librarycache.pl namespace='%TRI%'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on namespace (by default no filter is applied).
  • You can choose the column to sort the output on.
  • This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Unix and Windows.
Advertisement

Rac One Node : Create preferred node

In my previous Rac One Node post I gave a way to “stick” one Rac One Node database to a particular node of your cluster so that :

  1. It should re-start automatically on the “stick” node in case of database or node crash.
  2. It does not relocate automatically on other nodes.

In this post I’ll give a way to create a “preferred” node for a Rac One Node database so that:

  1. It should re-start and relocate automatically on the “preferred” node as soon as it is available.
  2. It relocates automatically on other nodes in case the “preferred” node crash.

Let’s go, first modify the SERVER_NAMES attribute of the associated server pool to keep only the “preferred” node:

To which pool belongs the db ?

crsctl status resource ora.bdto.db -p | grep -i pool
SERVER_POOLS=ora.BDTO

Which servers are part of this pool ?

crsctl status serverpool ora.BDTO -p | grep -i server
SERVER_NAMES=bdtnode1 bdtnode2

Modify the server pool to define the “preferred” node for the database (bdtnode2 for example):

crsctl modify serverpool ora.BDTO -attr SERVER_NAMES="bdtnode2"
crsctl status serverpool ora.BDTO -p | grep -i server
SERVER_NAMES=bdtnode2

Second step is to change the PLACEMENT attribute of the database.

Change the PLACEMENT attribute from restricted to favored:

crsctl status resource ora.bdto.db -p | grep -i place
ACTIVE_PLACEMENT=1
PLACEMENT=restricted

crsctl modify resource ora.bdto.db -attr PLACEMENT="favored"
crsctl status resource ora.bdto.db -p | grep -i place
ACTIVE_PLACEMENT=1
PLACEMENT=favored

Now the BDTO RAC One Node database:

  • will be re-started automatically on node bdtnode1 in case the “preferred” node bdtnode2 crash.
  • will be re-started and relocated automatically on the “preferred” node bdtnode2 as soon as it will be again available.

Remarks:

Be careful: The database will be relocated automatically on the “preferred” node as soon as it is up (Even if the database is currently running on other node).

If you try to relocate manually the database on the “non preferred” node:

srvctl relocate database -d BDTO -n bdtnode1

It will be done and the node bdtnode1 will be again member of the server pool (as a consequence the node “bdtnode2” is not anymore the “preferred” one).

Conclusion: You have the choice,

  • Stick the database to one node so that it does not relocate automatically on surviving nodes (This post).
  • Defined a “preferred” node and allow the database to relocate automatically on surviving nodes and on the “preferred” node as soon as it is available (The current post).

Rac One Node : Avoid automatic database relocation

Imagine you need to ensure that one of your many RAC One Node database:

  1. Should re-start automatically on the current node in case of database or node crash.
  2. Does not relocate automatically on other nodes.

So, you need somehow to “stick” this particular RAC One Node database to one node of your cluster.

For a good understanding of what RAC One Node is, you can have a look to Martin’s post.

Disabling the database will not help as it will not satisfy the second point mentioned above.

To answer this need, I’ll modify one property of the database’s server pool (database is administrator managed):

To which pool belongs the db ?

crsctl status resource ora.BDTO.db -p | grep -i pool 
SERVER_POOLS=ora.BDTO

Which servers are part of this pool ?

crsctl status serverpool ora.BDTO -p | grep -i server
SERVER_NAMES=bdtnode1 bdtnode2

Modify the server pool to “stick” the database to one node (bdtnode2 for example):

crsctl modify serverpool ora.BDTO -attr SERVER_NAMES="bdtnode2"
crsctl status serverpool ora.BDTO -p | grep -i server
SERVER_NAMES=bdtnode2

Now the BDTO RAC One Node database:

  • will not be re-started automatically on node bdtnode1 in case bdtnode2 crash.
  • will be re-started automatically on node bdtnode2 as soon as it will be again available.

Remarks:

  1. If the server pool “host” many databases, all of them will be affected by the change.
  2. If you try to relocate manually the database on the “excluded” node:
srvctl relocate database -d BDTO -n bdtnode1

It will be done and the “excluded” bdtnode1 will be again member of the server pool (as a consequence the database is not sticked to bdtnode2 anymore).

If you need another behaviour (create a preferred node), then you have to change the PLACEMENT attribute of the database (see this post).

Conclusion: The purpose of this post is not to explain why you could choose to stick one RAC One Node database to a particular node of your cluster, it just provide a way to do so 🙂

Real-Time enqueue statistics

Again the same story : Oracle provides a useful view to check enqueue statistics (v$enqueue_statistics), but this view is a cumulative one. So how to check what’s is going on my database right now with cumulative values ?

You have to substract values as it has been done into Riyaj Shamsudeen’s post.

So to get real-time enqueue statistics, you can use the enqueue_statistics.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the gv$enqueue_statistics view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./enqueue_statistics.pl

20:01:10   INST_NAME	EQ_NAME			EQ_TYPE		REQ_REASON	TOTAL_REQ 
20:01:10   BDT_1	Job Queue Date		JD		contention	1
20:01:10   BDT_1	Job Queue		JQ		contention	2 
20:01:10   BDT_1	Job Scheduler		JS		q mem clnup lck	2
20:01:10   BDT_1	Session Migration	SE		contention	4
20:01:10   BDT_1 	Job Scheduler		JS		contention	15
20:01:10   BDT_1 	Job Scheduler		JS		queue lock	15 
20:01:10   BDT_1	Media Recovery		MR         	contention	28    
20:01:10   BDT_1	Cursor		        CU		contention	107   
20:01:10   BDT_1	Transaction		TX		contention	122
20:01:10   BDT_1	DML	                TM		contention	166
--------------------------------------> NEW
20:01:11   INST_NAME	EQ_NAME			EQ_TYPE		REQ_REASON	TOTAL_REQ 
20:01:11   BDT_1	Job Queue Date		JD		contention	1
20:01:11   BDT_1	Job Queue		JQ		contention	1
20:01:11   BDT_1	Job Scheduler		JS		q mem clnup lck	1
20:01:11   BDT_1	Session Migration	SE		contention	2
20:01:11   BDT_1 	Job Scheduler		JS		contention	12
20:01:11   BDT_1 	Job Scheduler		JS		queue lock	17 
20:01:11   BDT_1	Media Recovery		MR         	contention	20    
20:01:11   BDT_1	Cursor		        CU		contention	100   
20:01:11   BDT_1	Transaction		TX		contention	134
20:01:11   BDT_1	DML	                TM		contention	185

As you can see during the last second the DML enqueue has been requested 185 times.

For lisiblity, I removed some columns into the output that are displayed by the script: TOTAL_WAIT,SUCC_REQ,FAILED_REQ,WAIT_TIME

Output is ordered based on the TOTAL_REQ column but you could choose to order on any column.

Let’s see the help:

./enqueue_statistics.pl help                   

Usage: enqueue_statistics.pl [Interval [Count]] [inst=] [top=] [eq_name=] [eq_type=] [req_reason=] [sort_field=]
        Default Interval : 1 second.
        Default Count    : Unlimited

  Parameter	    Comment							Default    
  ---------         -------                                                     -------    
  INST=             ALL - Show all Instance(s)                                   ALL        
                    CURRENT - Show Current Instance                                         
                    INSTANCE_NAME,... - choose Instance(s) to display                       

                       << Instances are only displayed in a RAC DB >>                       

  TOP=              Number of rows to display                                    ALL        
  EQ_NAME=          ALL - Show all ENQ NAME (wildcard allowed)                   ALL        
  EQ_TYPE=          ALL - Show all ENQ TYPE (wildcard allowed)                   ALL        
  REQ_REASON=       ALL - Show all REASONS (wildcard allowed)                    ALL        
  SORT_FIELD=       TOTAL_REQ|TOTAL_WAIT|SUCC_REQ|FAILED_REQ|WAIT_TIME           TOTAL_REQ  

Example : enqueue_statistics.pl
Example : enqueue_statistics.pl sort_field=WAIT_TIME
Example : enqueue_statistics.pl eq_name='%sactio%' sort_field=WAIT_TIME
Example : enqueue_statistics.pl req_reason='contention'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on enqueue name, enqueue type and enqueue reason (by default no filter is applied).
  • You can choose the column to sort the output on (new).
  • This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Unix and Windows.

Remark:

I’ll provide a few more real-time utility scripts (see this page for the existing ones) and after that I’ll put all those scripts together into a single “real-time” utility script.

Real-Time SGA component monitoring

From the V$SGA_RESIZE_OPS view, you observed that your database is doing frequent resize.

You want to know what’s going on and you decided to query the v$sgastat view at regular interval to understand which component of the sga is growing.

The sgastat.pl script (click on the link and then on the view source button to copy/paste the source code) do it for you : It takes a snapshot based on the gv$sgastat view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./sgastat.pl

16:48:54   INST_NAME	POOL	        NAME		        BYTES                         
16:48:54   BDT_1	shared pool	QSMQUTL summar	        32                            
16:48:54   BDT_1	shared pool	kzull		        512                           
16:48:54   BDT_1	shared pool	kksss		        1744                          
16:48:54   BDT_1	shared pool	kksss-heap	        4280                          
16:48:54   BDT_1	shared pool	parameter handle	7192                          
16:48:54   BDT_1	shared pool	library cache		10936                         
16:48:54   BDT_1	shared pool	PCursor			12544                         
16:48:54   BDT_1	shared pool	parameter table block	51896                         
16:48:54   BDT_1	shared pool	CCursor			449688                        
16:48:54   BDT_1	shared pool	sql area		6715208                       
--------------------------------------> NEW
16:48:55   INST_NAME	POOL	        NAME			BYTES                         
16:48:55   BDT_1	shared pool	kzull			128                           
16:48:55   BDT_1	shared pool	KTCCC OBJECT		144                           
16:48:55   BDT_1	shared pool	kksss			416                           
16:48:55   BDT_1	shared pool	kksss-heap		1040                          
16:48:55   BDT_1	shared pool	parameter handle	1776                          
16:48:55   BDT_1	shared pool	parameter table block	12920                         
16:48:55   BDT_1	shared pool	library cache		35904                         
16:48:55   BDT_1	shared pool	PCursor			67856                         
16:48:55   BDT_1	shared pool	CCursor			942688                        
16:48:55   BDT_1	shared pool	sql area		8876448

So as you can see, the sql area component grow by about 8MB during the last second.

Let’s see the help:

./sgastat.pl help

Usage: ./sgastat.pl [Interval [Count]] [inst=] [top=] [pool=] [name=] 
        Default Interval : 1 second.
        Default Count    : Unlimited

  Parameter    Comment                               			Default    
  ---------    -------                                                	-------    
  INST=        ALL - Show all Instance(s)                              	ALL        
               CURRENT - Show Current Instance                                         
               INSTANCE_NAME,... - choose Instance(s) to display                       

                  << Instances are only displayed in a RAC DB >>                       

  TOP=         Number of rows to display                                ALL        
  POOL=        ALL - Show all POOL (wilcard allowed)                    ALL        
  NAME=        ALL - Show all NAME (wilcard allowed)                    ALL        

Example : ./sgastat.pl 
Example : ./sgastat.pl pool='%shared%'
Example : ./sgastat.pl name='%free%'
Example : ./sgastat.pl pool='%shared%' name='%sql%'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on pool and name (by default no filter is applied).
  • This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Unix and Windows.

You can found a very good study about shared pool management in Coskan’s post.

Real-Time segments statistics

The v$segment_statistics and v$segstat views are a goldmine to extract statistics that are associated with the Oracle segments.

You can see how useful it could be in those posts :

Kevin Closson’s post

Jonathan Lewis’s post or this one

Arup Nanda’s post

But those views are cumulatives, so not so helpful to report real-time information on the segments (Imagine your database is generating a lot of I/O right now and you would like to know wich segments are generating those I/O).

To report real-time statistics on the segments I wrote the segments stats.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the v$segstat view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./segments_stats.pl

Connecting to the Instance...

07:10:45   INST_NAME	OWNER	OBJECT_NAME	STAT_NAME                VALUE     
07:10:45   BDT1		BDT	BDTTAB          physical read requests   6         
07:10:45   BDT1		BDT	BDTTAB          segment scans            6         
07:10:45   BDT1         BDT     BDTTAB          logical reads            48        
07:10:45   BDT1         BDT     BDTTAB          physical reads           85        
07:10:45   BDT1         BDT     BDTTAB          physical reads direct    85   
--------------------------------------> NEW
07:10:46   INST_NAME	OWNER	OBJECT_NAME	STAT_NAME                VALUE     
07:10:46   BDT1		BDT	BDTTAB          segment scans            19
07:10:46   BDT1		BDT	BDTTAB          physical read requests   28         
07:10:46   BDT1         BDT     BDTTAB          logical reads            48        
07:10:46   BDT1         BDT     BDTTAB          physical reads           285        
07:10:46   BDT1         BDT     BDTTAB          physical reads direct    285

So, as you can see the BDTTAB generated 285 physical reads during the last second.

Let’s see the help:

./segments_stats.pl help

Usage: ./segments_stats.pl [Interval [Count]] [inst] [top=] [owner=] [statname=] [segment=] [includesys=]

        Default Interval : 1 second.
        Default Count    : Unlimited

  Parameter		Comment						     Default    
  ---------		-------					             -------    
  INST=          	ALL - Show all Instance                              ALL        
       CURRENT - Show Current Instance                                         
       INSTANCE_NAME,... - choose Instance(s) to display                       

                       << Instances are only displayed in a RAC DB >>                       

  TOP=			Number of rows to display                            10         
  OWNER=		ALL - Show all OWNER                                 ALL        
  STATNAME=		ALL - Show all Stats                                 ALL        
  SEGMENT=		ALL - Show all SEGMENTS                              ALL        
  INCLUDESYS=           Show SYS OBJECTS                                     N          

Example : ./segments_stats.pl segment='AQ%' statname='physical%'
Example : ./segments_stats.pl segment='AQ%' statname='physical writes direct'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on statname, segment and owner (by default no filter is applied).
  • This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Unix and Windows.

Remark for Exadata: 

As you can filter on the statname, you could choose to filter on the particular ‘optimized physical reads‘ statistic that way:

./segments_stats.pl statname='%optimized%'

Real-Time Wait Events and Statistics related to Exadata

Into my first post related to Exadata I provided a perl script to extract real-time metrics from the cells based on their cumulative metrics (see here).

In this post I focus on the information available from the database: Cumulative “cell” Statistics and cumulative “cell” Wait Events related to Exadata (Uwe described the most important ones into this post).

As usual the philosophy behind the 3 scripts used into this post is: extract real-time information from the cumulative views by taking a snapshot each second (default interval) and computes the differences with the previous snapshot.

So to collect Real-Time Wait Events related to Exadata, I use a perl script “system_event.pl” (click on the link and then on the view source button  to copy/paste the source code) already used into one of my previous post “Measure oracle real-time I/O performance“.

But this time I focus only on “cell” Wait Events related to Exadata that way :

./system_event.pl event_like='%cell%'

02:30:41 INST_NAME	EVENT				  NB_WAITS	TIME_WAITED		ms/Wait
02:30:41 BDT1		cell smart file creation 	  7 		950197 			135.742
--------------------------------------> NEW
02:30:42 INST_NAME	EVENT 				  NB_WAITS 	TIME_WAITED 		ms/Wait
02:30:42 BDT1 		cell smart file creation 	  3 		543520 			181.173
02:30:42 BDT1 		cell single block physical read   3 		4420 			1.473

As you can see, during the last second the database waited 3 times on “Cell smart file creation”

Now to collect Real-Time Statistics related to Exadata, I use the “sysstat.pl” script (click on the link and then on the view source button to copy/paste the source code) that way:

./sysstat.pl statname_like='%cell%'

03:15:29   INST_NAME	NAME                                                               VALUE
03:15:29   BDT1		cell smart IO session cache lookups                                11
03:15:29   BDT1		cell scans                                                         17
03:15:29   BDT1		cell blocks processed by cache layer                               3551
03:15:29   BDT1		cell blocks processed by txn layer                                 3551
03:15:29   BDT1		cell blocks processed by data layer                                3551
03:15:29   BDT1		cell blocks helped by minscn optimization                          3551
03:15:29   BDT1		cell physical IO interconnect bytes returned by smart scan         1421352
03:15:29   BDT1		cell physical IO interconnect bytes                                2756648
03:15:29   BDT1		cell IO uncompressed bytes                                         29089792
03:15:29   BDT1		cell physical IO bytes eligible for predicate offload              29089792
--------------------------------------> NEW
03:15:30   INST_NAME	NAME                                                               VALUE
03:15:30   BDT1		cell smart IO session cache lookups                                33
03:15:30   BDT1		cell scans                                                         33
03:15:30   BDT1		cell blocks processed by cache layer                               9896
03:15:30   BDT1		cell blocks processed by txn layer                                 9896
03:15:30   BDT1		cell blocks processed by data layer                                9896
03:15:30   BDT1		cell blocks helped by minscn optimization                          9896
03:15:30   BDT1		cell physical IO interconnect bytes returned by smart scan         6602936
03:15:30   BDT1		cell physical IO interconnect bytes                                6701240
03:15:30   BDT1		cell IO uncompressed bytes                                         81068032
03:15:30   BDT1		cell physical IO bytes eligible for predicate offload              81068032

As you can see during the last second the statistic cell physical IO bytes has increased by 81068032 .
If we need to diagnose more in depth and link in real-time those “cell” statistics with one or more sql_id, we can use the “sqlidstat.pl” (click on the link and then on the view source button  to copy/paste the source code) that way:

./sqlidstat.pl statname_like='%cell%'

--------------------------------------> NEW
03:15:29   SID   SQL_ID         NAME	        		   			    VALUE
03:15:29   ALL   0ab8xuf6kuud5  cell smart IO session cache hits                            4
03:15:29   ALL   0ab8xuf6kuud5  cell scans                                                  10
03:15:29   ALL   0ab8xuf6kuud5  cell blocks processed by cache layer                        1193
03:15:29   ALL   0ab8xuf6kuud5  cell blocks processed by data layer                         1193
03:15:29   ALL   0ab8xuf6kuud5  cell blocks processed by txn layer                          1193
03:15:29   ALL   0ab8xuf6kuud5  cell blocks helped by minscn optimization                   1193
03:15:29   ALL   0ab8xuf6kuud5  cell physical IO interconnect bytes returned by smart scan  356096
03:15:29   ALL   0ab8xuf6kuud5  cell physical IO interconnect bytes                         2232064
03:15:29   ALL   0ab8xuf6kuud5  cell IO uncompressed bytes                                  9773056
03:15:29   ALL   0ab8xuf6kuud5  cell physical IO bytes eligible for predicate offload       9773056
--------------------------------------> NEW
03:15:30   SID   SQL_ID         NAME                                                        VALUE
03:15:30   ALL   0ab8xuf6kuud5  cell smart IO session cache hits                            34
03:15:30   ALL   0ab8xuf6kuud5  cell scans                                                  34
03:15:30   ALL   0ab8xuf6kuud5  cell blocks processed by cache layer                        10522
03:15:30   ALL   0ab8xuf6kuud5  cell blocks processed by data layer                         10522
03:15:30   ALL   0ab8xuf6kuud5  cell blocks processed by txn layer                          10522
03:15:30   ALL   0ab8xuf6kuud5  cell blocks helped by minscn optimization                   10522
03:15:30   ALL   0ab8xuf6kuud5  cell physical IO interconnect bytes returned by smart scan  6039016
03:15:30   ALL   0ab8xuf6kuud5  cell physical IO interconnect bytes                         6039016
03:15:30   ALL   0ab8xuf6kuud5  cell IO uncompressed bytes                                  86196224
03:15:30   ALL   0ab8xuf6kuud5  cell physical IO bytes eligible for predicate offload       86196224

I removed the “INST_NAME” column for lisibilty.

By default all the SID have been aggregated but you could also filter on a particular sid (see the help).
So we can see that during the last second, most of the offload processing that we observed at the database level is related to the sql_id 0ab8xuf6kuud5.

Remarks :

  • Those 3 scripts are not exclusively related to Exadata as you can use them on all the wait events or statistics, I simply used them with the event_like or statname_like arguments to focus on ‘%cell%’ .
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • The scripts are oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.

Part II of this subject available here.

Real-Time CPU utilization per oracle database

Suppose you have a lot of oracle databases running on the same host, the server is almost 100% cpu used and you would like to know which database is the top cpu consumer right now.

The well known “top” utility could help ? This is not sure in case the culprit database has a lot of active sessions as “top” works at the process level.

So, to answer this need, I wrote a perl script that collects real-time processes cpu consumption at the os level (the script does not connect to any database) and aggregate the result per database.

The script takes a snapshot of os processes each second (default interval), computes the differences with the previous snapshot and aggregate te result per database.

Let’s see an example of the os_cpu_per_db.pl perl script (click on the link and then on the view source button to copy/paste the source code):

./os_cpu_per_db.pl 

Collecting during 1 seconds......

                               SUMMARY PER DB

20:31:56       DB_NAME		CPU_SEC		NB_CPU    
20:31:56       MYDB1		0               0.0
20:31:56       MYDB3		2		2.0
20:31:56       MYDB2		23		23.0

So we find out our top oracle database cpu consumer: the MYDB2 oracle database spent 23 seconds into the cpus during the last second (default collection interval) that is to say used 23 cpus.

Once you have the top database cpu consumer you can connect to it and diagnose more in depth (Example of cpu spike on this post).

The script does a little bit more in case the cpu issue is not related to any oracle database:

  • It can display the cpu usage per os user.
  • It can display the top os PID and commands.

Let’s see the help:

./os_cpu_per_db.pl help                       

Usage: ./os_cpu_per_db.pl [Interval [Count]] [top=] [displayuser=[Y|N]] [displaycmd=[Y|N]] 

        Default Interval : 1 second.
        Default Count    : Unlimited

Parameter               Comment					Default    
---------               -------      		                -------         
TOP=                    Number of rows to display               10         
DISPLAYUSER=		REPORT ON USER TOO                      N          
DISPLAYCMD=             REPORT ON COMMAND TOO                   N
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You have to set .oraenv on one instance first.
  • The script has been tested on Linux and Solaris.