EM13c: Performance Tuning Access to Read Only Administrators

About six months ago, when I was in the OUGN Spring Conference, someone asked me how they can let developers access to performance tuning pages on Enterprise Manager Cloud Control 13c. He said that they granted full administrator privileges to the developers so they could access the performance page but it’s not acceptable. He also mentioned that they didn’t need to grant full administration privilege to the developers’ EM users while they were using EM12c.

Unfortunately I forgot to examine the situation until I saw a similar question on OTN (Oracle Community) forum on Friday. The forum reader has exactly the same problem. The database user was already granted required privileges but the EM administrator see the menu items related with “performance tools” are grayed out.

Real World Performance Tour: Istanbul, Turkey

I attended Real World Performance Tour, yesterday. TROUG (Turkish Oracle User Group) organized the event under the sponsorship of the Oracle Turkey. Although it was snowing, there were about 100 attendees. Well known Oracle experts Tom Kyte, Andrew Holdsworth and Graham Wood presented a 6 hour seminar in a conversational format.

Some of the topics:

  • Database connections
  • SQL statement parsing
  • Set based operations rather than row by row processing
  • Queuing
  • Proper use of indexes
  • Understanding JOINs

Simulating ASH (S-ASH)

I created a simple utility to monitor and administrate Oracle Databases about 2 years ago. It’s called GOAT (GOAT Oracle Administration Tool). It’s more suitable for monitoring than administrating, but I haven’t changed its name, because I liked that recursive acronym.

GOAT was designed to monitor Oracle 10g and higher databases so I used ASH (active session history) views while drawing performance graphics. In my current company, we have Oracle 9.2 databases. Although we use Enterprise Manager Grid Control to monitor and manage them, I decided to bring my old utility back to life and add support for Oracle 9i.

Unfortunately Oracle 9i doesn’t support ASH, so it forced me to find an alternative. I’ve found S-ASH project created by Kyle Hailey and maintained by Marcin Przepiorowski: http://ashmasters.com/ash-simulation/

Installing and Configuring StatsPack

Statspack is a performance tuning package provided by Oracle. It’s free to use and a reliable tuning tool since Oracle 8i. You can install it on Oracle 10g and 11g if you don’t have license for tuning pack. Oracle recommend installing the statspack package into a different tablespace than system tablespaces. “TOOLS” tablespace of Oracle 9i is  because I’ll install it to . If you use Oracle 10g+, you need to create this tablespace.

During the installation, the user PERFSTAT will be created. This user will own all objects of the STATSPACK package. The installation SQL script will prompt for the PERFSTAT user’s password and default and temporary tablespaces.

You need to check spcpkg.lis for any errors. You can test your statspack install by issuing:

You can create spauto.sql script to automatically schedule an hourly data collection for statspack:

If you want to remove statspack, run spdrop:

About vmstat (Virtual Memory Statistics)

The vmstat helps you to identify bottlenecks on your server. It displays real-time performance information about processes, memory, paging, disk I/O, and CPU usage.

Here’s sample output (Oracle Linux 5.6):

Descriptions of the columns:

r: The number of processes waiting for run time
b: The number of processes in uninterruptible sleep
swpd: Total virtual memory (swap) in use (KB)
free: Total idle memory (KB)
buff: Total memory used as buffers (KB)
cache: Total memory used as cache (KB)
si: Amount of memory swapped in from disk (KB/s).
so: Amount of memory swapped to disk (KB/s).
bi: Blocks received from a block device (blocks/s).
bo: Blocks sent to a block device (blocks/s).
in: The number of interrupts per second, including the clock.
cs: The number of context switches per second.
us: User-level code time as a percentage of total CPU time
sy: System-level code time as a percentage of total CPU time
id: Idle time as a percentage of total CPU time.
wa: Time spent waiting for IO.
st: Time stolen from a virtual machine.