EM12c · Oracle

Slow Performance on DB Home Page EM12c

EM12c is a great tool to use. We use EM12c for a lot of things. It is the very comprehensive tool that monitors and gathers information.
EM12c DB home gives you a brief status of the database. Some of them are Up Time, average active sessions, total Sessions, available Space, used Space, host CPU ….

We had a slowness problem. For some databases (but not all of them) on the DB Home page. It takes about 1 min to load. It was pretty annoying and we start to investigate the root cause of the problem.
Doc ID 1528334.1 was very helpful to diagnose the problem. The SQL that collects the summary DB information is running slow on the target database. You can easily see the SQL showing up on the long Running SQLs on the target database.

The main problem with this long SQL was the query over the dba_segments. This query need to be tuned but how 🙂

 
“select tablespace_name, sum(nvl(bytes,0))/(1024*1024*1024) used_gb from dba_segments group by tablespace_name"               

Actually It was not a tuning problem It was a bug addressed in this document. There exists some problematic segments which are so called slow segments 🙂 and they need to be fixed.

Encountering Slow Performance Reading *_SEGMENTS or *_TS_QUOTAS (Doc ID 1491748.1)

Solution

Solution 1
Follow the above Support documents and apply the patch on the target DB home. The solution to the problem is not easy. Applying the patch on the target DB is not enough. You need to modify the slow segment blocks.
By the way we didn’t chose this method.

Solution 2
We upgraded EM12c from 12.1.0.2 to the latest version 12.1.0.3 they fixed the slowness problem in this version 🙂 Yuppiiie

How To Upgrade
I benefit a lot from the following links during my upgrade process. Also Upgrade was smooth and easy.

http://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/upgrading_12101_PS1_gtgstrtd.htm#CIHJGEEB
.
http://pardydba.wordpress.com/2013/07/12/my-production-em12c-upgrade-from-r2-12-1-0-2-to-r3-12-1-0-3
.
http://www.snapdba.com/2013/07/em12c-upgrade-from-12-1-0-2-to-12-1-0-3

If you prefer the first solution this is my test case on a test server
Improvement is obvious but I prefer not to run on production:

SQL> select tablespace_name, sum(nvl(bytes,0))/(1024*1024*1024) used_gb from dba_segments group by tablespace_name;
 
 
TABLESPACE_NAME                   USED_GB
------------------------------ ----------
UNDOTBS1                        55.170105
UNDOTBS2                       56.5027466
.
.
.
 
10 rows selected.
 
Elapsed: 00:00:46.20
 
---------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> begin
  2    for t in (
  3      select distinct tablespace_name
  4      from sys.sys_dba_segs
  5      where bitand(segment_flags,131073) = 1
  6        and segment_type not in ('ROLLBACK', 'DEFERRED ROLLBACK', 'TYPE2 UNDO')
  7        and tablespace_name != 'SYSTEM'
  8    )
  9    loop
10      dbms_space_admin.tablespace_fix_segment_extblks(t.tablespace_name);
11    end loop;
12  end;
13  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:31:39.22
 
---------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> select tablespace_name, sum(nvl(bytes,0))/(1024*1024*1024) used_gb from dba_segments group by tablespace_name
  2  ;
 
TABLESPACE_NAME                   USED_GB
------------------------------ ----------
UNDOTBS1                       53.8462524
UNDOTBS2                       54.9580688
.
.
.
 
10 rows selected.
 
Elapsed: 00:00:05.71
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s