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

DBMS_XPLAN.DISPLAY_CURSOR,

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)

Dbms_stats.set_column_stats(‘scott’,’emp’,’hiredate’,srec=>sr);

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

Reporting

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.