May 30

Hanganalyze using oradebug for identifying blocking sessions

SQL> oradebug setmypid
SQL> oradebug dump hanganalyze 3
SQL> oradebug tracefile_name

Adaptive Cursor Sharing:  I was always belived that Adapative Cusror Sharing will happen only if on skewed column with histgrams.    How ever, in certain cases with bind values can create child cursors, even if histograms are not existing.   ACS maked the cursor a “Bind Sensitive’ and created a new child.

see bug:  8357294

Oracle 11gR2 documentation.

May 29

Oracle 11gR2 new feature ‘Database Smart Flash Cache’ is an extention to the buffer cache using faster flash memory cards.  When a block is flushed out of  SGA will be placed in the smart flash cache.  When a block get updated, the dirty block will be written to the disk and a copy will be palced in the smart flash cache and subsequent read will be from the flash.    Currently Smart Flash Cache (SFC) support only read operations which totaly different from Exadata Smart Flash Cache which support both read and write.  This feature is available only in Oracle Linux and Oracle Solaris OS.There are 2 new parameters introduced in 11gR2 to support this new feature.     They are



DB_FLASH_CACHE_FILE=+dg1/lfile                 —  in case of ASM

DB_FLASH_CACHE_SIZE=100GB                       —  Size of SFC

Since the meta data information about SFC (header information) is stored in the shared pool, that much space should be allocated extar to the shared pool.  See the Oracle white paper and documentation for more details

May 27

LAG and LEAD functions useful to get values from previous and next row values without a self  join.  See the examples from,

Below is an example from DBA_HIST_EVENT_HISTOGRAM

set linesize 200
col sntime for a25
select substr( snaptime,1,10) sntime ,sum(dela_wait)
from (select sn.snap_id,to_char(BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI:SS') snaptime,
lag(WAIT_COUNT) over (order by sn.SNAP_ID) as snap_wait,
(WAIT_COUNT -lag(WAIT_COUNT) over (order by sn.SNAP_ID)) dela_wait
from DBA_HIST_EVENT_HISTOGRAM eh ,dba_hist_snapshot sn
where event_name='&eventname'
and eh.SNAP_ID=sn.SNAP_ID
and WAIT_TIME_MILLI=&waitmill
order by sn.SNAP_ID)
group by substr( snaptime,1,10);

==> ASH for an SQL

set linesize 170
col event for a40
col stime for a15
col PROGRAM for a30
select INST_ID,to_char(sample_time,'dd-Mon hh24:MI') Stime, SQL_CHILD_NUMBER SQLChild,event, current_obj#,TIME_WAITED ,SESSION_ID,p1,p2
from gv$active_session_history
where sql_id ='&SQLID' and TIME_WAITED > 0
order by sample_time;

V$SYSSTAT event – daily sum

col start_time for a15
col end_time for a15
set pagesize 50
set linesize 150
col STAT_NAME for a30
select substr(start_time,1,11),sum(delta_value)
lag(value) over (order by n.snap_id) as Pre_value, VALUE, (value - lag(value) over (order by n.snap_id) ) as delta_value
from dba_hist_sysstat st,dba_hist_snapshot n
st.SNAP_ID=n.SNAP_ID and st.INSTANCE_NUMBER = &inst_num
order by n.SNAP_ID)
group by substr(start_time,1,11);

May 16

Database replay testing (RAT – Real Application Testing)

SQL> create user thomas identified by thomas;

User created.

SQL> grant connect,resource to thomas;

Grant succeeded.

SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='THOMAS';

------------------------------ ------------------------------
SQL> alter user system identified by manager;

User altered.

SQL> conn system/manager
$ mkdir rat
$ cd rat
$ pwd

$ $ORACLE_HOME/OPatch/opatch lsinventory -details|grep "Oracle Real Application Testing"

Oracle Real Application Testing
Oracle Real Application Testing Patch

==> A non-zero value indicates that RAT is enabled.  Here the value is 1

$ $ cd $ORACLE_HOME/rdbms/lib
$ ar -t libknlopt.a | grep -c kecwr.o

SQL> l
 1* select object_name, object_type, status from dba_objects where object_name like '%WORKLOAD_CAPTURE'
SQL> /

----------------------------- ------------------- -------

==> create directory ratdir as '/oracledata1/oradata/rat';

SQL> create directory ratdir as '/oracledata1/oradata/rat';

Directory created.

===> remembers to put 'dir' is capital letters

SQL> exec dbms_workload_capture.start_capture(name => 'capture', dir => 'RATDIR');

PL/SQL procedure successfully completed.
$ cd /oracledata1/oradata/rat
$ ls -ltr
total 6
drwxr-xr-x 12 oracle oinstall 1024 May 16 03:32 inst1
-rw-r--r-- 1 oracle oinstall 0 May 16 03:32 wcr_cap_00001.start
drwxr-xr-x 12 oracle oinstall 1024 May 16 03:32 inst2
-rw-r--r-- 1 oracle oinstall 100 May 16 03:32 wcr_scapture.wmd

SQL> create table test as
SELECT LEVEL v1,'Test string '|| level v2, level+level v3,level*2 v4
FROM dual
CONNECT BY LEVEL <= 1000000; 2 3 4

Table created.

SQL> delete from test;

1000000 rows deleted.

SQL> rollback;

Rollback complete.

sqlplus / as sysdba

SQL> exec dbms_workload_capture.finish_capture();

PL/SQL procedure successfully completed.

SQL> select id, name, status, start_time, end_time, connects, user_calls, dir_path from dba_workload_captures ;
SQL> select id from dba_workload_captures ;

SQL > set pagesize 0 long 30000000 longchunksize 2000
SQL > select ( 1,'TEXT') from dual;

=====> starting reply - on the same DB using same DIR ===



PL/SQL procedure successfully completed.

SQL> exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name => 'replay', replay_dir => 'RATDIR');

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

$ wrc userid=system password=manager replaydir=/oracledata1/oradata/rat

Workload Replay Client: Release - Production on Thu May 16 05:09:50 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (05:09:50)

===session will waits here ==

PL/SQL procedure successfully completed.
wrc session starts and ends the replay=====

Replay started (05:16:26)
Replay finished (05:45:52)

SQL> select id, name, status from dba_workload_replays;

---------- -------------------------- ----------------------------------------
 1 replay COMPLETED

SQL> exit
SQL> set pagesize 0 long 30000000 longchunksize 2000
SQL> spool replay.txt
SQL> select => 1,format => 'TEXT') from dual;

DOC: 1083063.1

==> Oracle RAT documentation for 11gR2

==>  Flashback archive oracle docs – total recall 11gR1

==> Step by step RAT

May 14

==> ITL waits on indexes from AWR snaps where count is more than 20

set linesize 150
set pagesize 100
col object_name for a35
select INSTANCE_NUMBER,snap_id,object_name,owner ,ITL_WAITS_DELTA
from dba_objects obj, dba_hist_seg_stat seg
where obj.object_id = seg.obj# and object_type ='INDEX' and owner not in ('SYS','SYSTEM') and ITL_WAITS_DELTA > 20
order by object_name,owner,snap_id;

==> BIND_AWARE hint can be used to hint the optimizer to use bind aware with out monitoring the query, on wards.  See the example.

==> Some times bind peeking and BIND_AWARE unnecessarily cause child cursor issue with shared pool latches.  Use NO_BIND_AWARE hint to turn of bind peeking for specific SQLs.  See the below example.

==> Hinting a packaged application SQLs.  To apply a hint for a packaged application use the PL/SQL package DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH.  See the below example.  Unfortunately we can not use SQL_ID, rather should use sql text directly.


A nice blog on Soft/ Hard parses.

====> Configuring and monitoring Exadata Smart Flash Log ==

Exadata Smart Flash Log provides high performance, low latency, reliable temporary  storage for Redo log data.

To implement Smart Flash Log, the versions must be minimum or above

–> Exadata storage server software :

–> Oracle database version : with PSU 11

To configure, first disable Flashcache to free up the flash storage and create Flash Log.

CellCLI> drop flashcache

CellCLI> create flashlog all

CellCLI> create flashcache all

To see current Flash Cache Log configuration

CellCLI> list flashlog detail

Default Smart Flash log size will be 512M and  can be changed by

CellCLI> create flashlog all size= 1G

You can configure Flash Log only in certain number of Flash disks

CellCLI> create flashlog all celldisk=’FD_07_ct01cel07,FD_14_ct01cel07′  size=256M

=====> Redo write performance histogram

set linesize 160
col event for a25
col LAST_UPDATE_TIME for a40
set pagesize 100
select * from v$event_histogram where event='log file parallel write';

set linesize 160
col event for a25
col LAST_UPDATE_TIME for a40
set pagesize 100
select * from gv$event_histogram where event='log file parallel write' order by inst_id,WAIT_TIME_MILLI;


11gR2 new feature Auto DOP.  Configuration parameters are



1) Auto DOP

2) Parallel Statement Queuing

3) In Memory Parallel execution


1) Auto DOP

PARALLEL_MIN_TIME_THRESHOLD =  5 seconds ==> means an SQL will run in parallel if the estimated run time is more than 5 seconds.

==> All about CF (Clustering Factor) with a bug fix.

Key definition of  CF is  Clustering Factor is calculated by performing a full index scan and looking at the rowid of each index entry .  If the table block being referenced differs from that of the previous index entry, the CF is incremented.

Bug: 13262857 fixed in on wards.   But this bug fix also considers ‘n’ number of cached blocks for calculating the CF.  We can define the value N using “statistics collection preferences” on a table as below.

SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'TEST',pname=>'TABLE_CACHED_BLOCKS', pvalue=><number>)

How ever, by setting a high value for pvalue  (for TABLE_CACHED_BLOCKS), can impact negatively on CF.  To prevent this, Oracle put a hard limit of 255 for TABLE_CACHED_BLOCKS pvalue.  Read more

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: