Oracle slow SQL query against dba_segments solved

July 18, 2014 by 3 Comments 

I remember when long time ago one database consultant confused my manager saying that our Oracle 9i database had poor performance just taking into account a slow response from dba_segments data dictionary view.  That was a nasty trick to blame a DBA and the Oracle database for poor performance at that time. In fact there were a few Oracle bugs related to those performance issues after switching from dictionary to locally managed tablesspaces at that time. Recently I’ve noticed similar performance degradation on Oracle 11gR2 (11.2.0.2 and 11.2.0.3) by querying DBA_SEGMENTS or USER_SEGMENTS data dictionary views involving the columns BYTES, BLOCKS, or EXTENTS. Queries on DBA_TS_QUOTAS or USER_TS_QUOTES on columns BYTES or BLOCKS were also slow.

Even if you personally do not care about these dictionary views they are still very important since they are used by some Oracle internal components and the other database tools including Oracle Enterprise Manager (OEM) Cloud Control and its Database Home Page. Thus, I’ll describe below the problematic of those data dictionary views and the way how to fix their performance issues.

First of all do not wonder why queries against those views often seem to slow. DBA_SEGEMENTS for example is a very complex view that is built on another SYS_DBA_SEGS view. In summary DBA_SEGMENTS view on Oracle 11gR2 consists of the following components:

– 25 columns
– around 110 lines of SQL code
– 3 UNION ALL clauses
– A lot of joins between following tables: sys.user$, sys.ts$, sys.undo$, sys.seg$, sys.file$


The main performance problem here however is that the values of the BYTES, BLOCKS & EXTENTS columns of the DBA/USER/ALL_SEGMENTS and DBA/USER/ALL_EXTENTS views are not stored in the Data Dictionary for segments that reside in Locally Managed Tablespaces. Instead they must be obtained by accessing the Segment Header Blocks for all segments processed in the query. These extra block accesses cause the slow performance. So with increasing number of database files and segments the query performance in this case can potentially degrade. Furthermore Oracle in certain circumstance does not cache properly those segment header blocks, thus forcing reading them from disk even at the next query run – so no caching happening. Do not be surprised if I say that a query against dba_segments can trigger even at the background a few more procedures from DBMS_SPACE_ADMIN package to get correct segment sizing information.

All in all, the symptoms of performance degradations with these dictionary views can vary. In my last case they were even different on different database platforms. If you encounter similar performance issues I strongly recommend contacting Oracle support. Additionally I’ll share below how I fixed my last case on Oracle 11.2.0.3.2.

1) Check if you have “slow segments” on your database with slow dba_segments, dba_extents, etc. views:
select tablespace_name
, count(*) as seg_cnt
, sum(DECODE(bitand(segment_flags,131072),0,1)) as slow_segs
from sys.sys_dba_segs
where bitand(segment_flags,1)=1
and segment_type not in ('ROLLBACK', 'DEFERRED ROLLBACK', 'TYPE2 UNDO')
group by tablespace_name
having sum(DECODE(bitand(segment_flags,131072),0,1)) > 0
/

If you see thousands of segments this can lead to poor query performance. In case you see a few of them, even hundreds, I would keep monitoring further with the same SQL to see if the number of slow segments increases. If case of one of the above or both, go to next steps.

2) Ensure you apply an Oracle Patch for a Bug 12940620 that fixes an issue when cached block/extent counts in SEG$ not updated after adding an extent. Based on Oracle, the Bug is fixed in the following database versions:

12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)
11.2.0.3.7 Database Patch Set Update
11.2.0.3 Bundle Patch 14 for Exadata Database
11.2.0.3 Patch 13 on Windows Platforms
11.2.0.2 Patch 23 on Windows Platforms

Note, that in my case however patching a database to Oracle 11gR2 11.2.0.3.8 did not fix the issue on Itanium / HP-UX platform. I could fix it only with 11.2.0.4.x upgrade.

3) After applying the patch, you can run the following PL/SQL block as sysdba which corrects sizing information on the segments:
begin
for t in (
select distinct tablespace_name
from sys.sys_dba_segs
where bitand(segment_flags,131073) = 1
and segment_type not in ('ROLLBACK', 'DEFERRED ROLLBACK', 'TYPE2 UNDO')
and tablespace_name != 'SYSTEM'
)
loop
dbms_space_admin.tablespace_fix_segment_extblks(t.tablespace_name);
end loop;
end;
/

It’s advisable to take a database backup before the above SQL execution. Additionally I would not run that SQL against SYSTEM tablespace (that’s why SYSTEM is excluded in the SQL) unless it’s recommended by Oracle Support. So that was a few insides in Oracle slow segments issue and the troubleshooting example of slow SQL query performance against dba_segements view on Oracle database 11gR2.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

3 Responses to “Oracle slow SQL query against dba_segments solved”
  1. Marius Chisa says:

    Hi Kirill,

    here is other fix(11.2.0.3):

    SQL> exec dbms_stats.gather_fixed_objects_stats;

    PL/SQL procedure successfully completed.

    Elapsed: 00:08:30.57

    14:59:48 SQL> select
    14:59:57 2 segment_name table_name,
    14:59:57 3 sum(bytes)/(1024*1024) table_size_meg
    14:59:57 4 from
    14:59:57 5 dba_extents
    14:59:57 6 where
    14:59:57 7 segment_type=’TABLE’
    14:59:57 8 and
    14:59:57 9 segment_name = ‘STREAMDATAPRIMITIVE’
    14:59:57 10 group by segment_name;

    TABLE_NAME TABLE_SIZE_MEG
    ——————————————————————————— ————–
    STREAMDATAPRIMITIVE 278

    Elapsed: 00:05:51.70 (close to 6 minutes, stats gathered)

    New session:

    SQL> alter session set “_optimizer_cartesian_enabled” = false;

    Session altered.

    SQL> alter session set “_smm_auto_cost_enabled” = false;

    Session altered.

    SQL> alter session set “_FIX_CONTROL” = “11814428:0”;

    Session altered.

    SQL> set time on timing on
    16:18:56 SQL> select
    16:19:02 2 segment_name table_name,
    16:19:02 3 sum(bytes)/(1024*1024) table_size_meg
    16:19:02 4 from
    16:19:02 5 dba_extents
    16:19:02 6 where
    16:19:02 7 segment_type=’TABLE’
    16:19:02 8 and
    16:19:02 9 segment_name = ‘STREAMDATAPRIMITIVE’
    16:19:02 10 group by segment_name;

    TABLE_NAME
    ——————————————————————————–
    TABLE_SIZE_MEG
    ————–
    STREAMDATAPRIMITIVE
    278

    Regards,
    Marius Chisa

  2. Marius Chisa says:

    please concat with this:

    SQL> set time on timing on
    16:18:56 SQL> select
    16:19:02 2 segment_name table_name,
    16:19:02 3 sum(bytes)/(1024*1024) table_size_meg
    16:19:02 4 from
    16:19:02 5 dba_extents
    16:19:02 6 where
    16:19:02 7 segment_type=’TABLE’
    16:19:02 8 and
    16:19:02 9 segment_name = ‘STREAMDATAPRIMITIVE’
    16:19:02 10 group by segment_name;

    TABLE_NAME
    ——————————————————————————–
    TABLE_SIZE_MEG
    ————–
    STREAMDATAPRIMITIVE
    278

    Elapsed: 00:00:02.96

  3. Kirill Loifman says:

    Interesting… hope it will work. but no environment to test.
    — Kirill

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email