Wednesday, November 28, 2012

Active Data Guard and UKOUG 2012

I am looking forward to participating again to the UKOUG annual conference (and also to attend the Sunday's OakTable event). This is for me a great opportunity to meet and discuss with many passionate Oracle experts who regularly attend the conference and also to get up-to-date with the latest technology and news from the Oracle community.
My colleague Marcin and I have just finished preparing our presentation on the subject of Active Data Guard. I have enjoyed the work even though, as it is often the case, this has taken quite some effort from both of us to reach the level of details we wanted.
Our goal with this presentation is to share our thoughts and experience on Active Data Guard as we have seen it mature in our production environment in the last 10 months. We will discuss, with examples, what we think are the great points of the technology for our use cases and what we have learned from production deployment. What has worked.. and what has not worked!
If you read this and are going to participate to the UKOUG conference, you are welcome to come to our presentation on December 4th at 15:10 in Hall 5!

Slides can be downloaded from this link.

Wednesday, September 26, 2012

Command-Line DBA Scripts

Topic: A review of the command-line monitoring scripts that I currently use for Oracle.

Note: last updated in June 2015. Download links: and

Command-line is still very useful for Oracle database administration, troubleshooting and performance monitoring. One of the first thins that I do when I want to work with a given Oracle database is to connect as a DBA user with Sql*plus and get information on the active sessions with a script. I will run the script a few times to get an idea of what's happening 'right now' and get a possible starting point for more systematic tuning/troubleshooting if needed.
I liken this approach to examining a few pictures from a remote webcam and trying to find out what's happening out there!.. The method can be powerful, although with some limitations. One of its advantages is that it is easy to implement in Oracle! One of the main source of information for this type of monitoring is GV$SESSION, which is available in Oracle since ages. More recently GV$ACTIVE_SESSION_HISTORY has proven very useful and I quite like the additional improvements introduced there in11gR2.

Examples: One of my favorites is a script I have called top.sql (not very much an original name I know :) which queries gv$session (RAC-aware) and prints out what I currently find the most interesting details (for my environment) on active sessions that I can fit in roughly 200 characters. Another script that does a similar job taking data from v$active_session_history is ash.sql. the scripts ash2.sql, ash3.sql and ash4.sql are the RAC versions for 2, 3 and 4 node RACs respectively.

One of the typical pain points when writing CLI monitoring scripts is deciding which information to display and how to pack it in a relatively short line. I have also found that with each major Oracle versions I want to refresh my scripts with a few changes.

Scripts details: I have packaged some of the scripts that I have written and that I find useful in a zip file which is available at this link (unzip locally and run using sqlplus).  Note that the scripts are tested on 11gR2, many of them will also work on 10g and 12c.

Monitoring Class (Active Session Monitoring)
Script Name Description
top.sqlList with selected details of the active sessions, from gv$session (RAC aware)
top_10g.sqlActive sessions details, from gv$session, 10g version  (RAC aware)
ash.sqlList with selected details of the active sessions, from v$active_session_history (one instance only)
ash_h.sqlUsage @ash_h <n#_sec>. Active sessions details for the last n#_sec seconds, from v$active_session_history (current instance only)
ash2.sql, ash3.sql ash4.sqlActive sessions details, from gv$active_session_history (for RAC 2, 3 and 4 nodes).

SQL Monitoring
Script NameDescription
monitor.sql Display information form gv$sql_monitor for running statements statements. See also monitor_details.sql
monitor_plan.sqlUsage: @monitor_plan <key>. A drill-down script from monitor.sql. Prints details of the execution plan from gv$sql_plan_monitor
Usage: @monitor_report_sid_active <sid>. Generates an active/html sql monior report for the given sid. See also @monitor_report_sid <sid> for the simpler html version.
Usage: @monitor_report_sql_id_active <sql_id>. Generates an active/html sql monior report for the given sql_id. See also @monitor_report_sql_id <sql)id> for the simpler html version.

Drill-down Class
Script Name Description
ash_sess.sqlUsage: @ash_sess <inst_id> <sid> <n#_sec>. Prints details from gv$active_session_history for a given session (RAC aware)
explain.sqlUsage: @explain <sql_id>. Prints detailed explain plan info for given sql_id from library cache (current instance only)
explain_awr.sqlUsage: @explain_awr sql_id. Prints detailed explain plan info for given sql_id from AWR (rac aware)
obj.sqlUsage: @obj <object_id>. Find details for a given object id, or data_object_id
sessinfo.sqlUsage example: @sessinfo sid=<num>. Prints info on sessions that match the search pattern (RAC aware)
sql.sqlUsage: @sql <sql_id>. Prints sql text for given sql_id (RAC aware)

Workload Characterization Class
Script Name Description
sysmetric.sql, sysmetric_details.sqlDisplay values of selected system metrics (RAC aware)
iometric.sql, iometric_details.sqlIO utilization metrics and details (RAC aware)
ehm.sql, ehm_local.sql
ehm_micro, ehm_micro_local
Usage: @ehm 10 "db file sequential read". Event history metric data, helps finding skew in wait times, for example to study random reads latency skew (RAC aware).
ehm_micro -> 12c version, latency drill down at microsecond.
eventmetric.sqlWait event metric details for top wait events (RAC aware)
filemetric.sqlFile metric details for top-usage files (RAC aware)
ash_top.sqlUsage @ash_top <n#_sec>. Aggregates ash data over n#_sec seconds (current instance only)
jobs_running.sqlList running jobs details (RAC aware)
load.sqlDisplays OS-load values (RAC aware)
longops.sql, longops_10g.sql, longops_details.sqlInfo on long running operations from gv$sessoin_longops (RAC aware)
transactions.sqlList of open transactions and crash recovery (RAC aware)

ASH and AWR Reports
Script NameDescription
ash_report.sqlUsage example: @ash_report sysdate-1/24 sysdate Generates an ASH report for the current instance and displays it in a browser.
awr_report.sqlUsage: @awr_report <snap_begin> <snap_end>. Generates an AWR report for the current instance and displays it in a browser.
awr_global_report.sqlUsage: @awr_global_report <snap_begin> <snap_end>. Generates a global AWR report for the current database and displays it in a browser.
awr_snapshots.sqlLists AWR snapshots for the last 24 hours.

Script Name Description
kill.sqlUsage @kill <sid> <serial#> kill the given session (current instance only)
kill_select.sqlUsage example: @kill_select "sql_id='xxxx'". Helps in preparing scripts for killing large number of sessions (current instance only)
locks.sql, locks_10g.sql, locks_details.sqlScripts to help investigating locks and blocking enqueues issues (RACaware)
binds.sqlUsage: @binds <sql_id>. Helps in finding bind values for a given sql_id (RAC aware)
login.sqlSql*plus startup/config script for my environment (windows client version

Command-line scripts are still of great help for monitoring and  performance troubleshooting of Oracle databases. Of particular interest are queries to print the details of active sessions, the SQL they execute and their wait events details. This makes for a quick and easy entry point to troubleshooting and to get an idea of what's happening on the DB server. More systematic troubleshooting may be needed beyond that. Command-line scripts are easy to modify and customize, many DBAs indeed have their own tool-set. A selection of my own DBA scripts can be downloaded from this link.

A few links to end this post: First of all the excellent session snapper and the rest of the collection of scripts by Tanel Poder. In addition a mention to a few cool tools that are a sort of bridge between command-line and graphical-interface approaches: MOATS by Tanel Poder and Adrian Billington , SQL Dashboard by Jagjeet Singh and topaas by Marcin Przepiorowski.

Thursday, August 16, 2012

How to Turn Off Adaptive Cursor Sharing, Cardinality Feedback and Serial Direct Read

Scope: New features and improvements on the SQL (cost-based) execution engine are great. However sometimes the need comes to turn some of those new features off. Maybe it's because of a bug that has appeared or simply because we just want consistency, especially after an upgrade. This is often the case when upgrading an application that has been tuned already with heavy usage of hints for critical SQL.

When this is relevant: using a full set of hints (an outline) often provides consistent SQL execution for a given Oracle version (and set of session/instance parameter values). However this is not guaranteed to be stable across upgrades. The reason is that additional functionality of the cost based optimization engine can play a role. This is indeed the case when moving from 10g to 11g and in particular to 11gR2. It's a good idea to investigate the new features and also to keep a note aside on how to disable them in case of need. The discussion of pros and cons of using hints is a very interesting topic but outside the scope of this entry.

Three 11g features of cost-based optimization and SQL execution to consider when upgrading from 10g:

Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve execution plans for repeated executions. See Support note 1344937.1 and documentation for additional info. You can be disable cardinality feedback with an underscore parameter which can be set at session or instance level. Usual caveats on setting underscore parameters apply:
alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;
This can also be done with a hint, therefore at statement level, using the opt_param syntax: /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */

Adaptive cursor sharing. This was introduced in 11gR1 to address SQL performance consistency issues related to bind variable peeking.  See support note 740052.1 and documentation for details. For the purposes of this doc, if you want to get the 10g behavior with  an Oracle version higher than 10g and overall if you want to disable this feature, you may want to add the hint:
/*+ NO_BIND_AWARE */. 
According to support note 11657468.8 adaptive cursor sharing can be disabled by setting the following 2 parameters (say at session level): _optimizer_adaptive_cursor_sharing = false_optimizer_extended_cursor_sharing_rel = "none"

Serial direct read. This feature, introduced in 11gR2, is about SQL execution and not strictly speaking about cost-based optimization. Serial direct reads allow Oracle to perform full scan very efficiently, using asynchronous I/O if available and also bypassing the the buffer cache, much like what parallel query does, but in serial mode. The decision of when to use or not to use serial direct reads is not taken by the optimizer. It still makes sense to discuss this here as this feature can change the behavior of full scans: by forcing the use of direct read instead of 'normal cache-based access'. There are many interesting details to this feature that are outside the scope of this discussion. To revert to 10g behavior and overall to disable the feature you need to set the following parameter (there is no hint for this unfortunately, as this feature is not directly linked to CBO):
alter session set "_SERIAL_DIRECT_READ"=never;
Note, at the opposite side of the spectrum one can force serial direct reads with: alter session set "_serial_direct_read"=always;

Additional info and comments:

1. When testing Oracle version upgrades and facing regression of SQL, it can be useful to set cost-based optimization to behave as it did in the older version. For example when upgrading from to 11gR2 we can use the following hint on SQL we are testing: SQL hint/*+ OPTIMIZER_FEATURES_ENABLE('') */ or set the parameter OPTIMIZER_FEATURES_ENABLE at session level (or in some cases even at system level).
However as discussed above, setting optimizer_features_enable parameter sometimes is not enough to ensure that a given SQL will execute in exactly the same way in the new system as it did in the older version. In particular related to the 3 examples above, adaptive cursor sharing and cardinality feedback are tuned off by reverting CBO compatibility to a 10g version, although the behavior of serial direct read is not affected by this hint (or any other hint for that matter).

2. How to get a full list of available hints? query V$SQL_HINT in Oracle 11.2 or higher

3a. Example of a logon trigger that can be used to set session-level parameters:

create or replace trigger change_session_parameters
AFTER LOGON ON mydbuser.SCHEMA --customize username, replace "mydbuser" with relevant user name
execute immediate 'alter session set "_serial_direct_read"=never';

3b. Alternative trigger definition

create or replace TRIGGER trace_trig
 sqlstr1 VARCHAR2(100) := 'alter session set "_serial_direct_read"=never';
  IF (USER IN ('USERNAME1', 'USERNAME2')) THEN -- customize user names as needed
    execute immediate sqlstr1;
END trace_trig;

4. Example of how to get the outline (full set of hints) for a given query:

select * from table(dbms_xplan.display_cursor('sql_id',null,'OUTLINE')); -- edit sql_id with actual value

Wednesday, July 18, 2012

Recursive Subquery Factoring, Oracle SQL and Physics

Introduction: this post  is about the use of recursive subquery factoring (recursive common table expressions) in Oracle to find numerical solutions to the equations of classical mechanics, for demonstration purposes. This technique will be introduced using 4 examples or growing complexity. In particular by the end of the post we will be able to compute the length of the (sidereal) year using the laws of physics and SQL.

Recursive subquery factoring (aka recursive common table expressions) is a feature first introduced in Oracle version 11gR2 that has been used by many both for business and for play. See for example this post on Jonathan Lewis blogthis post on solving sudokus and this about the Mandelbrot setThe computational strength of the feature comes from the fact that it makes recursion easily available from within SQL and so opens the door to natural ways of implementing several algorithms for numerical computation. 
Additional note: the amount of physics and maths used have been kept to a minimum and simplified where possible, hopefully without introducing too many mistakes in the process. 

Example 1: projectile in gravity field

The motion is on 1 dimension, say the x axis. Our first task is to find a representation of the state of the system with a structure that can be computed in the database. A natural choice is to use the tuple (t, x, v, a). Where t is time, x is the position along the 1-dimensional axis of motion, v is the velocity and a is the acceleration.
Newton's second law (F=ma) gives us the rule of the game when we want to compute the state of the system subject to and external force. Using calculus this can be written as:  

Next we need a method to find numerical (approximate) solutions. We can use the Euler integration method, which basically means doing the following:

Another important point is that we need to know the initial conditions for the state tuple, in particular initial  position and velocity. In the example we will take x(t=0)=0 m and v(t=0)=100 m/sec. The force is the gravitational pull at the surface of the Earth: F=--m*g, where g=9.81 m/sec^2. Note the mass cancels out in our equations. This example models the motion of a projectile that we shoot vertically into the air and we observe as it rises up to about 500 meters and then falls back down, all this happens in about 20.5 seconds. The SQL used and a graph of the results are here below:

define dT=0.1
define g=9.81
define maxT=20

-- SQL to compute the motion of a projectile subject to gravity
WITH data(t,x,v,a) AS (
 SELECT cast(0 as binary_double) t, cast(0 as binary_double) x, cast (100 as binary_double) v, cast(-&g as binary_double) a FROM dual
 SELECT t+&dT, x+v*&dT, v+a*&dT, -&g FROM data WHERE t < &maxT
SELECT t,x,v,a FROM data;

Example 2: Harmonic oscillator

In this example we investigate the motion of a mass attached to a spring. We expect the mass to oscillate around a central point (x=0).
For greater accuracy in calculations we use a different integration method: the Verlet integration method (see also this link). The equation for the acceleration is: a = F/m =-K*x and initial conditions are: x(t=0)=1 m and v(t=0)=0 m/sec. Moreover we take K0.1 sec^-2See below the SQL used for the calculation and a graph of the results.

define dT=0.1  
define K=0.1
define maxT=20

-- SQL to compute the motion of a harmonic oscillator
WITH data(t,x,v,a) AS (
 SELECT cast(0 as binary_double) t, cast(1 as binary_double) x, cast (0 as binary_double) v, cast(-&K*1 as binary_double) a FROM dual
 SELECT t+&dT, x+v*&dT+0.5*a*&dT*&dT, v+0.5*(a-&K*(x+v*&dT))*&dT, -&K*(x+v*&dT+0.5*a*&dT*&dT) FROM data WHERE t < &maxT
SELECT t,x,v,a FROM data;

Example 3: Motion of the Earth around the Sun

The motion of the system Earth-Sun is a problem of 2 bodies moving in space. With a 'standard trick' this can be reduced to a problem of 1 body, and 2 spatial variables, which represent the (vector) distance of the Earth from the Sun in the place of the orbit. Our description of the system will use the following tuple: (t,x,vx,ax,y,vy,ay), that is time, position, velocity and acceleration for a 2-dimensional problem in the (x,y) plane. The equation for the force is Newton's law of universal gravitationAnother important point again is to use the correct initial conditions. These can be taken from astronomical observations, x(t=0)=152098233 Km (also know as the aphelion point) and v=29.291 Km/sec (credits to this link and this other link). We will use again the Verlet integration method as in example 2 above. Note, for ease of computation, time and space will be re-scaled so that t=1 means 1000 sec and x=1 means 1 Km (same is true for the y axis). The SQL used is pasted here below as well as a graph of the computed results, that is our approximate calculation of the Earth's orbit

-- length unit = 1 km
-- time unit = 1000 sec
define dT=.1
define aph=152098233
define GM=132712838618000000
-- note this is GM Sun + Earth (reduced mass correction)
define v_aph=29291
define maxT=40000

-- SQL to compute the trajectory of the Earth around the Sun
WITH data(step, t,x,y,vx,vy,ax,ay) AS (
 SELECT 0 step,cast(0 as binary_double) t,cast(&aph as binary_double) x,cast(0 as binary_double) y,
        cast(0 as binary_double) vx, cast(&v_aph as binary_double) vy,
        cast(-&GM/power(&aph,2) as binary_double) ax, cast(0 as binary_double) ay  FROM dual
 SELECT step+1, t+&dT, x+vx*&dT+0.5*ax*&dT*&dT, y+vy*&dT+0.5*ay*&dT*&dT,
 FROM data WHERE t < &maxT
SELECT t,x,y,vx,vy,ax,ay FROM data WHERE mod(step,100)=0; -- output only one point every 100

Example 4: Compute the length of the sidereal year using the equations of motion of the Earth around the Sun and SQL

As a final example and an 'application' of the techniques above we can use SQL to compute the number of days year (or rather in a sidereal year, see the link for additional details). We find a result that is in agreement with measurements with 6 significant digits. This is an interesting result considering that it is obtained with just a few lines of SQL!

-- SQL to compute the number of days in one sidereal year
-- A sidereal year is the time taken by the Earth to orbit
-- the Sun once with respect to the fixed stars.
-- This builds on the equation and SQL discussed in example N.3
define dT=.1
define aph=152098233
define GM=132712838618000000
define v_aph=29291
define maxT=40000

select round(t*1000/(3600*24),3) days_in_a_sidereal_year  from (
 WITH data(step, t,x,y,vx,vy,ax,ay) AS (
  SELECT 0 step,cast(0 as binary_double) t,cast(&aph as binary_double) x,cast(0 as binary_double) y,
        cast(0 as binary_double) vx, cast(&v_aph as binary_double) vy,
        cast(-&GM/power(&aph,2) as binary_double) ax, cast(0 as binary_double) ay  FROM dual
  SELECT step+1, t+&dT, x+vx*&dT+0.5*ax*&dT*&dT, y+vy*&dT+0.5*ay*&dT*&dT,
  FROM data WHERE t < &maxT
 SELECT step,t,y,lead(y) over(order by step) next_y FROM data
) where y<0 and next_y>0;



We have discussed in 4 examples the usage of Oracle SQL and in particular of recursive subquery factoring (recursive common table expressions), applied to solve selected cases of differential equations of classical mechanics. Despite the simplifications and approximations involved, the technique has allowed us to compute the length of the sidereal year with good precision. This method can be extended to make calculation for more complex systems, such as systems of many particles.

Wednesday, July 11, 2012

Listener.ora and Oraagent in RAC 11gR2

Topic: An investigation of a few details of the implementation of listeners in 11gR2, including the configuration of listener.ora in RAC and the role of the cluster process 'oraagent'.

11gR2 comes with several important changes and improvements to the clusterware in general and in particular the way listeners are managed. While the listener process is still the 'good old' process  tnslsnr (Linux and Unix), it is now started from the grid home (as opposed to database oracle home). Moreover listeners are divided in two classes: node listeners and scan listeners, although they use the same binary for both functions. There are many more details and instead of covering them here I'd rather reference this excellent review: Markus Michalewicz's presentation at TCOUG.

Oraagent takes good care of our listeners

  • Node listeners and scan listeners are configured at the clusterware level, for example with srvctl and the configuration is propagated to the listeners accordingly
    • this integration is more consistently enforced in 11gR2 than in previous versions
  • The oraagent process spawned by crsd takes care of our listeners in terms of configuration and monitoring
    • note that there is normally a second oraagent on the system which is spawned by ohasd and does not seem to come into play here
  • Notably in the listener.ora file we can see which configuration lines are being taken care of automatically by oraagent, as they are marked with the following comment: # line added by Agent
    • experiment on a test DB: delete one or more of the listener.ora configuration lines and restart the listener (for example with srvctl stop listener; srvctl start listener). The original configuration will reappear in listener.ora and the manually modified listener.ora will be renamed (with a timestamp suffix)
  • The agent creates and maintains the file: endpoints_listener.ora
    • this file is there for backward compatibility, see docs and/or support site for more info. 
    • experiment on test: delete the file and restart the listerner, oraagent will recreate the file.
  • Oraagent log can be found at: $GRID_HOME/log/{nodename}/agent/crsd/oraagent_oracle/oraagent_oracle.log
  • Oraagent monitors the functioning of each listener 
    • from the log file entries (see above about the location of the oraagent log file) we can see that each listener is monitored with a frequency of 60 seconds
  • Oraagent comes into action also at instance startup, when the instance is started with srvctl (as opposed to 'manually' started instance from sqlplus) and sets LOCAL_LISTENER parameter, dynamically (this is done with an alter system command and only if the parameter has not been set on spfile).

    Dynamic listening endpoints 

    • Where are the TCP/IP settings of my listeners in listener.ora? 
      • Only IPC endpoints are listed in listener.ora, this is at first puzzling, where are the TCP settings that in previous versions were listed in listener.ora? on!
    • Notes: 
      • endpoint=the address or connection point to the listener. The most known endpoint in the oracle DB world being TCP, port 1521
      • dynamic listening endpoint and dynamic service registration are both concepts related to listener activities, but they are distinct.
    • Oraagent connects to the listener via IPC and activates the TCP (TCPS, etc) endpoints as specified in the clusterware configuration
      • experiment on test: $GRID_HOME/bin/lsnrctl stop listener; $GRID_HOME/bin/lsnrctl start listener; Note that the latter command starts only the IPC endpoint. However oraagent is posted at listener startup and makes active the rest of the endpoints (notably listening on the TCP  port), this can be seen for example by running the following a few seconds after listener restart: $GRID_HOME/bin/lsnrctl status listener (which will list all the active endpoints)
    • Another way to say that is that the endpoints for the listener in RAC 11gR2 are configured in a  dynamic way: TCP (TCPS, etc) endpoints are activated at runtime by oraagent
      • this is indicated in the listener.ora by the new (undocumented) parameters ENABLE_GLOBAL_DYNAMIC_ENDPOINT_{LISTENER_NAME}=ON
    • Experiment on test on disabling dynamic listening endpoint:  
      • stop the listener: $GRID_HOME/bin/lsnrctl stop listener
      • edit listener.ora and set  ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=OFF
      • start the listener: $GRID_HOME/bin/lsnrctl start listener
      • check listener.ora and check that the parameter edited above has not changed, that is  ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=OFF 
      • in this case the TCP endpoint will not be started, that is the listener will be listening only on IPC. Check with: $GRID_HOME/bin/lsnrctl status listener 
      • note: if we try do the same exercise by stopping and starting the listener with srvctl, as it would be the typical way to do it, we will see that the parameter ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER in listener.ora will be set again to ON. This will activate again dynamic listening endpoints, which is something we want in production, but not for this exercise!
    • Note: the examples have been tested on RAC for Linux. ORACLE_HOME needs to be set to point to the grid home (and TNS_ADMIN if used at all, needs to be set to $GRID_HOME/network/admin)

    I don't need to edit listener.ora any more, or do I?

    • We have seen above that the most important configurations related to listener.ora are automated via oraagent and the clusterware in general
    • There are additional listener.ora parameters that are not managed by the clusterware in11gR2 and need to be configured in listener.ora in case we want to use them
    • The steps for the configuration are very well described in support note 1340831.1. Here we just mention that for a 2-node RAC the following parameters will be needed in listener.ora (note parameters taken from listener.ora on RAC for Linux):
    WALLET_LOCATION =  (SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = ..put here directory of wallet..)))


    The handling of listeners in 11gR2 RAC has become much more integrated with the clusterware and for most RAC configurations there is not much need to play with listener.ora any more. At a closer look of however, new parameters and overall some new rules of the game in the 11gR2 implementation are revealed, most notably the use of dynamic endpoint registration by the clusterware.

    Thursday, June 28, 2012

    Purging Cursors From the Library Cache Using Full_hash_value

    Introduction: Purging cursors from the library cache is a useful technique to keep handy for troubleshooting. Oracle has introduced a procedure call to do that in version 11 with backports to 10g. Besides Oracle documentation, this has been covered by several blogs already (including Kerry Osborne, Harald van Breederode, Martin Widlake, Martin Bach), Oracle support (note 457309.1 for example) and the actual package file in $ORACLE_HOME/rdbms/admin/dbmspool.sql

    Most of the examples and discussions in the links above utilize with the following syntax:

    SQL> exec sys.dbms_shared_pool.purge(‘&address, &hash_value’,'c’)

    What's new in 11.2:

    A new (overloaded) procedure in dbms_shared_pool.purge is available in 11.2 and allows to purge statements identified by thier full_hash_value of the statement. One of the advantages compared to the previous method is that the full_hash_value is a property of a given sql statement and does not depend on the memory address of the (parent) cursor. Note this has been tested in 64 bit for Linux.


    myapp_user_SQL> select /*MYTEST*/ sysdate from dual; -- put test SQL statement that we want to flush in the following
    admin_user_SQL> select a.FULL_HASH_VALUE from V$DB_OBJECT_CACHE a where name='select /*MYTEST*/ sysdate from dual';  
    -- find full_hash_value to be used in the next step
    -- in this example the full_hash_value is 98d0f8fcbddf4095175e36592011cc2c
    admin_user_SQL> exec sys.dbms_shared_pool.purge(HASH=>'98d0f8fcbddf4095175e36592011cc2c',namespace=>0,heaps=>1) 

    Additional info:

    full_hash_value is a 128-bit MD5 hash of the sql statement. More details on full_hash_value at this link.
    A few methods to find full_hash_value given different input are listed here below:
    • find full_hash_value from cache, query v$db_object_cache 
      • select a.FULL_HASH_VALUE from V$DB_OBJECT_CACHE a where name='select /*MYTEST*/ sysdate from dual';
    • find full_hash_value from hash_value
      • select full_hash_value from  v$db_object_cache where hash_value=538037292
    • find full_hash_value from sql_id
      • find hash_value from sql_id using DBMS_UTILITY.SQLID_TO_SQLHASH
      • select  full_hash_value from  v$db_object_cache where hash_value= DBMS_UTILITY.SQLID_TO_SQLHASH('1frjqb4h13m1c');
    • compute full_hash_value from SQL text
    namespace=>0 means 'SQL  AREA' , which is the relevant namespace for cursors.


    We have discussed a method to purge cursors for the library cache that uses the full_hash_value of the cursor instead of the address and hash_value which is the more common approach (and the only one documented in previous versions). This method discussed here is available in 11.2.

    Thursday, June 21, 2012

    Kerberos Authentication and Proxy Users

    Topic: a discussion on how to use Kerberos authentication  and of proxy users in Oracle. This aimed at providing an alternative to the classical use of username and password credential and to help ease the burden of DB credentials management.

    Introduction: Kerberos authentication allows to connect to Oracle without specifying the username/password credentials. The authentication is done externally. Kerberos has been used by a large user community since many years (notably Windows Active Directory uses use Kerberos authentication).
    Proxy authentication in Oracle, allows connection to a target DB username via another DB user (the proxy user). For example you can authorize a user with a development account to connect to the application owner account using his/her credentials (thus no need to expose the application user's password).

    This post is about combining Kerberos and proxy authentication: you can provide a mean to connect to any given DB user via a Kerberos-authenticated user, i.e. without specifying the password at connection time.

    Example of client-side configuration (Linux) 

    • These tests have been performed using Oracle server and client for Linux  
      • See below for details of server-side configuration
    • Create oracle user and grant connection privileges: 
    create user "LUCA@MYDOMAIN.COM" identified externally;
    grant connect to  "LUCA@MYDOMAIN.COM" ;
    ...add tablespace config and other privileges if needed..
    • configure sqlnet.ora for the oracle client. This can be in $ORACLE_HOME/network/admin, $TNS_ADMIN or in $HOME/.sqlnet.ora depending on the scope of the configuration.
    SQLNET.AUTHENTICATION_SERVICES=(ALL)            # easy way to includes KERBEROS5
    SQLNET.KERBEROS5_CONF=/etc/krb5.conf            # need to have kerberos client configured
    SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orasrv  #this needs to match DB server config
    • Request kerberos pricipal for your user if not already present: kinit luca
      • At this point you'll need to put the password on the command line as asked
      • It's also possible to use the oracle utility okinit, in this case for our config we need to run okinit -e 23 luca. 
      • When using db link one need forwardable tickets (kinit -f)
    • Connect to the DB with kerberos credentials. In sqlplus use this syntax sqlplus /@MYDB
      • Show user will show LUCA@MYDOMAIN.COM
      • If you get instead ORA-12638: Credential retrieval failed means you need to troubleshoot. Unfortunately there are many causes for the error, staring with bugs and version incompatibilities (see also notes below).

    Proxy users and Kerberos used together 

    Suppose that we need to authorize the user  to connect to production application. I would normally use: sqlplus myappuser/mysecretpass@MYDB. With Kerberos and proxy authentication, used together, we can do the following:
    • Authorize the user to use proxy authentication: 
      • alter user myappuser grant connect through "LUCA@MYDOMAIN.COM";
    • Check the configuration with select * from PROXY_USERS; 
    • Setup and test Kerberos authentication for MYDB, see steps above 
    • Connect to the production user with this method (Kerberos+proxy user]. 
      • The syntax in sqlplus is: sqlplus [myappuser]/@MYDB 
      • show user from sqlplus will show myappuser as result
      • With this method the password for myappuser has never been used. Only the password for the kerberos user  LUCA@MYDOMAIN.COM has been used when requesting the ticket with kinit
      • One can still connect to myappuser with the orginal username/pass credentials too, this is just an additional path to it

    Notes on client-side configuration (Windows)

    • Kerberos authentication will work with the ticket granted by the domain at users logon
      • okinit, oklist etc are missing from instant client but are also not needed to make this work
      • do not use client if possible. 
      • you need to logon as domain user
      • in alternative if you want to use this from a local account or usea different kerberos user, just run cmd.exe with run as and specify a domain user's credentials 
      • check with klist that you have the ticket for the principal "LUCA" in this example
    • Create or copy over krb5.conf from a Linux machine (for example from DB server)
    • Edit sqlnet.ora in %ORACLE_HOME%\network\admin (or %TNS_ADMIN%) adding the following:
    SQLNET.KERBEROS5_CONF= ....put path here....\krb5.conf
    SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orasrv # match value on server
    SQLNET.KERBEROS5_CC_NAME=OSMSFT:    # only needed in Windows
    • connect to the DB with kerberos credentials
      • same as the case of Linux above. 
      • In sqlplus use this syntax sqlplus /@MYDB 
    • This has been tested with a Windows 7 client and Oracle instant client 32 bit

    Notes on DB server configuration for kerberos authentication

    This is described in Oracle documentation so I'll just put a few notes here.
    • Ask the kerberos administrator to add a new service principal. Best to use a short name, for example orasrv, as used in the examples above
    • Create the keytab file and on each DB server node(s) with correct permissions for the oracle user
      • for CERN installations the tool cern-get-keytab can be used:
      • sudo cern-get-keytab --service orasrv -k /etc/krb5.keytab.orasrv
      • sudo chown oracle:dba /etc/krb5.keytab.orasrv
    • Configure vi $ORACLE_HOME/network/admin/sqlnet.ora and add on all nodes 
      • note there are a few differences with client-only configuration discussed above
      • for RAC: edit sqlnet.ora in $ORACLE_HOME/network/admin, as opposed to $CRS_HOME/network/admin 
    SQLNET.KERBEROS5_KEYTAB=/etc/krb5.keytab.orasrv # see keytab generation step above


    Setting up Kerberos authentication requires just a few steps and is often quite simple. However there are many possible sources of issues that may need to be troubleshooted for a new installation. That ranges from bug in certain Oracle versions, incompatibilities with encryption levels, etc. I have tested with Oracle client both on Linux and Windows 7 again Oracle server server on (Linux 64 bit). This support note can be a useful starting point for troubleshooting if needed: Master Note For Kerberos Authentication [ID 1375853.1]
    A (quite) different technique also aiming at reducing the need of hard-coded credentials is Secure External Password Store. This is described in oracle documentation and in the following support note: Using The Secure External Password Store [ID 340559.1]
    Note added on licensing: a recent update to the Oracle licensing information states that "strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database."


    We have discussed how to set up Kerberos authentication for Oracle users both as a standalone method and in conjunction with Proxy Authentication. These techniques can be of help to reduce the need of hard-coded credentials when accessing Oracle database services.

    Monday, June 11, 2012

    Hash Collisions in Oracle: SQL Signature and SQL_ID

    Topic: Discussion and implementation of a method for finding hash collisions for sql_id and SQL signature in Oracle.


    MD5 hashing is used by Oracle to compute sql_id and SQL signature (see also a previous blog post on the topic). Those hash values are normally used as if they were a unique representation of a given SQL statement. However collisions (2 different statements with the same hash value) can happen or, as it is the case of this post, will happen!

    The underlying math is a short discussion of the birthday attack: Let's say you walk into a room an meet 30 people: shake hand with the first person you see and also disclose each other's the birthday. There is about 1 chance out 365 that that particular person has the same birthday as you. If you repeat this process with the rest of the 30 people in the room, chances of a 'positive event' increase but still stay below 10%. However when all the people in the room repeat the procedure of shaking hands and sharing birthday details, probabilities add up quickly (there are N(N-1)/2 different handshakes possible in a room of N people). The non-intuitive result is that it is likely that at least 2 people share a common birthday already in such a small group. See the link for the details.

    The main point is that to find collisions by brute force on a hash function of n bits we don't need to generate O(2^n) hash values but rather the square root of that O(2^n/2). In particular this means that the complexity of finding hash collisions for 32-bit and 64-bit hash functions by brute force are down to numbers that are easily computable with current HW. sql_id and signatures are indeed 64-bit hashes, as discussed here, so let's give it a try!

    Warm-up, collisions on hash_value:

    The hash_value of a a given SQL statement (as shown in V$SQL.hash_value for example) is a 32-bit hash. Collisions can be found using about 100K random hashes. A simple algorithm using Oracle and PL/SQL is:
    • Take a couple of different SQL statements and append to them a randomly-generated string
    • Compute the MD5 hash and take the number of bytes that are needed (4 bytes in this case)
    • Insert the hashes in a table
    • Repeat ~100K times
    • When finished look in the output table for duplicates in the hash value
    • If duplicates are found, those are the hash_value collisions
    The code can be found at this link. The execution takes just a few seconds and displays the collisions that have been found (results varies for each execution, normally 2-5 collisions are found for this size of search).

    SQL> @hash_birthday_sql hashtab 10 4
    SQL> @find_dup hashtab

    -- these 2 statements are different but have the same hash_value (=3577689134)
    SQL> select owner, index_name from dba_indexes where rownum<=9 --CTUUewnYYPJXFuFyAzcMtXTVNmPtNgAV;
    SQL> select owner, table_name from dba_tables where rownum<=10 --ooXUUTNfekKkRZGlCJfiPlxKcoCfpDAh;

    Scaling up to 64-bit hash: sql_id collisions

    The same algorithm used for hash_value collisions can be used for sql_id, although we need to scale up the calculation: the hash is 8 byte long now and we need ~10 billion random hashes to find a few meaningful collisions. SQL> @hash_birthday_sql hashtab 500000 8 would take about 60 hours to do such calculation (mileage may vary). A faster way is to split the execution in smaller chunks and parallelize  the execution across multiple CPU and possibly RAC nodes. See the link here for an example on 2 RAC nodes and parallelism 10 each.

    Results: the following two SQL statements are different but have the same sql_id (ayr58apvbz37z) and hash_value (1992264959).

    SQL> select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib;
    SQL> select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq;

    --confirm this by running:
    SQL> select sql_text from v$sql where sql_id='ayr58apvbz37z';

    Another 64 bit hash, SQL signature collisions

    A variation from the theme above is to calculate statements that have a SQL signature collision. The signature is used by SQL profiles, baselines and SQL patches. It is calculated in a similar but different way than sql_id, in particular SQL text is normalized before hashing (see this link for additional details). The base script is hash_birthday_signature.sql again parallel computation can be used to speed up the computation.

    Results: the following two SQL statements are different but have the same signature (6284201623763853025), note sql_id values are different.


    --confirm this by running:
    select sql_text from v$sql where force_matching_signature=6284201623763853025;

    How does Oracle cope with collisions

    In a few tests I have seen dbms_xplan throwing ORA-01422 when displaying plans for sql_id with collisions. There may be other packages/tools that make the assumption that sql_id values don't have collisions and they may throw errors.
    These are a few more problems with SQL signature collisions. In particular a source of potential trouble is that one of the main (and common) underlying tables for  SQL plan management (baselines, profiles, patches) which stores the mappings of sql_text to signature, SYS.SQL$TEXT, has a unique index on signature. However we have seen that it is quite unlikely to have a signature collision just by pure chance (i.e. in the normal usage of the DB).


    This post reports on an exercise to compute collisions for sql_id and SQL signatures of Oracle statements using the Oracle database and PL/SQL. We have seen how some simple math, called birthday attack, can be of help and how an implementation of this in Oracle is quite easy up to hash length of 64 bit. We also report on the findings, notably collisions of sql_id and SQL signature.

    Additional comments: a collision on full_hash_value (as reported in v$db_object_cache) could not be calculated with this method as it is a 128 bit hash and the brute force method would require resources out of a 'reasonable scale' . However, since 2005 there are methods known to find collisions for MD5 hashes on the full 128 bits that use weaknesses in the algorithm (that's why MD5 should not be used for security purposes any more). Those methods could be used to find full_hash_value collision.

    PS: SQL and scripts mentioned in this article can be found following this link

    Tuesday, June 5, 2012

    SQL Signature, Text Normalization and MD5 Hash

    Topic: A discussion on how Oracle computes SQL signature using MD5 hashing and on how SQL text is normalized before computing SQL signatures.

    Introduction and warm-up:

    SQL statements that are cached in the library cache are associated to a hash value, which is visible in various forms across a few V$ views. In 11gR2 for example: V$DB_OBJECT_CACHE.FULL_HASH_VALUE, V$SQL.SQL_ID, V$SQL.HASH_VALUE.

    From the work of Tanel, Marcin and Slavik we learn that the full hash is an md5 hash in hexadecimal, sql_id is a base-32 representation of the trailing 8 bytes of the same md5 hash and finally the good old hash_value is a decimal representation of the trailing 4 bytes of the md5 hash.
    Here below an example to illustrate how one can easily reproduce Oracle's hash values from md5 calculations (see also the blog entries mentioned above for more details):

    --11gR2 hash from library cache, in 10g use x$kglob.kglnahsv  
    SQL> select hash_value, to_char(hash_value,'xxxxxxxx') hex_hash_value, full_hash_value from GV$DB_OBJECT_CACHE where name='select 1 from dual';

    ----------- -------------- --------------------------------
    2866845384  aae096c8       7d4dc9b423f0bcfb510272edaae096c8

    The same  value can be calculated with md5_with_chr0.sql, a simple script based on DBMS_OBFUSCATION_TOOLKIT.MD5. Note chr(0) is added to the sql text before hashing.

    SQL> @md5_with_chr0 'select 1 from dual'

    7d4dc9b423f0bcfb510272edaae096c8 --> same value as full_hash_value above

    SQL signature calculation and SQL text normalization

    SQL signature is another type of hashing for SQL statements used by Oracle to support the features of plan management with SQL profiles, SQL patches, SQL plan baselines.

    1) SQL statements are 'normalized'. Oracle performs transformations to the text before calculating the signature. Among others extra spaces are removed and case is ignored (see also oracle doc on V$SQL). An example here below to illustrate this: 2 SQL statements that differ only on case and additional whitespaces are shown to have the same SQL signature. Spoiler: normalization of more complex statements has some additional surprises, see below.

    SQL> select DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('select  1  from   dual') signature_with_space, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 1 FROM DUAL') signature_normalized_sql from dual;

    -------------------- ------------------------
    12518811395313535686     12518811395313535686

    2) Force matching is an additional option/feature of SQL signatures. It is used to match the text of SQL that uses literals. Oracle performs an additional transformation, that is literals are transformed into system-generated names for bind variables before hashing. An example below using literals (the value 1). The signature is converted in hexadecimal in this example, as we will use that for the following.

    select to_char(DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 1 FROM DUAL'),'xxxxxxxxxxxxxxxx') exact_match_signature_hex,
           to_char(DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 1 FROM DUAL',force_match=>1),'xxxxxxxxxxxxxxxx') force_match_signature_hex from dual;

    ------------------------- -------------------------
     adbbc0a2f3c68ac6         9289f992520d5a86

    3) Computing SQL signatures directly with MD5. The script md5.sql  computes md5 hashes of the input (note it does not add a trailing chr(0)). We can reproduce the values obtained before and therefore confirm what is the transformation that Oracle does when calculating signatures.

    SQL> @md5 'SELECT 1 FROM DUAL'

    d9f6df623a086c51adbbc0a2f3c68ac6  -> see exact_match in (2)above

    SQL> @md5 'SELECT :"SYS_B_0" FROM DUAL'

    a995b29240a442869289f992520d5a86  -> see force_match in (2) above

    4) Additional investigations: when we have lists of columns or tables Oracle transforms ",", in " , " so actually in this case whitespaces may be added to the original statement and not removed as we have seen in point (1) above! An example here below:

    SQL> select to_char(DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT ID,ID FROM DUAL,DUAL',force_match=>1),'xxxxxxxxxxxxxxxx') force_match_signature_hex from dual;





    The signature of SQL statements is calculated by Oracle using MD5 on the normalized SQL text. Normalization steps include removing extra white-space characters, converting the text to upper case. Additional normalization steps concern comma-separated list of objects: the item separator is  transformed into ' , ' (space, comma, space).
    The MD5 hash calculated on the normalized SQL text is truncated to the last 8 bytes and is displayed in V$ views as a decimal number (SQL signature).
    In the case of force matching signatures literals are additionally replaced with:"SYS_B_<n>" (where <n> is a decimal number, see example above), very much like the case when using cursor_sharing=force.
    Note: a trailing chr(0) is added by Oracle when calculating hash_values/sql_id but not when calculating SQL signatures.

    md5.sql and md5_with_chr0.sql scripts can be found at this link.

    Monday, May 21, 2012

    SQL Patch and Force Match

    Discussion on how to create sql_patch with force_match=true in Oracle 11g and related topics.

    SQL patches have recently saved the day for me in a production issue where a given SQL had suddenly changed execution plan causing IO overload (a full scan was done instead of index-based read for a high-load statement). 11g allows for a quick fix in such situations (as in, stop the fire and buy time to find a more stable solution): a set of hints can be added to a given query via the use of SQL Patch.
    The official Oracle documentation has not many details on the topic (up to Oracle's optimizer blog and Dominic Brooks have very good posts on the topic though.
    I add here a few additional details that I have researched and found useful:

    1) Recap (from the references listed above): how to create a SQL_PATCH from command line 

      (sql_text  => '..put SQL here..',
       hint_text => '..put hints here..',
       name      => 'my patch name'); 

    2) What if I have a sql_id instead of the sql_text?

    SQL> var c clob
    SQL> exec select sql_fulltext into :c from v$sqlstats where sql_id='...' and rownum=1;

      (sql_text  => :c,
       hint_text => '..put hints here..',
       name      => 'my patch name'); 

    3) What if I want to create a SQL patch with FORCE_MATCH = TRUE? (force match will additionally target all SQL statements that have the same text after normalizing literal values to bind variables). 

    -- create sql patch
    -- allows to set FORCE_MATCH to TRUE which is currently not possible with dbms_sqldiag_internal.i_create_patch (
    -- run as sys
    -- this is undocumented stuff, handle with care

        sql_text clob            := '....put sql here...';
        hints    varchar2(1000)  :='....put hints here...';
        description varchar2(100):='my patch description';
        name varchar2(100)       :='my patch name';
        output   varchar2(100);
        sqlpro_attr SYS.SQLPROF_ATTR;
        sqlpro_attr := SYS.SQLPROF_ATTR(hints);
          SQL_TEXT => sql_text,
          NAME => name,       DESCRIPTION => description,     
          CATEGORY => 'DEFAULT',
          CREATOR => 'SYS',
          VALIDATE => TRUE,
          TYPE => 'PATCH',
          FORCE_MATCH => TRUE,
          IS_PATCH => TRUE);

    4) Additional considerations:
    What should I put as hints in the SQL PATCH? I would normally use the full outline content from an execution plan that I have tested and found OK. Mileage may vary. To print out the outline one can use:
    select * from table(dbms_xplan.display('PLAN_TABLE',null,'OUTLINE'));

    Drop and enable/disable SQL patches with: DBMS_SQLDIAG.DROP_SQL_PATCH and DBMS_SQLDIAG.ALTER_SQL_PATCH

    Diagnostics:  dba_sql_patches. That view is based on tables shared with sql profiles and sql baselines. Underlying tables of interest for baselines, profiles and sql patches are: sys.sqlobj$data sys.sqlobj$auxdata, sys.sql$text, sys.sqlobj$
    V$SQL.SQL_PATCH when not null reports the name of the sql patch that has been used to parse that particular child cursor.