Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Archive for the ‘Performance tuning’ Category

Dude, where is my redo?

Posted by Riyaj Shamsudeen on June 12, 2013

This blog entry is to discuss a method to identify the objects inducing higher amount of redo. First,we will establish that redo size increased sharply and then identify the objects generating more redo. Unfortunately, redo size is not tracked at a segment level. However, you can make an educated guess using ‘db block changes’ statistics. But, you must use logminer utility to identify the objects generating more redo scientifically.

Detecting redo size increase

AWR tables (require Diagnostics license) can be accessed to identify the redo size increase. Following query spools the daily rate of redo size. You can easily open the output file redosize.lst in an Excel spreadsheet and graph the data to visualize the redo size change. Use pipe symbol as the delimiter while opening the file in excel spreadsheet.

spool redosize.lst
REM  You need Diagnostic Pack licence to execute this query!
REM  Author: Riyaj Shamsudeen
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT  sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time ,  startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY  startup_time, sysst.instance_number
                ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
EXTRACT (DAY    FROM (end_interval_time-begin_interval_time))*24*60*60+
            EXTRACT (HOUR   FROM (end_interval_time-begin_interval_time))*60*60+
            EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+
            EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA
  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number, 
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt 
, sum(stat_value) redo1
from redo_sz
group by  instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') 
order by instance_number, 2
/
spool off

Visualizing the data will help you to quickly identify any pattern anomalies in redo generation. Here is an example graph created from the excel spreadsheet and see that redo size increased recently.

screenshot_redo
Read the rest of this entry »

Posted in 11g, Oracle database internals, Performance tuning, RAC | Tagged: , , , , | 21 Comments »

DOUG presentation on dbms_xplan

Posted by Riyaj Shamsudeen on October 22, 2012

Please join us at the DOUG (DALLAS ORACLE USERS GROUP) Oracle Database Forum meeting on Thursday, October 25, 2012 from 5 pm – 7 pm.
Presented by Riyaj Shamsudeen, OraInternals, & Sahil Thapar:

“Out with the old way, Enter dbms_xplan: A Swiss army knife for performance engineers”

Rough outline:
(i) Ability to query access path from memory, AWR repository
(ii) Ability to use cardinality feedback method to understand access plan issues. Few tips from a real world experience will be provided too.
(iii) Ability to understand issues with database links etc.
(iv) Options such as ADVANCED, ALLSTATS etc
(v) Why should you choose dbmx_xplan over tkprof+sql_trace combination?
(vi) Disadvantages of dbms_xplan and a quick introduction to dbms_monitor.

Refreshments sponsored by me :)

Update: Uploading the presentation pdf files. Enjoy :)

dbms_xplan

Posted in Performance tuning, Presentations | Tagged: , , , , | 5 Comments »

Open World 2012 – My Sunday presentation on truss, pstack etc.

Posted by Riyaj Shamsudeen on August 18, 2012

Just a quick note, I will be presenting on “Truss, pstack, pmap, and more” talking about advanced UNIX utilities and how it can be utilized to understand inner working of an application or even Oracle Database Engine.

My timeslot is between 2:15 and 3:15 in Room 2016.

http://blogs.ioug.org/2012/08/15/ioug-at-oracle-openworld-2012-the-sunday-technical-sessions-9302012/

Uploading presentation files. Thanks for attending at OOW12.
pstack_truss_etc

Posted in Oracle database internals, Performance tuning | Tagged: , , , | 1 Comment »

June 2012: Jonathan Lewis is coming to Dallas

Posted by Riyaj Shamsudeen on June 15, 2012

Quick note about Jonathan Lewis trip to Dallas: Jonathan Lewis will be presenting two day seminar on two topics, “Beating the Oracle Optimizer” (June 28) and “Troubleshooting and tuning” (June 29th).

The event will be held June 28-29, 2012 at SMU-in-Legacy in Plano, TX.

This is a must-attend event for experienced DBAs and Developers. Especially, if you are planning to upgrade your database/application in the near-future or if you are in the middle of an upgrade, you must attend these two seminars. This seminar series provide enormous value resolving complex Production performance issues.

Click Here for details.

Posted in Performance tuning, Presentations | Tagged: , | Leave a Comment »

Reverse Path Filtering and RAC

Posted by Riyaj Shamsudeen on June 1, 2012

This is a quick note about reverse path filtering and impact of that feature to RAC. I encountered an interesting problem recently with a client and it is worth blogging about it, with a strong hope that it might help one of you in the future.

Problem

Environment is 11.2.0.2 GI, Linux 5.6. In a 3 node cluster, Grid Infrastructure (GI) comes up cleanly in just one node, but never comes up in other nodes. If we shutdown GI in first node, we can start the GI in second node with no issues. Meaning, GI can be up in just one node at any time.

System Admins indicated that there are no major changes, only few bug fixes. Seemingly, problem started after those bug fixes. But there were few other changes to the environment /init.ora parameter change etc. So, the problem was not immediately attributable to just OS changes.

Read the rest of this entry »

Posted in Oracle database internals, Performance tuning, RAC | Tagged: , , , , , , | 11 Comments »

All about RAC and MTU with a video

Posted by Riyaj Shamsudeen on May 22, 2012

Let’s first discuss how RAC traffic works before continuing. Environment for the discussion is: 2 node cluster with 8K database block size, UDP protocol is used for cache fusion. (BTW, UDP and RDS protocols are supported in UNIX platform; whereas Windows uses TCP protocol).

UDP protocol, fragmentation, and assembly

UDP Protocol is an higher level protocol stack, and it is implemented over IP Protocol ( UDP/IP). Cache Fusion uses UDP protocol to send packets over the wire (Exadata uses RDS protocol though).

MTU defines the Maximum Transfer Unit of an IP packet. Let us consider an example of MTU set to 1500 in a network interface. One 8K block transfer can not be performed with just one IP packet  as the IP packet size (1500 bytes) is less than 8K. So, one transfer of UDP packet of 8K size is fragmented to 6 IP packets and sent over the wire. In the receiving side, those 6 packets are reassembled to create one UDP buffer of size 8K. After the assembly, that UDP buffer is delivered to an UDP port of a UNIX process. Usually, a foreground process will listen on that port to receive the UDP buffer.

Read the rest of this entry »

Posted in 11g, Oracle database internals, Performance tuning, Presentations, RAC, video | Tagged: , , , , , , , , , , , , , , , , , , | 11 Comments »

_gc_fusion_compression

Posted by Riyaj Shamsudeen on April 29, 2012

We know that database blocks are transferred between the nodes through the interconnect, aka cache fusion traffic. Common misconception is that packet transfer size is always database block size for block transfer (Of course, messages are smaller in size). That’s not entirely true. There is an optimization in the cache fusion code to reduce the packet size (and so reduces the bits transferred over the private network). Don’t confuse this note with Jumbo frames and MTU size, this note is independent of MTU setting.

In a nutshell, if free space in a block exceeds a threshold (_gc_fusion_compression) then instead of sending the whole block, LMS sends a smaller packet, reducing private network traffic bits. Let me give an example to illustrate my point. Let’s say that the database block size is 8192 and a block to be transferred is a recently NEWed block, say, with 4000 bytes of free space. Transfer of this block over the interconnect from one node to another node in the cluster will result in a packet size of ~4200 bytes. Transfer of bytes representing free space can be avoided completely, just a symbolic notation of free space begin offset and free space end offset is good enough to reconstruct the block in the receiving side without any loss of data.This optimization makes sense as there is no need to clog the network unnecessarily.

Read the rest of this entry »

Posted in 11g, Oracle database internals, Performance tuning, RAC | Tagged: , , , | 9 Comments »

My COLLABORATE 12-IOUG sessions

Posted by Riyaj Shamsudeen on April 19, 2012

If you are attending Collaborate 2012, you might be interested in my content-rich sessions below :

Session Number: 326
Session Title: SCAN, VIP, HAIP, and other RAC acronyms
Session Date/Time/Room: Tue, Apr 24, 2012 (10:45 AM – 11:45 AM) : Surf C

Session Number: 327
Session Title: Internals and Performance Boot Camp: Truss, pstack, pmap, and more
Session Date/Time/Room: Wed, Apr 25, 2012 (03:00 PM – 04:00 PM) : Palm A

Hope to see you there!

Update: I am uploading presentation files. Presentations are much more recent than the document :-)

pstack_truss_etc
2012_327_Riyaj_pstack_truss_doc
SCAN_VIP_HAIP_etc
2012_326_Riyaj_scan_vip_haip_doc

Thanks for attending!

Posted in Oracle database internals, Performance tuning, Presentations, RAC | Tagged: , , , , , , , , , , , , | 1 Comment »

gc buffer busy acquire vs release

Posted by Riyaj Shamsudeen on April 19, 2012

Last week (March 2012), I was conducting Advanced RAC Training online. During the class, I was recreating a ‘gc buffer busy’ waits to explain the concepts and methods to troubleshoot the issue.

Definitions

Let’s define these events first. Event ‘gc buffer busy’ event means that a session is trying to access a buffer,but there is an open request for Global cache lock for that block already, and so, the session must wait for the GC lock request to complete before proceeding. This wait is instrumented as ‘gc buffer busy’ event.

From 11g onwards, this wait event is split in to ‘gc buffer busy acquire’ and ‘gc buffer busy release’. An attendee asked me to show the differentiation between these two wait events. Fortunately, we had a problem with LGWR writes and we were able to inspect the waits with much clarity during the class.

Remember that Global cache enqueues are considered to be owned by an instance. From 11g onwards, gc buffer busy event differentiated between two cases:

  1. If existing GC open request originated from the local instance, then current session will wait for ‘gc buffer busy acquire’. Essentially, current process is waiting for another process in the local instance to acquire GC lock, on behalf of the local instance. Once GC lock is acquired, current process can access that buffer without additional GC processing (if the lock is acquired in a compatible mode).
  2. If existing GC open request originated from a remote instance, then current session will wait for ‘gc buffer busy release’ event. In this case session is waiting for another remote session (hence another instance) to release the GC lock, so that local instance can acquire buffer.

Example

Following output should show the differentiation with much clarity.

Read the rest of this entry »

Posted in 11g, Oracle database internals, Performance tuning, RAC | Tagged: , , , , | 11 Comments »

Temporary tablespaces in RAC

Posted by Riyaj Shamsudeen on February 13, 2012

Temporary tablespaces are shared objects and they are associated to an user or whole database (using default temporary tablespace). So, in RAC, temporary tablespaces are shared between the instances. Many temporary tablespaces can be created in a database, but all of those temporary tablespaces are shared between the instances. Hence, temporary tablespaces must be allocated in shared storage or ASM. We will explore the space allocation in temporary tablespace in RAC, in this blog entry.

In contrast, UNDO tablespaces are owned by an instance and all transactions from that instance is exclusively allocated in that UNDO tablespace. Remember that other instances can read blocks from remote undo tablespace, and so, undo tablespaces also must be allocated from shared storage or ASM.

Space allocation in TEMP tablespace

TEMP tablespaces are divided in to extents (In 11.2, extent size is 1M, not sure whether the size of an extent is controllable or not). These extent maps are cached in local SGA, essentially, soft reserving those extents for the use of sessions connecting to that instance. But, note that, extents in a temporary tablespace are not cached at instance startup, instead instance caches the extents as the need arises. We will explore this with a small example:

Read the rest of this entry »

Posted in 11g, Oracle database internals, Performance tuning, RAC | Tagged: , , , , , , , | 15 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 214 other followers