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$

Read more »

How to install Oracle 11gR2 64-bit client on Windows 7

Installation of Oracle 11gR2 64-bit client on Windows 7 is even trickier compared to Oracle 32-bit client installation. In fact after some series of installations, I do recommend installing Oracle client 32-bit on Windows 7 64-bit version, unless you have solid technical restrictions of that. This will avoid various installation issues as well as some client software incompatibility issues. I give however below a short installation plan for Oracle 11gR2 64-bit client with possible issues you can encounter during and after it. Read also my other article for some more tips on that: Oracle 11gR2 client installation on Windows 7 troubleshooting.
Read more »

Oracle 11g RAC database on ASM, ACFS or OCFS2

I see a lot of questions on shared file systems that can be used when people move from single instance to Oracle RAC database and Grid Infrastructure. The most common question is the following: “Should I place archive logs in OCFS2 or ASM or ACFS of 11g?” I’ll try to clarify this topic below.

First we need to understand a separation between Oracle database files and non-database files. Read more »

Oracle ADDM shows Virtual Memory Paging on 10gR2 11gR2

Problem description

I’ve recently notices a common issue on Oracle Enterprise Linux 5.x with Oracle database 10gR2 and 11gR2. The Oracle ADDM Performance Analysis showed the following alert as a top event  in both OEM console – Virtual Memory Paging and in the ADDM report – Significant virtual memory paging was detected on the host operating system.

Problem Analysis

From the first look I thought this was a virtual memory issue on the boxes but on the other hand I could not find any evidence at OS level of any problem with virtual memory paging. Additionally I was surprised that the issue occurred at the same time on different servers and Oracle versions. So, below I did some checks to prove there was no memory issue on these Linux boxes. Read more »

Oracle 11gR2 ASM and ACFS – Cloud File System License

I sense the confusion between Oracle users running Oracle Grid Infrastructure 11gR2 with Automatic Storage Management (ASM) and Automatic Storage Management Cluster File System (ACFS) about the new license policy from Oracle. Oracle introduced a new all in one license called Cloud File System. Since this new Oracle licensing policy is not so straight forward, I’ll try to put some light on the topic. But first let’s see what Oracle Cloud File System itself is. Read more »

Next Page »

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