My upcoming webinar on 11gR2 clusterware upgrade

Hi everyone,

A very quick update about my upcoming webinar on 9th Feb 2012 titled 'Upgrading to 11gR2 - a case study' where I will be sharing all my experience, tips and techniques of a successful 11gR2 upgrade project. The upgrade project gave us an opportunity to work with 4 cluster environments with nearly 400 databases, a complex environment.It was indeed a roller coaster journey. Be there to feel the real experience.

Glad to know that over 260 people have enrolled so far for the session and expected to be more in the coming days. If you have any upgrade plans in place or doubts over 11gR2 upgrade proceedings, do not let this opportunity go away, enroll yourself now here

Look forward seeing you (virtually)  people at the presentation. I would like to thank you James Murtagh, Marketing Manager Red Gate Oracle Tools for arranging this webinar.

Best Regards,

Jaffar

An easy way to obtain 10053 trace

I have just learnt a couple of new methods to obtain an Optimizer (10053) trace for any sql statement that is already in the cache, without actually executing it and also enabling 'events infrastructure'. With 11gR2 onwards, you can either use the DBMS_SQLDIAG.DUMP_TRACE package procedure or use new diagnostic events infrastructure to obtain the 10053 trace for a given sql_id.


Demonstration 1:


1. Find out the sql_id for the given statement:


SELECT sql_id,child_number FROM v$sql WHERE sql_text LIKE '%SQL TEXT%';


2. Input the sql_id to the DBMS_SQLDIAG.DUMP_TRACE package procedure:


execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'sql_id',  p_child_number=>0, 
p_component=>'Compiler',
p_file_id=>'ABCD');


Trace file with ABCD post fix can be found under $ORACLE_HOME/diag/rdbms/dbname/instance_name/trace directory.


Demonstration 2: (excerpt from MOS Note)

SQL> alter session set max_dump_file_size = unlimited;
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';

SQL> --Execute the query --

To disable the trace

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';





Refer to MOS ML: 
How to Obtain Tracing of Optimizer Computations (EVENT 10053) [ID 225598.1]