Search

Thursday, June 23, 2016

Oracle slow SQL query against dba_segments solved

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.

Posted by at