Understand database buffer cache usage

Posted by admin under Tuning, Oracle

As you know, the buffer cache is an “expensive” database resource. While the amount of RAM available in servers is getting larger and larger with every generation, so is the requirements for realtime transactions, caching more data, etc…

So, in terms of caching data, RAM could still be a bottleneck for your database.  We all want to avoid physical I/O. We need to make sure appropriate database segments (indexes, tables) are cached in memory.

We can set the Oracle automatic SGA tuning parameters  (or do manual memory sizing), but how do we know which objects are using the buffer cache? Which objects are getting cached? Simple!

To help us better understand which objects are cached in the database buffer cache, we can use the following query:

SELECT owner, object_name, object_type type, round((COUNT(*)*8)/1024) MB_SIZE
From V$bh
Join All_Objects On Object_Id = Objd
Group By Owner, Object_Name, Object_Type
ORDER BY MB_SIZE DESC;

The MB_SIZE column calculates the amount of blocks resident in the buffer cache X block size (it’s 8K here, but change according to your environment) / 1024 to get the output in megabytes.

Update! An even better query that will display results from the keep cache as well as the default cache. Remember to change * in the top query to the block size of your database.

Select Object_Name  As Parttion_Name, round(sum(Blocks)*8/1024) as MBYTES
from
(
Select Decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') Subcache,
bh.object_name,bh.subname, bh.blocks
From X$kcbwds Ds,X$kcbwbpd Pd,(Select /*+ use_hash(x) */ Set_Ds,
o.name object_name, o.subname, count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
And X.State !=0 And O.Owner# !=0
group by set_ds,o.name, o.subname) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
And Pd.Bp_Size != 0
and ds.addr=bh.set_ds
)
group by object_name
order by MBYTES desc

Expdp fails with ORA-01427

Posted by David Yahalom under Bug, Oracle

Well, here’s an interesting tidbit when using expdp I’ve noticed today. While doing a very simple export of a single schema on a 11.2.0.1 database I got the following error message:

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-01427: single-row subquery returns more than one row

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 8159

—– PL/SQL Call Stack —–
object line object
handle number name
0×7cf93e860 19028 package body SYS.KUPW$WORKER
0×7cf93e860 8191 package body SYS.KUPW$WORKER
0×7cf93e860 12728 package body SYS.KUPW$WORKER
0×7cf93e860 2425 package body SYS.KUPW$WORKER
0×7cf93e860 8846 package body SYS.KUPW$WORKER
0×7cf93e860 1651 package body SYS.KUPW$WORKER
0×7ef8a4448 2 anonymous block

Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-01427: single-row subquery returns more than one row

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 8159

This was generated by a very regular export command:

 expdp system/<PASSWORD> DIRECTORY=orabackup_nfs DUMPFILE=xxxx.DMP LOGFILE=xxxx.LOG SCHEMAS=xxxx

As it turns out, there’s a published Oracle bug on this which affects Oracle 11.2.0.1 - 11.2.0.2:

DataPump Export (expdp) Returns ORA-1427 When Partitioned IOT With Same Name Exists in More Than One Schema [ID 1064840.1]

As it turns out, the cause is if a partitioned IOT with same name exists in two different schemas. There’s any easy way to find these duplicate partitioend IOTs by running the following query as SYSDBA (change schema name to the schema you are trying to export):

select a.owner||'.'||a.table_name table1,
b.owner||'.'||b.table_name table2
from     dba_tables a,
dba_tables b
where   a.owner = 'TEST1' and
b.owner != 'TEST1' and
a.iot_type = 'IOT' and
b.iot_type = a.iot_type and
b.table_name = a.table_name and
exists (select 1
from   dba_part_tables
where  owner = a.owner and
table_name = a.table_name);

The solution provided is to either drop the IOT from one of the schemas (if possible of course), install the fix for bug 9214753 or change the KU$_IOTPART_DATA_VIEW view to a new version provided with the note in Metalink.

How to trace an Oracle session from another session

Posted by David Yahalom under Oracle

DBMS_MONITOR is a package which allows tracing of session activity.
Using DBMS_MONITOR you can start a trace of any session in the database – from whatever session you are connected to. It doesn’t even have to be the session you want to trace.

So, for example, if you have session A running and you are connected from session B, you can trace session A to diagnose its activity from session B using the DBMS_MONITOR package.

DBMS_MONITOR requests the database S.P to generate the trace files required. These trace files contain information about queries and query stats including wait events and even bind variable information.

The syntax is:

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id, wait info, bind variables);

For example:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE (40,105,true,true);

This will start a trace for a specific session identified with sid value of 40 and serial# value of 105 and will generate trace files which include wait events and bind variable information.

Stopping the trace once the information is collected is simple and ca be done by running:

exec DBMS_MONITOR.SESSION_TRACE_DISABLE(40,105);

Following the creation of the raw dump files, we can use the tkprof  utility to format the trace file output. Tracing a session can generate very large trace files if left in its enabled state or for a session doing a lot of activity – this can fill the user dump directory pretty fast. 

Increasing DML speed and throughput

Posted by David Yahalom under Oracle

1. Parallelism of the insert operation - invoke parallel DML (using the “PARALLEL” hint) to perform concurrent inserts on the same table.

Be advised that in order to avoid contention on the segment header, multiple freelists are required, or using ASSM which generates bitmap-based freelists.

In addition to INSERT, the PARALLEL hint also applies to - SELECT, MERGE, UPDATE, and DELETE statements.

2. Use the “APPEND” hint - causes the optimizer to use direct-path insert.

• Conventional INSERT is the default in serial mode. In serial mode, direct path can be used only if you include the APPEND hint.

• Direct-path INSERT is the default in parallel mode. In parallel mode, conventional insert can be used only if you specify the NOAPPEND hint.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT. Basically this tells Oracle to “grab” free blocks for the inserted data ABOVE the table HWM.

A major benefit of direct-load INSERT is that you can load data without logging redo or undo entries, which improves the insert performance significantly.

3. Use a larger block size - By using larger block sizes (such as 16K or 32K) you can reduce the amount of I/O as more rows fit into a single block before a “full block” condition is reached and causes the block to get unlisted from the Freelist (which is a latch that during multiple concurrent inserts can slow the database).

4. Disable indexes - It’s faster, considerably, to rebuild indexes after a bulk-data load all at once compared to having the indexes update after each insert statement.

5. Use Reverse key indexes - can help reduce insert contention on sequence-generated primary keys as data will spread across multiple blocks.

Temporary tablespace usage in Oracle

Posted by David Yahalom under Oracle

Temp is used for sorting - which, by itself, is performed automatically “the in background” for various operations such as creating an index, using order by or group by. In addition, using hash joins uses temp space for the hash tables created by the process.

 The session will begin by sorting data in memory and if exceeds a certain threshold (note – not necessarily exceeding the amount of memory available), Oracle will break the operation into smaller “chunks” and will write partial results to the temporary tablespace. This will result in temp space usage.

The behavior described above is controlled by the workarea_size_policy parameter. When set to AUTO, the pga_aggregate_target parameter will instruct Oracle how much memory can be used by all sessions for sort activities. Oracle will automatically determine how much of this memory each individual session can use.

 When the workarea_size_policy parameter is set to manual on the other hand, parameters such as sort_area_size, hash_area_size, and bitmap_merge_area_size define the amount of memory a session can use for various sort related operations.

 It’s also important to note that a single SQL statement can perform multiple sorts.

 When temp blocks which belong to a sort are no longer required, these blocks are marked as such and will be re-allocated when a new sort is started.

When there’s not enough free space in the temporary tabelsapce to meet the sort requirements, the sort operation will fail. This can occur when there are no unused blocks for the sort segments (too many parallel sorts running) or no space available in the temporary tablespace as a whole.

 When this happens Oracle will raise the “ORA-1652: unable to extend temp segment” error. Please note that not a lot of information is provided in addition to the ORA-1652 error. So further diagnostic of such errors requires some additional work.

Diagnosing temp space usage in Oracle can be done utilizing the internal Oracle diagnostic mechanism to give us information about ORA-1652 errors by generating trace files. These traces will contain all the required information for diagnostic including the SQL statement text. There will be overhead to the system, but should be minimal under most conditions as the trace file will only be written when an error occurs.

When an ORA-1652 error occurs, a trace file will be created in the udump directory.

 Tracing ORA-1652 errors can either be done at the session or instance level:

 ALTER SESSION SET EVENTS ‘1652 trace name errorstack’;

 OR

 ALTER SYSTEM SET EVENTS ‘1652 trace name errorstack’;

 Also, monitoring the temporary tablespace in realtime can be done using the following query:

SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Sort segment usage divided by session:

 SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

Sort segment usage divided by statement:

SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

What will happen to ASM when the disk path changes in Linux?

Posted by David Yahalom under Storage, ASM, RAC, Oracle

Have you even wondered what happens if disk device names and/or major-minor numbers changes at the operating system level? Will this cause any problems for ASM as it tires to access the drives even when the drive path has changed?

Well, no ! This will cause absolutely no problem what so ever to ASM. In fact, its one of ASMs best features.

You see, ASM only cares about the LOGICAL disk names and not the PHYSICAL drive paths or numbers. Changing the drive paths or major-minor numbers in the O/S is no problem for ASM. This is because ASM scans the disks based on what is defined in asm_diskstring - so during boot, all drives that have a valid ASM header will be automatically added to ASM.

These devices are opened with system calls (like fopen, etc.) so paths and/or major numbers are not even used.

The disk paths, names and major-minor numbers are not persistently recorded in any of the ASM metadata. Each ASM disk has a disk header which contains the disk name and diskgroup stamped in it.

If ASM discovery finds the required number of disks for a given diskgroup then it will be able to mount the diskgroup. The ASM metadata header will contain the fixed and persistent logical disk name (for example - ORCL_DATA_DISK1). On boot, ASM will scan all disks for one with the above name in its header.

Once it finds that disk it will make the connection between the current (again, not persistent) disk path and the logical disk name.

The relation between an Oracle instance and memory in Windows

Posted by David Yahalom under Windows, Hardware, Oracle

The Oracle instance memory allocation works differently in Windows then it does in UNIX-Linux like operating systems.

In Windows environments, on starting up the Oracle instance all global memory pages are committed (like the buffer cache, redo log buffer and the library cache). However, and this is an important difference compared to POSIX operating systems, only a small number of these memory pages are actually cached in memory upon instance startup causing most of the SGA not to be part of  Oracle’s active working set.

This forces Oracle to compete on equal grounds with other processes. The O/S might swap certain pages from Oracle’s working set in memory to disk during periods of increased activity or when other processes are paging more and compete for RAM. Windows pages according to activity using an MRU-like mechanism, so when paging is going-on system-wide, Windows will try to reserve RAM pages to the most active process, the one that is paging the most.

If Windows decides to swap the database instance to disk, this will cause a severe performance degradation for the Oracle Database and might even grind the instance to a complete halt.

There are two registry parameters that exist and allow us to manipulate the working set of the Oracle process. These are:

• ORA_WORKINGSETMIN or ORA_%SID%_WORKINGSETMIN:
  Minimum working set for the ORACLE.EXE process (units = MB)
• ORA_WORKINGSETMAX or ORA_%SID%_WORKINGSETMAX:
  Maximum working set for the ORACLE.EXE process (units = MB)

You can set these parameters under:

• For single ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE
• For multiple ORACLE_HOME installations: HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEx (for multiple homes)

ORA_WORKINGSETMIN can be used to prevent the pages allocated to the Oracle process from dropping below the defined threshold (in MB) until the instance is shutdown.

The biggest benefit of setting these parameters will be in an environment where Oracle coexists with other applications. Although it can be beneficial in other scenraios as well, such as when production and test instances are running on the same physical machine.

Another very useful parameter that exist in the Win32/64 platform is: PRE_PAGE_SGA. This parameter causes Oracle will force Oracle to allocate all SGA pages upon instance startup thus bringing all of them to memory as the working set of the Oracle executable.

This will allow the instance to reach maximum performance more quickly rather than through an incremental build up as pages are loaded on a need-to basis.

Combining ORA_WORKINGSETMIN  with PRE_PAGE_SGA will force the Oracle instance to start above the minimum threshold and not drop below. Using ORA_WORKINGSETMIN in isolation will cause the Oracle working set rises above the threshold it will not drop below.

Blog has been renamed!

Posted by David Yahalom under General IT

As you probably noticed I’ve changed my blog’s name and URL to www.davidyahalom.com instead of www.authoritybase.com. After doing some thinking I’ve decided that it is more appropriate for my blog at its current stage to reflect my name instead of using something more original. You can still access it by typing www.authoritybase.com, but all new articles and views will be published under the www.davidyahalom.com domain.

I’ve also created a new logo for my blog, one that better reflects the database related content on this site.

Cloud Oracle Storage: how to make ASM even better, the NAS way.

Posted by David Yahalom under ASM, RAC, Oracle

No one can argue about the merits of Oracle ASM. Having a transparent, easy to use and high performance LVM built using the same fundamentals of the Oracle database is a great technology for Oracle DBAs. ASM is a relatively new technology that have gained popularity fast. I’m using ASM on a daily basis and I’m sure many of  you do as well. But it could be even better.

Imagine how wonderful it would be if Oracle took ASM even furthur and made ASM an enterprise-wide network-based unified database storage solution, allowing many remote Oracle DB Instances access a unified “cloud like” storage layer via the network.

Currently using ASM, we need to start an ASM instance for every physical server running an Oracle DB instance. Why not have ASM act as a network-based LVM as well? Let us be able to start an ASM instance on one server having all other Oracle databases, even those running on remote  machines, access that ASM instance over the network.

That would be like exposing our storage as a NAS solution - no matter the vendor.

The benefits this technology would have over regular NAS (Network Attached Storage - such as using NFS mounts for storing Oracle Data Files)  would be having a unified storage deployment strategy for our organization and potentially enjoying database-specific performance benefits that are not available with generic NAS.

It’s no secret that I’m a big fan of deploying Oracle over NFS using fast networks (which can even perform better than fiber), the problem with NFS is that it isn’t database-centric in its roots. First, you need support at both the storage level and the client level: you need to run a NetAPP filler (NFS on EMC isn’t as good) and have Linux as the operating system for optimal performance. Second, tuning NAS for Oracle, while doable, isn’t easy. Oracle made great progress in 11g with the DirectNFS client but it is a Liunx/NETAPP only solution.

Most organizations have a mix of Linux/Unix/Windows servers with both EMC and Netapp equipment in the storage layer. Using Network ASM could really make the storage layer transparent from the database prospective. Think of it as Cloud Storage for Oracle Databases. All databases would have a single, redundant, POC for storage. Your Oracle Instance could access both EMC and NetAPP machines easily and transparently over the network. All the benefits of regular ASM would apply here as well - being able to add disks, remove disks, strip and load-balance on the fly.

COS - Cloud-computing Oracle Storage or N-ASM - Netowrked ASM.

Remember where you heard it first!

ORA-600: Oracle process has no purpose in life!

Posted by David Yahalom under Oracle

Yes, that’s an actual error. Or more precisely: ORA-600 [12235] “Oracle process has no purpose in life !”.

Actually, This error shows up when Oracle detects a defunct Oracle process. You see, when an Oracle server process starts it reads certain data from the SGA that sets various parameters for that process (and defines its existence so to speak) .
If the process does not locate any valid customization data, it will report back with this error.

The funniest Oracle ORA error I’ve ever came across.

You can read about this in metalink note 33174.1.

Btw, an easy way to recreate this would be to type “oracle” at the O/S prompt. Try it for yourself.