RMOUG – Graham Wood 25+ years at Oracle

Graham’s presentation on the “Hidden” Features of the Oracle Database:

Since, these presentations aren’t generally published, I took notes, not guaranteed to be correct.

Graham Wood Hidden Features

Free Features

dbms_monitor.session_trace_enable  10g

plan_stat   11g argument added to dbms_monitor.session_trace_enable 

dump plan when cursor is closed

now, after the first execution, never, all_executions, they dump row source statistics

cursor mapping broken 11gr1, statement text printed on every execute

long cursor numbers 11gR2, not so human readable

TKPROF enhancements

                Prints sql ID and plan hash for each statement

                Reports First, Avg, Max from multiple row source dunmps

                But not CLOSE … DOH!

Enable trace foe individual statements

                Alter session set events ‘sql_trace [sql:…]

Getting sql info V$SQLstat (10g), doesn’t use any latches

                Using V$SQL can make latch problems worse


EXPLAIN_PLAN doesn’t work correctly, so use DBMS_XPLAN

Select * from table (dbms_xplan.display_cursor (“atnoianf82398nhfa’, NULL, ‘ADVANCED’));

Select * from table (dbms_xplan.display_cursor (format=>’ALLSTATS LAST’))

Use the CARDINALITY HINT to check the actual plan

Select /*+ gather_plan_statistics +/

Set a parameter for running an individual statement

SELECT /* + OPT_PARAM(‘star_transformation’….

_optimizer_ignore_hints=TRUE, useful for testing

Optimizer Statistics

Pending Statistics

                Separates stats collection from stats publishing

                Capture stats as pending

                Test using pending stats

                Publish once verified

Controlled by parameter – optimizer_use_pending_statistics

                Determines if the optimizer will use pending stats

Set to FALSE by default in 11g

                Use dbms_stats.set_*_stats

                Very useful for min max values

Setting Statistics

Dbms_stats.datearray(to_date(‘01/01/1999’, ‘MM/DD/YYYY)’, sysdate)




Gather incremental stats in 11g uses “synopsis”. Make sure to clear all the stats and use one pass to create the “SYNOPSIS”.

LISTAGG – Analytic Query

First_value, nth value, last value

External Tables

External Table Preprocessor

Stop use SQL Loader – user External tables  – parallel loads with a degree of parallelism

                The use of a “preprocessor” allows for uncompressing zip files

                External tables allow us to filter the data before loading the data.


Sqlplus –prelim / as sysdba   gives access to in memory structures with no SQL access, no latches or SQL allowed.

Aren’t Free

AWR captures top 5

dbms_workload_repository.modify_snapshot_settings (topsql=>200)

dbms_workload_repository.add_colored_sql(‘sql_id’) to capture marked SQL, this marks the SQL



AWR SQL report

AWR Diff Report

AWR RAC report

ASH report any time period, instance or filtered, filtering at session level is like SQL trace

                Use ADDM report!

Check Cardinality using SQL Monitor, key to work out why the execution plan may not be optimal.

TruTek Consulting is “Key” to Upgrade Success

115102 Upgrade Client in PA going Live on R1213 – Utility company

“I think all of TruTek’s assistance and especially the upgrade class were key to making this project successful.” Dan 2-15-12

115102 Upgrade Client in CA going live on R1213 – Research Organization

“This latest upgrade test on the new production hardware went very well.  I don’t see any problem getting the  upgrade done over a 3 day weekend.  I have you guys to thank for that.”  Terry 1-31-12

Migrating to OATM

Don’t migrate CTXSYS schema to the  APPS_TS_TX_DATA tablespace. This will prevent you from using Cross Platform Transportable Tablespaces on 11i with 10gR2,  MOS Note 454574.1
Leave CTXSYS in it’s own tablespace as a default database schema.

The Oracle Applications Tablespace Model (OATM) introduces a new consolidated tablespace model in Oracle Applications, that uses 12 locally managed tablespaces for all products, including the temporary tablespace, system tablespace, and undo segments. In this revised Oracle Applications Tablespace Model, each Oracle Applications database object is mapped to a tablespace based on its Input/Output characteristics. This new model allows for easier maintenance, reduces space usage, and allows for potential run-time performance gains.
In this two day class we will:
1- Review OATM Model for Oracle Applications 11i
2- Install OATM migration utility
3- Run OATM migration utility
4- Review the parameters needed while starting OATM utility
5- Examine the OATM Menu Structure
6- Perform Additional Post Migration Steps
Combine this class with the “Database Migration from 32 bit to 64 bit, using DataPump and Transportable Tablespaces” class for 5 days of training.


Slow TNS connections due to Network issue

Using OEL and 11g, an old / invalid DNS ip address in the first line of resolv.conf adds 60 seconds to each connection, causes very slow LOCAL=NO connections. The ip address in resolv.conf are checked from the top down. If the first IP Address is BAD, it waits 60 seconds to timeout. During the EBS 1211 MP it adds about 35 hours with 125K jobs, for a total of 53 hours. It runs in 18 hours using the correct DNS server in resolv.conf. It happens that the DB and AS are on the same server and should logically use a BEQUETH connection to eliminate the network performance issue.

The other option is to turn off DNS in the /etc/nsswitch.conf.